Extracting Project Addresses to Excel in Dynamics AX 2012
In this blog, I’ll demonstrate how to extract project address details into an Excel file using X++ in Dynamics AX 2012. While CSV is a great format for small-scale data export, it has limitations when dealing with large datasets, as it lacks the concept of multiple sheets. To overcome this, I transitioned to Excel, which supports multiple sheets and can handle larger volumes of data seamlessly.
Why Excel Instead of CSV?
In my previous implementation, I used a CSV format to extract project addresses. While it was simple and effective for smaller datasets, it became evident that:
- CSV files are single-page only: If the record count exceeds the limit, we cannot paginate to a new sheet.
- Excel offers better structure: With multiple sheets, we can organize and store large amounts of data efficiently.
- Usability: Excel files are more user-friendly, especially when filtering, sorting, or visualizing data.
X++ Job for Exporting Project Addresses to Excel
Below is the X++ code to extract project addresses into an Excel file. The implementation handles data pagination by creating new sheets whenever the row limit (1,000,000 rows per sheet) is exceeded.
static void ProjectsExcel(Args _args)
{
SysExcelApplication excelApp; // Excel application object
SysExcelWorkbooks workbooks; // Excel workbooks object
SysExcelWorkbook workbook; // Excel workbook object
SysExcelWorksheets worksheets; // Excel worksheets collection
SysExcelWorksheet worksheet; // Current worksheet
SysExcelCells cells; // Current cells collection
ProjTable projTable;
CustTable custTable;
DirPartyTable dirPartyTable;
DirPartyLocation dirPartyLocation;
LogisticsLocation logisticsLocation;
LogisticsPostalAddress logisticsPostalAddress;
LogisticsAddressCountryRegion logisticsAddressCountryRegion;
utcdatetime currentDateTime = DateTimeUtil::newDateTime(systemDateGet(), 0, 0); // Current UTC date and time
utcdatetime dateFrom = DateTimeUtil::minValue(); // Min valid date
utcdatetime dateTo = DateTimeUtil::maxValue(); // Max valid date
str status;
int row = 2, sheetNumber = 1; // Row counter starts at 2 to skip header row
int maxRowsPerSheet = 1000000; // Max rows per sheet (excluding header)
str fileName = @"C:\Users\CustomerVendorAddress\ProjectAddressDetailsJob1.xlsx";
int countRec = 0;
// Initialize Excel
excelApp = SysExcelApplication::construct();
workbooks = excelApp.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
// Create or access the first sheet
worksheet = worksheets.itemFromNum(1); // Correct method to get the first sheet
worksheet.name("Sheet" + int2str(sheetNumber)); // Rename the first sheet
cells = worksheet.cells(); // Access the cells collection
// Add header
cells.item(1, 1).value("Project ID");
cells.item(1, 2).value("Partner Number");
cells.item(1, 3).value("Dlv Mode");
cells.item(1, 4).value("Partner Name");
cells.item(1, 5).value("Address");
cells.item(1, 6).value("Street");
cells.item(1, 7).value("City");
cells.item(1, 8).value("State");
cells.item(1, 9).value("Zip");
cells.item(1, 10).value("Country");
cells.item(1, 11).value("Country Code");
cells.item(1, 12).value("Status");
// Query to join tables and retrieve customer address details
while select crossCompany validTimeState(dateFrom, dateTo) projTable
where projTable.Status != ProjStatus::Completed
join logisticsLocation
where projTable.DeliveryLocation == logisticsLocation.RecId
join logisticsPostalAddress
where logisticsLocation.RecId == logisticsPostalAddress.Location
&& logisticsPostalAddress.ValidFrom <= currentDateTime
&& logisticsPostalAddress.ValidTo >= currentDateTime
outer join logisticsAddressCountryRegion
where logisticsPostalAddress.CountryRegionId == logisticsAddressCountryRegion.CountryRegionId
{
// Determine the status (active/history) based on ValidTo date
if (logisticsPostalAddress.ValidTo == DateTimeUtil::minValue()
|| logisticsPostalAddress.ValidTo >= currentDateTime)
{
status = "active";
}
else
{
status = "history";
}
// Write data to Excel
cells.item(row, 1).value(projTable.ProjId);
cells.item(row, 2).value(dirPartyTable.PartyNumber);
cells.item(row, 3).value(projTable.DlvName);
cells.item(row, 4).value(dirPartyTable.Name);
cells.item(row, 5).value(logisticsPostalAddress.Address);
cells.item(row, 6).value(logisticsPostalAddress.Street);
cells.item(row, 7).value(logisticsPostalAddress.City);
cells.item(row, 8).value(logisticsPostalAddress.State);
cells.item(row, 9).value(logisticsPostalAddress.ZipCode);
cells.item(row, 10).value(logisticsPostalAddress.CountryRegionId);
cells.item(row, 11).value(logisticsAddressCountryRegion.CountryRegionId);
cells.item(row, 12).value(status);
row++;
countRec++;
// Check if row limit is reached (accounting for the header)
if (row > maxRowsPerSheet + 1) // +1 accounts for the header row
{
sheetNumber++;
row = 2; // Reset to 2 for new sheet data, as row 1 is for headers
// Add a new sheet
worksheet = worksheets.add(); // Add a new sheet
worksheet.name("Sheet" + int2str(sheetNumber)); // Name the new sheet
cells = worksheet.cells(); // Access cells for the new sheet
// Add headers for the new sheet
cells.item(1, 1).value("Project ID");
cells.item(1, 2).value("Partner Number");
cells.item(1, 3).value("Dlv Mode");
cells.item(1, 4).value("Partner Name");
cells.item(1, 5).value("Address");
cells.item(1, 6).value("Street");
cells.item(1, 7).value("City");
cells.item(1, 8).value("State");
cells.item(1, 9).value("Zip");
cells.item(1, 10).value("Country");
cells.item(1, 11).value("Country Code");
cells.item(1, 12).value("Status");
}
}
// Save the Excel file
workbook.saveAs(fileName);
excelApp.quit();
// Display completion message
info(strFmt("Excel file created with %1 records across %2 sheets: %3", countRec, sheetNumber, fileName));
}
Comments
Post a Comment