C# OpenXML: Convert a DataSet to Excel Worksheets

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/

Posted by - Christoff Truter
Date - 2010-09-18 14:47:20
Comments - 0

Post/View comments

SQL 2005/2008 - XML Type : modify method

The modify method (when using the XML Type) allow developers to update/insert/remove specific nodes within the XML field.

Lets have a quick look at how to use this method.

First of all we're going to create a little play table - a table containing cars and a xml field containing the different colours we get for the car.

 
CREATE TABLE [dbo].[cars](
	[carID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[title] [varchar](255) NOT NULL,
	[colours] [xml] NULL
)

Some data (consider binding your xml type to a schema)

 
INSERT INTO cars(title, colours)
VALUES('VW', '<colours>
		<colour>Red</colour>
	</colours>')

Insert a new node at the end of the list (substitute last with first in order to insert a node at the top of the list)

 
UPDATE cars
SET colours.MODIFY('insert element colour {"Green"} 
					as last
					into (/colours)[1]')
WHERE carID = 1

Insert a new node after a specific node (substitute after with before in order to insert the node before the specific node)

Example 1

 
UPDATE cars
SET colours.MODIFY('insert element colour {"Lavendar"} 
					after (/colours/colour)[2]')
WHERE carID = 1

Example 2

 
UPDATE cars
SET colours.MODIFY('insert element colour {"Brown"} 
					after (/colours/colour[.="Green"])[1]')
WHERE carID = 1

Example 3 (Inserting multiple nodes)

 
UPDATE cars
SET colours.MODIFY('insert 
			(
			element colour {"Purple"}, 
			element colour {"Pink"}
			)
				as last
			into (/colours)[1]')
WHERE carID = 1

Example 4 (Doesn't work in SQL 2005)

 
DECLARE @colours XML
SET @colours = '<colour>Orange</colour><colour>White</colour>'
 
UPDATE cars
SET colours.MODIFY('insert 
			(
			sql:variable("@colours")
			)
			as last
			into (/colours)[1]')
WHERE carID = 1

Update a specific node - in this case Green with DarkGreen.

 
UPDATE cars
SET colours.MODIFY('replace value of (//colour[.="Green"])[1]
					with "DarkGreen"')
WHERE carID = 1

Delete specific node(s)

Example 1

 
UPDATE cars
SET colours.MODIFY('delete (//colour[.="Blue"])')
WHERE carID = 1

Example 2 (Delete a number of nodes - this case all the nodes that contain green)

 
UPDATE cars
SET colours.MODIFY('delete (//colour[contains(.,"Green")])')
WHERE carID = 1

Posted by - Christoff Truter
Date - 2010-09-13 15:52:14
Comments - 0

Post/View comments

First 16 17 18 19 20 21 22 23 24 25 Last / 62 Pages (124 Entries)

C# OpenXML: Convert a DataSet to Excel Worksheets

SQL 2005/2008 - XML Type : modify method

Latest Posts

Solving Cross Browser Issues - Part 1 (JQuery and GWT)

Be the best stalker you can be

AjaxControlToolkit (ASP.NET/C#) : CascadingDropDown Extender - Part 2

AjaxControlToolkit (ASP.NET/C#) : CascadingDropDown Extender - Part 1

Syntactic sugar (C#): Enum

OCR (C#) : IRIS (iDRS 14.0) Part 2 - Writing some code

OCR (C#) : IRIS (iDRS 14.0) Part 1 - Getting Started

ASP.NET Validators : CustomValidator Control

ASP.NET WebControl Critical Analysis: DropDownList

Interesting Exceptions: C# - Cannot load dynamically generated serialization assembly

Top 5 posts

Simple WYSIWYG Editor

Moving items between listboxes in ASP.net/PHP example

Cross Browser Issues: Firefox Word Wrapping

Populate a TreeView Control C#

C# YouTube : Google API