How to properly use OpenXmlWriter to write large Excel files

Brian Jones already wrote something on using the OpenXmlWriter with the Open XML SDK to write large Excel files. There are a couple of things that weren’t mentioned though.

OpenXmlWriter works best for creating new things

For the purposes of explanation, we’ll assume the “new thing” is a new worksheet, although OpenXmlWriter can be used to write any Open XML SDK part (such as WorkbookPart).

The reason for this is that OpenXmlWriter is essentially writing out XML tags. The SDK offers classes and properties that we’re familiar with, but underneath it all, an Open XML spreadsheet’s internal structure are XML files zipped together. So instead of using the SDK classes to form the “XML” parts, we use the OpenXmlWriter to directly write out the XML.

The problem comes when we need to save. If you’re working with an existing worksheet, there’s already an existing XML structure in place. This means there’s a corresponding WorksheetPart (to your Worksheet class), and this WorksheetPart already has the XML structure (and more importantly, a DOM structure).

So you can’t just use the OpenXmlWriter to write the XML stuff into an existing WorksheetPart, because any changes you made will be ignored.

This is why in Brian’s code, he reads in the existing worksheet and writes the content into a new worksheet, thus using a new WorksheetPart that’s “attached” to the WorkbookPart. After he’s done reading, he deletes the old WorksheetPart.

In conclusion, either you

  • Work with the DOM structure using the SDK, which means snail-pace if you’re writing many cells
  • Work with new WorksheetPart

If you have something like worksheetPart.Worksheet.Save() mixed with the use of OpenXmlWriter, it’s not going to work.

XML attributes

The more subtle (and possibly more crucial) information that’s missing is of attributes. When you use the OpenXmlWriter, it works best if it’s used all the way. As in you don’t mix SDK class/property usage code with OpenXmlWriter code.

Basically, if there’s an SDK class property you want to set, don’t assign to that property in code. Use the OpenXmlWriter to write that property, because that property is eventually an XML attribute anyway. In this case, think of the SDK as getting in the way of making your code run faster.

In Brian’s code, notice that the RowIndex property of the Row class is not assigned. Which is fine since it’s optional. In this case, Excel tries to guess which row it is, and places the first Row class content into the first row on the spreadsheet. This is good if that’s what you want. And bad if your want your “first” Row class to be on the 3rd row instead.

You might also notice that the Cell class has no properties assigned. And no, CellFormula and CellValue are not properties of the Cell class, they’re actually child classes. The SDK allows you to access them as though they’re properties. This means the “standard” set of cell properties are not assigned:

  • The cell reference, such as “A1”
  • The cell data type, such as string or number
  • The cell style index

The code works because the cell content is a random number. And the default data type of a cell is a number (so it doesn’t need to be explicitly assigned). It will fail if you need strings or other data types.

And because there’s no cell reference given, Excel tries to guess where each cell is. Which is to say, place all the cells in that row flush to the left. This means you can’t have a cell in A1, and a blank cell in B1, and then a cell in C1, because Excel mooshes all the Cell classes together.

And because there’s no style index given, the default style is used. This is typically the minor font (such as Calibri) with no special formatting (such as font colour or bold or italics or borders).

So here’s sample code on how to use OpenXmlWriter. Note the class OpenXmlAttribute. And if you understand this, you will know that the OpenXmlWriter isn’t only for writing large Excel files. It just happens that it directly writes XML tags and attributes, and thus works the fastest for writing a lot of data.

