Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Build an Excel Workbook in Memory with BytesIO

Create .xlsx entirely in memory with pandas, xlsxwriter or openpyxl: the close-and-seek rules, in_memory mode, sending bytes to S3 or email, and reading them back.

Writing a workbook to disk and reading it straight back is a habit from scripts, and it becomes a liability the moment code runs in a server, a container, or a Lambda. Building the file in an io.BytesIO buffer removes the filesystem from the picture: no temporary path to collide over, no cleanup, no read-only-volume surprise. The pattern is four lines, and two of them are the ones people miss. This guide covers all three libraries, the byte-level reasons the rules exist, and where the bytes go next. It is the shared foundation under Serving Excel Files from Python Web Apps.

Why a workbook must be closed before it is read Worksheet parts are written as they are produced, but the zip central directory that indexes them is only appended when the workbook closes, so a buffer read early has no index. Inside the buffer, over time sheet1.xml sharedStrings.xml styles.xml central directory (on close) read here: parts with no index — "not a zip file" read here: a valid file then seek(0) — otherwise the read starts after the last byte and returns nothing

Prerequisites

Bash
pip install pandas xlsxwriter openpyxl

io is in the standard library, so nothing else is required.

The pattern, with pandas

Python
"""Build a workbook in memory and get its bytes."""
import io

import pandas as pd

df = pd.DataFrame({"region": ["North", "South"], "revenue": [128_400.5, 96_220.0]})

buffer = io.BytesIO()
with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
    df.to_excel(writer, index=False, sheet_name="Sales")
buffer.seek(0)

payload = buffer.getvalue()
print(f"{len(payload):,} bytes")

Two rules govern everything else on this page. Close the writer before reading — the with block does it — because an .xlsx is a zip whose central directory is only appended at the end. And rewind before streaming: after the write, the buffer's position is at the end, so anything that reads it as a stream gets zero bytes. getvalue() is exempt from the second rule, since it returns the whole buffer regardless of position.

The same thing with xlsxwriter directly

Skip pandas when the data is already rows, or when you want the full formatting API:

Python
import io

import xlsxwriter

buffer = io.BytesIO()
with xlsxwriter.Workbook(buffer, {"in_memory": True}) as book:
    ws = book.add_worksheet("Sales")
    bold = book.add_format({"bold": True})
    money = book.add_format({"num_format": "#,##0.00"})

    ws.write_row(0, 0, ["region", "revenue"], bold)
    for row, (region, revenue) in enumerate([("North", 128_400.5), ("South", 96_220.0)], 1):
        ws.write(row, 0, region)
        ws.write(row, 1, revenue, money)

    ws.set_column(0, 0, 16)
    ws.set_column(1, 1, 14)
    ws.freeze_panes(1, 0)

buffer.seek(0)

{"in_memory": True} tells xlsxwriter not to use temporary files for worksheet data. Without it, a container with a read-only filesystem — a common hardening setting, and the default in some serverless runtimes — fails at write time with a permission error that looks unrelated to Excel.

And with openpyxl, when you are editing

openpyxl is the library that can read an existing workbook, change it, and save it back. Both ends work with buffers:

Python
import io

from openpyxl import load_workbook

# `source` might be an upload, an S3 object, or an HTTP response body
source = io.BytesIO(existing_bytes)
wb = load_workbook(source)
ws = wb.active
ws["B2"] = 42
ws["A1"].value = f"Updated {ws['A1'].value}"

out = io.BytesIO()
wb.save(out)          # save() accepts any file-like object
out.seek(0)

That is the round trip a template-filling service needs: read the template from storage, populate it, return the bytes — with no file ever landing on disk. The template techniques themselves are in Populate an Excel template without losing formatting.

Where in-memory bytes can go next The same byte string can be returned as an HTTP download, attached to an email, uploaded to object storage, or read straight back for verification. One buffer, four destinations BytesIO the finished workbook HTTP download email attachment object storage read back to verify

Send the bytes somewhere

As an HTTP download — the header pair and framework specifics are in Return an Excel file from a Flask download endpoint.

As an email attachment, with no temporary file:

Python
from email.message import EmailMessage

msg = EmailMessage()
msg["Subject"] = "Weekly sales summary"
msg.set_content("The summary is attached.")
msg.add_attachment(
    payload,
    maintype="application",
    subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    filename="sales.xlsx",
)

To object storage, straight from the buffer:

Python
import boto3

boto3.client("s3").upload_fileobj(
    io.BytesIO(payload), "reports-bucket", "weekly/sales.xlsx",
    ExtraArgs={"ContentType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
)

Setting ContentType on upload matters: without it the object is served as application/octet-stream, and a browser opening a signed URL saves a file Excel may not associate correctly. The full delivery paths are in Emailing Excel Reports with smtplib and Upload an Excel report to Amazon S3 with boto3.

Read the bytes back to check them

Because everything is in memory, verification is immediate and costs no I/O:

Python
import io

import pandas as pd

check = pd.read_excel(io.BytesIO(payload), engine="openpyxl")
assert list(check.columns) == ["region", "revenue"]
assert len(check) == 2
print(check)

This is the assertion worth keeping in a test: it catches the truncated-buffer bug, which is invisible to every other check because the byte string looks plausible and the headers are correct.

Wrap it in a function that returns bytes

The most useful shape for this code is a function whose input is data and whose output is bytes. It has no side effects, it is trivial to test, and every caller — a web route, a scheduled job, a queue worker — uses it unchanged:

Python
"""report.py — one builder, many callers."""
import io
from collections.abc import Sequence

import pandas as pd

XLSX_MIME = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

def workbook_bytes(frames: dict[str, pd.DataFrame], *, autofilter: bool = True) -> bytes:
    """Render one sheet per frame and return the finished workbook."""
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        for sheet, frame in frames.items():
            frame.to_excel(writer, index=False, sheet_name=sheet[:31])
            ws = writer.sheets[sheet[:31]]
            ws.freeze_panes(1, 0)
            if autofilter and len(frame.columns):
                ws.autofilter(0, 0, len(frame), len(frame.columns) - 1)
    return buffer.getvalue()

Returning bytes rather than the buffer removes the seek question from every call site: bytes have no position. Callers that need a stream wrap them in a fresh BytesIO, which is cheap and unambiguous.

Measure what a build actually costs

Before deciding whether in-memory generation scales for your case, measure one build. The numbers are usually smaller than people fear and occasionally much larger:

Python
import io
import time
import tracemalloc

import pandas as pd

df = pd.DataFrame({"id": range(50_000), "value": [i * 1.5 for i in range(50_000)]})

tracemalloc.start()
start = time.perf_counter()
payload = workbook_bytes({"Data": df})
elapsed = time.perf_counter() - start
_current, peak = tracemalloc.get_traced_memory()
tracemalloc.stop()

print(f"{len(payload)/1e6:.1f} MB file, {elapsed:.2f}s, peak {peak/1e6:.1f} MB")
Memory held during an in-memory build Peak memory is the source data plus the workbook being assembled plus the finished byte string, which is why concurrency rather than file size is usually the limit. Three things alive at the peak source data the DataFrame or rows workbook under way xlsxwriter structures finished bytes the compressed file Multiply by concurrent requests — that product, not one file, is the ceiling

If the peak is uncomfortable, the levers in order are: return fewer rows, drop the DataFrame and write rows directly, or move the build to a queue where one worker does it at a time.

Common pitfalls and gotchas

  • Reading inside the with block. The zip is not finalised, so the bytes are not a workbook.
  • Skipping seek(0). Anything that streams the buffer sends nothing.
  • Reusing one buffer for two workbooks. Create a new BytesIO per file; a rewritten buffer keeps trailing bytes from the previous, larger file.
  • StringIO instead of BytesIO. An .xlsx is binary; a text buffer raises a TypeError.
  • Holding buffers in a list. Every retained buffer is a retained workbook. Release them once sent, especially in a worker that processes many reports per run.

Performance and scale notes

In-memory generation is generally faster than writing to disk, because it skips the filesystem, and it is the only option in an environment with a read-only volume. The cost is memory: peak usage is roughly the size of the finished workbook plus the data it came from, per concurrent build. That is fine for a report of a few thousand rows and a real constraint for a hundred concurrent exports of a large one. If a single workbook is genuinely large, xlsxwriter's constant_memory mode streams rows out as they are written — but note that it needs a temporary directory, so it conflicts with in_memory and with a read-only filesystem. The trade-offs are laid out in Write a million rows to Excel with xlsxwriter constant memory.

Conclusion

io.BytesIO turns workbook generation into a pure function: data in, bytes out, nothing on disk. Close the writer before reading, rewind before streaming, and create a fresh buffer per file. From there the same bytes serve an HTTP download, an email attachment or an object-storage upload without change — and reading them back with pandas is a one-line check that the file you produced is really a file.

Frequently asked questions

Why is my in-memory file empty or corrupt? Two causes. The writer was still open when the buffer was read — an .xlsx is a zip finalised on close — or the buffer was never rewound, so the read started at the end. Close first, then seek(0).

Do I need seek(0) if I use getvalue()? No. getvalue() returns the whole buffer regardless of position. You need seek(0) when handing the buffer itself to something that will read it as a stream.

Is in-memory generation slower than writing to disk? No, it is usually faster, because it avoids the filesystem entirely. The trade-off is memory: the whole workbook exists in RAM, which matters only for very large files or high concurrency.

Can openpyxl save to a buffer too? Yes. wb.save(buffer) accepts any file-like object, which is the route to take when you are editing an existing workbook rather than creating a new one.

What is xlsxwriter's in_memory option for? It stops xlsxwriter using temporary files for worksheet data. Set it when the filesystem is read-only — a common container setting — or when temporary files would be a security concern.