/// <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()); } }
/// <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, 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> /// <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(object[,] values, RangeOutputType rangeOutputType = RangeOutputType.Standard) { return(GetExcelRangeOutput <object>(values, rangeOutputType)); }