using (SpreadsheetDocument xl = SpreadsheetDocument.Create("LargeFile.xlsx", SpreadsheetDocumentType.Workbook))
    List<OpenXmlAttribute> oxa;
    OpenXmlWriter oxw;

    WorksheetPart wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();

    oxw = OpenXmlWriter.Create(wsp);
    oxw.WriteStartElement(new Worksheet());
    oxw.WriteStartElement(new SheetData());

    for (int i = 1; i <= 50000; ++i)
        oxa = new List<OpenXmlAttribute>();
        // this is the row index
        oxa.Add(new OpenXmlAttribute("r", null, i.ToString()));

        oxw.WriteStartElement(new Row(), oxa);

        for (int j = 1; j <= 100; ++j)
            oxa = new List<OpenXmlAttribute>();
            // this is the data type ("t"), with CellValues.String ("str")
            oxa.Add(new OpenXmlAttribute("t", null, "str"));

            // it's suggested you also have the cell reference, but
            // you'll have to calculate the correct cell reference yourself.
            // Here's an example:
            //oxa.Add(new OpenXmlAttribute("r", null, "A1"));

            oxw.WriteStartElement(new Cell(), oxa);

            oxw.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));

            // this is for Cell

        // this is for Row

    // this is for SheetData
    // this is for Worksheet

    oxw = OpenXmlWriter.Create(xl.WorkbookPart);
    oxw.WriteStartElement(new Workbook());
    oxw.WriteStartElement(new Sheets());

    // you can use object initialisers like this only when the properties
    // are actual properties. SDK classes sometimes have property-like properties
    // but are actually classes. For example, the Cell class has the CellValue
    // "property" but is actually a child class internally.
    // If the properties correspond to actual XML attributes, then you're fine.
    oxw.WriteElement(new Sheet()
        Name = "Sheet1",
        SheetId = 1,
        Id = xl.WorkbookPart.GetIdOfPart(wsp)

    // this is for Sheets
    // this is for Workbook


This creates a spreadsheet with 100 columns and 50000 rows of data. On my machine, it ran in about 18 seconds.

I’m not going to list all the available XML attributes, but you can find out the correct attribute name by exploring the Open XML SDK documentation, or the official ECMA documentation.

Open XML SDK class structure






  • Relationship Part Classes (henceforth referred to as RPCs)
  • Root Classes (henceforth referred to as RCs)
  • Content Classes (henceforth referred to as CCs)
  • Special Classes


Relationship Part Classes




Root Classes



Content Classes




Special Classes



An illustration

Open XML SDK class structure





One of the most confusing parts…


  • Why are there so many classes?
  • Why are some of these classes duplicates of each other?










DefinedNames in Cells for Open XML spreadsheets

So a while back, a customer of mine asked me if I knew how to set names (or labels) to cells in a spreadsheet, so that a cell formula just referenced those names. Frankly, I didn’t even know I could do that. I’m not an Excel wizard, as you can tell.

In case you’re in a hurry, here’s the code and the resulting Excel spreadsheet. The code runs against the Open XML SDK.

To my surprise, the name given to the spreadsheet cell isn’t defined at the Cell class level. You’d think you would name your child and make sure to slap a name tag on your progeny just so everyone knows what to call your offspring, right? After looking through the documentation, and thinking it through, I guess it makes sense to separate it. It works by having a central depository with all the available names, contained within DefinedNames and DefinedName classes. Then the names are available throughout the spreadsheet’s workbook.

It’s sort of like the SharedStringTable, where every piece of text is stored in a SharedStringItem, and referenced with an index. This has problems, in that I don’t know what is contained in the Cell class itself, but I don’t really want to go there right now…

Let’s go through the code a bit. Here’s the part where you define DefinedName(s):

defname = new DefinedName();
defname.Name = "PrimeNum1";
defname.Text = "Sheet1!$C$2";

defname = new DefinedName();
defname.Name = "PrimeNum2";
defname.Text = "Sheet1!$C$3";

defname = new DefinedName();
defname.Name = "PrimeNum3";
defname.Text = "Sheet1!$C$4";

defname = new DefinedName();
defname.Name = "PrimeNum4";
defname.Text = "Sheet1!$C$5";

In case you’re unfamiliar with Excel, “Sheet1!$C$5” means the cell C5 of the sheet named “Sheet1”. The dollar sign acts as a separator, I think. In this case, the relevant cells are C2, C3, C4 and C5 (containing the 1st, 2nd, 3rd and 4th prime numbers). And then we have this part:

r = new Row();
r.RowIndex = 6;
c = new Cell();
c.CellReference = "B6";
c.DataType = CellValues.String;
c.CellValue = new CellValue("SUM");
c = new Cell();
c.CellReference = "C6";
c.CellFormula = new CellFormula("SUM(PrimeNum1, PrimeNum2, PrimeNum3, PrimeNum4)");
c.CellValue = new CellValue("17");

Note this particular line:


That’s our defined names in effect. Otherwise, we would use this:

c.CellFormula = new CellFormula("SUM(C2, C3, C4, C5)");
// or even
// c.CellFormula = new CellFormula("SUM(C2:C5)");

I teach you more about the CellFormula class, as well as a whole bunch of Open XML concepts in my programming guide.

3 facts about Open XML SDK

In the course of writing my guide on Open XML and Excel spreadsheets, I discovered some things about the Open XML SDK. There’s been some flak about the SDK or even on Open XML in general. But the price of the SDK is free, so you can’t beat that. While there are some inconveniences with using the SDK, the solutions and workarounds aren’t particularly nasty.

Here we go.

Open XML SDK is not a convenience library

If you’re looking for a neat little function that will toast bread, mop your floor, press your shirt and wish you “Have a nice day!” as you’re leaving the house, Open XML SDK is not what you’re looking for. (Actually if you find a software library that does the above, I wanna know.)

The Open XML SDK is a software development kit (hence the “SDK”). It is not a conventional software library in that sense. It provides atomic functions that allow you to do all sorts of nifty things to create/manipulate Open XML spreadsheets, word processing documents and presentation slides. The keyword is “atomic”.

This isn’t necessarily a bad thing. I know some companies will prefer not to use a software library (or third-party software). For example, government agencies or financial institutions, where data security is a concern and all software is (preferably) written in-house. The furthest they would allow is probably standard libraries.

I’m not going into a discussion about whether Open XML SDK is a “standard” library, since it’s from Microsoft. There seems to be a lot of unhappiness with Microsoft. Is it because of their monopoly? Because Bill Gates is rich? I like the .NET Framework, because it makes coding easier. Open XML SDK is built on top of that, so that makes Open XML documents easier to create and manipulate.

That said, because Open XML SDK offers atomic functions to do atomic tasks, this allows you to create your own functions to encapsulate whatever you’re trying to achieve. This flexibility is sometimes more valuable than convenience. Why do you think software companies charge a lot more for the source code of their libraries? You want to know what’s going on behind that function from a third-party library, because you don’t know if that bug is due to your own code or their code. (It’s probably your own code though…)

Open XML SDK is not a data manipulation library

There are 4 main parts to the Open XML SDK:

  • Spreadsheets
  • Word processing documents
  • Presentation slides
  • Graphics

The graphics part is a common set of class objects used by the other 3. Out of the first 3, spreadsheets are typically used to hold lots of data. Financial data, fiscal summaries, call logs, database dumps and so on. Text documents and slides hold data too, but not usually as intensive as spreadsheets.

Now it might surprise you that Open XML SDK offers no functions to manipulate any kind of data. None at all.

Need to dump 100 database records with 8 fields into a spreadsheet? You need to write code to fill in a 8-column-100-row block of cells.

Need to sort data? Do it within the database environment (there’s a SORT BY clause, you know?), or write your own sorting function. Because Open XML SDK ain’t got it.

This isn’t a big problem, but it does make writing code a little bit of a hassle. Particularly when you’re creating or manipulating a data-intensive spreadsheet. It’s not like when you’re attaching the DataReader object to a DataTable, and voila! All the data is nicely formatted and shown on a web page.

But this is also where the flexibility of writing your own functions come in.

Code execution order is important

When writing code with Open XML SDK, you have to put aside how you normally work with spreadsheets, text documents and slides. I’ll lump them together as “office documents”. Small “o” so it’s not copyright, right?

As a user, you would normally just go about your task of typing text, inserting images, styling text, cut-and-copy sections of an office document. The order of how you go about doing it is irrelevant, because you get to see the final result. And the final result is all that matters.

When working with Open XML SDK, you need to keep to an ordering of code sections. Each class in the SDK has to be appended to the appropriate parent class in the correct order, or you’ll get a corrupt file. This means your code has to be executed in the correct order too.

Go to the Open XML SDK help file. Let’s say we look at the Worksheet class. There will be a section called XML schema. In that, there will be an XML tag called “sequence”. See those “element” tags? That’s the order.

This code order isn’t a big problem. In your spreadsheet, it looks like an image is at the top, with a table of cell data below it. In code, you need to code for the table of cells first, then the image. Because that’s the order.

The solution is to ignore the visual placements of the data sections in your office document, and look at what is in your office document. Then you rearrange all the data parts according to the order, and then write code for that.


The Open XML SDK offers you flexibility in how you write your code and a great price (it’s free). It is backed by a large software company (Microsoft), so it’s not likely to disappear with no support in the future.

Sure it’s not as easy to use, but sometimes your situation doesn’t allow you to use a third-party library (cost, licensing, security). Hey, it works well enough. And sometimes, when you’re in a tight project, that’s all you really need. That you produce software (that produces office documents) well enough.

If you’re interested, you can check out my guide. I teach you how to create a spreadsheet with the standard functions in Excel such as text styling, image insertions, multiple worksheets, cell formulas. I also show you how to use a template in case you need something really complicated. And there’s working source code in C# and VB.NET.