C# OpenXML: Convert a DataSet to Excel Worksheets

September 18, 2010 by 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/


Leave a Comment


Brilliant September 27, 2016 by AA

The ToSheets function is brilliant! Thank you very much.

Content not visible in Open Office October 6, 2015 by Mahesh

Hi, It is a great post to create a excel with multiple worksheets. I have used your code and its working like a charm for me in MS excel. But when I try to open the same excel file in Open Office, it shows all the worksheets with appropriate sheet names but none of the sheets displays content i.e. the sheet is blank. Can you please help me out.

Styles.. March 11, 2014 by Asharaf

Nice article! One more thing, how can I add some styles to the columns like giving background color to header columns, giving borders to all columns

Great Article November 19, 2012 by Somang Im

Great Article. It was very helpful for what I wanted to accomplish

Very Usefull November 16, 2012 by Daniel

Excelent!! This post help me with the OverFlow exception with large datatable Thanks!!