sashanazarov: Exporting to Excel with Microsoft.Dynamics.AX.Fim.Spreadsheets.* classes

[Blog bot]

Источник: http://sashanazarov.blogspot.com/201...xcel-with.html
==============

While looking for a way to export to Excel in batch, I investigated what they do in financial statements (LedgerBalanceSheetDimPrintExcelEngine class).

Pros:

• It is possible to use these .NET components in batch on the server side
• Execution is way faster than that of the SysExcel* classes

Cons:

• It looks like it is not possible to create more columns than there are letters in the English alphabet
• Less flexible comparing to standard SysExcel classes, so manual formatting will likely be needed in the end.

static void AnotherWayToExportToExcel(Args _args)
{
#define.ReadWritePermission('RW')
#define.FileName('c:\myFile.xlsx')
#define.ExcelColumnWidth(15)
#define.ExcelCellFontSize("Microsoft.Dynamics.AX.Fim.Spreadsheets.CellFontSize")
#define.Size9("Size9")

CustTable custTable;

Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet spreadsheet;
Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties cellProperties;
Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties columnProperties;

void addColumn(str _name)
{
columnProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.ColumnProperties();
columnProperties.set_Width(#ExcelColumnWidth);
spreadSheet.InstantiateColumn(columnProperties);

cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));
cellProperties.set_Bold(true);

spreadSheet.AddStringCellToWorkbook(_name, cellProperties);
}

new FileIOPermission(#FileName, #ReadWritePermission).assert();

spreadSheet = new Microsoft.Dynamics.AX.Fim.Spreadsheets.Spreadsheet();

if (!spreadSheet.CreateSpreadsheet(#FileName))
{
throw error(strFmt("@SYS72245", #FileName));
}

addColumn("Customer name");
addColumn("Balance");

while select custTable
{
spreadSheet.MoveToNextRowInWorkbook();

cellProperties = new Microsoft.Dynamics.AX.Fim.Spreadsheets.CellProperties();
cellProperties.set_FontSize(CLRInterop::parseClrEnum(#ExcelCellFontSize, #Size9));

spreadSheet.AddStringCellToWorkbook(custTable.name(), cellProperties);
spreadSheet.AddNumberCellToWorkbook(real2double(custTable.openBalanceCur()), cellProperties);
}

spreadSheet.WriteFile();
spreadSheet.Dispose();

CodeAccessPermission::revertAssert();
}
Output:

Источник: http://sashanazarov.blogspot.com/201...xcel-with.html