Creating an Excel Workbook with the Open XML SDK 2.0

March 02, 2009

Introduction

I dislike Excel. A lot. It's not that it isn't a useful tool, it's just that every time it crosses my path someone has tried to make it do something it really doesn't want to do. Let's face it, it's not an RDBMS by any stretch of the imagination and it's a long way from being a fully functional reporting engine. However, cross my path it does, and it's likely to do so for the foreseeable future, so I might as well try and play nice with it.

As a follow up to Creating a Word Document with the Open XML SDK 2.0 I though I'd see how easy (or otherwise) it was to create a Workbook using similar tactics.

Creating the console application

I've added a second Console Application (imaginatively called WorkbookBuilder) to the Document Builder solution created last time round, and this time imported the following namespaces: –

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Firstly, add the following BuildWorkbook method to your new Program class.

private static void BuildWorkbook(string fileName)
{
    try
    {
        using (SpreadsheetDocument s = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookPart = s.AddWorkbookPart();
            WorksheetPart worksheetPart = workbookPart.AddNewPart();
            string relId = workbookPart.GetIdOfPart(worksheetPart);
            Workbook workbook = new Workbook();
            FileVersion fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            DateTime date = new DateTime(2009, 1, 1);
            int salesLastYear = 25185;
            int salesThisYear = 25348;
            for (UInt32 i = 1; i &lt;= 52; i++)
            {
                Row contentRow = CreateContentRow(i, date, salesLastYear, salesThisYear);
                sheetData.AppendChild(contentRow);
                date = date.AddDays(7);
                salesLastYear += (int)(salesLastYear * 0.031);
                salesThisYear += (int)(salesThisYear * 0.027);
            }
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            s.WorkbookPart.Workbook = workbook;
            s.WorkbookPart.Workbook.Save();
            s.Close();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        Console.ReadLine();
    }
}

Second, add the CreateContentRow to the mix: –

private static Row CreateContentRow(UInt32 index, DateTime date, int salesLastYear, int salesThisYear)
{
    Row r = new Row { RowIndex = index };
    Cell cell1 = CreateTextCell("A", index, date.ToString());
    Cell cell2 = CreateNumberCell("B", index, salesLastYear);
    Cell cell3 = CreateNumberCell("C", index, salesThisYear);
    r.Append(cell1);
    r.Append(cell2);
    r.Append(cell3);
    return r;
}

Next, add the CreateTextCell and CreateNumberCell methods: –

private static Cell CreateTextCell(string header, UInt32 index, string text)
{
    Cell c = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
    InlineString istring = new InlineString();
    Text t = new Text { Text = text };
    istring.Append(t);
    c.Append(istring);
    return c;
}
 
private static Cell CreateNumberCell(string header, UInt32 index, int number)
{
    Cell c = new Cell { CellReference = header + index };
    CellValue v = new CellValue { Text = number.ToString() };
    c.Append(v);
    return c;
}

Finally, add the call to the BuildWorkbook method from the Main method: –

public static void Main(string[] args)
{
    BuildWorkbook(@"C:\Test.xlsx");
}

Hit F5 to run the application as before.

I'm sure you'll agree that this is hardly the most involved of Workbooks but I have to admit I'm nonetheless impressed with the speed of execution of these applications. If you've ever had to use the Office Object Model you'll know that just instantiating the Application Classes can be quite time consuming.

Score one for Excel? No, I don't think so, this isn't technically Excel!

References

Brian Jones' Blog – http://blogs.msdn.com/brian_jones
Open XML SDK 2.0 – http://go.microsoft.com/fwlink/?LinkId=127912
OpenXMLDeveloper.org – http://openxmldeveloper.org/