public static object GetProfilePoints(double txLat, double txLon, double rxLat, double rxLon)
{
/*
* Test data to paste into excel:
*
* start -33.83953500 151.20694600
* end -33.87644513 151.22115513
*
* =GetProfilePoints(B1,C1,B2,C2)
*
*/
if (diffLossCalc == null)
{
diffLossCalc = new DiffractionLossCalculator();
}
GlobalCoordinates start = new GlobalCoordinates(new Angle(txLat), new Angle(txLon));
GlobalCoordinates end = new GlobalCoordinates(new Angle(rxLat), new Angle(rxLon));
var points = diffLossCalc.GenerateIntermediateProfilePoints(start, end);
var pointsArray = new object[points.Count, 3];
for (int i = 0; i < points.Count; i++)
{
pointsArray[i, 0] = points[i].coordinate.Latitude.Degrees;
pointsArray[i, 1] = points[i].coordinate.Longitude.Degrees;
pointsArray[i, 2] = points[i].height;
}
return(ArrayResizer.Resize(pointsArray));
}
// Makes an array, but automatically resizes the result
public static object dnaMakeArrayAndResize(int rows, int columns, string unused, string unusedtoo)
{
object[,] result = ExampleFunctions.dnaMakeArray(rows, columns);
return(ArrayResizer.dnaResize(result));
// Can also call Resize via Excel - so if the Resize add-in is not part of this code, it should still work
// (though calling direct is better for large arrays - it prevents extra marshaling).
// return XlCall.Excel(XlCall.xlUDF, "Resize", result);
}
// ReSharper disable UnusedMember.Global
public static object Query(
// ReSharper restore UnusedMember.Global
[ExcelArgument("Alias of the connection, this value can be received by calling Open.")] string alias,
[ExcelArgument("Name of the function to be called or string to be evaluated within q process.")] object
query,
[ExcelArgument("First parameter to the function call (optional).")] object p1 = null,
[ExcelArgument("Second parameter to the function call (optional).")] object p2 = null,
[ExcelArgument("Third parameter to the function call (optional).")] object p3 = null,
[ExcelArgument("Fourth parameter to the function call (optional).")] object p4 = null,
[ExcelArgument("Fifth parameter to the function call (optional).")] object p5 = null,
[ExcelArgument("Sixth parameter to the function call (optional).")] object p6 = null,
[ExcelArgument("Seventh parameter to the function call (optional).")] object p7 = null,
[ExcelArgument("Eighth parameter to the function call (optional).")] object p8 = null)
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(ExcelEmpty.Value);
}
try
{
var key = alias + query;
object[,] r;
if (MemoryCache.Default.Contains(key))
{
r = MemoryCache.Default[key] as object[, ];
}
else
{
var result = _qXL.qQuery(alias, query, p1, p2, p3, p4, p5, p6, p7, p8);
if (result == null)
{
return(query); //null gets returned only when function definition has been sent to q.
}
if (!(result is object[, ]))
{
return(result);
}
r = result as object[, ];
MemoryCache.Default.Add(key, r, DateTimeOffset.Now.AddSeconds(30));
}
return(ArrayResizer.ResizeCached(r, key));
}
catch (IOException io)
{
//this normally means that the process has been terminated on the receiving site
// so clear the connection alias.
return("ERR: " + io.Message);
}
catch (Exception e)
{
return("ERR: " + e.Message);
}
}
public static object GetRadialTerrainProfile([ExcelArgument(Description = "Valid Inputs - any value between -90 and 90")] string lattitude,
[ExcelArgument(Description = "Valid Inputs - any value between -180 and 180")] string longitude,
[ExcelArgument(Description = "Valid Inputs - any value > than 0 Note: Units in metres")] double siteRadius,
[ExcelArgument(Description = "Valid Inputs - any value > than 0 Note: Units in metres")] double stepLength,
[ExcelArgument(Description = "Valid Inputs - any value between 0 and 180")] double stepAngle)
{
try
{
if (ExcelDnaUtil.IsInFunctionWizard())
{
return("Finish inputting parameters");
}
// Generate and retrieve all the required data for the user inputted site
Site testsite = new Site(lattitude, longitude, "google");
testsite.GenerateRadial(siteRadius, stepLength, stepAngle);
int rows = (testsite.Count * testsite[0].Count) + 1;
int columns = 6;
object[,] result = new object[rows, columns];
int rowCounter = 1;
//Initilise the headers
result[0, 0] = "Angle Profile makes to horizontal i.e. to E-W";
result[0, 1] = "Lattitude";
result[0, 2] = "Longitude";
result[0, 3] = "X - Cartesian coordinate relative to site position";
result[0, 4] = "Y - Cartesian coordinate relative to site position";
result[0, 5] = "Elevation above sea level (m)";
foreach (ElevationProfileBase profile in testsite)
{
foreach (Position position in profile)
{
result[rowCounter, 0] = position.theta;
result[rowCounter, 1] = position.latitude;
result[rowCounter, 2] = position.longitude;
result[rowCounter, 3] = position.Xoffset;
result[rowCounter, 4] = position.Yoffset;
result[rowCounter, 5] = (double.IsNaN(position.elevation)) ? "NaN" : position.elevation.ToString();
rowCounter++;
}
}
return(ArrayResizer.Resize(result));
}
catch (Exception ex)
{
return(ex.Message);
}
}
public static object NextPoint(double lat, double lon, double az, double distance)
{
GeodeticCalculator geoCalc = new GeodeticCalculator();
Ellipsoid reference = Ellipsoid.WGS84;
GlobalCoordinates start = new GlobalCoordinates(new Angle(lat), new Angle(lon));
Angle azimuth = new Angle(az);
Angle endBearing;
GlobalCoordinates dest = geoCalc.CalculateEndingGlobalCoordinates(reference, start, azimuth, distance, out endBearing);
var point = new object[1, 2];
point[0, 0] = dest.Latitude.Degrees;
point[0, 1] = dest.Longitude.Degrees;
return(ArrayResizer.Resize(point));
}
public static object[,] GetHistoricalDataFromYahoo(
[ExcelArgument("Yahoo Ticker")] string ticker,
[ExcelArgument("From Date")] DateTime fromDate,
[ExcelArgument("To Date")] DateTime toDate)
{
var begin = fromDate;
var end = toDate;
var yahooURL =
@"http://ichart.finance.yahoo.com/table.csv?s=" +
ticker + @"&a=" + (begin.Month - 1).ToString(CultureInfo.InvariantCulture) + @"&b=" + begin.Day.ToString(CultureInfo.InvariantCulture) +
@"&c=" + begin.Year.ToString(CultureInfo.InvariantCulture) + @"&d=" + (end.Month - 1).ToString(CultureInfo.InvariantCulture) + @"&e=" + end.Day.ToString(CultureInfo.InvariantCulture) + @"&f=" + end.Year.ToString(CultureInfo.InvariantCulture) +
@"&g=d&ignore=.csv";
// string historicalData;
// var webConnection = new WebClient();
// try
// {
// historicalData = webConnection.DownloadString(yahooURL);
// }
// catch (WebException ex)
// {
// throw new Exception("Unable to download the data! Check your Internet Connection!", ex);
// }
// finally
// {
// webConnection.Dispose();
// }
//
// historicalData = historicalData.Replace("\r", string.Empty);
// var rows = historicalData.Split('\n');
// var headings = rows[0].Split(',');
var rnd = new Random();
var excelData = new object[10, 5];
for (var i = 0; i < 5; ++i)
{
excelData[0, i] = i.ToWords();
}
for (var i = 1; i < 10; ++i)
{
for (var j = 0; j < 5; ++j)
{
excelData[i, j] = rnd.Next(1, 10000);
}
}
// for (var i = 1; i < rows.Length; ++i)
// {
// var thisRow = rows[i].Split(',');
// if (thisRow.Length == headings.Length)
// {
// excelData[i, 0] = DateTime.Parse(thisRow[0]);
// for (var j = 1; j < headings.Length; ++j)
// {
// excelData[i, j] = double.Parse(thisRow[j]);
// }
// }
// }
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
ExcelAsyncUtil.QueueAsMacro(() =>
{
ArrayResizer.Resize(excelData, caller);
});
return(excelData);
}
