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;

#Excel

// 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(_cv.date(),123,2,1,2,1,4);

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

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

;

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

dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle(“Upload from Excel”);
dialog.caption(“Excel Upload”);

dialogFilename.value(filename);

if(!dialog.run())
return;

filename            =   dialogFilename.value();

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.itemFromNum(1);
cells               =   worksheet.cells();

try
{
ttsbegin;

do
{
row++;

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;

if(!_InventSize)
{
_InventSize.InventSizeId     =      InventSize;
_InventSize.ItemId           =      itemid;
_InventSize.insert();
}
else
{
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;
_InventBatch.insert();

// Insert into InventSerial Table

_InventSerial.InventSerialId    = srNo;
_InventSerial.ItemId            = itemid;
_InventSerial.ProdDate          = poddate;
_InventSerial.insert();

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

}

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

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

application.quit();

ttscommit;
}
catch
{
Error(“Upload Failed”);
}

}

These are the details i used to import from Excel.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: