Skip to content

Import Excel Using X++ for AX 4.0

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();
}
Published inTechnical Blog