Job for Importing & Exporting from Excel to Ax2012

static void ITL_Import_BOND_Excel(Args _args)

{

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    int row = 1;

    dialog                          d;

    Filename                        filename;

    DialogField                     dialogFilename;

   // CALF_ParentageSireMaster      _CALF_ParentageSireMaster;

    ITLINVBondCashflowDetails_TEST       _ITLINVBondCashflowDetails;  

    NumberSeq NumberSeq;

    ;

    d = new dialog();

    d.caption("select a file");

    dialogFilename = d.addField(extendedTypeStr(FilenameOpen));//add a field where you select your file in a specific path

    d.run();//execute dialog

    if(d.closedOk())

    {

    filename = dialogFileName.value();

        application = SysExcelApplication::construct();

        workbooks = application.workbooks();

        try

        {

        workbooks.open(filename);

        }

        catch (Exception::Error)

        {

        throw error("File cannot be opened.");

        }

        workbook = workbooks.item(1);

        worksheets = workbook.worksheets();

        worksheet = worksheets.itemFromNum(1);

        cells = worksheet.cells();

         do

       {

          row++;

           _ITLINVBondCashflowDetails.BondId  = cells.item(row, 1).value().bStr();

           

            if(cells.item(row, 2).value().bStr() == "Invoice")

           _ITLINVBondCashflowDetails.CashflowType = ITLCashflowType::Invoice;

    else

            _ITLINVBondCashflowDetails.CashflowType  = ITLCashflowType::Accrual; 

           

           //_ITLINVBondCashflowDetails.DueDate         =  str2Date(cells.item(row, 3).value().bStr(),213);

           _ITLINVBondCashflowDetails.DueDate         =  cells.item(row, 3).value().date();

           _ITLINVBondCashflowDetails.InterestRate    = cells.item(row, 7).value().double();

           _ITLINVBondCashflowDetails.IntCalcUpon     = cells.item(row, 8).value().double();

           _ITLINVBondCashflowDetails.InterestAmount  = cells.item(row, 9).value().double();

           _ITLINVBondCashflowDetails.SysCashflowAmount = cells.item(row, 10).value().double();

           _ITLINVBondCashflowDetails.JournalNum      = cells.item(row, 11).value().bStr();

           _ITLINVBondCashflowDetails.SourceVoucherNo = cells.item(row, 12).value().bStr();

           

          if(cells.item(row, 13).value().bStr() == "Yes")

           _ITLINVBondCashflowDetails.Posted = NoYes::Yes;

    else

            _ITLINVBondCashflowDetails.Posted = NoYes::No;

           

           _ITLINVBondCashflowDetails.insert();

           

           

           

            type = cells.item(row+1, 1).value().variantType();

       } 

        

        

       while (type != COMVariantType::VT_EMPTY);

       application.quit();

       }

}

                      EXPORTING
Copy-Paste underneath code and run the Job. The Job will create a Microsoft Excel workbook in the directory C:\Windows\Temp.

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;
    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();
    // 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");

    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");
            }
            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);
        }
        workSheetAdded = false;
    }
    // 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();

}

The result should look like this if you execute the job on the demo data inMicrosoft Dynamics AX 2009 with Microsoft Excel 2007:





This is the first worksheet in my Excel workbook with the complete list of my customers in it.





This is one of the other worksheets, being the one created for customer Forest Wholesales and showing all the backorder lines for this particular customer.

Remarks about some code snippets

• sysExcelApplication.visible(false);

  If set to false, Microsoft Excel will be opened in the background of your desktop.




• sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);

  sysExcelWorkSheetToBeDeleted is set to the second worksheet in yourExcel workbook.



• sysExcelWorkSheet.name("Customers");

  sysExcelWorksheet is renamed to Customers.



• sysExcelApplication.displayAlerts(false);

  If set to true, a pop-up window (A file named ExportToExcel.xlsx already exists in this location. Do you want to replace it?) will appear if an Excel file with the same name already exists at the save location. This can be very annoying if you want to put this X++ code in a class and you want to run this class in batch at night. If set to true, your batch will wait until somebody gives a yes to allow replacement of the existing Excel file. If set to false, an existing Excel file will be overwritten.



• sysExcelApplication.quit();

  Don’t forget this line of X++ code, or there will remain an Excel process running. This is important in the case where you did run Microsoft Excelin the background of your desktop. If you forget this line of X++ code and you run Excel in the background, you’ll have multiple Microsoft Excelprocesses after running this job a couple of times (you can check this by opening Task Manager and having a look at the Processes tab).