xlsxwriter
XlsxWriter creates Excel 2007+ .xlsx files from Python. You open a Workbook, add worksheets, write values cell by cell or row by row, and close the workbook to flush the file. Beyond plain values it covers most of what people actually open Excel for: number and date formats, merged cells, formulas, charts, conditional formatting, data validation dropdowns, autofilters, tables, images, and cell comments. It has no dependencies outside the standard library, and it is the engine pandas and polars call when you ask them to write an Excel file. The one thing to internalise before starting is that it only writes: it cannot open or modify an existing workbook.
The best way to produce a formatted Excel file from Python, with documentation and feature coverage that no competitor matches. Just decide up front whether you are writing a file or editing one, because the answer picks the library for you.
Use it if
- You generate .xlsx reports for people who will open them in Excel and expect native formatting, charts, and dropdowns rather than a raw CSV
- You already write DataFrames with pandas or polars and now need column widths, frozen headers, currency formats, or a chart on the same sheet
- You need zero dependencies and pure Python, which matters in a slim container, a Lambda package, or a PyPy runtime
- You are writing hundreds of thousands of rows and want the constant_memory mode that flushes each row instead of holding the whole sheet
- You need to read or edit an existing workbook: XlsxWriter is write-only by design, so opening a template and filling cells is not something it can do at all, and openpyxl is the answer instead
- The target format is .xls, .ods, or .csv-with-formatting: only the 2007-and-later .xlsx format is produced, with no converters in the box
- Something other than Excel consumes the output and you use formulas: the library stores formula strings and Excel calculates them on open, so pandas or another reader sees an empty value unless you pass the precomputed result yourself
- You need type checking: 3.2.9 deliberately removed the py.typed marker because it was breaking downstream CI, so mypy now treats the package as untyped again
- Your writes are not in row order and the file is huge: constant_memory keeps memory flat but requires strictly sequential rows, and it turns off autofit() and add_table() with a warning rather than an error
Setup reality
pip install xlsxwriter and you are finished: no compiler, no C extension, nothing to pin against. The friction is behavioural. Forgetting workbook.close() produces a zero-byte or corrupt file with no exception, which is the single most common first bug. Dates and times need a datetime object plus a Format carrying a num_format string, otherwise Excel shows the serial number. Format objects belong to the workbook that created them, cannot be shared between workbooks, and should not be mutated after first use, so build a small dictionary of formats once at the top. Column widths are set per column range and never inferred, though autofit() will approximate them for you if you call it after writing. Note the PyPI name is xlsxwriter and the import is xlsxwriter, but the GitHub project and most documentation spell it XlsxWriter.
Patterns
Create a workbook and write cellswrite-basic
import xlsxwriter
workbook = xlsxwriter.Workbook("report.xlsx")
worksheet = workbook.add_worksheet("Summary")
worksheet.write("A1", "Region")
worksheet.write("B1", "Revenue")
worksheet.write(1, 0, "EMEA") # row/col notation is zero-based
worksheet.write(1, 1, 120400)
workbook.close()close() is what actually writes the zip archive. Skip it and you get an empty or corrupt file with no traceback to explain why.
Let a with block close the workbookcontext-manager
with xlsxwriter.Workbook("report.xlsx") as workbook:
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, "Written safely")Workbook supports the context manager protocol, which is the reliable fix for forgotten close() calls and for exceptions thrown midway through generation.
Define formats once and reuse themcell-formats
fmt = {
"header": workbook.add_format({"bold": True, "bg_color": "#DDEBF7", "border": 1}),
"money": workbook.add_format({"num_format": "#,##0.00"}),
"pct": workbook.add_format({"num_format": "0.0%"}),
"date": workbook.add_format({"num_format": "yyyy-mm-dd"}),
}
worksheet.write("A1", "Revenue", fmt["header"])
worksheet.write("A2", 1204.5, fmt["money"])
worksheet.write("B2", 0.184, fmt["pct"])A Format belongs to the workbook that created it and cannot be reused across workbooks. Build them all up front rather than calling add_format inside a loop.
Write a list of records efficientlywrite-rows
headers = ["Region", "Revenue", "Margin"]
rows = [("EMEA", 120400, 0.184), ("APAC", 98100, 0.211)]
worksheet.write_row(0, 0, headers, fmt["header"])
for i, row in enumerate(rows, start=1):
worksheet.write_row(i, 0, row)write_row applies one format to the whole row; per-cell formats mean falling back to individual write() calls, which is also what write_column does column-wise.
Write dates so Excel shows dateswrite-dates
from datetime import datetime
worksheet.write_datetime("A2", datetime(2026, 8, 6, 14, 30), fmt["date"])
# strip tzinfo automatically instead of raising
workbook = xlsxwriter.Workbook("report.xlsx", {"remove_timezone": True})Without a num_format the cell shows the underlying serial number, for example 46240. Timezone-aware datetimes raise by default because the xlsx format has no timezone concept.
Write a formula and its cached valuewrite-formulas
worksheet.write_formula("D2", "=B2*C2", fmt["money"], 22153.6)
worksheet.write_dynamic_array_formula("F1:F1", "=UNIQUE(A2:A100)")XlsxWriter never evaluates formulas; Excel does that on open. The fourth argument is the cached result, and without it non-Excel readers such as pandas see a blank cell.
Size columns and freeze the headercolumn-widths
worksheet.set_column("A:A", 24)
worksheet.set_column("B:C", 14, fmt["money"])
worksheet.freeze_panes(1, 0)
# or approximate widths from the data already written
worksheet.autofit()autofit() estimates widths from the strings you wrote, so call it last, and be aware it is a warned no-op in constant_memory mode.
Highlight cells by ruleconditional-format
bad = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
worksheet.conditional_format("C2:C500", {
"type": "cell", "criteria": "<", "value": 0.05, "format": bad,
})
worksheet.conditional_format("B2:B500", {"type": "3_color_scale"})
worksheet.conditional_format("D2:D500", {"type": "data_bar"})The range must be written before or after, either is fine, but a rule over an empty range still exists in the file and will fire once someone types into those cells.
Add a dropdown list to a columndata-validation
worksheet.data_validation("E2:E500", {
"validate": "list",
"source": ["Open", "In progress", "Closed"],
"input_title": "Status",
"error_message": "Pick one of the listed values.",
})Inline source lists are capped by Excel at 255 characters total; longer lists have to live in a hidden sheet and be referenced as a range string instead.
Put a chart on the sheetinsert-chart
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"name": "Revenue",
"categories": ["Summary", 1, 0, 12, 0],
"values": ["Summary", 1, 1, 12, 1],
})
chart.set_title({"name": "Revenue by region"})
chart.set_y_axis({"num_format": "#,##0"})
worksheet.insert_chart("E2", chart, {"x_scale": 1.5, "y_scale": 1.5})Series ranges are [sheet_name, first_row, first_col, last_row, last_col] and point at cells, so the data has to be on a worksheet even if you would rather it were hidden.
Write huge sheets without growing memorylarge-files
workbook = xlsxwriter.Workbook("big.xlsx", {"constant_memory": True})
worksheet = workbook.add_worksheet()
for row, record in enumerate(stream_records()):
worksheet.write_row(row, 0, record)
workbook.close()Each row is flushed to a temp file once you move to the next, so rows must be written in ascending order and you cannot go back. autofit() and add_table() both warn and do nothing in this mode.
Format a DataFrame exportpandas-integration
import pandas as pd
with pd.ExcelWriter("out.xlsx", engine="xlsxwriter",
datetime_format="yyyy-mm-dd") as writer:
df.to_excel(writer, sheet_name="Data", index=False)
workbook, worksheet = writer.book, writer.sheets["Data"]
worksheet.set_column("A:A", 24)
worksheet.freeze_panes(1, 0)
worksheet.autofilter(0, 0, len(df), len(df.columns) - 1)writer.book and writer.sheets hand you the real XlsxWriter objects, so anything in this guide works inside a pandas export without writing the rows yourself.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| openpyxl | PyPI | You need to read an existing workbook or fill in a template, which XlsxWriter cannot do. |
| pandas | PyPI | You only want a DataFrame dumped onto a sheet, noting that to_excel calls this library underneath anyway. |
| pyexcelerate | PyPI | You are dumping very large volumes of plain rows and want raw write throughput more than charts and formatting. |