XLSX from the inside: why streaming writes solve almost everything

To understand why streaming a write into an XLSX file is so efficient, you first need to understand that an XLSX is just an ordinary ZIP archive. If you rename it to .zip and unpack it, inside you will see a number of XML files, roughly like this:

• [Content_Types].xml
• xl/workbook.xml
• xl/styles.xml
• xl/sharedStrings.xml
• xl/worksheets/sheet1.xml

And once you realize that none of these files requires knowing all the data in advance, that is exactly what makes streaming possible.

The data in XLSX and what the object model does with it#

The main sheet data is stored in xl/worksheets/sheet1.xml.

Simplified, it looks like this:

<worksheet>
  <sheetData>
    <row r="1">
      <c r="A1"><v>Header 1</v></c>
      <c r="B1"><v>Header 2</v></c>
    </row>
    <row r="2">
      <c r="A2"><v>Value 1</v></c>
      <c r="B2"><v>Value 2</v></c>
    </row>
  </sheetData>
</worksheet>

The key point: rows go strictly in sequence, and Excel does not require knowing the number of rows in advance, nor keeping previous rows in memory, nor having access to all the data at once.

But most popular libraries work like this:

• create a Workbook object
• create a Worksheet object
• for every row, create a Row object
• for every cell, create a Cell object

All the objects live in memory until the file is saved. At 300,000 rows × 10 columns that is 3 million cell objects, plus rows, plus styles, plus a lot more. Even if each cell takes only a few hundred bytes, the total ends up in the gigabytes.

The streaming model: what changes fundamentally#

A streaming write does one simple thing: it writes the XML straight to the file and forgets about it. No workbook objects, no “document model,” no holding data in memory.

Memory is used only for the current row, the current styles and some service buffers.

Here I should note that in XLSX the styles are moved into a separate file — styles.xml.

Example:

<cellXfs count="3">
  <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  <xf numFmtId="14" fontId="0" fillId="0" borderId="0"/>
  <xf numFmtId="0" fontId="1" fillId="0" borderId="0"/>
</cellXfs>

A cell stores only an index:

<c r="A2" s="1"><v>45234</v></c>

This means a style is created once and then used by index, and Excel wires everything together itself. So streaming writes do not get in the way of styles — they simply require caching styles, reusing indexes and not creating duplicates.

Why this is not a universal solution#

There are cases where the streaming model does not fit. For example, when you need to edit arbitrary cells, to “jump” around the sheet.

However, if we are talking about generating relatively small XLSX files, where holding all the data in memory is not a problem, FastExcelWriter handles that too — the library can be switched into a “direct” write mode for arbitrary cells, in which case the data accumulates in memory and, when finished, is written to the file row by row.

But that will be covered in future posts.