2 Simple ways of generating Microsoft Excel reports in Microsoft Dynamics AX

Excel is probably the number one reporting tool because it is on everyone’s computer, and also because it makes it so easy to manipulate data.

Dynamics AX allows to you create Microsoft excel reports to integrate with your application. There are two methods for creating excel reports:

1. To export all the data to excel by using forms Export to Excel function and
2. To export customized data by using Dynamics AX application classes prefixed with SysExcel.

Method 1

Almost every AX form has the Export to Excel function which allows loading data on the screen into Excel.

Here I am generating a report for Customers.

Go to Accounts Receivable -> Common -> Customers -> All customers. To generate the report just click on Export to Microsoft Excel Reports.

This will generate an Excel file as below:

Method 2

Dynamics AX holds a set of standard application classes prefixed with SysExcel. Basically, those classes are COM wrappers for Excel and they contain additional helper methods to make the developer’s task easier.

Below is an example of creating an excel file in X++. This example pulls a list of all Customers in Dynamics AX.

 

CustTable custTable;

int row;

 

The following classes are used for generating the excel report:

SysExcelApplication excelApp;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cellsHeader, cellsData;

SysExcelCell cellAccNum, cellName;

SysExcelFont headerFont;

 

The following block of code creates the excel file. First we create an excel file instance by calling SysExcelApplication::construct(), then we get the collection of documents by excelApp.workbooks() . Initially the document collection is empty so we create a new document add() method. Once the document is ready we get the reference of the first worksheet. We give this worksheet a name by calling this worksheet.name(“Customers”), then we get the references of cells in the worksheet. The first column in the sheet will contain a customer’s account number, so we have to make sure it is formatted as text. To do that we address the first column by using the A:A range and setting its format to @.

excelApp = SysExcelApplication::construct();

workbooks =excelApp.workbooks();

workbook = workbooks.add();

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

worksheet.name(“Customers”);

cellsHeader = worksheet.cells();

cellsData = worksheet.cells();

cellsHeader.range(‘A:A’).numberFormat(‘@’);

 

To get particular cell we call the cellHeader.item(row,column) method. The following code block sets the header in the report.

cellAccNum = cellsHeader.item(1, 1);

cellAccNum.value(“Account Num”);

headerFont = cellAccNum.font();

headerFont.bold(true);

cellName = cellsHeader.item(1, 2);

cellName.value(“Name”);

headerFont = cellName.font();

headerFont.bold(true);

 

Loop through the custTable and populate the customer account number and customer name.

row = 1;

while select custTable

{

row++;

cellAccNum = cellsData.item(row, 1);

cellAccNum.value(custTable.AccountNum);

cellName = cellsData.item(row, 2);

cellName.value(custTable.name());

}

 

The following lines of code grow the columns to the largest text in the cell and display the excel file.

worksheet.columns().autoFit();

excelApp.visible(true);

 

And that’s it! Feel free to provide feedback.