Monday, July 8, 2019

How to read excel and update record in AX2012 :

static void POCUpdatePurchaseDetails_JS(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    Dialog dialog = new     Dialog("Excel upload utility");
    dialogField             dialogFilename;
    int row;
    int                     totalUpdatedRecord = 0;
    FileName                filename;
    InventTable             inventTable;
    ItemId                  itemId;
    ItemGroup               itemGroup;
    InventTableModule       inventTableModule;

    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"@SYS53125");

    dialog.filenameLookupTitle("Update data from excel file");
    dialog.filenameLookupFilter(["@SYS28576",'*XLSX', "@SYS28576",'*XLS']);
    if (!dialog.run())
        return;
    filename = dialogFilename.value();
    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();
    row=1;
    do
    {
        row++;
        itemId = cells.item(row, 1).value().bStr();

        ttsbegin;

        inventTable = InventTable::find(itemId,true);
        inventTableModule=InventTableModule::find(inventTable.ItemId,ModuleInventPurchSales::Purch,true);
        if (inventTable)
        {
            inventTableModule.Price=str2num(cells.item(row, 2).value().bStr());
            inventTableModule.PriceUnit=str2num(cells.item(row, 3).value().bStr());
            inventTableModule.update();
            totalUpdatedRecord++;
        }

        ttscommit;
        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();

    info(strFmt('Data updated, total record=%1 record',totalUpdatedRecord));

}

1 comment: