Excel Helper class for AX

I often find it useful to give users the ability to export and import defined-format Excel files throughout AX.  For example, I'll put Export and Import buttons on the CustTable form that allows the user to do quick mass-updates of some set of fields that often needs updating.  There are more out-of-the-box ways to do this, but you all know that sometimes a simple bit of custom code goes a long way in satisfying a user.

The sysExcel* classes (SysExcelApplication, SysExcelWorkbooks, SysExcelWorkbook, SysExcelWorkSheets, SysExcelWorkSheet, SysExcelCells, Com, COMVariant) are fairly straight forward to use, but since I'm usually using the same few basic functions, I took the time to create a little helper class.

The ExcelHelper class is available for you to download at the end of this post.  The example code below is also included in the class.

Imports
I might know a file name, but more often I just want to display a File Open dialog box for the user to select their file.  I usually want to include some basic instructions about what kind of file they can import (i.e. what columns).  Below is some example import code using the ExcelHelper class:

private static void exampleOfUseForImport()
{
    ExcelHelper                 ExcelHelper;
    str                         dialogText;
    int                         lastRow, row;
    exampleTable                exampleTable;
    int                         recCount;
    ;
    ExcelHelper = new ExcelHelper();

    dialogText = strfmt("Excel file with no header row.   Columns: Customer Account, Item Id, Quantity, Ship Date, Purchase Order";

    if (ExcelHelper.openExcelFile("",dialogText))  //when no file name is passed, an open file dialog box will be displayed
    {
        global::startLengthyOperation();

        lastRow = ExcelHelper.findLastRow();

        for (row = 1; row <= lastRow; row++)
        {
            exampleTable.clear();
            exampleTable.CustAccount = ExcelHelper.readValue(row,1);
            exampleTable.ItemId = ExcelHelper.readValue(row,2);
            exampleTable.Qty = str2num(ExcelHelper.readValue(row,3));
            if (ExcelHelper.readValue(row,4) != "")
            {
                exampleTable.ShipDate= str2date(ExcelHelper.readValue(row,4),123);
            }
            exampleTable.PurchaseOrder = ExcelHelper.readValue(row,5);
            exampleTable.insert();
            recCount++;
        }
        global::endLengthyOperation();

        ExcelHelper.closeExcelFile();

        box::info(strfmt("%1 records inserted",recCount),"Import Results");
    }
}

Exports
To make exports as easy to use as possible, I just open Excel with the exported data in an unsaved worksheet.  The user can then use the standard Excel menus to save the file.  Below is some example export code using the ExcelHelper class:

private static void exampleOfUseForExport(CustInvoiceAccount _invoiceAccount)
{
    custTable                   custTable;
    ExcelHelper                 ExcelHelper;
    int                         row;
    ;
    ExcelHelper = new ExcelHelper();
    ExcelHelper.openBlankExcelFile();

    ExcelHelper.formatAsText('A:A');

    ExcelHelper.setValue(1,1,"Customer Account");
    ExcelHelper.setValue(1,2,"Customer Name");

    row = 2;

    global::startLengthyOperation();

    while select custTable
        where custTable.InvoiceAccount == _invoiceAccount
    {
        ExcelHelper.setValue(row,1,custTable.AccountNum);
        ExcelHelper.setValue(row,2,custTable.Name);
        row ++;
    }

    global::endLengthyOperation();

    ExcelHelper.showExcel();
}

There are plenty of more advanced things that you can do with the SysExcel classes, but the stuff that I've included in the ExcelHelper class seems adequate for the majority of my needs.

Here is the class:
Class_ExcelHelper.xpo (google docs - shortened via goo.gl - 9kb)

There are a series of blog posts on Excel that I've found helpful.  Here is one:
http://patrikluca.blogspot.com/2010/04/export-to-excel-with-x-code-sequel.html