Reading and Writing Excel Files With openpyxl in Python

Python has several ways to touch an Excel file, and openpyxl is the one that treats the workbook as a workbook. It opens the package, builds a model of sheets and cells, and lets you read a value, change a style, add a table or rewrite a formula, one cell at a time. pandas is faster for tabular data and lower in ceremony; XlsxWriter is stronger at producing formatted files; openpyxl is what you reach for when you need to look at a file as a person would, or change part of one without rebuilding it.

This guide is the map for the openpyxl cluster. It explains the object model and the choices that matter, then points to articles on each task: opening a workbook and reading cell values, reading values and not formulas, writing a new file, editing an existing workbook, read-only mode, write-only mode, styling, charts, merged cells, tables and fixing errors on files from other tools. Background on the format is in what is inside an Excel file.

What openpyxl is, and is not

openpyxl is a pure-Python library for reading and writing the Office Open XML formats: .xlsx and .xlsm (and the template variants .xltx and .xltm). It is installed with pip install openpyxl. Installing lxml alongside it speeds up some operations, and installing defusedxml is recommended when you read files from sources you do not control, because the library's own documentation warns that it does not protect against maliciously constructed XML such as entity-expansion attacks. Pillow is needed for images.

It is not a spreadsheet engine. It does not calculate formulas: it stores formula text and reads back whatever value the last program to save the file cached (what happens to formulas when code reads an Excel file). It does not read the old binary .xls format or .xlsb; for those you need xlrd, pyxlsb or calamine (XLS versus XLSX versus XLSB). It does not render anything: no charts to images, no PDF. And it does not preserve everything when you load and save a file: the documentation is direct that features it does not read, such as images and charts in existing files, are lost on resave. The last point shapes how you use it for editing.

The object model

Four objects cover most of what you do.

Workbook. The file in memory. Workbook() creates a new one with one empty sheet; load_workbook(path) reads an existing file. It holds the sheets, the defined names, the document properties and the styles. wb.save(path) writes it out.

Worksheet. One sheet. wb.active is the sheet that opens first, wb["Orders"] looks one up by name, wb.sheetnames lists the names in order, and wb.create_sheet("Name") adds one. A worksheet holds cells, merged ranges, tables, column and row dimensions, filters and freeze panes.

Cell. One cell. ws["B2"] and ws.cell(row=2, column=2) return the same object. It has a .value, a .data_type (n for numbers, s for strings, b for booleans, d for dates, e for errors, f for formulas), a .number_format, and style objects (.font, .fill, .border, .alignment). Accessing a cell that does not exist creates it, which is a common cause of unexpected growth: merely looking at ws["Z1000"] adds a cell to the sheet.

Reference helpers. openpyxl.utils contains get_column_letter(3) returning "C" and column_index_from_string("AB") returning 28, which saves you from writing base-26 arithmetic.

Here is the whole thing in a few lines:

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
print(wb.sheetnames)
ws = wb["Orders"]
print(ws["A1"].value, ws["B2"].value, ws["B2"].data_type)

['Orders', 'Notes']
Order No 19.99 n

For a sheet whose first row is a header and whose B2 holds a price, the output names the sheets, shows the header text and the price with its type n.

Reading: the options that change the result

load_workbook takes arguments that decide what you read:

The default, data_only=False, returns formula text such as =B2*C2, and data_only=True returns the cached number or None when none was stored (reading only the values and not the formulas). A common bug is to open with data_only=True and then save(): the saved file contains values where the formulas were, so the workbook silently loses its logic. Open twice if you need both views, and never save the data-only copy over the original.

To read rows efficiently, iterate:

for row in ws.iter_rows(min_row=2, values_only=True):
    order_no, customer, amount = row[:3]
    print(order_no, customer, amount)

values_only=True yields tuples of plain values instead of cell objects, which is faster and uses less memory. Skip it when you need styles or coordinates. ws.max_row and ws.max_column report the sheet's extent as known from the file's dimension record or from the cells you have touched; they can be much larger than the real data when formatting stretches the used range, so never use them as a row count (validating row counts after an Excel import).

Dates arrive as datetime objects when the cell's number format looks like a date, and as numbers otherwise (a cheat sheet of Excel data types). The workbook's epoch attribute tells you the date system in use. Cells that are blank come back as None.

Writing: from a list to a file

Writing is the simplest part of the library:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order No", "Customer", "Amount"])
ws.append(["0007", "Acme", 19.99])
ws.append(["0012", "Globex", 5.0])
wb.save("orders.xlsx")

ws.append adds a row after the last one. Strings stay strings, so "0007" keeps its zeros; numbers become number cells; datetime and date objects become date cells with a default format. A string starting with = becomes a formula, which is useful for formulas and a hazard for untrusted text (sending database results to Excel properly every time). wb.save overwrites the file without asking, and raises PermissionError on Windows if the file is open in Excel. Writing is covered in writing data to a new Excel file with openpyxl.

openpyxl can write to a stream as well as a path, which suits web applications:

from io import BytesIO

buffer = BytesIO()
wb.save(buffer)
data = buffer.getvalue()

The bytes can be returned as an HTTP response with the XLSX content type, with no temporary file.

Editing an existing workbook

Editing means load_workbook, change cells, save. It works well for changing values, adding rows, restyling and adding sheets. It works poorly for workbooks full of features openpyxl does not model. The documentation lists what is lost or dropped on resave, and the list includes images and charts in existing files, some conditional formatting and data validation extensions, form controls and features from newer Excel versions. For macro workbooks you need keep_vba=True, and even then forms can be affected.

The safe habits: work on a copy, never overwrite the original; compare the list of zip parts before and after (unzip -l or zipfile.namelist()), because a missing xl/media or xl/charts folder tells you what was lost; and open the result in Excel before sending it. The details and the alternatives are in editing an existing workbook without losing formatting. For a template with charts and images that must survive, a better pattern is to leave the template untouched and fill a data sheet in a copy using another tool, or to generate the whole workbook from code.

Large files: the two special modes

A normal workbook object keeps every cell in memory as a Python object, and Python objects are large. A file of a few hundred thousand cells is fine, a file with millions is not. Two modes address this.

Read-only mode (load_workbook(path, read_only=True)) parses the sheet XML as a stream and yields rows without building cell objects. Memory stays low, apart from the shared strings table. In exchange, you cannot index cells randomly, you do not get merged cell or table metadata, and you must close the workbook when done (reading large Excel files in read-only mode).

Write-only mode (Workbook(write_only=True)) writes rows to disk as you append them. You cannot go back, cannot read cells you wrote, and can save only once (writing large Excel files quickly with write-only mode).

Both are the tool when the cell count is large, and the wrong tool when you need random access or editing.

Styles, charts and tables

Styling uses small value objects assigned to cells: Font, PatternFill, Border, Alignment, plus a number_format string. Reuse the objects rather than creating one per cell, because each distinct combination becomes a style record in the file (styling cells, fonts and borders with openpyxl). Named styles bundle a combination under a name so you apply it in one step.

Charts are built from chart classes (BarChart, LineChart, PieChart) fed with Reference objects that point at ranges (adding charts to an Excel sheet from Python). openpyxl writes the chart definition; Excel draws it. Because openpyxl does not calculate, the data the chart refers to must be values or formulas that Excel will evaluate on opening.

Tables, the Excel feature with a header and a named extent, are available as openpyxl.worksheet.table.Table. Reading them is straightforward through ws.tables, and reading by name is the cleanest way to import data from a workbook whose layout moves (reading Excel tables by name with openpyxl).

Merged cells and layout

Merged ranges are in ws.merged_cells.ranges. Only the top-left cell holds a value, and the others are MergedCell objects with None. When you read, plan for that (handling merged cells when reading with openpyxl); when you write, avoid merges in data sheets. Column widths live in ws.column_dimensions, row heights in ws.row_dimensions, frozen panes in ws.freeze_panes and filters in ws.auto_filter (freezing panes, filters and column widths from code).

Common errors and what they mean

Most openpyxl exceptions are about the file, not your code.

A full treatment is in fixing openpyxl errors on files from other tools. When a file opens in Excel and fails in openpyxl, the file is usually unusual; reading it with pandas and the calamine engine, or re-saving it in Excel or LibreOffice, often removes the problem.

Choosing between openpyxl and its alternatives

Choosing between openpyxl, xlsxwriter and calamine develops the comparison, and working with Excel in pandas covers the DataFrame route. In practice many projects use two: pandas or calamine to read, XlsxWriter to write, and openpyxl for the cases that need cell-level access or editing.

Habits that prevent most trouble

Close what you open. In read-only mode call wb.close(), or use try/finally. A workbook left open keeps the file handle, which blocks deletion on Windows.

Name sheets and columns, not positions. wb["Orders"] survives a reordering of tabs; wb.worksheets[0] does not. Look up columns by header text.

Fail loudly on surprises. If an expected sheet or header is missing, raise. A script that quietly reads the wrong sheet produces confident wrong numbers.

Keep identifiers as text. Assign strings for ids, and read them back as strings; check cell.data_type when a value looks odd.

Separate reading from interpreting. Read cells into plain Python structures, then validate and convert in one place. The reading code stays simple and the rules are testable.

