Dynamics AX Blog - Dynamics AX 2009 - Posts from 2008

Recently I was forced to deal with how to read data from an Excel file into AX. Therefore in the following a job with a kind of basic structure, how to solve something like this in X++.

static void importFromExcel(Args _args) 
{
    Filename                fileNameExcel = "C:\temp\file.xls";

    SysExcelApplication     sysExcelApplication;
    SysExcelWorkbooks       sysExcelWorkbooks;
    SysExcelWorksheets      sysExcelWorksheets;
    SysExcelWorksheet       sysExcelWorksheet;
    SysExcelRange           sysExcelRange;
    SysExcelCells           sysExcelCells;
    SysExcelWorkbooks       sysExcelWorkBooksCollection;
    str                     column_a;
    str                     column_b;
    str                     column_c;
    str                     column_d;
    int                     i = 0;
    #Excel
    ;

    sysExcelApplication = SysExcelApplication::construct();
    sysExcelWorkbooks   = sysExcelApplication.workbooks();

    sysExcelWorkbooks.open(fileNameExcel);

    sysExcelWorksheets  = sysExcelApplication.worksheets();
    sysExcelWorksheet   = sysExcelWorksheets.itemFromNum(1);
    sysExcelRange       = sysExcelWorksheet.rows();
    sysExcelCells       = sysExcelWorksheet.cells();

    try
    {
        ttsbegin;

        while (sysExcelCells.item(i+1, 1).value().variantType() != ComVariantType::VT_EMPTY)  // In der Annahme, dass die erste Spalte nicht leer ist         
        {
            i++;

            column_a = sysExcelCells.item(i, 1).value().bStr();
            column_b = sysExcelCells.item(i, 2).value().bStr();
            column_c = sysExcelCells.item(i, 3).value().bStr();
            column_d = sysExcelCells.item(i, 4).value().bStr();

            // ... do something ...
        }

        ttscommit;

        info("Finished");

        sysExcelApplication.quit();
        sysExcelApplication = null;
    }
    catch (Exception::Error)
    {
        sysExcelApplication.quit();
        sysExcelApplication = null;
    }
}

If you try to change the AllowDuplicates-Property of a table index from No to Yes, the following error message may occur:

Illegal property value

In this case, presumably, the affected index is registered as PrimaryIndex or ClusteredIndex of the table.

Using the following code you can easily print sales invoice through code. With slight modifications to the code, this is also true for all other sales- and purchase-sided documents.

Short example in AX 2009

static void PrintSalesInvoice(Args _args)
{
   custInvoiceJour     custInvoiceJour;
   SalesFormLetter     salesFormLetter = SalesFormLetter::construct(DocumentStatus::Invoice, false);
   PrintJobSettings    printJobSettings = new PrintJobSettings();
   Args                args = new Args();
   boolean             prompt = true;
   boolean             printIt = true;
   ;

   if (prompt)
   {
       // Dialog
       printIt = printJobSettings.printerSettings('SysPrintForm');
   }
   else
   {
       // Printjobsettings
       printJobSettings.setTarget(PrintMedium::File);
       printJobSettings.format(PrintFormat::PDF);
       printJobSettings.fileName(@'c:	empmyfile.pdf');
   }

   if (!printIt)
   {
       return; 
   }

   salesFormLetter.updatePrinterSettingsFormLetter(printJobSettings.packPrintJobSettings());

   select firstOnly custInvoiceJour
       where custInvoiceJour.salesid == '100001';

   args.record(custInvoiceJour);
   args.caller(salesFormLetter);

   new MenuFunction(menuitemoutputstr(SalesInvoice), MenuItemType::Output).run(args);
}

Short example in AX 4.0

static void PrintSalesInvoice(Args _args)
{
   custInvoiceJour     custInvoiceJour;
   SalesFormLetter     salesFormLetter = SalesFormLetter::construct(DocumentStatus::Invoice, false);
   PrintJobSettings    printJobSettings = new PrintJobSettings();
   Args                args = new Args();
   boolean             prompt = true;
   boolean             printIt = true;
   salesPrintSetup     salesPrintSetup;
   ;

   if (prompt)
   {
       // Dialog
       printJobSettings = new PrintJobSettings(salesPrintSetup.PrintJobSettings);
       printIt = printJobSettings.printerSettings('SysPrintForm');
       salesPrintSetup.PrintJobSettings = printJobSettings.packPrintJobSettings();
   }
   else
   {
       // Printjobsettings
       printJobSettings.setTarget(PrintMedium::File);
       printJobSettings.format(PrintFormat::PDF);
       printJobSettings.fileName(@'c:	empmyfile.pdf');
   }

   if (!printIt)
   {
       return; 
   }

   salesFormLetter.updatePrinterSettingsFormLetter(printJobSettings.packPrintJobSettings());

   select firstOnly custInvoiceJour
       where custInvoiceJour.salesid == '100001';

   args.record(custInvoiceJour);
   args.caller(salesFormLetter);

   new MenuFunction(menuitemoutputstr(SalesInvoice), MenuItemType::Output).run(args);
}