Reading excel file with progress bar

Reading an excel is most common requirement you may come across while developing in Axapta world. This post will show the code for reading an excel file with progress bar indicator.

static void excelReadWithProgressBarJob(Args _args)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row, totalRowCount;
    int                     numberOfRecordsCleaned;
    int                     numberOfRecordsNotFound;
    Dialog                  dialog;
    DialogField             dialogField;
    Filename                filename;
    SysExcelRange           range;
    SysOperationProgress    progress = new SysOperationProgress();
    #Excel
    #AviFiles
    #define.Star('*')
    #define.Space(' ')
    
    VendAccount             vendAccount;
    VendTable               vendTableLocal;
    
    dialog = new Dialog("Caption of Dialog");
    dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), "Excel file");
    dialog.filenameLookupFilter(["@SYS28576", #XLSX, "@SYS28576", "*.xls"]);
    
    if (dialog.run())
    {
        filename = (dialogfield.value());
    }
    else
    {
        return;
    }
    progress.setCaption("Caption for progress bar");
    progress.setAnimation(#AviUpdate);    
    
    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();
    range = cells.range(#ExcelTotalRange);
    
    try
    {
        // Finds the row where the first contents is found.
        range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        totalRowCount = range.row();
        progress.setTotal(totalRowCount);
    }
    catch (Exception::Error)
    {
        error("@SYS59926");
        totalRowCount = 0;
        progress.setTotal(totalRowCount);
    }
    
    do
    {
        row++;
        //Vendor account
        switch(cells.item(row, 1).value().variantType())
        {
            case COMVariantType::VT_BSTR:
                vendAccount = strFmt("%1", cells.item(row, 1).value().bStr());
                break;
            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
                vendAccount = strFmt("%1", num2str(cells.item(row, 1).value().double(), -1, 0, DecimalSeparator::Auto, ThousandSeparator::None));
                break;
            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
                vendAccount = strFmt("%1", cells.item(row, 1).value().int());
                break;
            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
                vendAccount = strFmt("%1", cells.item(row, 1).value().uLong());
                break;
             case COMVariantType::VT_EMPTY:
                vendAccount = '';
                break;
            default:
                 throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 3).value().variantType()));
        }
        vendTableLocal = VendTable::find(vendAccount);
        if (vendTableLocal)
        {
            numberOfRecordsCleaned++;
        }
        else
        {
            numberOfRecordsNotFound++;
        }
        type = cells.item(row + 1, 1).value().variantType();
        progress.setText(strfmt("Vendor : %1 (%2 / %3)", vendAccount, row, totalRowCount));
        progress.setCount(row, 1);
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    info(strFmt('%1 / %2 number of records found', numberOfRecordsCleaned, row));
    info(strFmt('%1 / %2 number of records not found', numberOfRecordsNotFound, row));
}