Guide

Getting Started With Python Excel AutomationDeep dive

Pick an Excel Engine for .xlsx, .xlsm, .xls, .xlsb and .ods

read_excel dispatches to five different parsers. Map each extension to the engine that reads it, keep macros through an edit, and sniff files that arrive misnamed.

pandas.read_excel() looks like one function, but it is really a dispatcher over four or five independent parsers, and most of the confusing errors in Python Excel work come from the wrong one being selected. The extension decides the default, the default is sometimes not installed, and two of the formats are not related to each other at all despite both being "Excel files". This guide, part of Choosing a Python Excel Library, is the map from extension to engine.

Prerequisites

Install only what your formats need:

Bash

pip install openpyxl          # .xlsx and .xlsm, read and write
pip install python-calamine   # .xlsx, .xlsm, .xls, .xlsb — read only, fast
pip install pyxlsb            # .xlsb only, read only
pip install "xlrd==1.2.0"     # legacy .xls only (xlrd 2.0 dropped it)
pip install odfpy             # .ods, read and write
pip install XlsxWriter        # .xlsx write only, richest formatting

The map from extension to engine

.xlsx and .xlsm are the same OOXML container; the difference is that .xlsm carries a VBA project. .xls is the pre-2007 binary format and shares nothing with them. .xlsb is a modern binary format — the same logical model as .xlsx, stored as binary records instead of XML. .ods is OpenDocument, produced by LibreOffice. Treating those four as variations of one format is the root of most engine errors.

Python

import pandas as pd

modern = pd.read_excel("book.xlsx")                          # openpyxl by default
macro = pd.read_excel("book.xlsm")                           # openpyxl too
legacy = pd.read_excel("archive.xls", engine="xlrd")         # xlrd 1.2.0
binary = pd.read_excel("large.xlsb", engine="pyxlsb")        # or "calamine"
opendoc = pd.read_excel("sheet.ods", engine="odf")
fast = pd.read_excel("book.xlsx", engine="calamine")         # any of the four, quickly

Preserving macros when editing .xlsm

The .xlsm trap is quiet and expensive: openpyxl opens the file happily, saves it happily, and the result has no macros in it. The VBA project is a separate part of the container that openpyxl only carries across when told to.

Python

from openpyxl import load_workbook

book = load_workbook("dashboard.xlsm", keep_vba=True)
book["Data"]["B2"] = 128400
book.save("dashboard-updated.xlsm")        # extension must stay .xlsm

Both halves are required. keep_vba=True preserves the project; saving under .xlsx discards it regardless. Work with Macro-Enabled .xlsm Files in openpyxl covers what else survives that round trip.

Choosing between a reader and a writer

Half the engines in the list read only, and the split is not obvious from their names. calamine and pyxlsb never write. xlsxwriter never reads. openpyxl and odfpy do both. That matters for the shape of a job: a fast read with calamine and a formatted write with xlsxwriter is a perfectly ordinary pairing, and neither one could do the other's half.

Python

import pandas as pd

frame = pd.read_excel("source.xlsb", engine="calamine")      # fast read, binary format
with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Data", index=False)   # formatted write

Detecting the real format before trusting the extension

Files arrive misnamed constantly — a .xls that is really a CSV, an .xlsx that is really an HTML table saved by a reporting tool. Checking the magic bytes turns a confusing parser error into a clear message.

Python

from pathlib import Path

SIGNATURES = {
    b"PK\x03\x04": "OOXML (.xlsx/.xlsm/.ods)",
    b"\xd0\xcf\x11\xe0": "OLE2 (legacy .xls)",
}

def sniff(path: Path) -> str:
    head = path.open("rb").read(8)
    for magic, name in SIGNATURES.items():
        if head.startswith(magic):
            return name
    if head.lstrip()[:1] in (b"<", b"{"):
        return "text — HTML or JSON, not a workbook"
    return "text — probably CSV"

print(sniff(Path("suspect.xls")))

That check costs microseconds and removes a whole class of support question. Fix "Excel file format cannot be determined" in pandas walks through the error it prevents.

How pandas decides, and why the error is confusing

Understanding the resolution order explains almost every engine error message. pandas takes an explicit engine= argument if you gave one. Otherwise it maps the extension to a default. Then it imports the parser it chose — and that import is where a missing dependency surfaces, which is why the traceback names openpyxl when the real problem is that the file was a CSV all along.

Nothing in that sequence inspects the file's contents. A .xlsx that is really an HTML table gets sent to openpyxl, which fails deep inside a zip reader with a message about a corrupt archive. That is the reasoning behind sniffing the bytes yourself before the read, and behind Fix BadZipFile Error When Reading Excel in Python, which is the same failure seen from the other side.

A defensive reader that names the real problem

Wrapping the read once turns every one of these into a message an operator can act on, which matters far more in a scheduled job than in an interactive session.

Python

from pathlib import Path
import pandas as pd

ENGINE_BY_SUFFIX = {
    ".xlsx": "calamine", ".xlsm": "calamine", ".xlsb": "calamine",
    ".xls": "calamine", ".ods": "odf",
}

def read_any(path, **kwargs):
    path = Path(path)
    engine = ENGINE_BY_SUFFIX.get(path.suffix.lower())
    if engine is None:
        raise ValueError(f"{path.name}: unsupported extension {path.suffix!r}")
    try:
        return pd.read_excel(path, engine=engine, **kwargs)
    except ImportError as exc:
        raise RuntimeError(f"{path.name} needs the {engine} engine: pip install python-calamine") from exc
    except Exception as exc:
        head = path.open("rb").read(4)
        if not head.startswith((b"PK\x03\x04", b"\xd0\xcf\x11\xe0")):
            raise ValueError(f"{path.name} is not a workbook — it starts with {head!r}") from exc
        raise

