Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Styling pandas DataFrames in Excel with openpyxl

Use openpyxl after calling pandas to_excel() to style cells, add formulas, handle merged cells, and control presentation in Excel exports.

~5 min readPython
#openpyxl#pandas#Excel#DataFrame
Stylized Excel worksheet with a pandas DataFrame table and openpyxl cell formatting tools.

pandas writes DataFrames to Excel cleanly, but it leaves presentation to you. 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 when it is installed, so the two libraries already share a boundary. You can keep the pandas workflow and hand the finished worksheet to openpyxl for styling.

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.

python
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:

python
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

Format the rows pandas creates after to_excel(): the worksheet object then lets you apply fills, fonts, borders, and number formats to those cells.

python
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.

Keep the styling after the write. If you style a worksheet before pandas writes into it, verify that the cells retain their styles; the behavior of an existing cell is not guaranteed once pandas writes a value into it. Styling after to_excel() avoids that ambiguity.

Reading Excel Data Back Into a DataFrame

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

python
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 vertically merged category labels, you have to forward-fill those cells yourself:

python
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

If a cell needs a formula, write the formula string directly on the worksheet after to_excel().

python
ws['D2'] = '=B2*C2'

openpyxl stores the formula string in the file. A spreadsheet application such as Excel or LibreOffice can evaluate the formula when it opens the workbook; if the file is saved from that application, 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:

python
from openpyxl import load_workbook wb = load_workbook('inventory.xlsx', data_only=True) ws = wb['Inventory'] print(ws['D2'].value) # cached value if a spreadsheet app has stored one

pd.read_excel() does not evaluate formulas. It reads the cached value that was last stored by a spreadsheet application. If a script writes a formula and immediately reads the same file with pandas, the formula cell will not contain the calculated value (typically None), because no spreadsheet application has saved a cached result. This is a frequent source of confusion; the practical workaround is to open and save the file once in Excel or LibreOffice, or to compute the final value in Python when the export does not need a live formula.

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.

python
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. openpyxl does not support legacy binary .xls files, so pandas uses a different engine for those. If your pipeline must handle .xls, keep xlrd installed or convert the files once.

Version differences matter. Styling APIs, including the arguments to PatternFill, can differ between openpyxl releases, and pandas declares a minimum openpyxl version for its Excel engine. If a style call fails with a TypeError, check the openpyxl version and the API it expects before assuming the logic is wrong. 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.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.