Test with ugly files. A small fixture workbook with a merged cell, a formula, a date, a text number and a blank row catches most surprises. Generate it with openpyxl in the test setup.

Do not parse untrusted files without precautions. Install defusedxml, limit the file size, and run the job with narrow permissions. An XLSX is a zip, and a hostile one can be much bigger when unpacked.

A complete small example

This script reads an orders workbook defensively and prints a summary. It uses most of the ideas above: a named sheet, header lookup, text ids, a clear failure on missing columns, and a count:

from openpyxl import load_workbook

REQUIRED = ["Order No", "Customer", "Amount"]

wb = load_workbook("orders.xlsx", data_only=True, read_only=True)
try:
    ws = wb["Orders"]
    rows = ws.iter_rows(values_only=True)
    header = [str(h).strip() if h is not None else "" for h in next(rows)]
    missing = [c for c in REQUIRED if c not in header]
    if missing:
        raise SystemExit(f"missing columns: {missing}")
    idx = {name: header.index(name) for name in REQUIRED}

    count = 0
    total = 0.0
    for row in rows:
        if row[idx["Order No"]] in (None, ""):
            continue
        count += 1
        total += float(row[idx["Amount"]] or 0)
    print(f"{count} orders, total {total:.2f}")
finally:
    wb.close()

2 orders, total 24.99

For the two-row workbook above, the output is 2 orders, total 24.99. The script reads cached values, streams the rows, finds columns by name and closes the workbook even if something fails. Replace the print with a load into a staging table and you have the beginning of an import (getting Excel data into a database).

Cell values in depth: what you can assign and what you get back

