13 Nov 2018

Export customer address with LocationID to Excel Through code in AX 2012

static void AXAPTAEXP_ExportCustomerAddress(Args _args)
{
    #file
    CustTable               custTable;
    DirPartyTable           dirPartyTable;
    DirPartyLocation        dirPartyLocation;
    LogisticsLocation       logisticsLocation;
    LogisticsPostalAddress  logisticsPostalAddress;
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            font;
    int                     row;
    LogisticsPostalAddressView      view;
    fileName                fileName = "D:\\ address Export\\CustAddress.xlsx";
    FileIoPermission        filepermission;

    // intializing classes to export excel
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    if (WINAPI::fileExists(fileName))
    {
        WINAPI::deleteFile(fileName);
    }

    // Setting Header values
    cell = cells.item(1, 1);
    cell.value("AccountNum ");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 2);
    cell.value("LocationID");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 3);
    cell.value("Name ");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 4);
    cell.value("Address");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 5);
    cell.value("State ");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 6);
    cell.value("City");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 7);   
    cell.value("CountryRegionId ");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 8);
    cell.value("Street");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 9);
    cell.value("County ");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 10);
    cell.value("ZipCode");
    font = cell.font();
    font.bold(true);
    row = 1;

    // inserting data row wise
    while select AccountNum, Party from custTable
            join RecId from dirPartyTable
                    where   dirPartyTable.RecId == custTable.Party
            join Location, Party from dirPartyLocation
                    where   dirPartyLocation.Party == custTable.Party
            join RecId, Description, LocationID from logisticsLocation
                    where   logisticsLocation.RecId == dirPartyLocation.Location
                join logisticsPostalAddress
                     where   logisticsPostalAddress.Location == logisticsLocation.RecId
    {
        LogisticsPostalAddress = LogisticsPostalAddress::findByLocation(LogisticsLocation::findByLocationId(logisticsLocation.LocationID).RecId);
        row++;
        cell = cells.item(row, 1);
        cell.value(any2str(custTable.AccountNum));
        cell = cells.item(row, 2);
        cell.value(any2str(logisticsLocation.LocationId));
        cell = cells.item(row, 3);
        cell.value(any2str(logisticsLocation.Description));
        cell = cells.item(row, 4);
        cell.value(any2str(LogisticsPostalAddress.Address));
        cell = cells.item(row, 5);
        cell.value(any2str(LogisticsPostalAddress.State));
        cell = cells.item(row, 6);
        cell.value(any2str(LogisticsPostalAddress.City));
        cell = cells.item(row, 7);
        cell.value(any2str(LogisticsPostalAddress.CountryRegionId));
        cell = cells.item(row, 8);
        cell.value(any2str(LogisticsPostalAddress.Street));
        cell = cells.item(row, 9);
        cell.value(any2str(LogisticsPostalAddress.County));
        cell = cells.item(row, 10);
        cell.value(any2str(LogisticsPostalAddress.ZipCode));
    }
    application.displayAlerts(false);
    worksheet.columns().autoFit();
    workbook.saveAs(fileName);
    workbook.comObject().save();
    workbook.saved(true);
    application.quit();

    info(strFmt("File saved in %1", fileName));
}

No comments:

Create number sequence in D365 FO

Create the data type. Add code in the loadModule method of the appropriate NumberSeqModule subclass. Add a method to the module’s paramet...