Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Choosing a Python Excel Library

openpyxl, xlsxwriter, pandas, Polars or xlwings? Compare what each library can actually do — read, write, edit in place, drive Excel — and pick by the job.

Every Python Excel job starts with the same question: which library. The ecosystem has five real answers — openpyxl, xlsxwriter, pandas, Polars and xlwings — plus a handful of engines that sit underneath them, and the honest summary is that they are not competitors so much as tools for different halves of the job. This guide, part of Getting Started with Python Excel Automation, maps the landscape by the decision that actually drives it: what you need to do to the file.

Choosing a Python Excel library by the job at hand A decision node asks what the job needs to do to the file. Shaping data leads to pandas or Polars, producing a new formatted file leads to xlsxwriter, and editing an existing workbook or driving Excel leads to openpyxl or xlwings. What does the job do to the file? pandas / Polars group, join, reshape shape data xlsxwriter charts and formats build new openpyxl / xlwings change what exists edit / drive the library follows the verb, not the file extension

The short version: pandas and Polars are for the data, openpyxl and xlsxwriter are for the file, and xlwings is for the application. Almost every production script uses two of them together — a frame library to shape the numbers and a file library to lay them out — and the mistakes people make are nearly always the result of asking one of them to do the other's job.

What each library is actually for

Read the table below as a set of jobs, not a ranking. openpyxl is the only one that can open an existing workbook, change three cells and save it with everything else intact — that single capability is why it stays installed even on projects that write with something faster. xlsxwriter is write-only by design, and that constraint is what lets it stream a million rows in constant memory and expose the richest formatting API of the group. pandas and Polars do not touch Excel at all: they delegate to one of the others and spend their effort on the table in between.

What each Python Excel library can and cannot do A capability grid: openpyxl reads, writes and edits in place; xlsxwriter writes only but has the richest formatting; pandas and Polars delegate to an engine; xlwings drives a running copy of Excel. Library Reads Writes Edits in place Needs Excel openpyxl yes yes yes no xlsxwriter no yes no no pandas via engine via engine no no Polars via calamine via xlsxwriter no no xlwings yes yes yes yes capability, not quality — each row is a different job

The engines matter as much as the libraries. pandas.read_excel() is a thin front end over whatever parser can handle the extension you gave it, so "pandas is slow at Excel" is usually a statement about openpyxl. Swapping the engine — covered in Speed Up pandas Excel Reads with the calamine Engine — changes the number without changing a line of your own logic.

Python
import pandas as pd

# Same call, three different parsers underneath.
default = pd.read_excel("sales.xlsx")                          # openpyxl
fast = pd.read_excel("sales.xlsx", engine="calamine")          # python-calamine (Rust)
legacy = pd.read_excel("archive.xls", engine="xlrd")           # xlrd 1.2.0 only
print(default.shape, fast.shape, legacy.shape)

Reading: get the data in, then forget the format

For reading, the ranking is stable and mostly about the parser. openpyxl walks the sheet XML in Python; calamine does the same work in Rust and hands back a ready table. On a wide export the difference is the largest single speed lever available in this ecosystem, and it costs one keyword argument.

Relative time to read the same 200,000-row sheet openpyxl through pandas is the slowest path, python-calamine is several times faster on the same file, and reading a Parquet copy of the same data is faster again by a wide margin. pandas + openpyxl slowest pandas + calamine 4x faster Polars + calamine 5x faster Parquet copy read once, reuse relative cost shape holds across files; the exact ratios depend on column types

The numbers move with the file, but the shape holds: a Rust parser is several times faster than the pure-Python one, and converting the sheet to a columnar format once makes every later read almost free. That last point is the one worth internalising — if the same workbook is read more than twice, converting it is cheaper than parsing it again, as Convert Excel Files to Parquet with Python shows.

Python
import time
import pandas as pd

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

Reading is also where the defensive habits pay off. Whichever library you pick, name the sheet rather than trusting position, prune the columns you do not need, and pin the dtypes of anything that looks like an identifier — the reasoning is set out in Reading Excel Files with Pandas.

Writing: formatting versus volume

