openpyxl
openpyxl is the standard Python library for reading and writing Excel 2010+ files (xlsx and xlsm) without Excel installed. It models workbooks, worksheets, and cells as Python objects and covers styles, formulas, charts, merged cells, and conditional formatting. It is pure Python, hosted on Heptapod rather than GitHub, and it is the engine pandas uses under the hood for xlsx, which is why its download numbers dwarf its public profile.
The workhorse for xlsx in Python: dependable, pure Python, and everywhere, including inside pandas. Accept the slow release pace and know its hard limits (no formula evaluation, memory use in default mode) before betting a pipeline on it.
Use it if
- You need to read or generate .xlsx reports from Python on a machine without Excel
- You must edit an existing workbook in place while keeping sheets, styles, and formulas intact
- You need cell-level control (styles, merged cells, charts, number formats) that pandas.to_excel does not expose
- You process very large sheets and can use read_only or write_only mode to keep memory flat
- Your files are legacy .xls; openpyxl only handles the 2010+ xlsx/xlsm formats
- You expect computed formula results. openpyxl stores formulas as strings and never evaluates them; data_only=True only returns whatever value Excel cached on last save
- Your work is dataframe in, dataframe out; pandas is less code, and python-calamine reads spreadsheets much faster if speed matters
- You want a fast-moving project. The latest release, 3.1.5, shipped in June 2024, and the small team on Heptapod turns issues around slowly
Setup reality
pip install openpyxl brings a single pure-Python dependency (et-xmlfile), so installs never break. The friction is conceptual: the default mode loads the whole workbook into memory, which gets painful past a few hundred thousand cells, so you must learn read_only and write_only modes and their restrictions (write_only sheets can only append). Cell indexing is 1-based, images and some drawing content in an opened file are not preserved on save, and formula evaluation simply does not exist.
Patterns
Open a workbook and read cellsread-workbook
from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb["Sheet1"] # or wb.active
print(ws["A1"].value)
print(ws.cell(row=2, column=3).value)Row and column indexes are 1-based, not 0-based; ws.cell(row=1, column=1) is A1.
Create and save a new workbookwrite-workbook
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Data"
ws["A1"] = "name"
ws["B1"] = "qty"
wb.save("out.xlsx")save() silently overwrites an existing file, and saving over a file you loaded drops images and some drawing content.
Iterate rows efficientlyiterate-rows
for row in ws.iter_rows(min_row=2, max_col=3, values_only=True):
name, qty, price = row
print(name, qty, price)values_only=True yields plain tuples instead of Cell objects, which is both faster and easier to unpack.
Append rows of dataappend-rows
rows = [
("widget", 3, 9.99),
("gadget", 7, 24.50),
]
for r in rows:
ws.append(r)append() writes to the first empty row at the bottom of the used range, so a stray value far down the sheet shifts everything after it.
Style cells (font, fill, alignment)style-cells
from openpyxl.styles import Font, PatternFill, Alignment
cell = ws["A1"]
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill("solid", fgColor="4472C4")
cell.alignment = Alignment(horizontal="center")Styles apply per cell, not per row or column range; styling a whole header row means looping over its cells.
Write a formula and understand data_onlywrite-formula
ws["B10"] = "=SUM(B2:B9)"
wb.save("out.xlsx")
# later, to read cached results instead of formula strings:
from openpyxl import load_workbook
wb2 = load_workbook("out.xlsx", data_only=True)
print(wb2.active["B10"].value)openpyxl never computes formulas. data_only=True only returns the value Excel cached on last save; a file never opened in Excel returns None.
Read a huge file with constant memoryread-large-file
from openpyxl import load_workbook
wb = load_workbook("big.xlsx", read_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
process(row)
wb.close()read_only mode streams rows and must be closed explicitly; random cell access in this mode is very slow, so iterate in order.
Write a huge file with constant memorywrite-large-file
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in generate_rows():
ws.append(row)
wb.save("big.xlsx")write_only sheets support append() only: no reading cells back, no ws['A1'] assignment, and each row is committed once.
Merge cells and set column widthsmerge-cells-widths
ws.merge_cells("A1:D1")
ws["A1"] = "Quarterly Report"
ws.column_dimensions["A"].width = 24
ws.freeze_panes = "A2"After merging, only the top-left cell holds a value; writing to the other merged cells raises or is ignored.
Add a bar chart from sheet dataadd-chart
from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=8)
cats = Reference(ws, min_col=1, min_row=2, max_row=8)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E2")Charts reference cell ranges, not Python data; the chart renders from whatever those cells contain when opened in Excel.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| xlsxwriter | PyPI | When you only create new files and want a fast write-only library with strong formatting and chart support |
| python-calamine | PyPI | When you only read spreadsheets and want Rust-backed speed |
| pandas | PyPI | When your data is tabular and read_excel/to_excel covers everything you need |