Friday, March 20, 2015

Dynamics AX 2012: Use OpenXML SDK for Excel Export - Part 3

In Part 2, a sample project exporting data from AX to Excel is presented. In this post I'll talk about a few tips I learned.

1) Finding available OpenXML library
Chances is, the code you write will work on either OpenXML 2.0 or OpenXML 2.5. It'd be nice that the code will run as long as one of them is available on the machine. Add the following code to load the available library during runtime.

In the constructor of the export class, subscribe to the AssemblyResolve event.
public ExcelExport()
    AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(helper.CurrentDomain_AssemblyResolve);
//    ...

Implement the event handler.
public static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
    switch (args.Name)
        case "DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35":
            // Try to load ver 2.0 if 2.5 is not found.
            return Assembly.Load("DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35");
        case "DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35":                    
            // OpenXML2.0 and OpenXML2.5 both unavailable, throw error
            throw new Exception("Can't find suitable OpenXML libraries for loading.");

2) Debug using Visual studio.
Use a conditional statement to create a Session object if necessary. This allow you to run the code in Visual studio without having to create a job in AX to start debugging. It's much more convenient. Testing from AX can start after the managed code part is tested and ready.

3) Use AX Label
It is possible to use AX labels by pulling the SysLabel class to the project and write a simple helper method to use it.
// Get Labels
public static string GetLabel(string labelId, string langId = "en-us")
    return SysLabel.labelId2String(labelId, langId);

4) Linq to AX limitation
I tend to use Linq to AX in conjunction with OpenXML. Check out this post by Joris de Gruyter for his comments on, and especially some limitations of, Linq to AX.

5) Assembly hot swapping
When developing managed code solution the AOS assemblies hot swap flag is usually turned on. Remember to disable this flag if you are to compile the application with AXBuild to avoid issues.

In the next post, I'll talk about tools which helps developing OpenXML solutions.

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

No comments:

Post a Comment