Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Write a Polars DataFrame to Excel with Formatting

Use DataFrame.write_excel to produce a formatted workbook: number formats, autofit, Excel tables, conditional formats, multiple sheets, totals rows and charts.

Polars can write the report as well as compute it. DataFrame.write_excel() drives xlsxwriter beneath a much shorter API, so a formatted workbook — number formats, an Excel table, frozen headers, autofitted columns — comes out of a single call rather than thirty lines of cell styling. This guide covers the arguments worth knowing, how to put several sheets in one file, and the point at which you should drop through to xlsxwriter itself. It is the writing half of Reading Excel with Polars and Arrow.

What write_excel adds on top of the raw data A single call turns a DataFrame into a worksheet with a styled Excel table, number formats per column, autofitted widths and a frozen header row. One call, four presentation decisions DataFrame values only write_excel() wraps xlsxwriter one worksheet per call table_style: a real Excel table column_formats: #,##0.00 autofit: readable widths freeze_panes: header stays put

Prerequisites

Bash
pip install polars xlsxwriter

write_excel raises immediately if xlsxwriter is missing, since it is the engine doing the work.

A formatted sheet in one call

Python
import polars as pl

df = pl.DataFrame({
    "region": ["North", "South", "East", "West"],
    "orders": [412, 388, 195, 264],
    "revenue": [128_400.50, 96_220.00, 51_130.25, 74_905.75],
    "margin": [0.184, 0.212, 0.147, 0.196],
})

df.write_excel(
    "regional_summary.xlsx",
    worksheet="Summary",
    table_style="Table Style Medium 9",
    autofit=True,
    freeze_panes="A2",
    column_formats={
        "revenue": "#,##0.00",
        "margin": "0.0%",
        "orders": "#,##0",
    },
)

That produces a workbook a stakeholder can use immediately: a banded Excel table with filter buttons, thousands separators on money, percentages rendered as percentages, columns wide enough to read, and a header row that stays visible while scrolling. The format strings are Excel's own — the same vocabulary described in Applying Number and Date Formats in Excel.

Add a totals row and column widths

Two arguments handle the details reviewers always ask for:

Python
df.write_excel(
    "regional_summary.xlsx",
    worksheet="Summary",
    table_style="Table Style Medium 2",
    column_totals={"orders": "sum", "revenue": "sum", "margin": "average"},
    column_widths={"region": 140, "revenue": 120},
    header_format={"bold": True, "bg_color": "#DDEBF7", "border": 1},
    float_precision=2,
)

column_totals writes a real Excel totals row — the values are SUBTOTAL formulas, so they recalculate when a reader filters the table rather than sitting there as stale numbers. column_widths takes precedence over autofit for the columns it names, which is what you want when a header is far longer than its values.

Highlight values with a conditional format

conditional_formats accepts the xlsxwriter dictionaries directly, so anything that library can express is available:

Python
df.write_excel(
    "regional_summary.xlsx",
    worksheet="Summary",
    table_style="Table Style Medium 9",
    autofit=True,
    conditional_formats={
        "margin": {
            "type": "cell",
            "criteria": "<",
            "value": 0.15,
            "format": {"bg_color": "#FFC7CE", "font_color": "#9C0006"},
        },
        "revenue": {"type": "data_bar", "bar_color": "#5B5CF0"},
    },
)

A data bar turns a column of numbers into an at-a-glance ranking without a chart, and a cell rule flags the rows that need attention. The equivalents in openpyxl and xlsxwriter are covered in Applying Conditional Formatting with openpyxl and Apply conditional formatting with xlsxwriter.

The sheet the arguments above produce A banded table with a bold header, right-aligned money, a percentage column with a red rule on low margins, and a totals row of SUBTOTAL formulas at the bottom. Summary sheet, as a reader sees it region orders revenue margin North 412 128,400.50 18.4% East 195 51,130.25 14.7% Total 1,259 350,656.50 18.5% Totals are SUBTOTAL formulas, so they follow the reader's filters

Put several sheets in one workbook

write_excel writes one worksheet per call, so a multi-sheet report means owning the workbook object yourself:

Python
import polars as pl
import xlsxwriter

summary = pl.DataFrame({"region": ["North", "South"], "revenue": [128_400.5, 96_220.0]})
detail = pl.DataFrame({"order_id": ["00417", "00418"], "revenue": [1_204.0, 980.5]})

with xlsxwriter.Workbook("report.xlsx") as wb:
    summary.write_excel(workbook=wb, worksheet="Summary", autofit=True,
                        table_style="Table Style Medium 9",
                        column_formats={"revenue": "#,##0.00"})
    detail.write_excel(workbook=wb, worksheet="Detail", autofit=True,
                       table_style="Table Style Light 1")

Using the context manager matters: xlsxwriter only writes the file on close(), and a workbook that is never closed produces a zero-byte or corrupt .xlsx. The multi-sheet report pattern in full is Write multiple DataFrames to one Excel file.

Drop through to xlsxwriter for anything else

Polars deliberately does not wrap every xlsxwriter feature. When you need a chart, a merged title, or a sheet-level setting, take the objects back and use the library directly:

Python
import polars as pl
import xlsxwriter

df = pl.DataFrame({"region": ["North", "South", "East"], "revenue": [128400.5, 96220.0, 51130.25]})

with xlsxwriter.Workbook("with_chart.xlsx") as wb:
    ws = df.write_excel(workbook=wb, worksheet="Summary", autofit=True,
                        table_style="Table Style Medium 9")
    chart = wb.add_chart({"type": "column"})
    chart.add_series({
        "name": "Revenue",
        "categories": ["Summary", 1, 0, df.height, 0],
        "values": ["Summary", 1, 1, df.height, 1],
        "fill": {"color": "#5B5CF0"},
    })
    chart.set_title({"name": "Revenue by region"})
    chart.set_legend({"none": True})
    ws.insert_chart("E2", chart, {"x_scale": 1.2, "y_scale": 1.2})

write_excel returns the worksheet, which is the hand-off point. From there the whole xlsxwriter API is available — see Add a chart to an Excel file with xlsxwriter.

Format by dtype instead of by name

Naming every column in column_formats does not survive a schema change. Deriving the formats from the frame's own types does, which matters for a report whose columns come from a query rather than a fixed list:

Python
import polars as pl

def formats_for(df: pl.DataFrame) -> dict[str, str]:
    """Money-like floats get separators, dates get ISO, integers get grouping."""
    out: dict[str, str] = {}
    for name, dtype in df.schema.items():
        if dtype in (pl.Float32, pl.Float64):
            out[name] = "0.0%" if name.endswith(("rate", "margin", "share")) else "#,##0.00"
        elif dtype in (pl.Int8, pl.Int16, pl.Int32, pl.Int64):
            out[name] = "#,##0"
        elif dtype == pl.Date:
            out[name] = "yyyy-mm-dd"
        elif dtype == pl.Datetime:
            out[name] = "yyyy-mm-dd hh:mm"
    return out

df.write_excel("summary.xlsx", autofit=True, column_formats=formats_for(df))

A naming convention does the rest of the work: any column ending in rate, margin or share is a proportion and gets a percentage format, everything else numeric gets thousands separators. Add a column next month and it is formatted correctly with no change to the report code.

Ship the same workbook every time

A generated report is easier to trust when successive runs differ only in their numbers. Three habits get you there:

  • Sort deterministically before writing, so row order does not wander between runs.
  • Round in the data, not only in the format, when a figure is quoted elsewhere — pl.col("revenue").round(2) — so a reader copying a cell gets the number they see.
  • Stamp the run into a cell or the sheet name, so two copies of the file on someone's desktop can be told apart.
Python
report = summary.sort("region")
report.write_excel(
    f"regional_summary_{run_date:%Y-%m-%d}.xlsx",
    worksheet=f"Summary {run_date:%b %Y}",
    autofit=True,
    table_style="Table Style Medium 9",
)

Dated filenames also stop a scheduled job silently overwriting last month's output — the failure mode discussed in Automating Reporting Workflows.

Where Polars stops and xlsxwriter takes over Tables, number formats, autofit, totals and conditional formats are arguments to write_excel, while charts, merged titles, images and page setup are reached through the returned worksheet object. write_excel arguments via the returned worksheet table_style, column_formats, autofit column_totals, freeze_panes conditional_formats, header_format charts and sparklines merged title cells, images print setup, sheet protection

Common pitfalls and gotchas

  • Forgetting to close the workbook. Use with xlsxwriter.Workbook(...) or call close() explicitly; nothing is written until then.
  • Expecting to edit a template. xlsxwriter creates new files only. Template filling belongs to openpyxl — Fill an Excel template with Python and openpyxl.
  • Duplicate table names. Two sheets whose tables share a name make Excel offer to repair the file. Give each worksheet a distinct name and let Polars derive the table name from it.
  • Sheet names over 31 characters or containing / \ ? * [ ] are rejected by Excel — sanitise names derived from data.
  • Percentages written as text. Store 0.184 and format it as 0.0%; writing the string "18.4%" gives you text that cannot be totalled.

Performance and scale notes

xlsxwriter builds the whole workbook in memory before writing, so a very large sheet can be expensive. For hundreds of thousands of rows, write through xlsxwriter's constant_memory mode — pass it when constructing the workbook and hand that workbook to write_excel — which streams rows to disk at the cost of forbidding some features, including autofit and any write that revisits an earlier row. The details and limits are in Write a million rows to Excel with xlsxwriter constant memory. A better answer for genuinely large output is usually not to send it to Excel at all: write the detail to Parquet or CSV and put only the summary in the workbook.

Conclusion

write_excel() covers the presentation layer of a report in one call — table styling, number formats, autofit, frozen panes, totals and conditional formats — and hands you the underlying xlsxwriter objects the moment you need more. Compose several calls against one workbook for a multi-sheet report, keep openpyxl for templates and in-place edits, and store numbers as numbers so Excel's own formatting can do its job.

Frequently asked questions

What does Polars use to write xlsx? xlsxwriter. write_excel is a high-level wrapper over it, which is why xlsxwriter must be installed and why its formatting vocabulary — number format strings, table styles, conditional format dictionaries — appears in the arguments.

Can I write several DataFrames into one workbook? Yes. Create an xlsxwriter Workbook yourself and pass it to write_excel once per sheet, then close it. Each call adds a worksheet to the same file.

Can write_excel edit an existing workbook or fill a template? No. xlsxwriter only creates new files, so template filling and in-place edits still belong to openpyxl.

How do I add a chart? Get the underlying worksheet and workbook objects from write_excel, then use the xlsxwriter chart API directly. Polars hands you the objects rather than wrapping every xlsxwriter feature.

Does autofit measure the rendered text? It estimates from the string lengths in each column, which is close enough for most reports. Set an explicit column_widths for columns whose headers are much longer than their values.