SysExcelWorksheetHelper and SysExcelHelper classes in AX 2012–Quickly create excel [X++]

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.

static void 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);

 

    while select 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