How to Choose a Python Library for Excel Automation

Carol Liu

6 min read

·

Oct 10, 2026

--

Listen

Share

The right library depends on the Excel formats you need to handle, the operations you want to perform, and how the output workbook should behave.

Press enter or click to view image in full size

Excel automation can start with a simple task: calculate a total column, update a few cells, or combine data from several sheets. The choice of library becomes more important when the workbook has additional requirements.

You might start with openpyxl and later discover that some source files use the older .xls format. Or you might use pandas for data analysis, only to find that the output also needs specific formatting. If the workflow depends on Excel’s own calculation behavior, a library that edits files directly may not be the right fit.

This article compares four Python libraries using the same basic task: read quantities and prices from an Excel workbook, calculate the total for each row, and save the results. We’ll look at openpyxl, pandas, xlwings, and Spire.XLS, then discuss when each one makes sense.

The Task

Assume report.xlsx contains three columns:

• qty: the quantity of each item
• price: the price per item
• total: the calculated amount

Each example reads the first two columns, calculates qty * price, writes the result to total, and saves the workbook as report-out.xlsx.

The examples use the same input structure, but each library works with Excel data differently.

openpyxl — Direct Cell Editing

openpyxl is a good fit when you need to read or modify individual cells in an Excel workbook. It provides access to cell values, styles, number formats, and other worksheet features.

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb["Sheet1"]

for row in ws.iter_rows(min_row=2, min_col=1, max_col=3):
    qty = row[0].value
    price = row[1].value
    row[2].value = qty * price

wb.save("report-out.xlsx")

This approach works well for small, targeted changes without requiring a local Excel installation.

A few limitations are worth knowing:

• File formats: openpyxl supports .xlsx and .xlsm, but not the older .xls format. When editing a macro-enabled workbook, use keep_vba=True if you need to preserve VBA content, and save the result with the .xlsm extension.
• Formula calculation: openpyxl can write a formula such as =A2*B2, but it does not calculate the formula itself. A spreadsheet application such as Excel must calculate it to produce an updated result.
• Workbook features: You can edit cell styles and number formats, merge cells, and manage conditional formatting through the worksheet API. However, some advanced workbook features may not be preserved perfectly when a file is loaded and saved.

Choose openpyxl when the task mainly involves editing .xlsx or .xlsm files and you want direct control over cells and worksheet properties.

pandas — Data Analysis with DataFrames

pandas takes a different approach. Instead of working directly with individual cells, it loads worksheet data into a DataFrame. This is especially useful when the task involves filtering, grouping, joining, or aggregating records.

import pandas as pd

df = pd.read_excel("report.xlsx", sheet_name="Sheet1")
df["total"] = df["qty"] * df["price"]
df.to_excel("report-out.xlsx", index=False)

The calculation is concise because pandas operates on entire columns rather than requiring a loop over individual cells.

For data-processing tasks, this can be much more convenient than editing a workbook cell by cell.

There are two important considerations:

• Formatting: DataFrame.to_excel() writes the DataFrame to an Excel sheet but does not preserve the original workbook’s formatting. If the output needs specific styles, column widths, or conditional formatting, you’ll need to apply them separately or use a workbook library for that part.
• Excel engines: pandas relies on configurable Excel engines to read and write files. For .xlsx files, openpyxl is a common choice, but other engines are available. Support for older formats depends on the selected engine and its dependencies.

The index=False argument prevents pandas from adding the DataFrame index as an extra column.

Use pandas when the main work is manipulating tabular data. If the result must retain a particular workbook layout, consider handling the analysis and Excel output with separate tools.

xlwings — Automating Excel Itself

xlwings lets Python interact with a running Excel application. Rather than editing the workbook entirely through a file-processing library, you can use Excel’s own functionality through Python.

The following example writes a formula into the total column for each data row:

import xlwings as xw

wb = xw.Book("report.xlsx")
sheet = wb.sheets["Sheet1"]

last_row = sheet.range(
    f"A{sheet.cells.last_cell.row}"
).end("up").row

for row in range(2, last_row + 1):
    sheet.range(f"C{row}").formula = f"=A{row}*B{row}"

wb.save("report-out.xlsx")
wb.close()

Here, Excel evaluates the formulas when it recalculates the workbook. This is useful when the workflow depends on Excel’s calculation engine or other Excel-specific behavior.

xlwings also makes it possible to interact with workbooks that users already have open, automate Excel operations, and work with formulas and formatting through the application.

