I have a code snippet for the Import excel which is used to work in from AX 4.0 version
static void Job803(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
System.DateTime ShlefDate;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
int row=0;
//Table Declarations Starts
GxyStructField GxyStructFieldLoc;
GxyFieldId GxyFieldIdLoc;
LineNum lineNum;
//Table Declartions Ends
#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();
do
{
try
{
ttsbegin;
row++;
GxyFieldIdLoc = COMVariant2Str(cells.item(row,1).value());
if(row > 1)
{
//Insert into Table
select firstonly GxyStructFieldLoc
where GxyStructFieldLoc.GxyStructId == "D365 Customers V3"
&& GxyStructFieldLoc.GxyStructLineId == "CustTable" ;
if(!GxyStructFieldLoc)
{
GxyStructFieldLoc.initValue();
GxyStructFieldLoc.GxyFieldId = GxyFieldIdLoc;
GxyStructFieldLoc.insert();
}
else
{
warning(strfmt("Record already exists %1 : ",GxyStructFieldLoc.GxyFieldId));
}
}
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("uploaded successfully"));
application.quit();
}
Category: Uncategorized