Fix Excel Corruption When Updating Large Workbooks with Python

In a community I participate in, a developer recently ran into a frustrating issue while automating report updates. They were using Python to open a 150MB Excel file, update a few specific cells, and save it back. However, the resulting file was consistently “corrupted” and Excel would trigger a “Repair” prompt that wiped out all formulas and formatting.

The Original Question

“I am using openpyxl to load a large workbook (approx. 200,000 rows). After modifying just ten cells and calling wb.save('filename.xlsx'), the file size drops significantly and Excel says the file is corrupted. I have 16GB of RAM, but Python’s memory usage spikes and then the save fails. How can I update a large Excel file without breaking it?”

---

Immediate Fix: Use Read-Only and Write-Only Modes

The most common cause of corruption in large files is the exhaustion of system resources. By default, openpyxl loads the entire workbook into RAM as a collection of Python objects. For a 100MB+ file, this can easily consume several gigabytes of memory. If the process is throttled or interrupted during the write phase, the XML structure of the .xlsx (which is just a zipped collection of XMLs) becomes malformed.

To fix this, you should read the data in read_only mode and write the results to a new file using write_only mode.

# Python 3.10+ illustrative example — verify in your environment
from openpyxl import load_workbook, Workbook

source_filename = "large_data.xlsx"
destination_filename = "large_data_updated.xlsx"

# 1. Load in read_only mode to save memory
wb_source = load_workbook(filename=source_filename, read_only=True, data_only=True)
sheet_source = wb_source.active

# 2. Create a new workbook in write_only mode
wb_dest = Workbook(write_only=True)
sheet_dest = wb_dest.create_sheet()

# 3. Stream data from source to destination with modifications
for row in sheet_source.iter_rows(values_only=True):
    # Example modification: If the first column is 'ID_123', update the second column
    row_list = list(row)
    if row_list[0] == "ID_123":
        row_list[1] = "Updated Value"

    # Append the row to the new file
    sheet_dest.append(row_list)

wb_dest.save(destination_filename)
wb_source.close()

---

Detailed Explanation: Why Corruption Happens

An .xlsx file is a ZIP archive containing multiple XML files representing sheets, styles, and shared strings. When you use openpyxl in standard mode:

1. De-serialization: It parses every single XML node into a Python object.
2. In-Memory Storage: Large sheets create millions of objects, often hitting memory limits.
3. The “Save” Trap: When you call .save(), the library attempts to re-serialize everything. If the script crashes or the OS kills the process due to an OOM (Out of Memory) error mid-save, the ZIP archive is left in a “truncated” state. Excel sees this as a corrupted file.

Solution 2: Using Pandas with the openpyxl Engine

If you are performing bulk data updates rather than cell-by-cell formatting, pandas is often more robust. However, you must use the ExcelWriter context manager properly to ensure the file stream is closed correctly.

# Python 3.11+ illustrative example
import pandas as pd

file_path = "large_data.xlsx"

# Use the 'calamine' engine for faster reading if available (pip install python-calamine)
df = pd.read_excel(file_path, engine="openpyxl")

# Perform your logic
df.loc[df['Status'] == 'Pending', 'Status'] = 'Complete'

# Write back using a context manager to ensure proper file closing
with pd.ExcelWriter("large_data_fixed.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, index=False, sheet_name="Sheet1")

Why this works: Pandas handles the underlying data structures more efficiently than raw openpyxl for tabular data. By using the context manager (with statement), you guarantee that the file header is finalized even if an error occurs later in the script.

---

Edge Cases and Prevention

1. “Styles are lost when saving”

If you use read_only or write_only modes, openpyxl discards styles (colors, fonts, borders) to save memory.

• The Fix: If you must preserve styles on a massive file, you cannot use the “streaming” approach. Instead, you may need to increase your swap space/RAM or use a tool like pywin32 (on Windows) to control the Excel application directly, though this is significantly slower.

2. “The file size doubles after saving”

This usually happens because openpyxl does not automatically optimize the “Shared Strings” table.

• The Fix: Use pandas for writing if formatting isn’t a priority. Pandas writes strings directly to the sheet XML, which can sometimes be more efficient for very large, repetitive datasets.

Related Follow-up Questions

Q: Is there a faster way to read large Excel files? Yes. As of 2023/2024, the calamine engine is significantly faster than openpyxl for reading. You can use it in pandas via pd.read_excel(file, engine="calamine"). It is written in Rust and handles memory much more efficiently.

Q: Can I update an existing sheet without loading the whole workbook? Not easily with openpyxl. Because of how the ZIP/XML structure works, you generally have to rewrite the entire XML part for that specific sheet. For “in-place” updates without rewriting the whole file, consider using an actual database (like SQLite) for the data and only exporting to Excel for the final report.

Q: How do I prevent corruption if my script crashes? Never save directly to your source file. Always write to a temporary file (e.g., temp_output.xlsx) and use os.replace() to overwrite the original file only after the save process has successfully completed.