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

}







3 comments:

  1. Thanks vishal for this useful information

    ReplyDelete
    Replies
    1. Hi Dominic, Thank you! Keep following for more posts :)

      Delete
    2. Hi Vishal....How we can Merge 2 Cells in excel through X++ Code or else how we can increase the width of the Particular cells in the excel sheet, so that we can enter a number more than 13 digits in a cell. (While i am trying to write table Rec id's into excel sheet through X++ code it is showing as (1.23457E+13) , but the real value is (12345678912345)....

      Delete

Powered by Blogger.