XLSX Format: Inside Microsoft Excel's Open XML Spreadsheet

XLSX Format: Inside Microsoft Excel's Open XML Spreadsheet

XLSX (Excel Open XML Workbook) is Microsoft's spreadsheet format introduced with Office 2007, replacing the binary XLS format. Like DOCX, it is a ZIP archive containing XML files conforming to the Office Open XML (OOXML) standard. Understanding XLSX's internal structure is essential for programmatic data processing, automated report generation, and accurate data extraction at scale.

XLSX Internal Structure

workbook.xlsx/
├── [Content_Types].xml
├── _rels/.rels
├── xl/
│   ├── workbook.xml          — workbook structure (list of sheets, named ranges)
│   ├── styles.xml            — cell formats, number formats, fonts, fills, borders
│   ├── sharedStrings.xml     — string lookup table (all unique strings, referenced by index)
│   ├── theme/theme1.xml      — color theme
│   ├── worksheets/
│   │   ├── sheet1.xml        — Sheet 1 data
│   │   ├── sheet2.xml        — Sheet 2 data
│   │   └── _rels/
│   │       └── sheet1.xml.rels  — relationships (charts, images, hyperlinks)
│   ├── charts/
│   │   └── chart1.xml        — chart definitions
│   ├── drawings/
│   │   └── drawing1.xml      — chart/image positioning
│   └── calcChain.xml         — calculation dependency chain
└── docProps/
    ├── app.xml               — application metadata
    └── core.xml              — workbook properties

The sharedStrings.xml Optimization

The most important structural concept in XLSX: all string values are stored once in a shared string table, not inline in each cell. This dramatically reduces file size when the same string appears in many cells.

In xl/sharedStrings.xml:

<sst count="4" uniqueCount="3">
  <si><t>Product Name</t></si>   <!-- index 0 -->
  <si><t>Quantity</t></si>        <!-- index 1 -->
  <si><t>Price</t></si>           <!-- index 2 -->
</sst>

In xl/worksheets/sheet1.xml, a cell with string value references the index:

<c r="A1" t="s">   <!-- t="s" means type="sharedString" -->
  <v>0</v>          <!-- index 0 = "Product Name" -->
</c>

Numeric cells store values directly:

<c r="B2">          <!-- no t attribute = numeric -->
  <v>42.5</v>
</c>

Date cells store dates as serial numbers (days since January 0, 1900 — the famous Excel epoch bug where 1900 is incorrectly treated as a leap year):

<c r="C2" s="14">  <!-- s="14" refers to style index 14 (date format) -->
  <v>45292</v>      <!-- January 15, 2024 = day 45292 -->
</c>

Cell Addressing and Ranges

XLSX uses A1-style notation:

• Column letters: A–Z (1–26), AA–AZ (27–52), ..., XFD (column 16,384)
• Row numbers: 1–1,048,576 (2^20 rows)
• Cell reference: A1, B2, XFD1048576
• Range reference: A1:D10 (4 columns × 10 rows)

Internally, row/column data is stored in sheet.xml with r (reference) attributes:

<sheetData>
  <row r="1" spans="1:4">
    <c r="A1" t="s"><v>0</v></c>  <!-- Product Name -->
    <c r="B1" t="s"><v>1</v></c>  <!-- Quantity -->
    <c r="C1" t="s"><v>2</v></c>  <!-- Price -->
    <c r="D1" t="s"><v>3</v></c>  <!-- Total -->
  </row>
  <row r="2" spans="1:4">
    <c r="A2" t="s"><v>4</v></c>  <!-- "Widget A" from sharedStrings -->
    <c r="B2"><v>100</v></c>      <!-- 100 (numeric) -->
    <c r="C2"><v>9.99</v></c>     <!-- 9.99 (numeric) -->
    <c r="D2">
      <f>B2*C2</f>                <!-- formula -->
      <v>999</v>                  <!-- cached result -->
    </c>
  </row>
</sheetData>

Note that formulas store both the formula expression AND the last calculated value. This enables reading the value without recalculating.

Styles in XLSX

The xl/styles.xml file defines a layered style system:

1. Fonts: font name, size, bold, italic, color
2. Fills: background color or pattern
3. Borders: line style and color for each cell side
4. Number formats: date, currency, percentage, custom patterns
5. Cell XFs (Cell Style Format): combine font+fill+border+numFmt into a "xf" (format) record
6. Cell styles: named styles like "Normal", "Good", "Bad", "Heading 1"

Each cell references a style index (s attribute), pointing to an XF record that cascades font, fill, border, and number format.

Working with XLSX Programmatically

Python — openpyxl

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference

# Create new workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Write headers with formatting
headers = ["Product", "Qty", "Price", "Total"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="4472C4")
    cell.alignment = Alignment(horizontal="center")

# Write data with formula
data = [("Widget A", 100, 9.99), ("Widget B", 250, 4.99)]
for row, (product, qty, price) in enumerate(data, 2):
    ws.cell(row=row, column=1, value=product)
    ws.cell(row=row, column=2, value=qty)
    ws.cell(row=row, column=3, value=price)
    ws.cell(row=row, column=4, value=f"=B{row}*C{row}")

# Add chart
chart = BarChart()
chart.title = "Sales by Product"
data_ref = Reference(ws, min_col=4, min_row=1, max_row=3)
chart.add_data(data_ref, titles_from_data=True)
ws.add_chart(chart, "F2")

# Set column widths
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 10

wb.save("output.xlsx")

# Read existing workbook
wb2 = load_workbook("data.xlsx", data_only=True)  # data_only=True reads cached formula values
ws2 = wb2.active
for row in ws2.iter_rows(min_row=2, values_only=True):
    print(row)

pandas (data processing)

import pandas as pd

# Read XLSX
df = pd.read_excel("data.xlsx", sheet_name="Sheet1", header=0)

# Process data
summary = df.groupby("Category")["Revenue"].sum().reset_index()

# Write multiple sheets
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Raw Data", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)

LibreOffice Calc conversion

# Convert XLSX to CSV
libreoffice --headless --convert-to csv:Text/Separators",44,34,UTF-8" data.xlsx

# Convert XLSX to PDF
libreoffice --headless --convert-to pdf data.xlsx

XLSX Size Optimization

# Save as XLSB (binary XLSX — much smaller, same data)
import xlsxwriter
workbook = xlsxwriter.Workbook('output.xlsb')
# Note: openpyxl doesn't support xlsb; xlsxwriter writes xlsb on supported platforms

XLSX vs. XLS vs. CSV vs. ODS

*CSV is limited by available memory.

Summary

XLSX's ZIP+XML architecture makes it the most accessible spreadsheet format for programmatic processing. The shared string table eliminates redundant string storage, formulas cache their last computed values for offline reading, and the style system enables rich formatting without per-cell redundancy. Libraries like openpyxl, pandas, and Apache POI make XLSX generation and parsing straightforward in every major programming language. For pure data exchange, CSV is simpler. For richly formatted reports, pivot tables, and charts, XLSX is the standard.

Related conversions

Document conversions that follow this topic naturally:

• PDF to DOCX
• DOCX to PDF
• MD to PDF
• HTML to PDF
• ODT to PDF
• PDF to TXT