Writing splits cleanly. If the output is a table someone will open, sort and read, xlsxwriter gives you the most control for the least code: number formats, conditional formats, charts, autofilters and frozen panes are all first-class, and its constant_memory mode keeps a million-row export inside a fixed footprint. If the output has to merge into a workbook that already exists — a template with a logo, a summary tab, three years of history — openpyxl is the only option, because xlsxwriter cannot open a file at all.

Choosing a writer by what the output has to be Three writing paths: xlsxwriter for new formatted files, openpyxl for editing a workbook that already exists, and pandas.ExcelWriter as the shortest route from a DataFrame to a sheet. xlsxwriter new files only charts, formats, sparklines constant-memory mode fastest bulk writes openpyxl opens what exists patch cells, keep the rest styles as objects lossy for pivot caches pandas.ExcelWriter frame in, sheet out wraps either engine least code by far reach through for detail the question is whether the file already exists

pandas sits on top of either. DataFrame.to_excel() with an ExcelWriter is the shortest path from a frame to a formatted sheet, and the engine choice is yours:

Python
import pandas as pd

frame = pd.DataFrame({"Region": ["North", "South"], "Revenue": [128400.0, 96150.5]})

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Summary", index=False)
    book, sheet = writer.book, writer.sheets["Summary"]
    money = book.add_format({"num_format": "#,##0.00"})
    sheet.set_column("B:B", 14, money)
    sheet.freeze_panes(1, 0)

The same script with engine="openpyxl" writes the same values but reaches formatting through openpyxl's style objects instead. Neither is more correct; the deciding question is whether the file already exists.

Editing a workbook without breaking it

This is the capability that surprises people. Most of the ecosystem writes files; only openpyxl and the application drivers edit them. And openpyxl's edit is not lossless — it rebuilds the parts of the file it understands and drops what it does not, which historically has meant pivot table caches, some chart types, VBA in files opened without keep_vba=True, and slicers.

Python
from openpyxl import load_workbook

book = load_workbook("quarterly.xlsx")           # add keep_vba=True for .xlsm
sheet = book["Summary"]
sheet["B2"] = 128400.0                            # patch one number
book.save("quarterly-revised.xlsx")               # never save over the original

Saving under a new name is not fussiness — it is the difference between a bad run costing you a minute and costing you the source file. When the workbook carries objects openpyxl cannot round-trip, the alternatives are to drive Excel itself (see below) or to rebuild the file from scratch, and Populate an Excel Template Without Losing Formatting walks through choosing between them.

Driving Excel itself, and when it is worth it

xlwings and pywin32 are a different category: they do not parse the file, they remote-control a running copy of Excel. That buys the things the format-level libraries cannot do — recalculating formulas with Excel's own engine, refreshing a Power Query connection, running an existing macro, exporting a range to PDF exactly as Excel would print it — at the cost of needing Excel installed, being effectively Windows- or macOS-only, and being an order of magnitude slower per operation.

The rule that holds up in practice: reach for an application driver only when the value you need comes from Excel, not from the data. Recalculated values, a native PDF export and a macro run all qualify. Writing 40,000 rows does not — that belongs to xlsxwriter, and pushing it through COM will take minutes instead of seconds. Automating Excel with xlwings Basics covers the friendly API, and Automating Excel with COM and pywin32 covers the raw interface underneath it.

Combining them in one pipeline

The realistic answer to "which library" is usually "two". A reporting job that reads a large export, reshapes it and ships a formatted workbook uses a fast reader, a frame library and a formatting writer, and each hands off cleanly to the next.

A typical pipeline uses three libraries in sequence A fast parser reads the export, a frame library groups and sorts it, and a formatting writer emits the delivered workbook. one job, three libraries calamine parse the export pandas clean and group xlsxwriter format and ship pick the reader for speed, the writer for what the file must look like
Python
import pandas as pd

raw = pd.read_excel("export.xlsx", engine="calamine", dtype={"Account": "string"})
summary = (
    raw.dropna(subset=["Revenue"])
       .groupby("Region", as_index=False)["Revenue"].sum()
       .sort_values("Revenue", ascending=False)
)

