Reading, Writing, and Formatting Excel Files with Python Packages

Excel files are often the final handoff format in business systems. A developer may collect data from a service, clean it with Python, calculate a summary, and still need to deliver the result as an .xlsx workbook that a finance, operations, or sales team can open without installing anything special.

Python gives you several ways to work with Excel files directly on disk. You do not need to automate the Excel desktop application for every task. You can read workbook files, create new workbooks, style report output, preserve simple macro workbooks, and support older Excel formats from Python code.

The tricky part is choosing the correct package. Some packages only read files. Some only write files. Some can edit existing workbooks, but only within limits. Some are designed for modern .xlsx files, while others exist mostly for legacy .xls or binary .xlsb files.

This tutorial explains how to think about those choices and how to build practical Excel workflows with reader and writer packages.

The Problem

A typical Excel automation task has one of these shapes:

1. Read values from one or more Excel files.
2. Create a new workbook as a report.
3. Export a pandas DataFrame with additional formatting.
4. Modify a simple existing workbook.
5. Support an older or less common Excel file format.

These tasks sound similar, but they are technically different.

Reading a workbook means extracting cell values from a file. Writing a workbook means creating a new file. Editing a workbook sounds like changing an existing file, but most Python libraries do not truly edit every internal part of an Excel workbook. They usually read the parts they understand and then create a new file from that representation.

That distinction matters because Excel workbooks can contain much more than plain cells:

• Worksheets
• Formulas
• Cell formats
• Tables
• Charts
• Images
• PivotTables
• Macros
• External workbook links

A library that works perfectly for simple cell values may not preserve every advanced Excel feature.

A practical workflow should start with the question: What do you really need to do with the workbook?

Excel task
  |
  |-- Read values only
  |     |-- Prefer calamine-based readers
  |
  |-- Create a new report
  |     |-- Prefer XlsxWriter
  |
  |-- Modify a simple existing workbook
  |     |-- Use OpenPyXL carefully
  |
  |-- Need PivotTables or legacy formats
        |-- Use a specialized package

Choosing the Right Package

The Python Excel ecosystem has several packages with overlapping names and different responsibilities. The following table gives a practical starting point.

For most modern development work, you can remember this simpler rule:

• Use pandas or Polars with a calamine engine when you need to read Excel data.
• Use XlsxWriter when you need to create a new Excel report.
• Use OpenPyXL when you need to modify a simple existing workbook.
• Use specialized packages only when your file format or feature requires them.

Reading Excel Files with a Calamine-Based Reader

Calamine is a fast Excel reader implemented in Rust. Python packages such as python-calamine and fastexcel expose calamine functionality to Python.

If you already use pandas, the most direct approach is to let pandas read the workbook with the calamine engine:

import pandas as pd

orders = pd.read_excel("reports/monthly_orders.xlsx", engine="calamine")
print(orders.head())

This is useful when you want the result as a DataFrame. A DataFrame is a table-like pandas object with labeled rows and columns, similar to a spreadsheet table but controlled from code.

Reading Without pandas

Sometimes you do not need the full pandas stack. For example, maybe you only need a nested list of values to pass into another function. In that case, python-calamine can read the workbook directly.

from python_calamine import CalamineWorkbook

workbook = CalamineWorkbook.from_path("reports/store_inventory.xlsx")
sheet_values = workbook.get_sheet_by_name("Inventory").to_python(
    skip_empty_area=True
)

for row in sheet_values[:3]:
    print(row)

The result is a list of rows, where each row is itself a list of cell values.

The skip_empty_area=True behavior is helpful when the worksheet has empty rows or columns before the real data begins. If your layout depends on exact worksheet positions, set it to False so the empty area is preserved.

Reading Excel Tables with fastexcel

fastexcel is also built on calamine. It can be very efficient for large files, and it can load Excel tables. When using it with pandas, PyArrow is required because the data exchange goes through Apache Arrow.

import fastexcel

reader = fastexcel.read_excel("reports/store_inventory.xlsx")
print(reader.table_names())

stock_table = reader.load_table("CurrentStock").to_pandas()
print(stock_table.head())

This is especially useful when the workbook uses Excel tables as the official data boundary. Instead of guessing the used range of the worksheet, you read the named table directly.

Writing New Excel Reports with XlsxWriter

XlsxWriter is a writer package. It creates Excel files, but it does not read or edit existing ones.

That limitation is also a strength. If your goal is to generate a fresh report from Python data, XlsxWriter is a strong default choice.

It supports common report features such as:

• Writing cell values
• Applying formats
• Writing formulas
• Adding images
• Creating charts
• Writing macro-enabled workbooks when you provide an existing VBA project file

A Small Report Example

The following example creates a workbook with a few cells, formatting, a formula, a small data block, and a chart. The values and names are different from the examples in the workbook, but the pattern is the same.

import datetime as dt
import xlsxwriter


def cell_to_position(cell_address):
    import re

    match = re.match(r"([A-Z]+)([0-9]+)", cell_address.upper())
    letters, row_text = match.groups()

    column_number = 0
    for letter in letters:
        column_number = column_number * 26 + ord(letter) - ord("A") + 1

    return int(row_text) - 1, column_number - 1


def write_grid(worksheet, rows, start_at="A1"):
    row_start, col_start = cell_to_position(start_at)
    for row_offset, row_values in enumerate(rows):
        worksheet.write_row(row_start + row_offset, col_start, row_values)


workbook = xlsxwriter.Workbook("regional_report.xlsx")
worksheet = workbook.add_worksheet("Summary")

worksheet.write("A1", "Regional Sales Report")
worksheet.write(1, 0, "Generated with Python")

header_style = workbook.add_format(
    {
        "bold": True,
        "font_color": "#FFFFFF",
        "bg_color": "#4F81BD",
        "align": "center",
        "border": 1,
    }
)

money_style = workbook.add_format({"num_format": "#,##0.00"})
date_style = workbook.add_format({"num_format": "yyyy-mm-dd"})

worksheet.write("A4", "Report Date", header_style)
worksheet.write("B4", dt.date(2026, 5, 1), date_style)

sales_rows = [
    ["Region", "Last Month", "This Month"],
    ["North", 18200, 22400],
    ["South", 20500, 19800],
    ["West", 17600, 24100],
]

write_grid(worksheet, sales_rows, start_at="A7")
worksheet.set_column("B:C", 14, money_style)

worksheet.write("A12", "Total")
worksheet.write("B12", "=SUM(B8:B10)", money_style)
worksheet.write("C12", "=SUM(C8:C10)", money_style)

chart = workbook.add_chart({"type": "column"})
chart.set_title({"name": "Sales by Region"})
chart.add_series(
    {
        "name": "=Summary!B7",
        "categories": "=Summary!A8:A10",
        "values": "=Summary!B8:B10",
    }
)
chart.add_series(
    {
        "name": "=Summary!C7",
        "categories": "=Summary!A8:A10",
        "values": "=Summary!C8:C10",
    }
)
worksheet.insert_chart("E7", chart)

workbook.close()

There are a few important details here.

First, XlsxWriter accepts both Excel-style addresses, such as A1 and zero-based row and column indices. Zero-based means the first row is row 0 and the first column is column 0.

Second, formulas should use English Excel function names and commas as argument separators. This matters even if your local Excel installation uses another language or separator style.

Third, the file is created when workbook.close() is called. Forgetting this step can leave you without a finished workbook on disk.

Writing Macro-Enabled Workbooks

XlsxWriter can create .xlsm files if you add an extracted VBA project. The macro code itself comes from an existing macro-enabled workbook.

On Windows, the extraction command has this shape:

uv run .venv\Scripts\vba_extract.py xl\macro.xlsm

On macOS, the command has this shape:

uv run vba_extract.py xl/macro.xlsm

After extraction, you can add the vbaProject.bin file to the generated workbook.

import xlsxwriter

book = xlsxwriter.Workbook("generated_macro_report.xlsm")
sheet = book.add_worksheet("Control")

sheet.write("A1", "Run the macro from the button below")
book.add_vba_project("xl/vbaProject.bin")

sheet.insert_button(
    "A3",
    {
        "macro": "Hello",
        "caption": "Run Macro",
        "width": 140,
        "height": 35,
    }
)

book.close()

This does not write VBA code from scratch. It packages an existing VBA project into the workbook that XlsxWriter creates.

