blog for Dynamics Axapta

Here i post code for Import Excel Data into Dynamics using X++ code.

Here the code written in Command Button clicked event, and also i  added the Excel format below of this post which i used.

void clicked()
SysExcelApplication             application;
SysExcelWorkbooks               workbooks;
SysExcelWorkbook                workbook;
SysExcelWorksheets              worksheets;
SysExcelWorksheet               worksheet;
SysExcelCells                        cells;
COMVariantType                 type;
System.DateTime                  ShlefDate;

FilenameOpen                     filename;
dialogField                          dialogFilename;

Dialog                               dialog;

//Table Declarations Starts

InventSize                      _InventSize;
InventBatch                    _InventBatch;
InventSerial                    _InventSerial;
InventTable                   _InventTable;
VendParameters              _vendParameters;

//Table Declartions Ends

InventBatchId                   batchNumber;
InventBatchExpDate              expdate;
itemId                          itemid;
TransDate                       poddate;
CertificatesofSterilization  Certs;
CertificatesofAnalysis     CertiAnalysis;
InventSizeId                    InventSize;
ConfigId                        _ConfigId;
InventColorId                   _InventColorId;
InventSiteId                    _InventSiteId;
WMSLocationId                   _WMSLocationId;
InventLocationId                _InventLocationId;
WMSPalletId                     _WMSPalletId;
NoYesId                         ClosedTransactions;
NoYesId                         ClosedTransQty;
str                             pONo;
str                             srNo;
real                            quantity;
int                             row;
InventBatchExpDate              ShelfLifeDate;


// convert into str from excel cell value

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
switch (_cv.variantType())
case (COMVariantType::VT_BSTR):
return _cv.bStr();

case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DATE):
return date2str(,123,2,1,2,1,4);

case (COMVariantType::VT_EMPTY):
return “”;

throw error(strfmt(“@SYS26908″, _cv.variantType()));
return “”;


dialog              =   new Dialog(“Excel Upoad”);
dialogFilename      =   dialog.addField(typeId(FilenameOpen));

dialog.filenameLookupTitle(“Upload from Excel”);
dialog.caption(“Excel Upload”);



filename            =   dialogFilename.value();

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

catch (Exception::Error)
throw error(“File cannot be opened.”);

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



pONo                    =   COMVariant2Str(cells.item(row, 1).value());
itemid                  =   COMVariant2Str(cells.item(row,2).value());
InventSize              =   COMVariant2Str(cells.item(row, 3).value());
batchNumber             =   COMVariant2Str(cells.item(row, 4).value());
poddate                 =   cells.item(row, 5).value().date();
expdate                 =   cells.item(row, 6).value().date();
srNo                    =   COMVariant2Str(cells.item(row, 7).value());
Certs                =   str2enum(Certs,cells.item(row, 8).value().bStr());
CertiAnalysis        =   str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());
quantity                =   cells.item(row, 10).value().double();
_ConfigId               =   COMVariant2Str(cells.item(row, 12).value());
_InventColorId          =   COMVariant2Str(cells.item(row, 13).value());
_InventSiteId           =   COMVariant2Str(cells.item(row, 14).value());
_WMSLocationId          =   COMVariant2Str(cells.item(row, 15).value());
_InventLocationId       =   COMVariant2Str(cells.item(row, 16).value());
_WMSPalletId            =   COMVariant2Str(cells.item(row, 17).value());
ClosedTransactions      =   str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());
ClosedTransQty          =   str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());

if(row > 1)

//Insert into InventSize Table

select firstonly _InventSize where _InventSize.ItemId == itemid && _InventSize.InventSizeId == InventSize;

_InventSize.InventSizeId     =      InventSize;
_InventSize.ItemId           =      itemid;
warning(strfmt(“Item Id and InventSize (%1   –   %2) already exists”,itemid,InventSize));

// Insert into InventBatch Table

_InventBatch.inventBatchId      =       batchNumber;
_InventBatch.itemId             =       itemid;
_InventBatch.prodDate           =       poddate;
_InventBatch.expDate            =       expdate;

// Insert into InventSerial Table

_InventSerial.InventSerialId    = srNo;
_InventSerial.ItemId            = itemid;
_InventSerial.ProdDate          = poddate;

info(strfmt(“Item(%1) uploaded successfully”,itemid));


type = cells.item(row+1, 1).value().variantType();

}while (type != COMVariantType::VT_EMPTY);


Error(“Upload Failed”);


These are the details i used to import from Excel.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: