StackPractices
intermediate By Mathias Paulenko

Read and Write Excel Files in Python (openpyxl + pandas)

How to read, write, and format Excel spreadsheets in Python using pandas and openpyxl, with copy-paste examples for real reports.

Topics: data

Overview

Sooner or later every backend job ingests an Excel file — a CRM export, a financial report, an inventory dump. Python handles them well with two libraries: pandas for data-centric work and openpyxl for cell-level control like formatting and formulas. This recipe covers both approaches for common tasks: reading sheets, writing data, styling a report, and dealing with workbooks too large to load in one go.

When to Use

  • You need to load Excel exports from business tools into a DataFrame for analysis
  • You’re generating Excel reports from a database or API and they need styling (colors, number formats, column widths)
  • You’re automating a workflow that produces or consumes several sheets in one workbook
  • You need to append data to an existing file without destroying its formatting

Skip this when the source is already a database (query it directly instead of round-tripping through Excel) or when you only need a one-off look at the data. For flat files, see Parse CSV Files with Python and Pandas. CSV is much faster to parse.

Choosing the right library:

flowchart diagram: Excel task

Solution

The snippets below are copy-paste ready. A runnable version with all the examples lives in the companion repository.

Install both libraries. pandas uses openpyxl as its engine for .xlsx files, so a missing openpyxl is the most common setup failure.

pip install pandas openpyxl

Reading Excel with pandas

import pandas as pd

# Read a single sheet
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")
print(df.head())
print(df.columns)

# Read all sheets into a dict of DataFrames
sheets = pd.read_excel("data.xlsx", sheet_name=None)
for name, df in sheets.items():
    print(f"Sheet: {name}, rows: {len(df)}")

Writing Excel with pandas

import pandas as pd

df = pd.DataFrame({
    "name": ["Alice", "Bob", "Charlie"],
    "score": [85, 92, 78],
})

# Basic write
df.to_excel("output.xlsx", index=False, sheet_name="Results")

# Multiple sheets
with pd.ExcelWriter("report.xlsx") as writer:
    df.to_excel(writer, sheet_name="Summary", index=False)
    df[df["score"] > 80].to_excel(writer, sheet_name="High Scores", index=False)

Cell-level control with openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

wb = Workbook()
ws = wb.active
ws.title = "Report"

# Header row with styling
headers = ["Name", "Score", "Grade"]
header_fill = PatternFill(start_color="1a56db", end_color="1a56db", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)
thin_bottom = Border(bottom=Side(style="thin", color="1a56db"))

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.border = thin_bottom
    cell.alignment = Alignment(horizontal="center")

# Data rows
data = [("Alice", 85, "B"), ("Bob", 92, "A"), ("Charlie", 78, "C")]
for row_idx, (name, score, grade) in enumerate(data, 2):
    ws.cell(row=row_idx, column=1, value=name)
    ws.cell(row=row_idx, column=2, value=score)
    ws.cell(row=row_idx, column=3, value=grade)

# Auto-size columns (openpyxl doesn't do this for you)
for col in ws.columns:
    max_length = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_length + 2

# Freeze the header and add a filter for readability
ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions

wb.save("formatted_report.xlsx")

Reading with openpyxl

from openpyxl import load_workbook

wb = load_workbook("data.xlsx", data_only=True)  # data_only reads cached values
ws = wb["Sheet1"]

for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)

# Access a specific cell
print(ws["A1"].value)

Adding formulas

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = 10
ws["A2"] = 20
ws["A3"] = 30
ws["A4"] = "=SUM(A1:A3)"
ws["A5"] = "=AVERAGE(A1:A3)"

wb.save("formulas.xlsx")

Explanation

pandas wraps openpyxl internally when reading and writing .xlsx files. Use pandas for data-centric operations (filtering, grouping, joining) and openpyxl when you need cell-level control (formatting, formulas, merged cells, charts). If you need the same task in Java or JavaScript, Parse Excel Files covers it.

Key differences:

  • pd.read_excel returns a DataFrame. Great for analysis, but it drops all formatting.
  • openpyxl.load_workbook preserves formatting and gives you cell objects. Slower on large files.
  • pd.ExcelWriter with engine="openpyxl" writes DataFrames while keeping an existing workbook’s formatting.

Two caveats about formulas bite everyone at least once:

  • openpyxl writes formula strings but never computes them. =SUM(A1:A3) is stored as text until Excel (or LibreOffice) opens the file and calculates it.
  • data_only=True returns None for formula cells if the file was never opened and saved by a real spreadsheet app, because openpyxl-generated files have no cached values to read. If you need the computed result, calculate it in Python instead of writing a formula.

Working with Large Files

openpyxl loads the entire workbook into memory. A 50MB .xlsx can consume 500MB+ of RAM, which is a problem on small servers or CI runners. Two escape hatches:

from openpyxl import load_workbook

# Streaming read: rows are yielded, not loaded all at once
wb = load_workbook("big.xlsx", read_only=True, data_only=True)
ws = wb["Sheet1"]

for row in ws.iter_rows(values_only=True):
    process(row)

wb.close()  # important in read_only mode: releases the file handle

