How to upload the (.xls) excel format to AX through x++

Create new class to upload the (.xls) excel sheet format to AX 2012 and use the below to upload the customized fields to the Item master. Here i have create the new fields in Inventtable, through this below X++ coding you can upload the created data.

static void ExcelImportAGDQty(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogFileName;
    Filename                filename;
    SysOperationProgress    simpleProgress;
    Container               filterCriteria;



    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    COMVariantType          type;
    int                     row = 1;

    Qty                         qty;
    InventTable             inventTable;
    ItemName                ItemName;
    ItemId                      itemId;
    ItemgroupID     ItemgroupID;
    PurchUOM     PurchUOM;
    PurchPrice     Purchprice;
    SalesUOM     SalesUOM;
    SalesPrice     SalesPrice;
    BOMUOM     BOMUOM;
    ModelGroupID     ModelGroupID;
    SACCode     SACCode;
    HSNCode     HSNCode;

    #File
    #avifiles
    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    dialog = new Dialog("Importing Excel File");
    dialogFileName = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
    filterCriteria = ['*.xls'];
    filterCriteria = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();

    if (dialog.run())
        filename = dialogFileName.value();

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error(strFmt("File cannot be opened."));
    }

    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();


    do
    {
        row++;
        itemId = cells.item(row, 1).value().bStr();
Qty = cells.qty(row,2).value().bstr();
ItemGroupID = cells.item(row,3).value().bstr();
PurchUOM = cells.item(row,4).value().bstr();
PurchPrice = cells.item(row,5).value().bstr();
SalesUOM = cells.item(row,6).value().bstr();
SalesPrice = cells.item(row,7).value().bstr();
BOMUOM = cells.item(row,8).value().bstr();
ModelGroupID = cells.item(row,9).value().bstr();
SACCode = cells.item(row,10).value().bstr();
HSNCode = cells.item(row,11).value().bstr();

        select inventTable where inventTable.ItemId == itemId;

        if(inventTable)
        {
             ttsbegin;
             select for update ItemId where ItemId.InventTable == inventTable.ItemID;
             if(ItemID)
             {
                          qty = cells.item(row, 2).value().double();
                          ItemID.Qty = qty;
                          Qty.update();
ItemID.PurchUOM = PurchUOM;
//  PurchUOM.update();
ItemID.Purchprice = Purchprice;
// PurchPrice.update();
ItemID.SalesUOM = SalesUOM;
// SalesUOM.update();
ItemID.Salesprice = Salesprice;
// Salesprice.update();
ItemID.BOMUOM = BOMUOM;
// BOMUOM.update();
ItemID.ModelGroupID = ModelgroupID;
//  ModelgroupID.update();
ItemID.SACCode = SACCode;
//  SACCode.update();
ItemID.HSNCode = HSNCode;
//  HSNCode.update();
ItemID.update();
             }
             else
             {
                          ItemID.clear();
                          ItemID.ItemId = inventTable.ItemId;
                          ItemID.Qty = qty;
ItemID.ItemGroupID = ItemGroupID;
ItemID.PurchUOM = PurchUOM;
ItemID.Purchprice = Purchprice;
ItemID.SalesUOM = SalesUOM;
ItemID.Salesprice = Salesprice;
ItemID.BOMUOM = BOMUOM;
ItemID.ModelgroupID = ModelgroupID;
ItemID.SACCode = SACCode;
ItemID.HSNCode = HSNCode;
                        ItemID.insert();
            }
             ttscommit;
        }


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

    application.quit();
}

2 comments:

  1. Howdy! I know this is somewhat off topic but I was wondering which blog platform are you using for this website?

    ReplyDelete