AX import data from a excel file

This method must be based on a form, and using in the system enviroment 'windows  2008' may face compatibility error.

void clicked()
{
    SysExcelApplication             Excel;
    SysExcelWorkbooks               Books;
    SysExcelWorkbook                Book;
    SysExcelWorksheets              Sheets;
    SysExcelWorksheet               Sheet;
    SysExcelCells                   Cells;
    SysExcelCell                    Cell;
    SysExcelStyles                  Styles;
    SysExcelStyle                   Style;
    int                             RowCount,RecNum,n,i;
    boolean                         isContinue = true;

    Filename                        _FilenameOpen;
    ConfigTable                     ConfigTable;

    ItemId                          ItemId;
    ConfigId                        ConfigId;
    InventLocationId                InventLocationId;
    inventBatchId                   inventBatchId;
    wMSLocationId                   wMSLocationId;
    Qty                             Qty;
    InventDimId                     InventDimId;

    str                             a_ItemId[],a_ConfigId[],a_WareHouse[],a_Batch[],a_Location[];
    real                            a_Counted[];

    Dialog                          _Dialog;
    DialogField                     dlgPath;
    System.Exception                ex;
    #WINAPI
    FileNameFilter                  filter = ['Excel Files','*.xls;*.xlsx'];//['Image Files','*.bmp;*.jpg;*.gif;*.jpeg'];
    ;

    try
    {
        _FilenameOpen = Winapi::getOpenFileName(element.hWnd(),filter,WinAPI::getFolderPath(#CSIDL_Personal), "@SYS53008", '','');
        if(!_FilenameOpen)
            return ;
        if(!box::yesNo("Are you sure Import to AX 2009?",DialogButton::No,'Import to AX2009'))
            return ;

        Excel = SysExcelApplication::construct();
        Books = Excel.workbooks();
        Books.open(_FilenameOpen,true);

        Book   = Books.item(1);
        Sheets = Book.worksheets();
        Sheet  = Sheets.itemFromNum(1);
        Cells  = Sheet.cells();

        RowCount =   2;     //from second line fect data

        InventJournalTrans_DS.delete();

        for(RowCount = 2;Cells.item(RowCount,1).value().bStr() != "";RowCount++)
        {
            n = 1;

            ItemId           = Cells.item(RowCount,n).value().bStr();    n++;
            ConfigId         = Cells.item(RowCount,n).value().bStr();    n++;
            InventLocationId = Cells.item(RowCount,n).value().bStr();    n++;
            inventBatchId    = Cells.item(RowCount,n).value().bStr();    n++;
            wMSLocationId    = Cells.item(RowCount,n).value().bStr();    n++;
            Qty              = Cells.item(RowCount,n).value().double();

            RecNum++;

            a_ItemId[RecNum]    = ItemId;
            a_ConfigId[RecNum]  = ConfigId;
            a_WareHouse[RecNum] = InventLocationId;
            a_Batch[RecNum]     = inventBatchId;
            a_Location[RecNum]  = wMSLocationId;
            a_Counted[RecNum]   = Qty;
        }
    }
    catch(Exception::CLRError)
    {
        Excel.quit();
        ex = CLRInterop::getLastException();
        while( ex )
        {
            info( ex.get_Message() );
            ex = ex.get_InnerException();
        }
    }
    catch(Exception::Error)
    {
        Excel.quit();
        global::exceptionTextFallThrough();
    }
    Excel.quit();

    for(i = 1;i <= RecNum;i++)
    {
        InventJournalTrans_DS.create();
        InventJournalTrans.ItemId  = a_ItemId[i];
        journalFormTrans.fieldModifiedItemIdPost();

        InventDim.configId         = a_ConfigId[i];
        InventDim.InventLocationId = a_WareHouse[i];
        InventDim.inventBatchId    = a_Batch[i];
        InventDim.wMSLocationId    = a_Location[i];
        journalFormTrans.fieldModifiedInventDimFieldsPost(InventDim,fieldnum(InventDim,wMSLocationId));
        InventDimId                = InventDim::findOrCreate(InventDim).inventDimId;

        InventDim.inventDimId           = InventDimId;
        InventJournalTrans.InventDimId  = InventDimId;

        InventJournalTrans.Counted      = a_Counted[i];
        journalFormTrans.fieldModifiedCountedPost();
        journalFormTrans.fieldModifiedItemIdPost();
        journalFormTrans.fieldModifiedCostAmountPost();

        InventJournalTrans_DS.write();
    }

    info(strfmt("Import into total of %1 records!",RecNum));
}
posted @ 2012-08-28 09:49  Sprite.z  Views(663)  Comments(0Edit  收藏  举报