ExcelWriter Engines in Python (Pandas 2.0+)

Let’s be honest—Excel isn’t going anywhere. Whether you’re building a weekly report, automating insights, or sending a “fancy” output to a stakeholder, chances are, you’re still saving stuff as .xlsx.

And if you’re in Python land, pandas.ExcelWriter is your trusty sidekick. But—what powers this tool behind the scenes? Yep, we’re talking about the engines.

With Pandas 2.0 and above, the engine support got cleaner, faster, and more focused on what matters today. Let’s break it down.

---

What is ExcelWriter?

The ExcelWriter class lets you write DataFrames to Excel files. It gives you control over file formats, sheet names, formatting, and more.

A simple use:

with pd.ExcelWriter("myfile.xlsx") as writer:
    df.to_excel(writer)

But wait… What engine is it using? Why does it matter? Let’s decode that.

---

Updated List of Supported Engines in Pandas 2.0+

Here’s what’s in and officially supported now:

Referred from https://github.com/pandas-dev/pandas/blob/v2.0.3/pandas/io/excel/_base.py#L1131-L1134

Engine	Format	Mode	Key Features	Best Use
openpyxl	.xlsx	Read/Write	Default engine for modern Excel	General purpose Excel work
xlsxwriter	.xlsx	Write-only	Advanced formatting, charts, macros	Reports with styling & visuals
odf	.ods, .odf	Read/Write	OpenDocument support (LibreOffice)	Interoperability or open formats
pyxlsb	.xlsb	Read-only	For reading binary Excel files	Handling large .xlsb files
xlrd	.xls	Read-only	Legacy binary format support	Reading old .xls files only

❗ xlrd no longer supports .xlsx files as of version 2.0. It only supports .xls.
❗ xlwt is deprecated and no longer supported for writing Excel files.

---

✅ Choosing the Right Engine (With Examples!)

1️⃣ openpyxl – Your Safe Default

import pandas as pd

df = pd.DataFrame({"Name": ["Alice", "Bob"], "Score": [90, 95]})

with pd.ExcelWriter("students.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Scores", index=False)

Great for:

• Creating and reading .xlsx files
• Clean, basic reports
• Excel automation

---

2️⃣ xlsxwriter – If Your Excel File Needs to Be Pretty

with pd.ExcelWriter("styled.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Scores", index=False)

    workbook = writer.book
    worksheet = writer.sheets["Scores"]
    bold_format = workbook.add_format({"bold": True, "bg_color": "#DDEEFF"})
    worksheet.set_row(0, None, bold_format)

Great for:

• Rich formatting
• Charts and conditional formatting
• Excel files with polish

Note: Only for writing. Can’t be used to read Excel files.

---

3️⃣ odf – For LibreOffice & Open Formats

with pd.ExcelWriter("report.ods", engine="odf") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)

Great for:

• Open-source workflows
• Saving in .ods or .odf formats
• Institutions avoiding proprietary formats

---

4️⃣ pyxlsb – Reading Binary Excel Files

df = pd.read_excel("huge_file.xlsb", engine="pyxlsb")

Great for:

• Reading large Excel .xlsb files
• Corporate environments using .xlsb

Write support? Nope—this engine is read-only.

---

5️⃣ xlrd – Reading Old .xls Files Only

df = pd.read_excel("legacy_data.xls", engine="xlrd")

Great for:

• Reading legacy binary Excel .xls files
• Migration of old data to newer formats

❗ Avoid using this for .xlsx. It won’t work. Use openpyxl instead.

---

Real Life Use Case

Let’s say you’re building a data pipeline that dumps monthly KPIs into Excel. Here’s how to decide:

Scenario	Recommended Engine
Daily reports with simple tables	openpyxl
Dashboards with charts and style	xlsxwriter
Your org uses LibreOffice	odf
Huge legacy .xlsb files	pyxlsb
Really old .xls archives	xlrd

---

Final Thoughts

Pandas 2.0 makes engine support lean and focused. The new set of engines covers most modern use cases, while trimming out outdated ones like xlwt. Know your use case, choose the right engine—and let ExcelWriter do the heavy lifting!

Spreadsheets may be old school, but that doesn’t mean your code has to be 😉✨