Dynamics AX 2009 : Exporting data to Excel (Part1)

Quiet often we get the request from the clients that they wish if the reports or query data they design can be seen in excel in the Excel formatted way. Though the standard reports output can be sent to Excel  but that is not formatted . Almost all the enquiries have a button to export the grid data to Excel but that gives them limitation of only the GRID data.

We have recently given the following two solutions in different scenarios to different clients. Where the data is not very huge ( the user is expecting a couple of hundred lines as a query output then the following solution will work nice.

Dynamics AX’s classes prefixed with SysExcel actually do the export to excel. We would use these classes and create a small list of customers alongwith the itemIds they have bought or placed order for.

Open the AOT. Create a shared project e.g. SD_ExcelExport

Create the a new class as shown below :

=================================================================================

class SDExcelExport
{
}

public static void main(Args args)
{

    CustTable custTable;
    SysExcelApplication application;
    SysExcelWorkBooks    workbooks;
    SysExcelWorkBook     workbook;
    SysExcelWorksheets  worksheets;
    sysExcelWorksheet   worksheet;
    SysExcelCells       cells;
    SysExcelCell        cell;
    int                 row;

    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks(); //gets the workbook object
    workbook = workbooks.add();  // creates a new workbook
    worksheets = workbook.worksheets(); //gets the worksheets object
    //The following selects the first worksheet in the workbook to insert data 
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    // numberFormat ‘@’ is to insert data as Text

    cells.range('A:A').numberFormat('@');  
    while select custTable
    //The following loop will provide the data to be populated in each column
    {
        row++;
        cell = cells.item(row,1);
        cell.value(custTable.AccountNum);
        cell = cells.item(row,2);
        cell.value(custTable.Name);
        cell = cells.item(row,3);
        cell.value(CustTable.CustGroup);
        cell = cells.item(row,4);
        cell.value(CustTable.Currency);
        cell = cells.item(row,5);
        cell.value(CustTable.CreditMax);
        cell = cells.item(row,6);
        cell.value(CustTable.CreditRating);
    }
    application.visible(true);  '// opens the excel worksheet
}

 

=================================================================================

When you run the class then you will get the following output formatted

Complex queries can be written and the data can be straight away sent to Excel.