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