/// <summary>Gets the Excel range output for a specific array.
/// </summary>
/// <typeparam name="T">The type of the values.</typeparam>
/// <param name="values">The collection of values.</param>
/// <param name="header">An optional header.</param>
/// <param name="rangeOutputType">A value indicating how the values should be presented in Excel.</param>
/// <returns>The Excel range which contains the <paramref name="values"/>.</returns>
public static object GetExcelRangeOutput <T>(IEnumerable <T> values, string header = null, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
int valueRowCount = values.Count() + ((header != null) ? 1 : 0);
if (valueRowCount > excelRangeRowCount)
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x 1 Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least 1 x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
int k = 0;
if (header != null)
{
returnValue[k++, 0] = header;
}
foreach (T value in values)
{
returnValue[k++, 0] = value;
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
int k = 0;
foreach (T value in values)
{
var cellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex);
cellReference.SetValue(value);
k++;
}
});
return((header != null) ? header : "<unknown>");
}
}
/// <summary>Gets the Excel range output for a specific array.
/// </summary>
/// <typeparam name="T1">The type of the first item.</typeparam>
/// <typeparam name="T2">The type of the second item.</typeparam>
/// <param name="values">The collection of values.</param>
/// <param name="header1">An optional header for the first item.</param>
/// <param name="header2">An optional header for the second item.</param>
/// <returns>The Excel range which contains the <paramref name="values"/>.</returns>
public static object GetExcelRangeOutput <T1, T2>(IEnumerable <Tuple <T1, T2> > values, string header1 = null, string header2 = null, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
bool containsHeader = (header1 != null) || (header2 != null);
int valueRowCount = values.Count() + (containsHeader == true ? 1 : 0);
if (valueRowCount > excelRangeRowCount)
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x 2 Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least 2 x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
int k = 0 + (containsHeader == true ? 1 : 0);
foreach (var value in values)
{
returnValue[k, 0] = value.Item1;
returnValue[k++, 1] = value.Item2;
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
int k = 0;
foreach (var value in values)
{
var firstCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex);
firstCellReference.SetValue(value.Item1);
var secondCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex + 1);
secondCellReference.SetValue(value.Item2);
k++;
}
});
var returnValue = new object[1, 2];
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
return(returnValue);
}
}
/// <summary>Gets the excel range output, i.e. converts the output such that no '#NV' values are
/// shown and print an error message, if the given range is to small for the output.
/// </summary>
/// <typeparam name="T">The type of the values.</typeparam>
/// <param name="values">The values.</param>
/// <param name="rangeOutputType">A value indicating how the values should be presented in Excel.</param>
/// <returns>The range which contains <paramref name="values"/> or some error message.</returns>
public static object GetExcelRangeOutput <T>(T[][] values, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
int valueRowCount = values.GetLength(0);
int valueColumnCount = values[0].GetLength(0);
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
if ((valueRowCount > excelRangeRowCount) || (valueColumnCount > excelRangeColumnCount))
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x " + valueColumnCount + " Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least " + valueColumnCount + " x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
for (int i = 0; i < valueRowCount; i++)
{
for (int j = 0; j < valueColumnCount; j++)
{
returnValue[i, j] = values[i][j];
}
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
var returnValue = ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
for (int i = 0; i < valueRowCount; i++)
{
for (int j = 0; j < valueColumnCount; j++)
{
var cellReference = new ExcelReference(firstRowIndex + i + 1, firstColumnIndex + j);
cellReference.SetValue(values[i][j]);
}
}
});
return(String.Format("<Below result of> {0}", returnValue).ToTimeStampString());
}
}