For writing, Workbook(write_only=True) streams cells to disk with the same memory benefit. You lose random access (you can only append rows), but files with hundreds of thousands of rows become feasible.

When speed matters more than formatting, pd.read_excel(..., engine="calamine") (pandas 2.2+, requires the python-calamine package) is a Rust-based reader that’s noticeably faster than the openpyxl engine. And if the file is just a data source (not something a human will open), converting it to CSV or Parquet first is almost always the right call.

Appending to an Existing Workbook

A plain df.to_excel("report.xlsx") call replaces the whole file. To add a sheet to a workbook that already exists, open it in append mode:

import pandas as pd

new_rows = pd.DataFrame({"name": ["Dana"], "score": [95]})

with pd.ExcelWriter(
    "report.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace"
) as writer:
    new_rows.to_excel(writer, sheet_name="Summary", index=False)

if_sheet_exists accepts "replace" (rewrite that sheet), "overlay" (write over the existing cells), "new" (create Summary1), or "error". Other sheets and their formatting stay untouched.

Variants

LibraryLevelBest ForDependencies
pandasDataFrameData analysis, bulk read/writepandas, openpyxl
openpyxlCellFormatting, formulas, chartsopenpyxl
xlsxwriterCellWrite-only reports, charts, conditional formattingxlsxwriter
python-calamineDataFrameFast reads as pandas enginepython-calamine
xlrdRead-onlyLegacy .xls filesxlrd

If you need to export the same data to CSV or Excel from an app, Export CSV and Excel covers that flow; Import CSV and Excel covers the upload side.

Best Practices

  • Use pandas for reading and writing data; use openpyxl for formatting and formulas.
  • Always pass index=False to to_excel unless you actually need the index column.
  • Read formula results with data_only=True, and fall back to computing in Python when the cache is missing.
  • Set column widths explicitly; openpyxl doesn’t auto-fit.
  • Add freeze_panes and auto_filter to reports people will open; it costs two lines and makes large sheets usable.
  • Use the pd.ExcelWriter context manager so the file is always closed and flushed.
  • Keep number formats explicit (cell.number_format = "0.00", "yyyy-mm-dd") when the display matters.

Common Mistakes

  • Forgetting to install openpyxl. pandas needs it as an engine for .xlsx files.
  • Loading a huge workbook with plain load_workbook. Switch to read_only=True when a file won’t fit comfortably in memory.
  • Expecting data_only=True to return values on a file openpyxl wrote. The cache doesn’t exist until a spreadsheet app saves it.
  • Overwriting an existing workbook with to_excel. It replaces the file; use ExcelWriter with mode="a" to append.
  • Assuming openpyxl evaluates formulas. It writes them; it doesn’t compute them.
  • Ignoring number formats. Excel may render dates and floats differently than Python produces them.
  • Saving over a workbook somebody has open in Excel. On Windows that fails with PermissionError, so write to a different name or catch it.

Security Considerations

Excel files deserve a short security pass when they cross a trust boundary (user uploads, files sent to other people):

  • Formula injection: a cell value starting with =, +, -, or @ executes as a formula when someone opens the file in Excel. If you write user-controlled data to a workbook, prefix risky strings with ' or reject them. This is the classic CSV injection, and it applies to .xlsx too.
  • Zip bombs: .xlsx is a zip container, so a small upload can decompress into gigabytes of XML. Enforce a size limit on uploads before parsing.
  • Macros: .xlsm files can carry VBA code. openpyxl’s keep_vba=True preserves macros but never runs them. Still, don’t forward macro-enabled files to end users without a reason.
  • Never eval() anything read from a workbook. Treat cell values as untrusted strings.

See Also

Frequently Asked Questions

Should I use pandas or openpyxl?

pandas for anything that looks like data analysis: filtering, aggregating, joining, bulk exports. openpyxl when you need to control the file itself: cell styles, formulas, merged cells, charts, or editing a template workbook in place.

How do I install the libraries?

pip install pandas openpyxl. pandas calls into openpyxl for .xlsx support, so both are required.

How do I read a specific range of cells?

With openpyxl, use ws.iter_rows(min_row=2, max_row=10, min_col=1, max_col=3, values_only=True). With pandas, use the usecols and skiprows parameters of read_excel.

How do I add conditional formatting?

Use openpyxl.formatting.rule or xlsxwriter. Color scales and data bars are supported via ColorScaleRule and DataBarRule.

How do I append rows to an existing Excel file?

Open it with pd.ExcelWriter(path, mode="a", engine="openpyxl", if_sheet_exists="replace") and write the new sheet; other sheets and formatting are preserved. For row-level edits, load the workbook with openpyxl and write specific cells.

How do I handle .xls (legacy) files?

Use xlrd for reading and xlwt for writing. pandas supports them with engine="xlrd" and engine="xlwt". Note that xlrd dropped .xlsx support in version 2.0, so for old files you need xlrd<2.0 or python-calamine.

Can I create charts in Excel with Python?

Yes. openpyxl.chart supports bar, line, and pie charts. xlsxwriter also supports charts with a similar API. It can't read files, but it's often the better choice for polished write-only reports.