Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Write Large DataFrames to Excel with write_only Mode

Generate hundred-thousand-row workbooks without the memory — openpyxl write_only, xlsxwriter constant_memory, streaming a DataFrame row by row, and the formatting you must plan ahead.

Generating a large workbook has the same problem as reading one, in reverse: the default path builds every cell in memory before anything reaches disk. openpyxl's write_only mode streams instead, appending each row to the file as it is produced. This guide, part of Working with Large Excel Files in Python, covers the mode, its constraints, the pandas bridge, and the xlsxwriter alternative.

Normal writing versus write-only streaming In normal mode every appended row is retained in a worksheet object until save, so memory grows with the output. In write-only mode each appended row is serialised and released, so memory stays at roughly one row plus the shared string table. Workbook() every row kept until save() memory grows with the sheet Workbook(write_only=True) current row previous rows already written to disk append, serialise, release memory stays flat

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

The minimal streaming write

Python
from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Export")

ws.append(["Order_ID", "Region", "Revenue"])
for order_id in range(1, 300_001):
    ws.append([order_id, "North", round(order_id * 1.5, 2)])

wb.save("streamed.xlsx")
print("done")

Two differences from ordinary openpyxl matter immediately. Workbook(write_only=True) starts with no sheets at all, so create_sheet is mandatory — wb.active is None. And the only supported operation on the sheet is append: there is no ws["A1"], no ws.cell(...), and no way to go back.

Feeding it a DataFrame

to_excel needs a normal worksheet, so stream the frame yourself. itertuples is the fast iterator — it avoids building a Series per row:

Python
import numpy as np
import pandas as pd
from openpyxl import Workbook

rng = np.random.default_rng(1)
df = pd.DataFrame({
    "Order_ID": np.arange(1, 250_001),
    "Region": rng.choice(["North", "South", "West"], 250_000),
    "Quantity": rng.integers(1, 20, 250_000),
    "Unit_Price": rng.uniform(5, 200, 250_000).round(2),
})
df["Revenue"] = (df["Quantity"] * df["Unit_Price"]).round(2)

wb = Workbook(write_only=True)
ws = wb.create_sheet("Orders")
ws.append(list(df.columns))
for row in df.itertuples(index=False, name=None):
    ws.append(list(row))
wb.save("orders_streamed.xlsx")

name=None gives plain tuples rather than named tuples, which is measurably quicker over a quarter of a million rows. If the frame itself is the memory problem, combine this with a chunked read so neither side is ever fully resident.

Styling as you go

Formatting is still possible, but it has to be attached to the cell at the moment its row is appended. WriteOnlyCell is the vehicle:

Python
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Font, PatternFill

wb = Workbook(write_only=True)
ws = wb.create_sheet("Orders")

header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill("solid", start_color="4338CA")

header = []
for name in ["Order_ID", "Region", "Quantity", "Revenue"]:
    cell = WriteOnlyCell(ws, value=name)
    cell.font = header_font
    cell.fill = header_fill
    header.append(cell)
ws.append(header)

for order_id, region, qty, revenue in [(1, "North", 4, 79.96), (2, "South", 2, 99.0)]:
    money = WriteOnlyCell(ws, value=revenue)
    money.number_format = "#,##0.00"
    ws.append([order_id, region, qty, money])

ws.freeze_panes = "A2"                 # sheet-level settings are fine
ws.column_dimensions["B"].width = 14   # set before the rows are flushed

wb.save("styled_stream.xlsx")

Reuse the Font and PatternFill objects rather than constructing them per row: openpyxl deduplicates identical styles, and a fresh object per cell inflates the workbook's style table, which is the very thing that makes large files slow to open.

Sheet-level properties such as freeze_panes and column widths work because they are not per-cell data — but set them before saving, since the sheet is finalised at that point.

The xlsxwriter alternative

xlsxwriter has an equivalent mode and is generally faster for pure generation. It also plugs into pandas directly, which is the reason to prefer it when you want to_excel convenience:

Python
import pandas as pd

with pd.ExcelWriter(
    "constant_memory.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"constant_memory": True}},
) as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)

constant_memory flushes each row as it is written, with the same in-order constraint: once a row is flushed you cannot revisit it, so set_column widths and any conditional formatting must be declared before the data goes in. The upside is that pandas does the iteration for you.

Choosing between the three ways to write a big sheet Normal openpyxl suits sheets small enough to hold, and allows revisiting cells. openpyxl write-only suits large output where you control the loop. xlsxwriter constant memory suits large output written straight from a DataFrame. openpyxl, normal revisit any cell totals added at the end holds the whole sheet up to ~50k rows openpyxl, write_only append in order only WriteOnlyCell for styling flat memory you own the loop xlsxwriter, constant works with to_excel fastest generation cannot read or edit files pandas does the loop

Plan the things you cannot go back and add

Everything that would normally be done "afterwards" has to move before or beside the loop. Totals are the usual casualty:

Python
from openpyxl import Workbook

rows = [(1, "North", 79.96), (2, "South", 99.0), (3, "West", 85.75)]

wb = Workbook(write_only=True)
ws = wb.create_sheet("Orders")
ws.append(["Order_ID", "Region", "Revenue"])

