From July 9th till 13th we will be at #MSInspire. See you there!

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

17 Feb 2014

Microsoft Dynamics AX Export Microsoft Excel

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.

Microsoft Dynamics AX Export Microsoft Excel

This will generate an Excel file as below:

Microsoft Excel Report

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.

Share

Raza Rabbani

Latest From Our Blog
7 Ways Dynamics 365 Finance and Operations Boos...
dynamics 365 finance
November 9, 2018
Introduction Launched by Microsoft, Dynamics 365 ERP is one of the most effective for businesses today. It is perfect to save the time, effort and ...
Read more
The Ultimate Guide to Dynamics 365 HR Payroll P...
dynamics hr payroll
October 30, 2018
For any business, irrespective of its size, payroll processing is one of the most important functions. Payroll processing is a method through which...
Read more
An Ultimate Guide to Dynamics 365 Business Cent...
microsoft dynamics business central
September 24, 2018
Microsoft Dynamics Business Central can capably support all your financial processes, whereby enabling smooth integration with supply chain, operat...
Read more
Contact Form