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.
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:
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_excelreturns a DataFrame. Great for analysis, but it drops all formatting.openpyxl.load_workbookpreserves formatting and gives you cell objects. Slower on large files.pd.ExcelWriterwithengine="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=TruereturnsNonefor 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
| Library | Level | Best For | Dependencies |
|---|---|---|---|
| pandas | DataFrame | Data analysis, bulk read/write | pandas, openpyxl |
| openpyxl | Cell | Formatting, formulas, charts | openpyxl |
| xlsxwriter | Cell | Write-only reports, charts, conditional formatting | xlsxwriter |
| python-calamine | DataFrame | Fast reads as pandas engine | python-calamine |
| xlrd | Read-only | Legacy .xls files | xlrd |
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=Falsetoto_excelunless 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_panesandauto_filterto reports people will open; it costs two lines and makes large sheets usable. - Use the
pd.ExcelWritercontext 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
.xlsxfiles. - Loading a huge workbook with plain
load_workbook. Switch toread_only=Truewhen a file won’t fit comfortably in memory. - Expecting
data_only=Trueto 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; useExcelWriterwithmode="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.xlsxtoo. - Zip bombs:
.xlsxis a zip container, so a small upload can decompress into gigabytes of XML. Enforce a size limit on uploads before parsing. - Macros:
.xlsmfiles can carry VBA code. openpyxl’skeep_vba=Truepreserves 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
- openpyxl documentation: the full API for styles, charts, and workbook features.
- pandas
read_excelreference: engines, sheet selection, and dtype control. - xlrd 2.0 changelog: explains why modern xlrd only reads
.xls.
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.
Related Resources
Parse Excel Files in Python, JavaScript, and Java
How to read and write Excel (.xlsx) files in Python, JavaScript, and Java with openpyxl, SheetJS, and Apache POI.
RecipeImport Data from CSV/Excel
How to parse and import data from CSV and Excel files with validation, streaming, and batch inserts.
RecipeExport Data to CSV and Excel Files
How to export structured data to CSV and Excel files efficiently.
RecipeParse CSV Files with Python and Pandas
How to read, filter, and transform large CSV files efficiently using Python pandas and the csv module.
RecipeConvert CSV to JSON
Turn CSV files into structured JSON using Python, JavaScript, and Java. Pick the right library for quick one-off conversions or large streaming pipelines.
RecipeGenerate PDF Reports in Python: ReportLab & fpdf2 Guide
Create styled PDF documents from data using ReportLab and fpdf2 in Python.