openpyxl converts Python values to cell types on assignment, and the rules are worth knowing because they decide what a reader sees. Integers and floats become numbers. Decimal values are accepted and stored as numbers, with the 15-digit limit of Excel applying when it displays them. Strings become text, except those that start with an equals sign, which become formulas, and those that look like errors (#N/A, #DIV/0!), which become error cells. Booleans become TRUE or FALSE. datetime, date and time objects become serial numbers with a default date or time format, and timedelta becomes a duration. None clears the cell. Anything else raises a ValueError with a message that names the type, which is the usual way to find a database driver that returned an unexpected object, such as a UUID or a memoryview.

On the way back, the type depends on the stored cell. A numeric cell returns an int when the stored text has no decimal point and a float otherwise, so 5 and 5.0 may come back differently from the same sheet depending on how the writer stored them. A cell whose number format is a date format returns a datetime; one whose format is a time format returns a time; a duration format such as [h]:mm:ss returns a timedelta. A formula cell returns the formula text, or the cached value under data_only. A merged cell other than the top left returns None. Always check cell.data_type and cell.number_format when a value looks wrong; they say what the file holds, which is more reliable than guessing from the Python value (a cheat sheet of Excel data types as programs see them).

Strings deserve one more rule: openpyxl returns them exactly as stored, including leading and trailing spaces and the non-breaking space (code 160) that survives str.strip() only if you pass it explicitly. Data that was typed by people carries such characters, and joins against clean keys fail without an error. Strip and normalize on the way in.

Dates, times and time zones

Date handling is the most frequent source of bugs, and the rules are simple. Excel stores a date as a number of days since an epoch, and the cell's number format decides whether it displays as a date. openpyxl detects date formats by inspecting the format string and converts the number to a datetime using the workbook's epoch, so a workbook in the 1904 system returns correct dates as long as the flag is set in the file. When the format is custom or unusual, the detection may fail and you receive a number; convert it with openpyxl.utils.datetime.from_excel(value), which also accepts the epoch.

Writing is the mirror image: assign a datetime and openpyxl writes the serial and a default format (yyyy-mm-dd h:mm:ss). Change cell.number_format to something shorter. Time zones are not supported: assigning a timezone-aware datetime raises an error saying that Excel does not support timezones, so convert to the zone your readers expect and drop the zone with .replace(tzinfo=None) (why an exported Excel date shows as a number). Dates before 1 March 1900 are subject to Excel's leap-year quirk, and openpyxl follows Excel's own interpretation (the 1900 leap year bug); real data rarely goes that far back, and historical dates are better stored as ISO text.

Validation, conditional formats and names

Beyond cells and styles, a worksheet holds objects that govern behavior. Data validation restricts what a reader may type in a range, through DataValidation objects added with ws.add_data_validation: a list drawn from a range or a fixed set of strings, a whole number between limits, a date after a given day. It is the cheap way to make an input sheet safer, and it applies only inside spreadsheet programs, not to code that writes cells. Conditional formatting applies styles by rule, through ws.conditional_formatting.add, with rule classes such as CellIsRule, FormulaRule and ColorScaleRule. Both are preserved on load and save in the common cases and dropped in some extension cases, which the library reports with a warning.

Defined names are in wb.defined_names, and names local to a sheet in ws.defined_names in 3.1. A name's attr_text is the reference as text. Creating one is a matter of constructing a DefinedName and adding it to the workbook. Print settings, such as print areas and repeated header rows, are properties of the worksheet. None of these changes the cell values, but they decide whether a workbook feels finished (Excel named ranges and tables explained for developers).

Using openpyxl and pandas together

The two libraries complement each other, and the combination is common. pandas reads and writes tables through openpyxl as its default engine for XLSX, and gives you openpyxl objects when you need them. After df.to_excel(writer, ...) with engine="openpyxl", writer.book is the openpyxl workbook and writer.sheets["Name"] the worksheet, so you can apply styles and widths with the calls in this guide. In the other direction, read with openpyxl when you need structure (the header row's position, a table's extent, a merged region's value) and hand the resulting lists to pd.DataFrame for analysis.

One practical pattern for messy workbooks: use openpyxl to find where the data is, then let pandas read it. Locate the header row by scanning for expected names, find a table's reference from ws.tables, or compute the last data row by counting non-empty key cells. Pass the result to read_excel as skiprows, usecols and nrows. That keeps the fragile layout logic in a short function with tests and leaves the type handling to pandas (working with Excel in pandas).

Performance and memory

The cost of openpyxl is mostly in two places: parsing XML and holding cell objects. Loading a workbook builds a Python object for every cell that exists, so memory grows with the number of non-empty cells, and a sheet with a million cells can take hundreds of megabytes. Three levers help. Use read-only mode for one-pass reads. Install lxml, which the library will use for faster parsing and writing when it is available. And read only what you need: iter_rows with min_row, max_row, min_col and max_col limits the work to a window.

Writing has the same shape. Normal mode builds the whole sheet in memory before it writes. Write-only mode streams. Styles add cost: every distinct combination is stored once, but assigning a style object to a million cells still makes a million assignments. Apply column-level formats where you can, and reuse style objects.

When a job is slow, measure before changing it. A simple timer around the load and around the iteration shows which half dominates. Reading a workbook is often twice as slow as the processing that follows it, and a different reader (calamine) or a different format (CSV) removes the problem at its source (reading Excel files faster with python calamine). The numbers depend on the file, the machine and the version, and I have not measured them for this guide.

Testing code that touches workbooks

Code that reads or writes workbooks fails in ways that small unit tests do not predict, so test it with files. Generate fixture workbooks in the test setup with openpyxl rather than committing binary files: a few lines create a workbook with a merged cell, a formula without a cache, a text id with leading zeros, a date, a blank row in the middle and a hidden sheet. Then assert on what your code returns for each. For writers, read the output back and check sheet names, header text, cell types and number formats, not only the values.

Keep one real file, anonymized, from each source system as a regression fixture, because real files contain the quirks nobody thinks to invent. When a new quirk appears in production, add the file and the test before fixing the bug. A small library of ugly workbooks is the most valuable test asset in this area.

Security

An XLSX file is a zip of XML, and both layers have known abuse patterns. A zip bomb is a small archive that expands to gigabytes; a crafted XML file can expand entities to exhaust memory. openpyxl's documentation recommends installing defusedxml when reading untrusted files, and the library uses it if present. Add file-size limits before you read an upload, check ZipInfo.file_size for the uncompressed sizes, and run the parser in a process with a memory limit. Do not open untrusted workbooks in a process that holds credentials.

Treat the contents as untrusted too. Cell text can be a formula waiting to run when someone opens a re-exported copy, and hyperlinks in cells can point anywhere. Never evaluate cell contents as code, never build SQL from them by string formatting, and neutralize leading =, +, - and @ when you write user text back into a workbook (sending database results to Excel properly every time).

When openpyxl is not the right library

You only need a table in a DataFrame. pandas.read_excel is shorter and handles dtypes, and it uses openpyxl under the hood anyway.

You need speed on a huge read. calamine is much faster to parse XLSX in typical use, and reads XLS and XLSB too (reading Excel files faster with python calamine).

You are producing a formatted report from scratch. XlsxWriter has richer formatting, better charts and a constant-memory mode that is simpler than write-only openpyxl.

You need to calculate formulas. Use a spreadsheet engine: Excel, LibreOffice headless, or a Python formula library for simple models. Or compute the numbers in Python or SQL.

The file is XLS or XLSB, or password-protected. openpyxl reads neither. Convert, decrypt with a dedicated tool, or use a different reader.

The library is documented in the openpyxl documentation, including its pages on optimized modes and simple usage. To look at a small extract as JSON, use the CSV to JSON converter.