Import from Excel file using X++ in AX 2009

//Apply this method in any Button Override Method.
// This method is used to Import the Excel Data to AX Table
//
//
// Excel File Contains 3 Columns which is EquipmentId, Cost, EffectiveDate
// Same way here we have created Code For the particular Three field....
public void ImportData()
{
 SysExcelApplication application;
 SysExcelWorkbooks workbooks;
 SysExcelWorkbook workbook;
 SysExcelWorksheets worksheets;
 SysExcelWorksheet worksheet;
 SysExcelCells cells;
 COMVariantType type;
 System.DateTime ShlefDate;
 FilenameOpen filename;
 dialogField dialogFilename;
 Dialog dialog;
 #AviFiles
 // Progress Bar
 SysOperationProgress progress = new SysOperationProgress(1, NoYes::Yes);
 TestXls _TestXls; // Table name and decleration
 str equipId;
 str cost;
 str effectiveDate;
 int datevalue, row = 0;
 real costr;
 Date dater;
 #Excel
 str COMVariant2Str(COMVariant _cv,
 int _decimals = 1,
 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("ExcelUpload");
 dialogFilename = dialog.addField(typeId(FilenameOpen));
 dialog.filenameLookupFilter(["@SYS28576",#XLS, "@SYS28576",#XLSX]);
 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();
 // Progress Caption & Animation
 progress.setCaption("Copying..");
 progress.setAnimation(#AviUpdate);
 do
 {
 try
 {
 ttsbegin;
 row++;
 // Getting EquipmentId value form Excel Row wise and assigning as str
 equipId = COMVariant2Str(cells.item(row,1).value());
 // Getting Cost value form Excel Row wise and assigning as str
 cost = COMVariant2Str(cells.item(row,2).value());
 // Converting str to Real
 costr = any2Real(cost);
 // Getting Date value form Excel Row wise and assigning as str
 effectiveDate = COMVariant2Str(cells.item(row,3).value());
 // Converting String to Date
 dater = str2Date(effectiveDate,123);
 // While Revecing datas from Excel Sheet If invalid value presents it wil not allowe to insert and the
 // last record also deleted
 if (!equipId || !dater || costr==0.00)
 {
 ttsbegin;
 TestXls.delete();
 ttscommit;
 box::warning(strfmt("Check the value in Excel Sheet row %1", row));
 return;
 }
 else
 {
 if (row > 1)
 {
 // Progress bar Text and Total
 progress.setText(strfmt("Importing to Ax Table : %1", row));
 progress.setTotal(row, 1);
 _TestXls.initValue();
 _TestXls.EquipId = equipId;
 _TestXls.Cost = costr;
 _TestXls.EffectiveDate = dater;
 _TestXls.insert();
 }
 }
 ttscommit;
 }
 catch
 {
 Error(strfmt("Upload Failed in row %1", row));
 }
 type = cells.item(row + 1, 1).value().variantType();
 } while (type!= COMVariantType::VT_EMPTY);
 info(strfmt("Equipments Details Uploaded Successfully"));
 application.quit();
}
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