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.