Import Data into AX from excel file
static void DataImportToAXFromExcel(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorksheets xlsWorkSheetCollection;
SysExcelWorksheet xlsWorkSheet;
SysExcelRange xlsRange;
SysExcelCells Cells;
SysExcelCell RCell;
CommaIO inFile;
int nRow,i;
DialogField dialogPath;
Dialog dialog;
Filename filename;
CustTable custTable;
CustAccount custAccount;
CustGroupId custGroupId;
CurrencyCode currencyCode;
;
dialog = new Dialog(“Import”);
dialogPath = dialog.addField(typeid(Filenameopen), “File Name”);
dialog.run();
if (dialog.run())
{
filename = (dialogPath.value());
}
inFile = new CommaIO (filename, ‘R’);
if (!inFile || infile.status() != IO_Status::Ok )
{
throw error (strfmt(“@SYS19312”,filename));
}
try
{
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBookCollection.open(filename);
xlsWorkSheetCollection = xlsApplication.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
Cells = xlsWorkSheet.Cells();
nRow = 2;
RCell = Cells.Item(nRow, 1);
while(RCell.value().bStr() != “”)
{
custAccount = RCell.value().bStr();
RCell = Cells.item(nRow,2);
custGroupId = RCell.value().bStr();
RCell = Cells.item(nRow,3);
currencyCode = RCell.value().bStr();
if(!CustTable::exist(custAccount))
{
if(CustGroup::exist(custGroupId) && Currency::exist(currencyCode))
{
custTable.initValue();
custTable.AccountNum = custAccount;
custTable.CustGroup = custGroupId;
custTable.Currency = currencyCode;
custTable.insert();
}
}
nRow++;
RCell = Cells.Item(nRow, 1);
}
xlsApplication.quit ();
xlsApplication.finalize ();
info(“Imported completed”);
}
catch( Exception::Error)
{
//Close Excel.
xlsApplication.quit ();
xlsApplication.finalize ();
ttsabort;
info(“Unable to process the excel import “);
}
}