How to read an Excel file

In this post, i will explain how to read an Excel file and write data from rows into AX system.

Below job opens a dialog box and lets the user to select an excel file and then reads the excel starting from first row and transfers the cell data to a variable in Axapta.

When the excel finished, application quits.

static void readFromExcel(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    CommaIO                 comio;
    Container               line;
    FileName                filename;
    
    int                     row;
    ItemId                  exItemId;
    ItemName                exItemName;

    Dialog                  dialog = new Dialog("Select File and Path");
    DialogField             DFPath;

    ;

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

    DFPath          = dialog.addField(extendedTypeStr(FilenameSave));
    if (dialog.run())
    {
        filename = DFPath.value();
    
        try
        {
            workbooks.open(filename);
        }
        catch (Exception::Error)
        {
            throw error("File could not be opened!");
        }

        workbook        = workbooks.item(1);
        worksheets      = workbook.worksheets();
        worksheet       = worksheets.itemFromNum(1);
        cells           = worksheet.cells();
    
        do
        {
            row++; // Pass the headers row 
            
            exItemId    = cells.item(row, 1).value().bStr();
            exItemName  = cells.item(row,2).value().bStr();
            
            info(strfmt("%1 - %2", exItemId, exItemName));
            type = cells.item(row+1, 1).value().variantType(); // Control the excel if there are more rows or not
        }
        while (type != COMVariantType::VT_EMPTY);
    
        application.quit();
    }
}