Creating a simple XLSX from scratch using the Open XML SDK


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.

minimalistfile.xlsx


Comments (17)

  1. Yogik says:

    Is it possible to imeplement this in vc++ with vs2003 or vs2010?

  2. andrea says:

    If an error of System.IO.packaging is raises then you have to add also a reference to "WindowBase" component.

  3. Ross Jones says:

    Thanks for the post Chris it has been helpful.

    I am working on a solution where I would like to transform an existing XML document into the xlsx format using an xslt transform. Are there any tools available in the sdk which will assist in doing this.

    I aim to create the worksheet using xslt then inject it into a document which I build up using the SDK. Is this possible?

  4. EricWhite says:

    Hi Ross,

    There is a way to work with Open XML using the Open XML SDK and XSLT:

    blogs.msdn.com/…/transforming-open-xml-documents-using-xslt.aspx

    Reading between the lines, you may be interested in this:

    openxmldeveloper.org/…/generating-open-xml-documents-using-t4-text-templates.aspx

    -Eric

  5. Dale says:

    If I have to install Open XML SDK then why not just install Office libraries for .Net?  I had always believed and hoped that the simplicity of Excel as XML would be that I could create a simplet set of XML and open it in Excel as an XLSX file.

    For example, what would be the minimum amount of XML that has to be generated from scratch in order to end up with a working XLSX file with 10 rows of 10 columns, all saying "Hello World"?

  6. chrisrae says:

    Hi Dale – the OpenXML format is actually a zip file containing a number of XML files and some other components. I'm afraid it's not just a flat XML file, so that's why it's easier to work with it through SDKs. Most other compound document formats (PDF, ODF etc) are built in a similar way to allow for easier embedding of binary data, images et cetera and allow relationships to be defined between the various package parts. To answer your question, I'd say you'd need a ZIP file with roughly seven XML parts in it, and 3 metadata parts.

  7. Matthew says:

    Thank you!  This is by far the best example of how to create an excel spreadsheet using OpenXML.  I was easily able to take this example and expand upon it to do what I needed!

  8. Antoine says:

    Hi!

    I'm french, and I would know how can I have more sheets ?

    I have try : Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)1U, Id = "rId1" };

               sheets1.Append(sheet2);

    but it doesn't work…

    Thank you for this tutorial

  9. chrisrae says:

    Hi Antoine – you need to replicate the entire sheet declaration and subsequent Workbook append – you can't append a sheet to a sheet.

  10. Divya says:

    Hi Chris

    Is there any method to generate dynamic xsl for xml spreadsheet?

  11. chrisrae says:

    Hi Divya – I don't think so.

  12. DPF says:

    I am having the same issue as Antoine.

               Workbook workbook1 = new Workbook();

               workbook1.AddNamespaceDeclaration("r", "schemas.openxmlformats.org/…/relationships");

               Sheets sheets1 = new Sheets();

               Sheet sheet1 = new Sheet() { Name = "About you (The Carer)", SheetId = (UInt32Value)1U, Id = "rId1" };

               sheets1.Append(sheet1);

               Sheets sheets2 = new Sheets();

               Sheet sheet2 = new Sheet() { Name = "Identifying Young Carers", SheetId = (UInt32Value)1U, Id = "rId2" };            

               sheets2.Append(sheet2);

               workbook1.Append(sheets1);

               workbook1.Append(sheets2);

               workbookPart1.Workbook = workbook1;

           }

    thanks in advance

  13. chrisrae says:

    You only want one sheets collection. So instead of having a sheets2, you should just do  sheets1.Append(sheet2) and then workbook1.Append(sheets1). Apologies, I think I said something somewhat contrary to this in my earlier comment about replicating the workbook append.

  14. DPF says:

    I understand now

    Thanks for your help with this,

    it works fine now.

  15. DPF says:

    Hi Chris

    I am currently trying to set the width of a column and cannot seem to see how this is done.

    thanks in advance

  16. Matt says:

    Hi,

    For some reason, whenever i create a brand new xlsx file using this example, and try to open it using Excel 2010, i get the "Excel found some unreadable data …" error, and then i have to "Attempt to recover it". The strange part is that the log shown afterwards says that there really was nothing fixed or changed.

    Keep in mind that i am doing this via MVC, creating the file in a MemoryStream, then pushing it to the view/browser via a FileContentResult. I am assuming that this really is the problem and not the code to generate the file itself?

    With that in mind, here is my entire code for the ActionResult method (hopefully it fits here):

    var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                           var filename = "SearchRecherche_" + results.Terms + "_" + results.RanOn + ".xlsx";

                           System.IO.MemoryStream stream = new System.IO.MemoryStream();

                           using (SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))

                           {

                               WorkbookPart workbookPart1 = package.AddWorkbookPart();

                               Workbook workbook1 = new Workbook();

                               workbook1.AddNamespaceDeclaration("r", "schemas.openxmlformats.org/…/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;

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

                               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;

                           }

                           var result = new FileContentResult(stream.GetBuffer(), contentType);

                           result.FileDownloadName = filename;

                           return result;

    Thanks a million in advance for any help!

  17. Johan says:

    @Matt: Use stream.ToArray() instead of stream.GetBuffer(). stackoverflow.com/…/when-is-getbuffer-on-memorystream-ever-useful