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:

Untitled

Current Excel file format:

Untitled

 

 

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()));
    }
}
Advertisements

AX 2012: Setting Labels in X++

To set Label property of all the fields of a table in AOT, you may refer to the following code which loops through all the fields of a table and sets their Label property with their Name property.

static void MAKSetLabels(Args _args)
{
    #Properties
    SysDictTable    dictTable = new SysDictTable(tableNum(CustTable));
    SysDictField    dictField;
    TreeNode        treeNode;
    FieldId         fieldId = dictTable.fieldNext(0);
    str             fieldName;
    

    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);

        if (dictField.isSql() && !dictField.isSystem())
        {
            treeNode = dictField.treeNode();
            
            fieldName = treeNode.AOTgetProperty(#PropertyName);
            treeNode.AOTsetProperty(#PropertyLabel, fieldName);
            treeNode.AOTsave();
            
            info(strFmt("Field name: %1 | Field label: %2",
                treeNode.AOTgetProperty(#PropertyName),         //Field name
                treeNode.AOTgetProperty(#PropertyLabel)));      //Field label
            
            treeNode.treeNodeRelease();
            treeNode = null;
        }

        fieldId = dictTable.fieldNext(fieldId);
    }
}

AX 2012: Sales Order Posting Custom Validations

It is often a customer requirement to check for custom validations before posting a Sales Order. The best place to put your code for custom validations can be found below:

Validations for Sales Table:

AOT > Classes > SalesFormLetterProvider > checkHeading()

For example,

To check for custom field SalesTable.MAKBlocked, this is how custom validation should be added in checkHeading method. You may refer to Line# to know exactly where I have placed the code.

Untitled

Validations for Sales Line:

AOT > Classes > SalesFormLetterProvider > checkLines()

For example,

To check for custom field SalesLine.MAKBlocked, this is how custom validation should be added in checkLines method. You may refer to Line# to know exactly where I have placed the code.

Untitled