Writing a DataFrame to Excel With Formatting in pandas

df.to_excel("out.xlsx") produces a file that is correct and plain: bold bordered headers, default widths, dates as full timestamps. For a file someone will read, five more calls make it far better, and none requires leaving pandas. This post builds one formatted export in layers, so you can stop at the layer you need. It is a spoke of sending database results to Excel, and it uses the XlsxWriter engine, whose formatting model is the most complete of the Python writers.

The data

A small frame stands in for a query result:

import pandas as pd

df = pd.DataFrame({
    "order_no": ["0007", "0012", "0031"],
    "customer": ["Acme AS", "Globex", "Initech"],
    "amount": [1234.5, 87.0, 15.25],
    "share": [0.62, 0.04, 0.34],
    "placed": pd.to_datetime(["2024-03-01 09:15", "2024-03-02 14:00", "2024-03-05 08:30"]),
})

order_no holds strings with leading zeros, which stay text in the file because they are object columns of strings. share is a fraction that should display as a percentage, and placed is a date-time.

Layer one: the writer and the basics

with pd.ExcelWriter(
    "report.xlsx",
    engine="xlsxwriter",
    datetime_format="yyyy-mm-dd hh:mm",
    date_format="yyyy-mm-dd",
) as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    book = writer.book
    sheet = writer.sheets["Orders"]

datetime_format and date_format set the display of datetime and date cells for the whole file, which cures dates appearing as numbers. The context manager saves and closes the workbook when the block ends; do the rest of the formatting inside it, because the workbook is closed afterwards. writer.book is the XlsxWriter Workbook and writer.sheets["Orders"] the Worksheet, which means everything in the XlsxWriter documentation is available.

Layer two: column formats and widths

Create formats once and apply them to columns:

    money = book.add_format({"num_format": "#,##0.00"})
    pct = book.add_format({"num_format": "0.0%"})
    text = book.add_format({"num_format": "@"})

    sheet.set_column("A:A", 12, text)
    sheet.set_column("B:B", 28)
    sheet.set_column("C:C", 14, money)
    sheet.set_column("D:D", 10, pct)
    sheet.set_column("E:E", 18)

set_column takes a column range, a width in character units and an optional format. A column format applies to every cell in the column that has no format of its own, including cells a reader adds later, and it costs one style entry instead of one per cell. The number format codes are the same ones Excel's Format Cells dialog uses: #,##0.00 for thousands separators and two decimals, 0.0% for a percentage of a stored fraction, @ for text. The cell D2 holds 0.62 and displays 62.0%.

Note a subtlety about the @ format. It makes the cells display as text and makes future typing text, but the cell values already written as strings are text because of how pandas wrote them, not because of the format. Both together are the safe combination for identifiers.

Instead of guessing widths, compute them from the data:

    for i, col in enumerate(df.columns):
        longest = max(len(str(col)), df[col].astype(str).map(len).max())
        sheet.set_column(i, i, min(longest + 2, 50))

This sets each column to its longest value plus two characters, capped at 50. It overrides the widths from the previous block, so use one approach or the other; if you want per-column formats too, pass the format in the same set_column call. XlsxWriter also has sheet.autofit(), which estimates widths for the data already written, and is a shorter route when approximate widths are good enough. Widths are measured in the number of characters of the default font, so proportional fonts make the fit approximate.

Layer three: restyle the header

pandas writes the header with its own bold, bordered style, and you cannot change it through to_excel. To use your own, write the header cells again after the data:

    header = book.add_format({
        "bold": True,
        "bg_color": "#1F3A5F",
        "font_color": "#FFFFFF",
        "align": "left",
        "valign": "vcenter",
        "text_wrap": True,
        "border": 0,
    })
    for col_num, name in enumerate(df.columns):
        sheet.write(0, col_num, name, header)
    sheet.set_row(0, 24)

sheet.write(0, col, name, header) overwrites the header cell in row 0 with your format. set_row(0, 24) makes the header row taller. Keep it to a restrained palette with high contrast between the font and the fill. If you do not need a custom header, skip this layer: the default is readable.

