Monday, March 23, 2015

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

This is the final post on my experience with OpenXML SDK. The previous posts are here: Part 1, Part 2, Part 3.

In this post I'll introduce the Open XML SDK Productivity tool and a couple of extended libraries for OpenXML.

Despite the benefits of OpenXML, there is also a learning curve as it is quite different from working with Excel object model. Luckily, there is help from Microsoft and other developers.

The Open XML SDK Productivity Tool
This is a tool provided by Microsoft to help developers create OpenXML solution. One of it's most useful feature is you can load up a document and get generated C# source code for creating that document.

Here's a link to an 8 mins video showcasing what the Tool does.
This tool can be downloaded for free from Microsoft. For example, here is the download link for the SDK and the tool for OpenXML SDK 2.5.

Wrappers based on OpenXML SDK
Custom libraries are available based on OpenXML SDK. These libraries aims at hiding away lower level details of using OpenXML SDK to simplify the creation of OpenXML documents, without sacrificing performance. I haven't used them as I stick with OpenXML SDK for personal reason. But many developers are finding them very helpful.

ClosedXML - An often updated wrapper for OpenXML, with detailed documentation.
Simple OOXML - adds the DocumentFormat.OpenXml.Extensions namespace to version 2.0 of the Open Office SDK.

So this concluded my series of OpenXML post. For anyone who haven't used OpenXML SDK before I hope these posts encouraged you to give it a try!
This posting is provided "AS IS" with no warranties, and confers no rights.

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
        default:
            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.

Tuesday, March 17, 2015

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.

Monday, March 16, 2015

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

Do you know about OpenXML and the SDK that goes with it? I saw it being mentioned in forum discussion here and there. However, not until recently that I've been start using it for exporting excel documents. And now I won't ever go back to SysExcelApplication.

What is OpenXML and OpenXML SDK?
For those of you who don't know what OpenXML is:
Long answer - http://en.wikipedia.org/wiki/Office_Open_XML
Short answer - It's the office document format whose extensions has an 'x' at the end (xlsx, docx, etc)
The SDK is the library for manipulating files in OpenXML format.

What's good about it?
  • Better performance compare to Excel Object Model
  • You don't need to install Office to be able to use it. The library can be downloaded and installed separately. 
  • In terms of AX, it means it is server-batch-friendly

How's the code look like?
Create a new Console Application project. Add WindowsBase and DocumentFormat.OpenXML as reference. (Use NuGet and lookup "OpenXML" for the library) Then paste the code below for a quick example of generating excel documents using OpenXML library:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OpenXMLExportTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // Generate a random temp file name
            string tempFile = System.IO.Path.GetTempFileName();
            string tempFilePath = System.IO.Path.GetDirectoryName(tempFile);
            string tempFileName = System.IO.Path.GetFileNameWithoutExtension(tempFile);
            string filename = tempFilePath + tempFileName + 
                              ".xlsx";

            // Create the file
            CreateWorkbook(filename);

            // Open the file in excel
            System.Diagnostics.Process.Start(filename);
        }

        /// Creates the workbook
        public static SpreadsheetDocument CreateWorkbook(string fileName)
        {
            SpreadsheetDocument spreadSheet = null;
            WorkbookStylesPart workbookStylesPart;

            try
            {
                // Create the Excel workbook
                using (spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook, false))
                {
                    // Create the parts and the corresponding objects
                    // Workbook
                    var workbookPart = spreadSheet.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    // WorkSheet
                    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new SheetData();
                    worksheetPart.Worksheet = new Worksheet(sheetData);
                    var sheets = spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    var sheet = new Sheet()
                    {
                        Id = spreadSheet.WorkbookPart
                            .GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "Sheet 1"
                    };
                    sheets.AppendChild(sheet);

                    // Stylesheet                    
                    workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                    workbookStylesPart.Stylesheet = new Stylesheet();
                    workbookStylesPart.Stylesheet.Save();

                    // Write some values
                    WriteSomeValues(worksheetPart);

                    // Save the workbook
                    worksheetPart.Worksheet.Save();
                    spreadSheet.WorkbookPart.Workbook.Save();
                }

            }
            catch (System.Exception exception)
            {
                Console.WriteLine(exception.Message);
            }

            return spreadSheet;
        }

        private static void WriteSomeValues(WorksheetPart worksheetPart)
        {
            int numRows = 5;
            int numCols = 3;

            for (int row = 0; row < numRows; row++)
            {
                Row r = new Row();
                for (int col = 0; col < numCols; col++)
                {
                    Cell c = new Cell();
                    CellFormula f = new CellFormula();
                    f.CalculateCell = true;
                    f.Text = "RAND()";
                    c.Append(f);
                    CellValue v = new CellValue();
                    c.Append(v);
                    r.Append(c);
                }

                worksheetPart.Worksheet.GetFirstChild<SheetData>().Append(r);
            }
        }

    }
}              

So far it's pure OpenXML and no AX. In Part 2, there will be an example of exporting AX data with OpenXML SDK.

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