The main limitation is its environment requirement. The open-source version of xlwings requires Microsoft Excel on Windows or macOS. It is therefore less suitable for a headless server that has no desktop Excel installation. xlwings offers separate PRO features for certain server and file-reading workflows, so the requirements depend on which functionality you use.

Choose xlwings when the automation needs to work through Excel itself rather than simply read and write workbook files.

Spire.XLS — Working with Excel Files Without Excel

Spire.XLS provides a Python API for reading, modifying, and saving Excel workbooks directly. It does not require Microsoft Excel to be installed, and it supports both legacy .xls files and newer Excel formats.

The following example performs the same calculation and also applies basic formatting to the header row:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("report.xlsx")
sheet = workbook.Worksheets[0]

for row in range(2, sheet.LastRow + 1):
    qty = float(sheet.Range[row, 1].Value)
    price = float(sheet.Range[row, 2].Value)
    sheet.Range[row, 3].NumberValue = qty * price

sheet.Range["A1:C1"].Style.Color = Color.get_LightGray()
sheet.Range["A1:C1"].Style.Font.IsBold = True
sheet.AllocatedRange.AutoFitColumns()

workbook.SaveToFile(
    "report-out.xlsx",
    ExcelVersion.Version2013
)
workbook.Dispose()

This example assumes the quantity and price cells contain valid numeric values. If the source data includes blank cells or text, add validation before converting the values.

Spire.XLS is useful when the script needs to process workbook data and control the output file in the same workflow. Its APIs cover cell values, formatting, conditional formatting, charts, pivot tables, and other workbook features.

It also supports saving workbooks to other formats. For example, after preparing a workbook, you can export it as a PDF:

workbook.SaveToFile("report-out.pdf", FileFormat.PDF)

This can be useful when a workflow needs to generate a spreadsheet and a PDF report from the same source. The supported output formats and feature behavior depend on the specific API and workbook content.

Choose Spire.XLS when you need direct file processing without Excel, compatibility with .xls files, or a workflow that combines data editing, workbook formatting, and document conversion.

How to Choose

The best option depends on what the automation needs to accomplish.

• Choose openpyxl for direct cell editing in .xlsx and .xlsm files when you don’t need Excel itself to run the calculations.
• Choose pandas when the main task is data analysis, such as filtering, joining, grouping, or aggregating large tables.
• Choose xlwings when you need to automate Excel itself, interact with an open workbook, or rely on Excel’s calculation behavior.
• Choose Spire.XLS when you need to process .xls and .xlsx files without a local Excel installation, or when the output requires workbook formatting and conversion features.

These choices are not mutually exclusive. A project can use one library for data processing and another for writing the final workbook.

Combining pandas and Spire.XLS

For some workflows, pandas and Spire.XLS work well together. pandas handles the data analysis, while Spire.XLS writes the results to an Excel workbook and applies the required formatting.

For example, suppose a DataFrame contains region and total columns. You can aggregate the totals by region in pandas, then use Spire.XLS to create the output workbook:

import pandas as pd
from spire.xls import *
from spire.xls.common import *

summary = df.groupby(
    "region", as_index=False
)["total"].sum()

workbook = Workbook()
sheet = workbook.Worksheets[0]

sheet.Range["A1"].Text = "Region"
sheet.Range["B1"].Text = "Total"

for row, (region, total) in enumerate(
    summary.itertuples(index=False),
    start=2
):
    sheet.Range[row, 1].Text = str(region)
    sheet.Range[row, 2].NumberValue = float(total)

sheet.Range["A1:B1"].Style.Font.IsBold = True
sheet.AllocatedRange.AutoFitColumns()

workbook.SaveToFile(
    "summary.xlsx",
    ExcelVersion.Version2013
)
workbook.Dispose()

This example assumes df is an existing DataFrame with region and total columns. The aggregation happens in pandas, and Spire.XLS handles the Excel output.

The same pattern can be extended to reports that need custom headers, number formats, multiple worksheets, or PDF output. It also avoids asking pandas to handle workbook formatting that falls outside its main purpose.

Conclusion

There is no single Python library that fits every Excel automation task. openpyxl is well suited to cell-level edits, pandas simplifies data analysis, xlwings connects Python to Excel itself, and Spire.XLS supports direct workbook processing without a local Excel installation.

Start with the file formats you need to support and the behavior expected from the output. Then choose the library that fits those requirements. For more complex workflows, combining a data-processing library with a workbook-focused library may be the most practical approach.