total = 0.0
for order_id, region, revenue in rows:      # accumulate while streaming
    total += revenue
    ws.append([order_id, region, revenue])

ws.append([])                                # spacer
ws.append(["", "Total", round(total, 2)])    # written last, as a value

wb.save("streamed_with_total.xlsx")

A =SUM(C2:C4) formula would work equally well here, because it can be appended as the last row — what you cannot do is put the total at the top. When a summary must appear first, write it to its own sheet and make that sheet active, which is a better layout anyway for a file with hundreds of thousands of detail rows.

Streaming from source to output

The two streaming modes compose. Reading a large workbook row by row and writing a transformed one row by row keeps peak memory at a couple of rows regardless of how big either file is:

Python
from openpyxl import Workbook, load_workbook

src = load_workbook("large.xlsx", read_only=True, data_only=True)
out = Workbook(write_only=True)

try:
    ws_in = src["Orders"]
    ws_out = out.create_sheet("Enriched")

    rows = ws_in.iter_rows(values_only=True)
    header = list(next(rows))
    qty, price = header.index("Quantity"), header.index("Unit_Price")
    ws_out.append(header + ["Revenue"])

    written = 0
    for row in rows:
        revenue = round((row[qty] or 0) * (row[price] or 0), 2)
        ws_out.append(list(row) + [revenue])
        written += 1
finally:
    src.close()

out.save("enriched_streamed.xlsx")
print(f"{written:,} row(s) transformed without holding either sheet")

This is the shape most "add a calculated column to a huge export" jobs should take. Nothing accumulates, the transformation is a couple of arithmetic operations per row, and the job's memory profile is flat enough to run in a small container.

A read-only stream feeding a write-only stream Rows flow from the source workbook through a per-row transformation into the output workbook. Only the current row exists in memory at any moment, so the size of neither file affects peak memory. large.xlsx read_only stream one row in flight compute Revenue enriched.xlsx write_only stream Neither file is ever fully resident. peak memory ≈ two rows + the shared string table

Common pitfalls and gotchas

SymptomCauseFix
AttributeError: 'NoneType' object has no attribute 'append'wb.active is None in write-only modeCall wb.create_sheet(...) first
Cell is read-only or index errorsTried ws["A1"] or ws.cell(...)Only append is supported
Styling ignoredStyle applied after the row was appendedBuild WriteOnlyCell objects before appending
Workbook still huge in memoryA style object created per cellReuse Font/PatternFill instances
to_excel fails on the write-only bookpandas needs a normal worksheetIterate with itertuples, or use xlsxwriter
Totals cannot be added at the topRows cannot be revisitedPut the summary on its own sheet
Column widths missingSet after save()Set column_dimensions before saving

Splitting output across sheets

A single sheet caps at 1,048,576 rows, and long before that a reader gives up. Write-only mode handles the split naturally, because a new sheet is just another create_sheet before the next batch of appends:

Python
from openpyxl import Workbook

ROWS_PER_SHEET = 200_000
header = ["Order_ID", "Region", "Revenue"]

wb = Workbook(write_only=True)
ws = None
for i, row in enumerate(df.itertuples(index=False, name=None)):
    if i % ROWS_PER_SHEET == 0:
        ws = wb.create_sheet(f"Part_{i // ROWS_PER_SHEET + 1}")
        ws.append(header)
    ws.append(list(row))

wb.save("split_output.xlsx")
print("sheets:", wb.sheetnames)

Repeating the header on each part is what makes the split usable — a reader who opens Part_3 should not have to go back to Part_1 to learn what the columns are. If the consumer is a machine rather than a person, splitting into several files is usually kinder still, and each one can then be read independently.

Performance and scale notes

Streaming turns peak memory from "the whole sheet" into "one row plus the shared string table" — and that string table is the remaining growth term, so a column of a million distinct free-text values still costs. Categorical text is nearly free by comparison, since each distinct string is stored once. On raw speed, xlsxwriter in constant_memory mode is typically the fastest generator, openpyxl write_only close behind, and both are several times faster than the ordinary path once you are past a hundred thousand rows. If the output is only ever read by machines, writing CSV or Parquet instead is faster still by an order of magnitude.

Conclusion

Workbook(write_only=True) streams rows straight to disk, so generating a 300,000-row workbook costs roughly the memory of one row. In exchange you append in order, style with WriteOnlyCell at append time, reuse style objects, and plan anything that would normally be added afterwards. When the data is already a DataFrame and you want to_excel, xlsxwriter's constant_memory gives the same benefit with less code.

Frequently asked questions

Can I still style cells in write_only mode? Yes, per cell, using WriteOnlyCell — but you must style each cell as you append its row, because rows cannot be revisited afterwards.

Can I use write_only with pandas to_excel? Not directly. to_excel needs a normal worksheet. Either iterate the DataFrame yourself with itertuples, or use xlsxwriter's constant_memory option through ExcelWriter.

Does write_only work with an existing file? No. A write-only workbook is created from scratch. To add a large sheet to an existing file, generate it separately and combine, or accept a normal load.

How much memory does it actually save? Peak memory becomes roughly one row plus the shared string table, instead of the whole sheet — the difference between hundreds of megabytes and a few.

Up to the parent guide:

Related guides: