AX 2012: Off-The-Shelf Excel Reader

Download it, adapt it, run it!

 

Please find above the project XPO of the Off-the-shelf Excel reader. To adapt and run it for your Excel file format:

1. Update macros in the class declaration to match your Excel file columns.
2. Update readExcelFile() method to use your table buffer to persist records in.
3. Click Menu Items > Action > MAKExcelReader to run it.

Project overview:

Current Excel file format:

 

 

Deep dive into Class methods:

class declaration:

class MAKExcelReader
{
    Filename            fileName;

    SysExcelApplication application;
    SysExcelWorkbooks   workbooks;
    SysExcelWorkbook    workbook;
    SysExcelWorksheets  worksheets;
    SysExcelWorksheet   worksheet;
    SysExcelCells       cells;
    COMVariantType      type;

    RecordInsertList    recordInsertList;

    #Define.colCustID(1)
    #Define.colCustName(2)
    #Define.colCustCountry(3)
    #Define.colProductSold(4)
    #Define.colSalesChannel(5)
    #Define.colUnitsSold(6)
    #Define.colDateSold(7)
}

main:

static void main(Args _args)
{
    MAKExcelReader  makExcelReader;

    makExcelReader = new MAKExcelReader();
    makExcelReader.run();
}

run:

public void run()
{
    int recordCount;

    this.openFileDialog();
    this.initExcelApplication();
    recordCount = this.readExcelFile();
    this.finalizeExcelApplication();
    this.persistRecords();

    info(strFmt("Successfully read %1 rows.", recordCount));
}

openFileDialog:

private void openFileDialog()
{
    Dialog          dialog;
    DialogField     dialogField;

    dialog = new Dialog("Import Excel");
    dialogfield = dialog.addField(extendedtypestr(FilenameOpen));
    dialog.run();

    if (dialog.run())
    {
        fileName = (dialogfield.value());
    }
}

initExcelApplication:

private boolean initExcelApplication()
{
    boolean ret = false;

    try
    {
        application = SysExcelApplication::construct();
        workbooks = application.workbooks();
        workbooks.open(fileName);
        workbook = workbooks.item(1);
        worksheets = workbook.worksheets();
        worksheet = worksheets.itemFromNum(1);
        cells = worksheet.cells();

        ret = true;
    }
    catch (Exception::Error)
    {
        throw error("Exception occured in reading file.");
    }

    //Return rows read excluding header row
    return ret;
}

readExcelFile:

private int readExcelFile()
{
    int             row = 1;
    boolean         EOF;
    MAKSalesTable   makSalesTable;

    recordInsertList = new RecordInsertList(tableNum(MAKSalesTable));

    try
    {
        do
        {
            row++;

            makSalesTable.clear();
            makSalesTable.CustID = this.getCellValue(cells.item(row, #colCustID));
            makSalesTable.CustName = this.getCellValue(cells.item(row, #colCustName));
            makSalesTable.CustCountry = this.getCellValue(cells.item(row, #colCustCountry));
            makSalesTable.ProductSold = this.getCellValue(cells.item(row, #colProductSold));
            makSalesTable.SalesChannel = this.getCellValue(cells.item(row, #colSalesChannel));
            makSalesTable.SalesQty = this.getCellValue(cells.item(row, #colUnitsSold));
            makSalesTable.TransDate = this.getCellValue(cells.item(row, #colDateSold));

            recordInsertList.add(makSalesTable);
            EOF = this.checkEOF(cells, row + 1);
        }
        while (!EOF);
    }
    catch
    {
        error(strFmt("Failed to read row# %1.",row));
    }

    return (row-1);
}

getCellValue:

private anytype getCellValue(SysExcelCell _cell)
{
    anytype retVal;

    switch(_cell.value().variantType())
    {
        case COMVariantType::VT_DATE:
            retVal = _cell.value().date();
            break;

        case COMVariantType::VT_BSTR:
            retVal = strFmt("%1", _cell.value().bStr());
            break;

        case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4:
            retVal = strFmt("%1", any2int(_cell.value().double()));
            break;

        case COMVariantType::VT_R8:
            retVal = _cell.value().double();
            break;

        case COMVariantType::VT_CY:
            retVal = _cell.value().currency();
            break;

        case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
            retVal = strFmt("%1", _cell.value().int());
            break;

        case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
            retVal = strFmt("%1", _cell.value().uLong());
            break;

        case COMVariantType::VT_EMPTY:
            retVal = '';
            break;

        default:
            throw error(strfmt('Unhandled variant type (%1).', _cell.value().variantType()));
    }

    return retVal;
}

checkEOF:

private boolean checkEOF(SysExcelCells _cells, int _row)
{
    boolean ret;

    if (_cells.item(_row, #colCustID).value().variantType() == COMVariantType::VT_EMPTY)
    {
        ret = true;
    }

    return ret;
}

finalizeExcelApplication:

private void finalizeExcelApplication()
{
    workbook.close(true);
    application.quit();
}

persistRecords:

private void persistRecords()
{
    try
    {
        ttsBegin;
        recordInsertList.insertDatabase();
        ttsCommit;
    }
    catch
    {
        error(strFmt("An exception occured while persisting records in %1", funcName()));
    }
}