Recently I've been scouting around Stack Overflow looking for Open XML-related questions - it's always interesting to see what real people are doing with technologies you've been involved in.

One topic that often comes up is the question of how to create SpreadsheetML files from scratch. The way most people go about this is to save a blank file directly from Excel and then, using the Open XML SDK, open that template file, insert your necessary data into it and save it as the destination output file. While this works fine I can't help feeling it's a little inelegant. Why not create a file from scratch? The Open XML SDK comes with a great tool called the Reflector. You can open a file, click "Reflect code" and it'll generate the code necessary to create that file. I saved a blank file from Excel, and then opened it in the reflector. It generated 900 lines of code. Which wasn't exactly what I had in mind.

It turns out that a "blank" file saved by Excel contains a lot of data that isn't required by the standard. As well as styles, font data, margins and other layout information, it also contains many application properties and some theme definitions created by Microsoft. These are useful when you want your annual report to look great in non-Microsoft products, but they're not so helpful if you just want a grid of numbers to show up. In addition, the reflector is geared towards helping you write code to structure your workbook, not really to write large amounts of static data into it.

Nearly five years ago, my colleague Doug wrote a blog post, "CreateXlsx sample program", which walked the user through creation of a minimalist SpreadsheetML file using the System.IO.Packaging library. This was before the Open XML SDK existed, so I thought I'd write an updated version of that code using the SDK.

Without further ado, here is the code. To run it:

  • Get a copy of Visual Studio 2010, and download and install the Open XML SDK
  • Click File..New..Project and select Visual C# \ Windows \ Console Application
  • Add a reference to the DocumentFormat.OpenXml library
  • Replace the entire text of Program.cs with the following code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace MakeXLSX
{
    public class MostlyGeneratedClass
    {
        // Creates a SpreadsheetDocument
        public void CreatePackage(string filePath)
        {
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
            {
                CreateParts(package);
            }
        }

        // Adds child parts and generates content of the specified part
        private void CreateParts(SpreadsheetDocument document)
        {
            WorkbookPart workbookPart1 = document.AddWorkbookPart();
            GenerateWorkbookPart1Content(workbookPart1);

            WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPart1Content(worksheetPart1);
        }

        // Generates content of workbookPart1. 
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }

        // Generates content of worksheetPart1. 
        private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = new Worksheet();
            SheetData sheetData1 = new SheetData();

            Row row1 = new Row();
            Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.InlineString };
            InlineString inlineString1 = new InlineString();
            Text text1 = new Text();
            text1.Text = "hello";
            inlineString1.Append(text1);
            cell1.Append(inlineString1);
            row1.Append(cell1);

            sheetData1.Append(row1);
            worksheet1.Append(sheetData1);
            worksheetPart1.Worksheet = worksheet1;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            MostlyGeneratedClass x = new MostlyGeneratedClass();
            x.CreatePackage("c:\\chris.xlsx");
        }
    }
}

Running this code will create a c:\chris.xlsx which contains one worksheet, with "hello" in cell A1. Using this template and the reflector you should be able to build onwards to develop whatever solution you're after.

Of course, not everyone is using the SDK. In case you're generating spreadsheet files using another platform, I've attached the file that this code creates. As you probably know, you can look at the content of this file by simply renaming it to .zip and then viewing it with a normal zip file editor. It contains only five Parts, with around 32 lines of XML in total, so it should be easy enough to port this to your platform.

Code is provided under the Apache License 2.0 - I hope this is permissive enough to allow reuse in any circumstances; please get in touch if you would like to use this code under a different license for any reason.