Layer four: an Excel table

A formatted range with banded rows and filter buttons is exactly what an Excel Table is, and XlsxWriter creates one in a call. Write the data without pandas' header, then add the table, which writes the header itself:

    df.to_excel(writer, sheet_name="Table", index=False, header=False, startrow=1)
    t = writer.sheets["Table"]
    t.add_table(0, 0, len(df), len(df.columns) - 1, {
        "name": "Orders",
        "style": "Table Style Medium 2",
        "columns": [{"header": c} for c in df.columns],
    })

The table's coordinates are (first_row, first_col, last_row, last_col) including the header row, so with 3 data rows and 5 columns it covers rows 0 to 3 and columns 0 to 4. The table name Orders is how formulas and other programs refer to it. A table gives you banding, filter buttons and an extent that grows when a reader types below it, and anyone who imports the file later can read it by name (Excel named ranges and tables explained). Table column formats are applied through the format key of each column dictionary: {"header": "amount", "format": money}. Do not call autofilter on a range that is a table; the table already has its own.

Layer five: conditional formatting

Rules that color cells by value live inside the workbook and keep working after edits:

    sheet.conditional_format("C2:C4", {
        "type": "cell",
        "criteria": ">=",
        "value": 1000,
        "format": book.add_format({"bg_color": "#FFF2CC"}),
    })
    sheet.conditional_format("D2:D4", {"type": "data_bar", "bar_color": "#9DC3E6"})

The first rule fills amounts of 1,000 or more with a pale yellow, and the second draws data bars for the shares. Cells C2 (1234.5) is highlighted, and the others are not. Use conditional formatting sparingly; a column of bars is useful, and five colors in one table is noise. Color should never be the only signal, since people print in black and white and some readers do not distinguish certain colors.

Details that cause trouble

Missing values. pandas writes NaN as an empty cell by default. na_rep="" is the default, and a string such as "n/a" shows a marker in numeric columns, which turns the cell into text and breaks sums. Prefer empty cells in numeric columns.

Infinity and NaN in XlsxWriter. Writing inf or NaN cells directly with XlsxWriter's write_number raises an error unless the workbook is created with the nan_inf_to_errors option. Clean the values first.

float_format. The float_format argument to to_excel rewrites the numbers pandas hands to the writer, which can change stored values. For display, prefer a cell number format.

Text beginning with an equals sign. XlsxWriter's write() treats such strings as formulas by default. For untrusted data, turn off strings_to_formulas through engine_kwargs, as shown in sending database results to Excel properly every time.

Timezones. Convert timezone-aware columns to naive ones before writing (exporting a query to Excel from PostgreSQL with Python).

Reopening the file. XlsxWriter creates new files only. To modify an existing workbook, use openpyxl or the openpyxl engine with mode="a" (appending a sheet to an existing workbook with pandas).

Check the result

Do not trust the code to have produced what you pictured. Open the file in Excel, and also in LibreOffice or Google Sheets if recipients might use them. Check the header, the widths, the format of each column, that numbers are right-aligned (a sign they are numbers) and that dates are dates. Read the file back with openpyxl and assert the key facts as a test: sheet names, number of rows, cell.number_format of the money column and cell.data_type of the id column. Such a test takes ten lines and prevents regressions when someone edits the export months later.

When formatting a DataFrame export in pandas is not enough

You need a designed report. Layouts with merged titles, logos, charts and print areas are better built in a template and filled with openpyxl, or produced as PDF.

You are editing an existing workbook. XlsxWriter cannot read or modify files. Use openpyxl, and accept its slower formatting.

The file must be very large. Per-cell formats and tables cost memory and time. Format at the column level and consider the constant-memory mode (writing large Excel files quickly with write-only mode).

Readers will import the file back into a system. Keep it plain: one table, no decoration. Formatting is for people.

XlsxWriter documents the pandas integration in working with pandas and the format properties in its Format class reference. For a quick look at a small extract as JSON, use the CSV to JSON converter.