Writing Large Workbooks with XlsxWriter

Large reports can consume a lot of memory if the library keeps all rows in memory before writing the file. XlsxWriter provides a constant_memory option for this situation.

The tradeoff is that data must be written sequentially, row by row.

import random
import xlsxwriter

book = xlsxwriter.Workbook(
    "large_measurement_report.xlsx",
    options={"constant_memory": True},
)
sheet = book.add_worksheet("Measurements")

for row_number in range(1000):
    row_values = [random.random() for _ in range(200)]
    sheet.write_row(row_number, 0, row_values)

book.close()

When constant_memory is enabled, design your export code around rows. Avoid workflows that require going back and rewriting earlier rows or filling columns independently.

Reading and Writing with OpenPyXL

OpenPyXL can read, write, and modify modern Excel files. It is a good choice when you want one pure Python package for .xlsx style workflows or when you need to make a simple change to an existing workbook.

Reading Values with OpenPyXL

When reading values from a workbook, use data_only=True if you want the calculated result of formula cells instead of the formula text.

import openpyxl

book = openpyxl.load_workbook("reports/store_inventory.xlsx", data_only=True)
sheet = book["Inventory"]

print(book.sheetnames)
print(sheet.max_row, sheet.max_column)
print(sheet["B3"].value)
print(sheet.cell(row=3, column=2).value)

OpenPyXL uses one-based row and column indices when you call sheet.cell. In other words, row 1 and column 1 point to cell A1.

That differs from XlsxWriter, which uses zero-based indices for row and column operations. Mixing these two conventions is a common source of off-by-one errors.

Reading a Cell Range

A helper function makes range reading easier and keeps the rest of your code clean.

import openpyxl


def read_range(sheet, top_left="A1", bottom_right=None):
    min_row, min_col = openpyxl.utils.cell.coordinate_to_tuple(top_left)

    if bottom_right is None:
        max_row, max_col = sheet.max_row, sheet.max_column
    else:
        max_row, max_col = openpyxl.utils.cell.coordinate_to_tuple(bottom_right)

    rows = []
    for cells in sheet.iter_rows(
        min_row=min_row,
        min_col=min_col,
        max_row=max_row,
        max_col=max_col,
        values_only=True,
    ):
        rows.append(list(cells))

    return rows


book = openpyxl.load_workbook("reports/store_inventory.xlsx", data_only=True)
values = read_range(book["Inventory"], top_left="A1", bottom_right="E20")
print(values[:2])

The values_only=True option returns plain values instead of cell objects. Use this when you only care about data, not styling or cell metadata.

Reading Large Files with OpenPyXL

For large files, OpenPyXL can reduce memory usage with read_only=True. When using this mode, close the workbook yourself after reading.

If the file contains external workbook links and you do not need to preserve them, keep_links=False can make loading faster.

import openpyxl

book = openpyxl.load_workbook(
    "reports/large_input.xlsx",
    data_only=True,
    read_only=True,
    keep_links=False,
)

sheet = book["RawData"]
for row in sheet.iter_rows(values_only=True):
    process_row = row

book.close()

OpenPyXL does not support the with statement for this case, so closing the file is your responsibility.

Writing a Workbook with OpenPyXL

OpenPyXL writes the workbook when you call save.

import datetime as dt
import openpyxl
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.alignment import Alignment

book = openpyxl.Workbook()
sheet = book.active
sheet.title = "Summary"

sheet["A1"] = "Inventory Summary"
sheet["A1"].font = Font(bold=True, color="FFFFFF")
sheet["A1"].fill = PatternFill(fgColor="4F81BD", fill_type="solid")
sheet["A1"].alignment = Alignment(horizontal="center")

sheet["A3"] = "Created"
sheet["B3"] = dt.date(2026, 5, 1)
sheet["B3"].number_format = "yyyy-mm-dd"

rows = [
    ["Category", "Count"],
    ["Books", 32],
    ["Tools", 18],
    ["Parts", 41],
]

for row in rows:
    sheet.append(row)

chart = BarChart()
chart.title = "Inventory Count"
chart_data = Reference(sheet, min_row=5, min_col=2, max_row=7, max_col=2)
chart_labels = Reference(sheet, min_row=5, min_col=1, max_row=7, max_col=1)
chart.add_data(chart_data)
chart.set_categories(chart_labels)
sheet.add_chart(chart, "D4")

book.save("inventory_openpyxl.xlsx")

Compared with XlsxWriter, the API style is different. XlsxWriter creates formats through the workbook object and passes them into write calls. OpenPyXL exposes workbook, sheet, cell, style, image, and chart objects that you modify more directly.

Writing Large Files with OpenPyXL

OpenPyXL has a write_only=True mode for large exports. In this mode, you append rows, and you cannot freely address individual cells.

The lxml package can make OpenPyXL writing faster. It can be added to the project with:

uv add lxml

A write-only export looks like this:

import openpyxl

book = openpyxl.Workbook(write_only=True)
sheet = book.create_sheet("Export")

for row_number in range(1000):
    sheet.append(list(range(200)))

book.save("openpyxl_large_export.xlsx")

Use this mode when the workbook is mainly a data export, and you can write it from top to bottom.

Editing Existing Workbooks with OpenPyXL

OpenPyXL can modify simple existing workbooks, but it is safer to think of this as load, change, and save rather than true in-place editing.

import openpyxl

book = openpyxl.load_workbook("reports/store_inventory.xlsx")
sheet = book["Inventory"]

sheet["A1"] = "Updated Inventory"
sheet["F1"] = "Checked"
sheet["F2"] = True

book.save("reports/store_inventory_updated.xlsx")

This works well for simple worksheets with values and basic formatting. Be careful with files that contain complex charts or advanced features. Unsupported content may be changed or removed when the workbook is saved again.

Preserving Macros with OpenPyXL

If you need to modify an existing macro-enabled workbook, load it with keep_vba=True.

import openpyxl

book = openpyxl.load_workbook("reports/template_macro.xlsm", keep_vba=True)
book["Control"]["A1"] = "Ready to run"
book.save("reports/template_macro_updated.xlsm")

This starts from an existing .xlsm file. OpenPyXL is preserving the VBA project while changing the supported workbook content.

Combining pandas with XlsxWriter for Formatted DataFrame Reports

pandas is convenient for data preparation, grouping, filtering, and DataFrame export. Writer packages are better when you need exact Excel formatting.

You can combine both. pandas writes the DataFrame, then you access the underlying XlsxWriter workbook and worksheet objects to apply additional Excel formatting.

import pandas as pd

summary = pd.DataFrame(
    {
        "Region": ["North", "South", "West"],
        "Sales": [22400, 19800, 24100],
        "Target": [21000, 20500, 23000],
    }
)

with pd.ExcelWriter("sales_summary.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(
        writer,
        sheet_name="Summary",
        startrow=4,
        startcol=1,
        index=False,
    )

    workbook = writer.book
    worksheet = writer.sheets["Summary"]

    title_format = workbook.add_format(
        {"bold": True, "font_size": 14, "bg_color": "#D9EAF7"}
    )
    number_format = workbook.add_format({"num_format": "#,##0", "align": "center"})

    worksheet.write("B2", "Monthly Sales Summary", title_format)
    worksheet.set_column(first_col=2, last_col=3, width=12, cell_format=number_format)

This pattern is useful because you do not need to choose between pandas and Excel formatting. Use pandas for the table. Use the writer engine for workbook-level details.

Formatting DataFrame Headers and Indexes Yourself

pandas can export a DataFrame quickly, but you may want full control over the header row and index styling. One approach is to export the data without headers and indexes, then write those pieces yourself.

import pandas as pd


def cell_to_position(cell_address):
    import re

    match = re.match(r"([A-Z]+)([0-9]+)", cell_address.upper())
    letters, row_text = match.groups()

    column_number = 0
    for letter in letters:
        column_number = column_number * 26 + ord(letter) - ord("A") + 1

    return int(row_text) - 1, column_number - 1


metrics = pd.DataFrame(
    {"Actual": [101, 94], "Planned": [98, 100]},
    index=["Week 1", "Week 2"],
)
metrics.index.name = "Period"

with pd.ExcelWriter("styled_metrics.xlsx", engine="xlsxwriter") as writer:
    start_row, start_col = cell_to_position("D3")

    metrics.to_excel(
        writer,
        sheet_name="Metrics",
        header=False,
        index=False,
        startrow=start_row + 1,
        startcol=start_col + 1,
    )

    workbook = writer.book
    worksheet = writer.sheets["Metrics"]
    label_style = workbook.add_format({"bold": True, "bg_color": "#D9D9D9"})

    for column_offset, column_name in enumerate(metrics.columns):
        worksheet.write(start_row, start_col + column_offset + 1, column_name, label_style)

    index_values = [metrics.index.name] + list(metrics.index)
    for row_offset, index_value in enumerate(index_values):
        worksheet.write(start_row + row_offset, start_col, index_value, label_style)

This is more code than a plain to_excel, but it gives you predictable control over the final workbook layout.

Formatting Dates and Datetimes

When exporting DataFrames, date and datetime formatting can be configured directly on the Excel writer.

import datetime as dt
import pandas as pd

schedule = pd.DataFrame(
    {
        "RunDate": [dt.date(2026, 5, 1)],
        "CreatedAt": [dt.datetime(2026, 5, 1, 9, 30)],
    }
)

with pd.ExcelWriter(
    "scheduled_report.xlsx",
    date_format="yyyy-mm-dd",
    datetime_format="yyyy-mm-dd hh:mm:ss",
) as writer:
    schedule.to_excel(writer, index=False)

This is cleaner than formatting date cells one by one after export.

Creating PivotTables with Excelize

Most Python Excel writer packages do not create PivotTables. Excelize is useful when PivotTable output is required.

Excelize is implemented in Go and exposes a Python API for creating modern Excel files. A simplified PivotTable workflow looks like this:

import excelize

book = excelize.new_file()

rows = [
    ["Product", "Units", "Price"],
    ["Keyboard", 6, 45.0],
    ["Mouse", 10, 20.0],
    ["Keyboard", 4, 47.0],
]

for row_number, row_values in enumerate(rows, start=1):
    book.set_sheet_row("Sheet1", f"A{row_number}", row_values)

book.add_pivot_table(
    excelize.PivotTableOptions(
        data_range="Sheet1!A1:C4",
        pivot_table_range="Sheet1!E1:G6",
        rows=[excelize.PivotTableField(data="Product")],
        data=[
            excelize.PivotTableField(data="Units", name="Total Units", subtotal="Sum"),
            excelize.PivotTableField(data="Price", name="Average Price", subtotal="Average"),
        ],
        row_grand_totals=True,
        col_grand_totals=True,
    )
)

book.save_as("pivot_report.xlsx")
book.close()

Use this kind of package only when the feature requirement justifies it. For regular tabular reports, XlsxWriter is usually simpler.

Reading Binary .xlsb Files with pyxlsb

The .xlsb format is Excel Binary Workbook. If you need a pure Python reader for this format, pyxlsb can read sheet values.

import pyxlsb

with pyxlsb.open_workbook("reports/store_inventory.xlsb") as book:
    print(book.sheets)

    with book.get_sheet("Inventory") as sheet:
        for row in sheet.rows():
            values = [cell.v for cell in row]
            print(values)
            break

One limitation to know: date-formatted cells may be returned as Excel date numbers. Convert them explicitly when needed.

from pyxlsb import convert_date

excel_date_number = 43301.0
python_datetime = convert_date(excel_date_number)
print(python_datetime)

In most cases, calamine-based readers are preferable for .xlsb, especially when automatic date handling and speed matter. Use pyxlsb when a pure Python dependency is specifically useful.

Working with Legacy .xls Files

The old .xls format has separate packages:

• xlrd reads .xls files.
• xlwt writes .xls files.
• xlutils connects the two for simple edit-like workflows.

These packages are not the best default for modern Excel work, but they are still useful when you must handle old files.

Reading .xls with xlrd

import xlrd

book = xlrd.open_workbook("reports/legacy_inventory.xls")
print(book.sheet_names())

sheet = book.sheet_by_name("Inventory")
print(sheet.nrows, sheet.ncols)
print(sheet.cell(1, 0).value)

When reading large .xls files, on_demand=True lets you load sheets only when needed.

import xlrd

with xlrd.open_workbook("reports/legacy_inventory.xls", on_demand=True) as book:
    first_sheet = book.sheet_by_index(0)
    print(first_sheet.name)

Writing .xls with xlwt

import datetime as dt
import xlwt

book = xlwt.Workbook()
sheet = book.add_sheet("Summary")

header_style = xlwt.easyxf(
    "font: bold on, color red;"
    "align: horiz center;"
    "pattern: pattern solid, fore_color yellow;"
)

sheet.write(0, 0, "Legacy Report", header_style)
sheet.write(2, 0, "Created")

date_style = xlwt.easyxf(num_format_str="yyyy-mm-dd")
sheet.write(2, 1, dt.datetime(2026, 5, 1), date_style)

sheet.write(4, 0, xlwt.Formula("SUM(B1, 2)"))
book.save("legacy_report.xls")

Legacy .xls support is mainly for compatibility. Prefer modern workbook formats when you control the output.

Practical Workflow for a Report Generator

A clean Excel report generator usually separates reading, transforming, and writing.

Input Excel files
  |
  v
Reader package or pandas
  |
  v
Python transformation code
  |
  v
pandas DataFrame or nested lists
  |
  v
XlsxWriter or OpenPyXL
  |
  v
Formatted Excel report

A practical implementation can follow these steps:

1. Decide whether the input is modern .xlsx, binary .xlsb, or legacy .xls.
2. Read values using pandas with engine="calamine" unless you have a reason to use a lower-level reader directly.
3. Transform data in Python or pandas.
4. Create a new report with XlsxWriter when you do not need to modify an existing workbook.
5. Use OpenPyXL when you must start from an existing workbook, but verify that the workbook does not depend on unsupported advanced features.
6. Apply number, date, header, and column formatting intentionally.
7. Save the result under a new name first, especially when changing existing files.
8. Open the output manually during development to verify layout, formulas, charts, and formatting.

Common Mistakes to Watch For

Confusing row and column indexing rules

XlsxWriter uses zero-based row and column indices. OpenPyXL uses one-based indices when using sheet.cell. Excel addresses such as A1 avoid some of this confusion, but helper functions must be clear about which convention they return.

Expecting writer packages to read files

XlsxWriter writes new workbooks. It does not read or edit existing ones. If you need to load a workbook first, choose a reader package or OpenPyXL.

Treating OpenPyXL editing as perfect preservation

OpenPyXL is useful for simple workbook edits, but it does not preserve every possible Excel feature. Complex charts or advanced workbook content may not round-trip exactly.

Forgetting to close or save the workbook

XlsxWriter needs close. OpenPyXL needs save. OpenPyXL in read_only=True mode also needs close after reading.

Reading formulas when you wanted values

With OpenPyXL, use data_only=True if you want cached formula results instead of formula strings.

Using column-based writing in streaming modes

XlsxWriter constant_memory=True and OpenPyXL write_only=True are row-oriented workflows. Write rows in sequence.

Choosing a heavy stack for a tiny task

If you only need a nested list of values, you may not need pandas. A direct reader such as python-calamine can be enough.

Checklist

Use this checklist before choosing your Excel package:

• Do I need to read, write, or modify a workbook?
• Which file format do I need to support: .xlsx, .xlsm, .xlsb, or .xls?
• Do I need a DataFrame, or are plain nested lists enough?
• Am I generating a new file or changing an existing one?
• Does the workbook contain macros, charts, PivotTables, or external links?
• Is the file large enough to require streaming or memory-saving options?
• Do I need exact formatting for headers, indexes, numbers, dates, or charts?
• Can I save to a new output file instead of overwriting the original?
• Have I manually opened and checked the generated workbook?

Conclusion

Python can handle Excel files directly without opening the Excel application. The best package depends on the task.

For reading, calamine-based readers are a strong default, especially through pandas with engine="calamine". For writing new reports, XlsxWriter is usually the best starting point. For simple edits to existing modern workbooks, OpenPyXL is the practical choice, but you should understand its preservation limits. For PivotTables, binary workbooks, and old .xls files, use specialized packages only when your requirements call for them.

The most reliable Excel automation code is explicit about file format, package choice, indexing rules, memory behavior, and output formatting. Once those decisions are clear, Python becomes a powerful way to produce repeatable Excel reports from code.