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.
