Import record from MS Excel to AX2009 / AX2012

The below code will help you to import the Excel file in to your MS Dynamics AX 2009 / AX 2012.


static void ImportFromExcelTOAX(Args _args)

{

#AviFiles
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelWorkSheet workSheetInventTableModule;
SysExcelCells cellsInventTableModule;
SysExcelCells cells;
COMVariantType type;
COMVariantType typeModule;
int row;
FileName fileName;
InventTable inventTable;
InventTableModule tabmodule;
LedgerJournalTrans_Project proj;
ItemId itemId;
FileIoPermission perm;
int64 rec;



Dialog dialog = new dialog();
dialogField dialogFilename;

#define.FileMode('R')
;


fileName = "C:\\Users\\vishal\\Desktop\\Refrec1.xlsx";
perm = new FileIOPermission(FileName, #FileMode);
perm.assert();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(Filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromName('refer');
if (worksheet)
{
cells = worksheet.cells();
}
else
{
throw warning('File cannot be opened.');
}


progress.setCaption("Migrating ...");
progress.setAnimation(#AviTransfer);


try
{
ttsbegin;


do
{
row++;
if (row > 1)
{
rec = any2int64(cells.item(row, 1).value().double());


select firstonly forUpdate proj
where proj.RefRecId == rec;
if (proj)
{
proj.CostPrice = any2real(cells.item(row, 2).value().double());
proj.update();


progress.setText('Processing item ' + proj.ProjId);
}
else
{
throw warning('Rec id' + rec+ ' does not exist in the project transaction.' );
}


/*
Suppose you want to insert the record from excel to inventory table in AX.
then just declare the buffer in class declaration as : itemtable itemtab;
And then you can write the below code from line 69 to 83.

Code:

select firstonly itemtab
where itemtab.ItemId == any2str(cells.item(row, 1).value().bStr());
if (!itemtab)
{
itemtab.itemid = any2str(cells.item(row, 1).value().bStr());
itemtab.name = any2str(cells.item(row, 2).value().bStr());
itemtab.insert();


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


}


catch (Exception::Error)
{
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
}

workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();


//element.close();


}