I have been successfully exporting data to Excel using X++, but I am concerned about the performance.
The data is being written to each cell one at a time, but I would like to switch to setting a range of values at once.
Does anyone know the proper syntax for this? I have tried a variety of possibilities, but I think that what I am missing is the ability to pass a 2D array to the proper Excel function.
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
class ExcelTest
{
public static void main(Args args)
{
System.IO.MemoryStream memoryStream = new MemoryStream();
var package = new ExcelPackage(memoryStream);
var worksheets = package.get_Workbook().get_Worksheets();
OfficeOpenXml.ExcelWorksheet Worksheet = worksheets.Add("Export");
OfficeOpenXml.ExcelRange cells = Worksheet.get_Cells();
OfficeOpenXml.ExcelRange cell;
OfficeOpenXml.ExcelRange range;
var currentRow = 1;
;
cell = cells.get_Item(currentRow, 1);
System.String value = "Title";
cell.set_Value(value); // This works
currentRow++;
// next part tries to send an array, but it doesn't work. Instead the cells are all filled with "System.String[]"
System.String[] strArray = new System.String[9]();
strArray.SetValue("Col1",0);
strArray.SetValue("Col2",1);
strArray.SetValue("Col3",2);
strArray.SetValue("Col4",3);
range = Cells.get_Item(strfmt("A%1:D%1",currentRow));
range.set_Value(strArray);
currentRow++;
package.Save();
file::SendFileToUser(memoryStream, 'Test.xlsx');
}
}
I believe the problem is that the range.setValue() function requires a 2D array (Excel RangeValueDataType), and I cannot figure out the correct syntax to pass. Alternatively if anyone can point to an example of Microsoft exporting in bulk in the standard code, that would be great.
