ax-erp: SysExcelWorksheetHelper and SysExcelHelper classes in AX 2012–Quickly create excel [X++]

[Blog bot]

Источник: http://microsoft-dynamics-ax-erp.blo...elper-and.html
==============

Friends,
SysExcelWorksheetHelper class in AX 2012 will help to quickly create an excel with the desired fonts, summation[sum ranges] features, auto fit columns, populate field names as headers from the tables etc.
SysExcelHelper class will help to create and manage the overall workbook. Remember the same thing can be achieved using SysExcel* classes.
Below is the sample code. This example displays Account Number, Currency, Customer group and BalanceMST fields and it will sum the BalanceMST fields and will displays as total at the end.
staticvoid SR_SysExcelWorksheetHelper(Args _args)
{
CustTable custTable;
SysExcelWorksheetHelper worksheetHelper;
SysExcelHelper sysExcelHelper;
SysExcelWorksheet worksheet;
int currentRow = 1;
int sumRow;
str worksheetName;
int redColor = WinAPI::RGB2int(255, 0, 0);
SysExcelRange range;
COMVariant cellValue = new COMVariant(COMVariantInOut::Out);
str fileName;
str attachmentPath = "C:\\";


// Sets the font color for a range of cells
void setRangeFont(int _fromColumn, int _fromRow, int _toColumn, int _toRow, int _rgbIntColor)
{
range = worksheetHelper.getWorksheetRange(_fromColumn, _fromRow, _toColumn, _toRow);
worksheetHelper.setFontColor(range, _rgbIntColor);
}




// Defines the columns of the spreadsheet
#define.AccountNum(1)
#define.Currency(2)
#define.CustGroup(3)
#define.BalanceMST(4)


worksheetName = "@SYS135880";


sysExcelHelper = SysExcelHelper::construct();


sysExcelHelper.initialize();


worksheet = sysExcelHelper.addWorksheet(worksheetName);


worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);


// Populate the header row with the appropriate field labels and format the columns
worksheetHelper.addColumnFromTableField(#AccountNum, tablenum(CustTable), fieldnum(CustTable, AccountNum));
worksheetHelper.addColumnFromTableField(#Currency, tablenum(CustTable), fieldnum(CustTable, Currency));
worksheetHelper.addColumnFromTableField(#CustGroup, tablenum(CustTable), fieldnum(CustTable, CustGroup));
worksheetHelper.addColumn(#BalanceMST, "Balance MST", Types::Real);


whileselect custTable
{
currentRow ++;
worksheetHelper.setCellValue(#AccountNum, currentRow, custTable.AccountNum);
worksheetHelper.setCellValue(#Currency, currentRow, custTable.Currency);
worksheetHelper.setCellValue(#CustGroup, currentRow, custTable.CustGroup);
worksheetHelper.setCellValue(#BalanceMST, currentRow, custTable.balanceMST());
}
if (currentRow > 1)
{
sumRow = currentRow + 2;


worksheetHelper.setCellValue(#BalanceMST, sumRow, "@SYS58659");


worksheetHelper.sumRange(worksheetHelper.getWorksheetRange(#BalanceMST, 2, #BalanceMST, currentRow), #BalanceMST, sumRow);


setRangeFont(#BalanceMST, 2, #BalanceMST, currentRow, redColor);


cellValue = worksheet.cells().item(sumRow, #BalanceMST).value();
if (cellValue.currency() > 0)
{
setRangeFont(#BalanceMST, sumRow, #BalanceMST, sumRow, redColor);
}
}
worksheetHelper.autoFitColumns();
worksheetHelper.formatWorksheetTableStyle(sysExcelHelper.getOfficeVersion());


// Generate the file using the current UTC date time (without the ‘:’ character)
// since it is not allowed for file names.
fileName = strfmt(‘%1%2%3′, attachmentPath, strReplace(DateTimeUtil::toStr(DateTimeUtil::utcNow()), ‘:’, ”), sysExcelHelper.getFileExtension());


sysExcelHelper.save(filename);
sysExcelHelper.launchExcel();
}


Output:

Happy Dax6ng,
Sreenath Reddy

Источник: http://microsoft-dynamics-ax-erp.blo...elper-and.html