Dynamics AX 2012: Use OpenXMLSDK for Excel Export - Part 2

Part 1 of the series introduced OpenXML. This post will provide an example how to use OpenXML library to export data from AX. (Full example can be downloaded here.)

First of all, a Session object is needed. It will be available if the method is called from AX. However, it is much easier/faster to test/debug using Visual studio alone. Therefore, add the following code to create a Session if necessary.

            
// Create a Session object and login if necessary
if (MIL.Session.Current == null)
{
    MIL.Session axSession = new MIL.Session();
    axSession.Logon(null, null, null, null);
}

The next thing is the SharedStringTable. They help reduce redundancy when the same Text value appears more than once in the workbook. A good post here by Tim Murphy explains it well.

// Get the SharedStringTablePart. If it does not exist, create a new one. 
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
    shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
    shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}

Then comes the interesting part. I used Linq to AX to get a list of Customers order by AccountNum. Then this list is parsed one by one and their values put into the worksheet.
There are the three helper methods here.

• The InsertSharedStringItem takes a string value and return an index that represent the passed in string. The index returned will be assigned to the CellValue property of the Cell object.
• The GetCellInWorksheet method takes in colPos and rowPos, among other parameters, and returned the proper Cell object. It will create a new Cell object if it doesn't already exists.
  Then, the index value is assigned to CellValue property and DateType property is set to SharedString.
• The MergeAdjCells method merge cells in the worksheet. Download the source code to see the implementation.

private void InsertTableData()
{
    QueryProvider provider = new AXQueryProvider(null);
    var custTableQuery = new QueryCollection<CustTable>(provider);
    var dirPartyQuery = new QueryCollection<DirPartyTable>(provider);
    var customerList = from ct in custTableQuery
                       join dp in dirPartyQuery
                       on ct.Party equals dp.RecId
                       orderby ct.AccountNum ascending
                       select new { ct.AccountNum, ct.CustGroup, dp.Name };

    foreach (var item in customerList)
    {
        InsertData(item.AccountNum, "A");
        InsertData(item.Name, "B");
        InsertData(item.CustGroup, "C");
        helper.MergeAdjCells(worksheetpart, new List<string>() { "C" + rowPos.ToString(), "D" + rowPos.ToString() });
        rowPos++;
    }
}

private void InsertData(string value, string colPos, uint borderIndex = 0)
{
    int index = helper.InsertSharedStringItem(value, shareStringPart);
    Cell newCell = helper.GetCellInWorksheet(colPos, rowPos, worksheetpart, true, false);
    newCell.CellValue = new CellValue(index.ToString());
    newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    newCell.StyleIndex = borderIndex;
}

The exported file looks like this:

The full example can be downloaded here. You probably need to fix some project references before it'd work. The solution is created in VS2010 against an AX2012 R3 environment. Set AxOpenXML_Part2 as the startup project and hit F5 to see the result. This post have not covered all the details but hopefully enough to get you started.

In the next post, I'll talk about some tips I've learned working with OpenXML and AX.

This posting is provided "AS IS" with no warranties, and confers no rights.