Engineer / Solution Architect / Dynamics 365-AX Technical Consultant / Instructor (MCT)

Microsoft Office Excel format is one of the formats that have been supported by Dynamics AX
since its early versions. For example, the Document handling feature allows producing Excel
files using the data from the system. In the new version of Dynamics AX, i.e. Dynamics AX
2009, this feature is extended even more. Now almost every form has a button called Export
to Excel, which quickly allows loading form data into Excel for further analysis using powerful
Excel tools.
Microsoft Office Excel format handling is done with the help of standard Dynamics AX
application classes prefixed with SysExcel. Basically, those classes are Excel COM wrappers
plus they contain additional helper methods to ease the developer’s tasks.
To demonstrate the principle in this recipe, we will create a new Excel file and fill it with a
customer list. This technique could be used to export any business data in a similar way.
How to do it…

1. Open AOT, and create a new class named CreateExcelFile with the following code:
class CreateExcelFile
{
}
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();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range(‘A:A’).numberFormat(‘@’);
while select custTable
{
row++;

cell = cells.item(row, 1);
cell.value(custTable.AccountNum);
cell = cells.item(row, 2);
cell.value(custTable.Name);
}
application.visible(true);
}
2. Run the class, and check the exported list of customers on the screen:
3. Save the list as a file to the documents folder or somewhere on your filesystem for
further use in the Reading Excel files recipe.
How it works…
We start the code by creating the application object, which represents Excel using one
of the already mentioned SysExcel classes—SysExcelApplication. This is the very top of the
Excel object hierarchy. The next object is a collection of Excel workbooks, which we get by
calling workbooks() on the application object. We have to create a new workbook by
calling add() on the workbooks object. Each workbook contains a collection of worksheets,
which consists of three default worksheets. We get the worksheets object by calling
worksheets() on the workbook, and we get the first sheet by calling itemFromNum() with
the argument, 1, of the first sheet object. And finally, we get a collection of cells by calling
cells() on the worksheet.
Once we have reached the cells object, we set the format of the first column to @. The
range of A:A is the first column and format @ means text format. This will force the first
column to be nothing else but text.

Next, we start looping through the CustTable table and fill customer account into the first
column and customer name into the second column in each row. In this way, we populate as
many rows as we have customers in the system.
Finally, we set the Excel application to show up on the screen by calling its visible() with
a true argument. It is a good practice to show the Excel after all operations are completed,
otherwise the user might start changing something while data is still being populated.
As you have noticed, we forced the first column of the sheet, customer account, to be formatted
as text, but we left the second column, customer name, as per default. This is because in my
data and in most cases, customer account actually is a number like 1000, 1001, and so on
even though it is stored in a string field in Dynamics AX. If we leave the format of the first column
intact, then Excel would automatically detect that customer account is a number and would
store it as a number. This might lead to confusion and data type mismatches when processing
this file later. This is a very common issue when dealing with Excel files. Customer name
normally cannot have only numbers so it is always automatically detected by Excel as text.

Source

Microtsoft Dynamics AX 2009 Development Cookbook