python openpyxl with pandas dataframe styling

pandas writes DataFrames to Excel cleanly, but it leaves presentation entirely to the reader. When an export needs column widths, number formats, merged headers, or formulas, pandas alone is not enough. That is where openpyxl comes in: pandas uses openpyxl as its default engine for .xlsx files, so the two libraries already share a boundary. Combining python openpyxl with pandas dataframe lets you produce styled, production-ready Excel output without leaving the pandas workflow.

Why pandas and openpyxl Work Together

When you call df.to_excel() on a DataFrame, pandas writes the file through an engine. For .xlsx files, that engine is openpyxl when it is installed. The practical consequence is that you can keep using pandas for the data work — filtering, aggregating, pivoting — and then hand the resulting worksheet to openpyxl for presentation.

The division of labor is simple:

• pandas decides which values go into which cells.
• openpyxl controls what those cells look like and how the workbook is structured.

If the export is a plain table that another script will consume, pandas alone is fine. If a human opens the file and needs to read it quickly, openpyxl styling matters.

Writing a DataFrame with the openpyxl Engine

The standard pattern is to create an ExcelWriter with the openpyxl engine, write the DataFrame, and then access the workbook and worksheet objects that the writer exposes.

import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment

df = pd.DataFrame({
    "Product": ["Widget", "Gadget", "Gizmo"],
    "Price": [19.99, 29.99, 9.99],
    "Stock": [120, 45, 300],
})

writer = pd.ExcelWriter("inventory.xlsx", engine="openpyxl")
df.to_excel(writer, sheet_name="Inventory", index=False)
writer.close()

Two attributes on the writer matter here. writer.book is the openpyxl Workbook object, and writer.sheets is a dictionary mapping sheet names to openpyxl Worksheet objects. After to_excel() runs, you can reach the worksheet through either path:

ws = writer.sheets["Inventory"]

Once you have the worksheet, every openpyxl cell API is available. This is the key point: pandas writes the data, and openpyxl formats it afterward.

Styling Cells After the DataFrame Is Written

Formatting must happen after to_excel() because pandas controls the initial cell writes. The worksheet object lets you apply fills, fonts, borders, and number formats to the rows pandas created.

writer = pd.ExcelWriter("inventory.xlsx", engine="openpyxl")
df.to_excel(writer, sheet_name="Inventory", index=False)
ws = writer.sheets["Inventory"]

header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
for cell in ws[1]:
    cell.fill = header_fill
    cell.font = Font(bold=True, color="FFFFFF")
    cell.alignment = Alignment(horizontal="center")

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = '"$"#,##0.00'

for col_letter, width in (("A", 12), ("B", 10), ("C", 10)):
    ws.column_dimensions[col_letter].width = width

writer.close()

Row 1 contains the column headers because to_excel() writes them by default. The loop over ws[1] styles each header cell. The second loop applies a currency number format to the price column, and the third sets column widths.

A common mistake is to style the worksheet before writing the DataFrame. Any styling applied before to_excel() is likely to be overwritten, because pandas re-creates or overwrites the cells it writes. Style after the write, not before.

Reading Excel Data Back Into a DataFrame

Reading works through pd.read_excel(), which also uses openpyxl under the hood for .xlsx files.

df_read = pd.read_excel("inventory.xlsx", sheet_name="Inventory")

The sheet_name parameter accepts a name, a zero-based index, or None to return a dictionary of DataFrames keyed by sheet name. When the source file was produced by a script, the default behavior — first row as header, no index column — usually matches what was written.

Merged cells are the case where reading needs manual handling. openpyxl reports the top-left cell of a merged range as the value holder, and pandas leaves the remaining cells of the range as NaN. If the source workbook contains merged headers or merged category labels, you have to forward-fill those cells yourself:

df_read = pd.read_excel("report.xlsx", sheet_name="Sheet1")
df_read = df_read.ffill()

This only works when the merge pattern is a simple vertical grouping. Complex two-dimensional merges require a custom reader that inspects ws.merged_cells.ranges and maps each merged range back to its top-left value before constructing the DataFrame.

Writing Formulas with openpyxl

pandas writes literal values. If a cell needs a formula, you write it as a string directly on the worksheet after to_excel().

ws["D2"] = "=B2*C2"

openpyxl stores the formula string in the file. When Excel or LibreOffice opens the workbook, the formula is evaluated and the cached result is stored. When you read the file back with openpyxl, you get the cached value only if you load the workbook with data_only=True:

from openpyxl import load_workbook

wb = load_workbook("inventory.xlsx", data_only=True)
ws = wb["Inventory"]
print(ws["D2"].value)  # cached computed value

pd.read_excel() does not evaluate formulas. It reads the cached value that was last stored by an Excel application. If a script writes a formula and immediately reads the same file with pandas, the formula cell will contain None because no spreadsheet application has ever opened the file to compute and cache a result. This is a frequent source of confusion, and the workaround is to open the file once in Excel or to compute the value in Python and write both the formula and the cached result.

Performance Considerations for Large Workbooks

openpyxl keeps the entire workbook in memory. For a few thousand rows this is irrelevant. For hundreds of thousands of rows, the memory cost becomes visible.

The main lever is write_only mode. A workbook created with write_only=True streams rows to disk instead of holding the full tree in memory. The tradeoff is that you lose most styling and cell access — you append rows in order and cannot revisit a cell after it is written.

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Inventory")
ws.append(["Product", "Price", "Stock"])
for row in df.itertuples(index=False):
    ws.append(list(row))
wb.save("inventory_stream.xlsx")

When you need both streaming and pandas, the practical path is to iterate over the DataFrame with itertuples() and append each row to the write-only worksheet. This bypasses to_excel() entirely, because to_excel() does not expose openpyxl's write-only mode.

On the read side, pd.read_excel() loads the whole sheet. For very large files, reading with openpyxl's read_only=True and iterating rows row by row keeps memory flat, at the cost of more code and no pandas conveniences like header inference.

Compatibility and Maintainability

The openpyxl engine is only used when openpyxl is installed. pandas falls back to other engines for older .xls files, which openpyxl does not support. If your pipeline must handle legacy binary workbooks, keep xlrd or convert the files once.

Version differences matter. The PatternFill signature changed across openpyxl releases, and pandas pins a minimum openpyxl version for its Excel engine. If a style call fails with a TypeError, the usual cause is an openpyxl version mismatch rather than a logic error. Pin both libraries in your environment and upgrade them together.

The maintainability rule is to keep the boundary explicit. Let pandas own the data transformation and let openpyxl own the presentation. A function that takes a DataFrame and returns a styled workbook path is easy to test: the DataFrame logic is testable without Excel, and the styling logic is testable against the worksheet object. Mixing data manipulation into the styling layer makes both harder to verify.