print(read_any("monthly.xlsb").shape)

One dictionary and one try block replaces the guessing, and the failure message names the file and the reason rather than a line inside somebody else's parser. That is the same principle applied in Log Python Excel Script Output to a File — fail where the cause is, not three stages downstream.

Common pitfalls

Symptom	Cause	Fix
`InvalidFileException: openpyxl does not support the old .xls format`	Legacy binary file sent to an OOXML parser	`engine="calamine"`, or `xlrd==1.2.0`
`XLRDError: Excel xlsx file; not supported`	xlrd 2.0 asked to read `.xlsx`	Let pandas pick openpyxl, or install calamine
Macros vanish after an edit	`keep_vba` not set, or saved as `.xlsx`	`load_workbook(path, keep_vba=True)` and keep the `.xlsm` extension
`ValueError: Excel file format cannot be determined`	The file is not a workbook at all	Sniff the magic bytes as above
`.xlsb` write fails	No mainstream library writes `.xlsb`	Write `.xlsx`, or save through Excel itself
`ModuleNotFoundError: odf`	`.ods` needs odfpy	`pip install odfpy`

Converting between formats once you can read them

Once the right engine is reading the file, converting it is usually the next step — a .xls archive that nobody can process, or an .xlsb export that has to become a shareable workbook. The pattern is the same in every direction: read with whatever handles the source, write with whatever produces the target.

Python

from pathlib import Path
import pandas as pd

def to_xlsx(source: Path) -> Path:
    target = source.with_suffix(".xlsx")
    tabs = pd.read_excel(source, sheet_name=None, engine="calamine")
    with pd.ExcelWriter(target, engine="xlsxwriter") as writer:
        for name, frame in tabs.items():
            frame.to_excel(writer, sheet_name=name[:31], index=False)
    return target

print(to_xlsx(Path("archive-2011.xls")))

Two details make that reliable on real archives. sheet_name=None reads every tab rather than only the first, which is how multi-sheet legacy files quietly lose data during a conversion. And name[:31] respects Excel's sheet-name limit — a tab called something longer raises on write, and the error names the limit rather than the tab, which makes it slower to diagnose than it should be.

What no conversion carries across is formatting: this reads values and writes values, so styles, merged cells and formulas from the source are gone. When those matter, the file has to be edited rather than rebuilt, and that means openpyxl for OOXML or Excel itself for anything else. Convert .xls to .xlsx with Python covers the cases where the fidelity matters.

Performance and scale

Engine choice is the largest read-side lever in this ecosystem, and it changes nothing else about your code. calamine parses in Rust and typically finishes a wide sheet several times faster than openpyxl; on binary .xlsb it beats pyxlsb comfortably too. The one caveat is that it returns values, not cells — no number formats, no fills, no comments — so it is a data reader rather than a file reader.

Python

import time
import pandas as pd

for engine in ("openpyxl", "calamine"):
    start = time.perf_counter()
    frame = pd.read_excel("wide.xlsx", engine=engine)
    print(f"{engine:>9}: {time.perf_counter() - start:5.2f}s  {frame.shape}")

When the same file is read repeatedly, the engine stops being the interesting variable at all — see Excel vs CSV vs Parquet for Python Data Pipelines.

Conclusion

Pin the engine explicitly rather than relying on the extension, and install exactly the parsers your formats need. .xlsx and .xlsm are one format with an optional macro payload; .xls and .xlsb are unrelated binary formats that need their own readers; .ods needs odfpy. calamine collapses most of that into one fast read-only dependency, leaving openpyxl for in-place edits and xlsxwriter for formatted output — a three-package set that covers nearly every job.

Frequently asked questions

Which engine reads the most formats? python-calamine. One install covers .xlsx, .xlsm, .xls and .xlsb, which is why it has largely replaced the pile of format-specific packages. It is read-only, so you still need a writer.

Why does openpyxl refuse my .xls file? Because .xls is a completely different binary format, not an older version of .xlsx. openpyxl only implements OOXML and raises InvalidFileException rather than guessing. Use calamine, or xlrd pinned to 1.2.0.

Do I lose macros when I edit an .xlsm file? Yes, unless you pass keep_vba=True to load_workbook and save with the .xlsm extension. Without it openpyxl writes a workbook with no VBA project and Excel opens it as a plain .xlsx.

Can I write .xlsb from Python? Not with the mainstream libraries — pyxlsb and calamine both read it and neither writes it. Write .xlsx instead, or drive Excel itself and save as .xlsb through the application.

What reads OpenDocument .ods files? odfpy, through pandas with engine='odf' for reading and to_excel(engine='odf') for writing. It is slower than the .xlsx path but it is the only pure-Python route to the format.

Up one level: Choosing a Python Excel Library — the library comparison this engine map sits inside.
Handling Excel File Formats and Conversions — converting between the formats once you can read them.
Read .xls Files in Python with xlrd and pandas — the legacy format in detail, including the xlrd 2.0 break.
Work with Macro-Enabled .xlsm Files in openpyxl — keeping the VBA project through an edit.
Troubleshooting Common Python Excel Errors — the errors an engine mismatch produces, decoded.