Import the record from MS Excel to AX2009 / AX2012

The below code will help you to import the Excel file in to your MS Dynamics AX 2009 / AX 2012.

static void ImportFromExcelTOAX(Args _args)

{

    #AviFiles

    SysOperationProgress        progress = new SysOperationProgress();

    SysExcelApplication         application;

    SysExcelWorkbooks           workbooks;

    SysExcelWorkbook            workbook;

    SysExcelWorksheets          worksheets;

    SysExcelWorksheet           worksheet;

    SysExcelWorkSheet           workSheetInventTableModule;

    SysExcelCells               cellsInventTableModule;

    SysExcelCells               cells;

    COMVariantType              type;

    COMVariantType              typeModule;

    int                         row;

    FileName                    fileName;

    InventTable                 inventTable;

    InventTableModule           tabmodule;

    LedgerJournalTrans_Project  proj;

    ItemId                      itemId;

    FileIoPermission            perm;

    int64                       rec;

  

    Dialog                          dialog = new dialog();

    dialogField                     dialogFilename;

   

    #define.FileMode('R')

    ;

    fileName        = "C:\\Users\\vishal\\Desktop\\Refrec1.xlsx";

    perm = new FileIOPermission(FileName, #FileMode);

    perm.assert();

    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.itemFromName('refer');

    if (worksheet)

    {

        cells = worksheet.cells();

    }

    else

    {

        throw warning('File cannot be opened.');

    }

    progress.setCaption("Migrating ...");

    progress.setAnimation(#AviTransfer);

    try

    {

        ttsbegin;

        do

        {

            row++;

            if (row > 1)

            {

                rec              = any2int64(cells.item(row, 1).value().double());

                select firstonly forUpdate proj

                    where proj.RefRecId                == rec;

                if (proj)

                {

                    proj.CostPrice = any2real(cells.item(row, 2).value().double());

                    proj.update();

                    progress.setText('Processing item ' + proj.ProjId);

                }

                else

                {

                    throw warning('Rec id' + rec+ ' does not exist in the project transaction.' );

                }

                /*

                Suppose you want to insert the record from excel to inventory table in AX.

                then just declare the buffer in class declaration as : itemtable itemtab;

                And then you can write the below code from line 69 to 83.

               

                Code:

               

                select firstonly itemtab

                                    where itemtab.ItemId == any2str(cells.item(row, 1).value().bStr());

                                if (!itemtab)

                                {

                                    itemtab.itemid = any2str(cells.item(row, 1).value().bStr());

                                    itemtab.name = any2str(cells.item(row, 2).value().bStr());

                                    itemtab.insert();

                */

            }

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

        }

        while (type != COMVariantType::VT_EMPTY);

        ttscommit;

    }

    catch (Exception::Error)

    {

        workbooks.close();

        CodeAccessPermission::revertAssert();

        application.quit();

        ttsabort;

    }

    workbooks.close();

    CodeAccessPermission::revertAssert();

    application.quit();

    //element.close();

}