with pd.ExcelWriter("regional-summary.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="By region", index=False)
    sheet = writer.sheets["By region"]
    sheet.set_column("A:A", 18)
    sheet.set_column("B:B", 16, writer.book.add_format({"num_format": "#,##0"}))
    sheet.autofilter(0, 0, len(summary), 1)

Three libraries, twelve lines, and each one is doing the job it is best at. That is the pattern the rest of this section elaborates: pick the reader for speed, the frame library for the transform, and the writer for what the file has to look like when it lands.

Memory: the number that decides for you

Speed is negotiable; memory is not. A .xlsx file is a zip of XML, and every library that reads one has to expand it. openpyxl's normal mode builds a Python object per cell, which is roughly one to two kilobytes each once you count the object header, the style reference and the dictionary that holds it — so a 500,000-cell sheet can cost well over a gigabyte before your own code sees a single value. That is the number that turns "it works on my laptop" into a killed container at 06:00.

Normal mode versus streaming mode on a large sheet In normal mode openpyxl builds a Python object for every cell, so memory grows with the sheet. In read-only or constant-memory mode a single row is held at a time and the footprint stays flat. normal mode one object per cell grid held in RAM random access grows with the file streaming mode one row at a time flat footprint forward-only survives huge sheets read_only the same file, two very different memory curves

Each library has one lever that changes the shape of that curve. openpyxl has read_only=True, which streams rows instead of materialising the grid, and write_only=True, which does the same in reverse. xlsxwriter has constant_memory, which flushes each row to disk as soon as it is written and never holds more than one row. calamine parses into a compact Rust structure and hands back one table. Polars keeps columnar Arrow buffers rather than Python objects.

Python
from openpyxl import load_workbook

# Streaming read: rows arrive as tuples, the grid is never built.
book = load_workbook("huge.xlsx", read_only=True, data_only=True)
sheet = book["Data"]
total = 0.0
for row in sheet.iter_rows(min_row=2, values_only=True):
    if row[3] is not None:
        total += row[3]
book.close()
print(f"{total:,.2f}")

The catch with every streaming mode is that it gives up random access: you get the rows in order, once, and you cannot go back. That trade is nearly always worth taking on a file large enough to care about, and Speed Up openpyxl with read-only Mode covers what changes in the API when you do.

What the wrong choice costs

Three failure modes account for most of the wasted afternoons in this ecosystem, and each is a library choice made on the wrong axis.

Reaching for pandas when the job is cell-level. to_excel writes values; it does not write borders, merged title rows, conditional formats or column widths. Scripts that try to force those through pandas alone end up reopening the file with openpyxl anyway, and the honest version of that script is shorter than the one that resisted it.

Reaching for openpyxl when the job is bulk. Writing 300,000 rows through the normal workbook API means 300,000 Python objects and a save step that serialises all of them at once. The same export through xlsxwriter in constant_memory mode finishes in a fraction of the time and a fixed amount of RAM, and the code is not meaningfully longer.

Reaching for xlwings when the job is data. Every read and write through the automation interface is a cross-process call; a loop that writes cells one at a time will take minutes where a file-level library takes a second. When xlwings is genuinely needed, the fix is to move whole ranges in one call rather than to abandon it.

Python
# Slow: one cross-process round trip per cell.
for row_index, value in enumerate(values, start=2):
    sheet.range(f"B{row_index}").value = value

# Fast: one round trip for the whole block.
sheet.range("B2").value = [[value] for value in values]

A thirty-second checklist

Answer these in order and the choice usually makes itself. Does the output need to land inside a workbook that already exists? If yes, openpyxl, and no other file-level library will do. Does the result need native charts, conditional formats or more than about 100,000 rows? If yes, xlsxwriter. Is the hard part the transform — joins, groupings, reshaping? Then pandas or Polars, with the file libraries reduced to the first and last line of the script. Does the answer depend on Excel recalculating something, refreshing a connection, or running an existing macro? Only then does an application driver enter the picture.

The one question worth asking before any of these: does the deliverable have to be a spreadsheet at all? A CSV or Parquet handoff between two automated systems removes the entire question, and Excel vs CSV vs Parquet for Python Data Pipelines makes that case in full. Excel earns its place when a person opens the file — not when a machine reads it.

Version traps worth knowing before you pin

Most of the confusing errors in this ecosystem are version problems wearing a library's name, and four of them recur often enough to be worth memorising.

xlrd 2.0 dropped support for .xls entirely. A requirements file that says xlrd and a workbook that ends in .xls will produce XLRDError: Excel xlsx file; not supported or a flat refusal, depending on which way round the mismatch falls. Pin xlrd==1.2.0 if you genuinely need the legacy format, or install python-calamine and stop thinking about it — it reads .xls, .xlsx and .xlsb through one parser. Fix "openpyxl does not support the old .xls format" walks through that specific message.

pandas needs an engine and does not install one. ModuleNotFoundError: No module named 'openpyxl' from a script that only imported pandas is not a bug — pandas deliberately leaves the parser as an optional dependency. Install the engines you actually use and pin them alongside pandas itself.

openpyxl's data_only=True returns None, not a value, when the workbook was last written by a tool that never calculated the formulas — which includes every file openpyxl itself produced. The cached value only exists if Excel put it there. That trap is set out in Read Formula Results with openpyxl data_only.

Engine names changed. engine="calamine" in read_excel requires a reasonably recent pandas and the python-calamine package — the pip name and the engine string are deliberately different, and installing calamine instead gets you an unrelated project.

Text
pandas>=2.2          # engine="calamine" support
openpyxl>=3.1        # .xlsx read/write and in-place edits
XlsxWriter>=3.2      # formatted writes, constant_memory
python-calamine>=0.2 # fast reads, .xls/.xlsb/.xlsx in one parser

A pinned requirements block like that one is worth more than any benchmark: it makes the container that runs at 06:00 behave the way the laptop did, which is the failure this ecosystem produces most often. Keep Excel Report Settings in a Config File covers the rest of making a job reproducible once the libraries are settled.

Key takeaways

  • The libraries divide by job, not by quality: pandas and Polars shape data, openpyxl and xlsxwriter produce files, xlwings and pywin32 drive the application.
  • openpyxl is the only one that can edit an existing workbook, and that edit is lossy for pivot caches, some charts and macros unless you take precautions.
  • xlsxwriter cannot open a file, which is exactly why it can stream a million rows in constant memory and expose the richest formatting API.
  • pandas.read_excel() is a front end over an engine; swapping in calamine is usually the single biggest read speedup available, and it needs one keyword argument.
  • Application drivers earn their overhead only when the value comes from Excel itself — recalculated formulas, native PDF output, an existing macro — never for bulk writing.
  • Most production scripts use two libraries together, and the combination is the answer, not a compromise.

Frequently asked questions

Which library should I install first? pandas plus openpyxl. That pair reads and writes .xlsx, covers the read-transform-write shape of most reporting jobs, and needs no Excel installation. Add xlsxwriter when the output needs charts or heavy formatting, and python-calamine when reads become the bottleneck.

Can openpyxl and xlsxwriter be used in the same script? Not on the same file handle, but easily in the same job. openpyxl edits an existing workbook in place; xlsxwriter builds a new one from scratch and cannot open a file. A common split is to write the data-heavy sheets with xlsxwriter and reopen the result with openpyxl only if something must be patched afterwards.

Do any of these libraries need Excel installed? Only xlwings and pywin32 do — they drive a real copy of Excel through its automation interface, so they need Windows or macOS with Excel present. openpyxl, xlsxwriter, pandas, Polars and calamine parse and write the file format directly and run happily on a headless Linux container.

Is Polars a replacement for pandas in Excel work? For the transform step, often yes — it is faster and its expression API is stricter. For the Excel edges it is thinner: read_excel delegates to calamine or openpyxl anyway, and write_excel wraps xlsxwriter. Choose Polars for the middle of the pipeline, not because of its spreadsheet support.

What reads .xlsb and legacy .xls files? Binary .xlsb needs pyxlsb or python-calamine; legacy .xls needs xlrd pinned to 1.2.0, or calamine, which handles both. openpyxl deliberately refuses anything that is not an OOXML file and raises InvalidFileException.

How much does the choice actually matter? For a 500-row monthly report, almost not at all — any of them finishes in under a second. The choice starts to matter at three points: when files grow past roughly 100,000 rows, when the output has to carry native charts or conditional formats, and when an existing workbook must be edited without losing what is already in it.