Create an Excel File using X++

Creating an excel file is essential mostly in data import or export. Microsoft Dynamics AX has a feature to import and export through excel template. But in some cases, you need to create an excel to integrate with other application. Below is an example of creating an excel file in X++ using an existing table in AX.

static void ExportToExcel(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks sysExcelWorkBooks;
// Filename to which you will be writing your data
FileName fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx";
SysExcelWorkbook sysExcelWorkBook;
SysExcelWorkSheets sysExcelWorkSheets;
SysExcelWorkSheet sysExcelWorkSheet;
SysExcelWorkSheet sysExcelWorksheetBackOrder;
SysExcelWorksheet sysExcelWorkSheetToBeDeleted;
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
int row = 1;
int rowBackOrder;
CustTable custTable;
SalesTable salesTable;
SalesLine salesLine;
boolean workSheetAdded = false;
int nbrOfCustomers;
;

// Initialising progress bar
progress.setCaption("Export To Excel in progress...");
progress.setAnimation(#AviTransfer);
// Initialisation of some objects
sysExcelApplication = SysExcelApplication::construct();
// Create new workbook
sysExcelWorkBooks = sysExcelApplication.workbooks();
sysExcelWorkBook = sysExcelWorkBooks.add();
// Create new style
styles = sysExcelWorkBook.styles();
style = styles.add("Header");
// Set font for this style to bold and color to red
font = style.font();
font.bold(true);
font.color(255);
// Get worksheets collection
sysExcelWorkSheets = sysExcelWorkbook.worksheets();
// Excel visible on desktop running the job or not?
sysExcelApplication.visible(false);
// Newly created Excel files have by default some worksheets
// Delete those worksheets created by default
while(sysExcelWorkSheets.count() > 1)
{
sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);
sysExcelWorkSheetToBeDeleted.delete();
}
// Add as many worksheets as there are customers
select count(RecId) from CustTable;
sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId);
// Add another worksheet
sysExcelWorkSheet = sysExcelWorkSheets.add();
//Rename the first worksheet
sysExcelWorkSheet.name("Customers");
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorkSheet.cells().item(1,1).value("Customer account");
// set a value in cell on row 1 column 2
sysExcelWorksheet.cells().item(1,2).value("Name");
// set the title row in the Header style
sysExcelWorksheet.rows().item(1).style("Header");
while select custTable
{
progress.setText(strfmt("Customer %1", custTable.Name));
row++;
rowBackOrder = 1;
sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum);
sysExcelWorksheet.cells().item(row,2).value(custTable.Name);
while select salesLine
where salesLine.SalesStatus == salesStatus::Backorder
&& salesLine.ConfirmedDlv < Today()
&& salesLine.RemainSalesPhysical > 0
join salesTable
where salesTable.SalesId == salesLine.SalesId &&
salesTable.CustAccount == custTable.AccountNum
{
if(!workSheetAdded)
{
// Use the next Excel worksheet and rename it
sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum(
row);
//Name of worksheet can have maximum 31 characters
sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31));
workSheetAdded = true;
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorksheetBackOrder.cells().item(1,1).value(
"Ship Date");
// set a value in cell on row 1 column 2
sysExcelWorksheetBackOrder.cells().item(1,2).value(
"Item Number");
// set a value in cell on row 1 column 3
sysExcelWorksheetBackOrder.cells().item(1,3).value(
"Item Name");
// set a value in cell on row 1 column 4
sysExcelWorksheetBackOrder.cells().item(1,4).value(
"Deliver Remainder");
// set the title row in the Header style
sysExcelWorksheetBackOrder.rows().item(1).style("Header");
}
rowBackOrder++;
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value(
salesLine.ConfirmedDlv);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value(
salesLine.ItemId);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value(
InventTable::find(salesLine.ItemId).ItemName);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value(
salesLine.RemainSalesPhysical);
}
// Set the column width to autofit
sysExcelWorksheetBackOrder.columns().autoFit();
// Format the 4th column as Number with 2 decimal places
cells = sysExcelWorksheetBackOrder.cells();
cells.range("D2:D99").numberFormat("0,00");
workSheetAdded = false;
}
// Set the column width to autoFit
sysExcelWorksheet.columns().autoFit();
// Suppress the pop-up window:
// A file named foo already exists in this location. Do you want to replace it?
sysExcelApplication.displayAlerts(false);
// Save the Excel file
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
// Make sure you close the Excel application
// Especially if you run the job without showing Excel on the desktop
// (sysExcelApplication.visible(false))
sysExcelApplication.quit();

}