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:

Excel

Happy Dax6ng,

Sreenath Reddy

Advertisements

3 Responses to “SysExcelWorksheetHelper and SysExcelHelper classes in AX 2012–Quickly create excel [X++]”

  1. Ashish Says:

    Brilliant work sree….I am going to harvest your code as we say in accenture

  2. Hima Bindu Says:

    hi Srinath,

    this is hima, can u pls calriy the below doubt.
    How to export the data from excel to ax table by using X++ Code

    can u pls Replay for this.

    Thanks and Regards,
    Hima Bindu

  3. axTr Says:

    How to Export itemimage table picture in excel report ax


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: