python openpyxl large excel files: Practical Usage and Code

python openpyxl large excel files requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When you load a workbook with openpyxl.load_workbook(), the entire file is parsed into memory as a tree of Python objects. For a typical spreadsheet this is fine, but for files with hundreds of thousands of rows or many sheets, memory usage can grow to hundreds of megabytes or more. The core issue is that openpyxl is designed for feature-rich manipulation, not for streaming. However, openpyxl provides two modes that drastically reduce memory consumption: read_only and write_only. This article explains how to use them effectively for python openpyxl large excel files, and what trade-offs come with each approach.

Why openpyxl Uses So Much Memory

openpyxl represents every cell as a Cell object, and each cell holds references to its value, style, font, fill, border, and other attributes. When a workbook is loaded in default mode, all of these objects are created eagerly for every cell in the used range. A sheet with 100,000 rows and 20 columns creates two million Cell objects, each with its own attribute dictionary. This overhead is why a 50 MB .xlsx file can consume several gigabytes of RAM.

Additionally, openpyxl keeps the entire worksheet hierarchy in memory: each sheet, its dimensions, merged cells, and defined names. Even if you only need a few columns, the whole structure is built. This design makes random access and editing convenient, but it is wasteful when you only need to read or write sequentially.

Reading Large Files with read_only Mode

The read_only mode is a streaming mode that loads worksheets lazily. Instead of building all cells up front, it reads rows from the XML as you iterate. To enable it, pass read_only=True to load_workbook:

from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb['Sheet1']

for row in ws.iter_rows(values_only=True):
    # process each row as a tuple of cell values
    print(row)

wb.close()

The iter_rows method yields rows one at a time. With values_only=True, each row is a tuple of raw values, not Cell objects, which further reduces memory overhead. You can also use ws.iter_rows(min_row=1, max_row=1000, min_col=1, max_col=5) to limit the range, but note that openpyxl still reads the underlying XML sequentially.

Limitations of read_only Mode

Read-only mode has several constraints. You cannot access cells randomly; you must iterate from the beginning. You cannot modify the workbook, and you cannot read formulas — only the last calculated value is available. Also, some features like merged cells and charts are not fully supported. If your workflow requires random access or editing, read-only mode is not suitable.

Writing Large Files with write_only Mode

For generating large Excel files, write_only mode is the counterpart to read_only. It writes rows directly to the XML stream without keeping the entire workbook in memory. Use Workbook(write_only=True) to create a workbook in this mode:

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet('LargeSheet')

# Write header
ws.append(['ID', 'Name', 'Value'])

# Stream rows from a generator or file
for i in range(1000000):
    ws.append([i, f'item_{i}', i * 3.14])

wb.save('large_output.xlsx')

Each call to append writes a row to the underlying XML writer. The workbook object does not retain the rows, so memory usage stays flat regardless of how many rows you write. This is ideal for exporting database queries or log files to Excel.

Constraints of write_only Mode

In write-only mode, you cannot read from the workbook, and you cannot modify cells after they are appended. You also lose access to some styling features — for example, you cannot apply styles to individual cells after appending, though you can set column widths and row heights before writing. If you need to post-process the data or apply complex formatting, you may need to write to a temporary file and then reload it in normal mode, but that defeats the memory advantage.

Using Iterators and Generators Effectively

Both read-only and write-only modes work best when you pair them with generators or iterators. Instead of building a list of rows in memory, yield rows from a function or read them from a CSV file. For example, to copy data from a large CSV to an Excel file without loading all rows at once:

import csv
from openpyxl import Workbook

def csv_rows(path):
    with open(path, newline='') as f:
        reader = csv.reader(f)
        for row in reader:
            yield row

wb = Workbook(write_only=True)
ws = wb.create_sheet('Data')

for row in csv_rows('input.csv'):
    ws.append(row)

wb.save('output.xlsx')

This pattern keeps memory usage proportional to the size of a single row, not the entire dataset. The same principle applies when reading: process each row as it arrives and discard it after use.

Reducing Memory Footprint with Data Types and Cell Values

Even in normal mode, you can reduce memory usage by being deliberate about what you load. If you only need values, use data_only=True to skip formulas and retrieve cached values. This avoids storing formula strings and the formula parser tree. For example:

wb = load_workbook('calc.xlsx', data_only=True)

Another technique is to disable styles when you don't need them. openpyxl does not offer a direct "no styles" flag, but you can avoid loading the workbook entirely and instead use a streaming parser like pandas.read_excel with the openpyxl engine, which internally uses read-only mode. However, if you must use openpyxl, be aware that styles are always loaded in normal mode; only read-only mode avoids them.

Practical Considerations for Production

When handling large Excel files in a production environment, consider the following:

File size limits: Excel's .xlsx format has a hard limit of 1,048,576 rows per sheet. If your data exceeds that, you must split it across multiple sheets or files.
Concurrency: openpyxl is not thread-safe. If you process multiple files in parallel, use separate processes or ensure each thread has its own workbook instance.
Error handling: Always close workbook objects in a finally block or use a context manager to release file handles, especially in read-only mode.
Memory monitoring: For very large files, monitor memory usage with psutil or tracemalloc to verify that your streaming approach is working as expected.

When to Choose Alternative Libraries

openpyxl is not the only option for large Excel files. pandas with the openpyxl engine can read and write in a streaming fashion, but it adds its own overhead. xlsxwriter is another write-only library that is highly optimized for writing large files. If your primary goal is to convert data between formats and you don't need openpyxl's feature set, consider whether a simpler library or even a direct CSV export would meet your needs. For most cases, though, openpyxl's read-only and write-only modes provide a balanced solution that avoids pulling in additional dependencies.