C# OpenXML: Convert a DataSet to Excel Worksheets

September 18, 2010 by Christoff Truter C#   Microsoft Office  

I've been threatening for quite a while now that I am going to write some posts regarding OpenXML (the file format Microsoft uses in Office 2007 & greater - not to be confused with Office XML).

An OpenXML document (in most cases) is simply a zip file that contains XML files and various resource files thats used to build up a document; If one would for example go and rename the docx extension on a document to zip, one would be able to browse the contents of the file.

The first time I used this format was somewhere in 2006/2007 Microsoft didn't provide much of a SDK - I actually wrote a class back then, where I manually created the required XML according to the OpenXML specifications and simply "packed" it using the System.IO.Packaging classes.

Luckily Microsoft eventually created a SDK, which makes it a lot easier (in theory at least) to create Office documents using this format.

In this post we're going to have a look at how to convert a DataSet to an xlsx (Excel Workbook) document.

First of all you'll need to download the latest SDK (in this case version 2.0) from here

Next its necessary to include references to the DocumentFormat.OpenXml assembly into your project and the WindowsBase assembly (which contains the System.IO.Packaging namespace).

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;

Next we create a very basic function thats going to be responsible for creating the Excel document.

static void Create(string path, Dictionary<String, List<OpenXmlElement>> sets)
{
    using (SpreadsheetDocument package = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookpart = package.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());

        foreach (KeyValuePair<String, List<OpenXmlElement>> set in sets)
        {
            WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
            worksheetpart.Worksheet.Save();

            Sheet sheet = new Sheet()
            {
                Id = workbookpart.GetIdOfPart(worksheetpart),
                SheetId = (uint)(sheets.Count() + 1),
                Name = set.Key
            };
            sheets.AppendChild(sheet);
        }
        workbookpart.Workbook.Save();
    }
}

In the preceding snippet:

• Create a SpreadSheetDocument package
• Add a Workbook Part to it
• Add a Workbook instance to the workbookpart
• Add Sheets to the workbook
• Iterate through a dictionary containing sheet data
• Add a Worksheet Part to the Workbook Part
• Add a Worksheet instance to the worksheetpart
• Pass Rows of data to the worksheet
• Create a sheet and add it to previously created sheets

To give you a basic idea of what you need to pass to the method, have a look at the following code:

List<OpenXmlElement> elements = new List<OpenXmlElement>();

for (int i = 0; i < 10; i++)
{
    Row row = new Row
    (
        new Cell[] 
        {
            new Cell() 
            {
                CellValue = new CellValue(i.ToString()), DataType = CellValues.Number 
            },
            new Cell()
            {
                CellValue = new CellValue("test"), DataType = CellValues.String
            }
        }
    );
    elements.Add(row);
}

Dictionary<String, List<OpenXmlElement>> sets = new Dictionary<string, List<OpenXmlElement>>();
sets.Add("Worksheet 1", elements);
Create(@"c:\tests\1.xlsx", sets);

Obviously this is something one would automate in code and not hardcode like seen in the previous example - so I thought it might be interesting (perhaps useful) to take it a bit further and create a method that converts a DataSet to WorkSheets.

DataSets are very similar to Worksheets e.g. DataSets contain DataTables which we can easily "map" to worksheets, in the following snippet I used LinQ (another subject I've been threatening to post about) in this "mapping" process, observe:

static Dictionary<string, List<OpenXmlElement>> ToSheets(DataSet ds)
{
    return
        (from dt in ds.Tables.OfType<DataTable>()
         select new
         {
             // Sheet Name
             Key = dt.TableName,
             Value = (
             // Sheet Columns
             new List<OpenXmlElement>(
                new OpenXmlElement[] 
                {
                    new Row(
                        from d in dt.Columns.OfType<DataColumn>()
                        select (OpenXmlElement)new Cell()
                        {
                            CellValue = new CellValue(d.ColumnName),
                            DataType = CellValues.String
                        })
                })).Union
             // Sheet Rows
             ((from dr in dt.Rows.OfType<DataRow>()
               select ((OpenXmlElement)new Row(from dc in dr.ItemArray
                                               select (OpenXmlElement)new Cell()
                                               {
                                                   CellValue = new CellValue(dc.ToString()),
                                                   DataType = CellValues.String
                                               })))).ToList()
         }).ToDictionary(p => p.Key, p => p.Value);
}

Not sure how readable the previous snippet are (I believe its pretty straightforward), but basically we pass a dataset to this method and return the results to the first snippet (Create(string path, Dictionary> sets)) - which creates an excel represention of our DataSet.

Here is some additional reading on the subject:

Official Microsoft Site http://openxmldeveloper.org/