XlsxWriter Cheat Sheet

1Install & hello4 steps

pip install XlsxWriter★
Pure Python, zero dependencies. Import is import xlsxwriter.
import xlsxwriter wb = xlsxwriter.Workbook("out.xlsx") ws = wb.add_worksheet("Sheet1") ws.write("A1", "Hello") wb.close()★
The whole lifecycle: Workbook → add_worksheet → write → close. The file is only written on close().
with xlsxwriter.Workbook("out.xlsx") as wb: ws = wb.add_worksheet()★
Context manager auto-closes (and thus writes) the file — safer than a manual close().
# forgot wb.close()? -> the .xlsx is empty/corruptgotcha
Nothing is written until close(). Always close (or use with).

2Writing cellswrite*

ws.write(0, 0, "text") # (row, col) — 0-indexed ws.write("A1", 42) # or A1 notation★
write takes (row, col) or an "A1" string, and auto-detects the type (number/string/formula/date). Both indexing styles work everywhere.
ws.write_row("A1", [1, 2, 3]) ws.write_column("A1", [1, 2, 3])★
Write a list across a row or down a column in one call.
ws.write_number · write_string · write_boolean · write_datetime · write_url · write_blank
Type-specific writers when you want to force a type. write_datetime needs a date format (card 3) to display correctly.
ws.write("A1", value, cell_format)★
The optional last arg applies a Format object (next card).

3Formatsadd_format

bold = wb.add_format({"bold": True, "font_color": "red"}) ws.write("A1", "Hi", bold)★
Create a reusable Format on the workbook and pass it to write. Props: bold, italic, font_size, font_color, bg_color, align, border.
money = wb.add_format({"num_format": "$#,##0.00"}) pct = wb.add_format({"num_format": "0.0%"}) date = wb.add_format({"num_format": "yyyy-mm-dd"})★
num_format uses Excel's format codes — currency, percent, thousands, and dates (dates need a format or they show as serial numbers).
fmt = wb.add_format({"align":"center", "valign":"vcenter", "border":1, "bg_color":"#FFEB9C", "text_wrap":True})
Alignment, borders, fills, wrapping. Colors are names or #RRGGBB.
# create each Format ONCE on the workbook, reuse itgotcha
Don't call add_format in a loop — make the format objects up front and reuse. A format belongs to its workbook.

4Rows, columns & sizedimensions

ws.set_column("A:A", 20) # width ws.set_column(1, 3, 12, fmt) # cols B:D, width + format★
Set column width (in character units) & a default format by range or index.
ws.set_row(0, 30, header_fmt) # row height + format
Row height (points) & format. autofit() approximates auto-width for all columns.
ws.merge_range("A1:C1", "Title", title_fmt)★
Merge cells — the value/format go on the merged range.
ws.set_column("D:D", None, None, {"hidden": 1}) ws.set_column("E:E", 15, None, {"level": 1})
Hide or group/outline columns (and rows) for collapsible sections.

5Formulas & tableslive values

ws.write_formula("D2", "=SUM(A2:C2)") ws.write("D3", "=AVERAGE(A3:C3)") # write() also detects formulas★
Formulas are stored, then computed by Excel on open. Use US-English function names & commas.
ws.write_formula("D2", "=SUM(A2:C2)", None, 6) # cache a valuegotcha
XlsxWriter doesn't calculate — other tools (LibreOffice headless, some viewers) may show 0 until recalc. Pass a cached result as the 4th arg if needed.
ws.add_table("A1:D51", {"columns":[{"header":"Name"}, ...], "style":"Table Style Medium 9", "total_row":True})★
A real Excel Table object with headers, banding, filters, and a totals row.
ws.write_dynamic_array_formula("F1:F10", "=FILTER(A:A, B:B>0)")
Modern spill/dynamic-array formulas (FILTER, UNIQUE, SORT) are supported.

6Conditional formattingrules by value

ws.conditional_format("B2:B100", {"type":"cell", "criteria":">", "value":100, "format":red_fmt})★
Color cells that meet a condition. The format is an add_format object (usually just fill/font color).
ws.conditional_format("B2:B100", {"type":"3_color_scale"}) ws.conditional_format("C2:C100", {"type":"data_bar"})★
Visual rules: 2_color_scale/3_color_scale, data_bar, icon_set — heatmaps & in-cell bars.
{"type":"formula", "criteria":"=$B2>$C2", "format":fmt}
Formula-based rules for full control (row highlighting, cross-column comparisons).
{"type":"top", "value":10} · {"type":"duplicate"} · {"type":"text", "criteria":"containing"}
Also top/bottom-N, above/below-average, duplicate/unique, and text-match rules.

7Chartsnative Excel charts

chart = wb.add_chart({"type":"column"}) # line/bar/pie/scatter/area/...★
Create a chart object on the workbook. Types: column, bar, line, pie, doughnut, scatter, area, radar, stock.
chart.add_series({ "name":"Sales", "categories":"=Sheet1!$A$2:$A$7", "values":"=Sheet1!$B$2:$B$7"})★
A series points at cell ranges (or use the [sheet, r1, c1, r2, c2] list form). Add multiple series for grouped charts.
chart.set_title({"name":"Q3"}); chart.set_x_axis({"name":"Month"}) ws.insert_chart("D2", chart, {"x_scale":1.5, "y_scale":1.2})★
Title/axes/legend, then insert_chart to place it on a sheet. Charts stay live & editable in Excel.
chart2 = wb.add_chart({"type":"line"}); chart.combine(chart2)
Combine two charts (e.g. column + line on a secondary axis).

8Images & widgetsinsert

ws.insert_image("E2", "logo.png", {"x_scale":0.5, "y_scale":0.5})★
Embed PNG/JPEG/GIF/BMP. Pass image bytes via {"image_data": BytesIO(...)} instead of a path.
ws.add_sparkline("F2", {"range":"A2:E2", "type":"line"})
In-cell sparklines (line/column/win_loss) — tiny trend charts.
ws.insert_checkbox("A1", True)recent
Boolean checkboxes (a 2024 Excel feature) via insert_checkbox.
ws.data_validation("A1", {"validate":"list", "source":["Yes","No"]}) ws.write_comment("B2", "note")
Dropdowns/validation, cell comments/notes, and insert_button for macros.

9Panes, protection & printsheet setup

ws.freeze_panes(1, 0) # freeze the header row ws.autofilter("A1:D1")★
Freeze rows/cols so headers stay visible; add an autofilter dropdown to a header range.
ws.protect("password") locked = wb.add_format({"locked": 0}) # unlock editable cells
Protect a sheet; cells are locked by default, so mark editable ones with a {"locked":0} format.
ws.set_landscape(); ws.fit_to_pages(1, 0) ws.repeat_rows(0); ws.set_header("&CReport")
Print setup: orientation, fit-to-width, repeated header rows, and headers/footers.
wb.add_worksheet(); ws.set_tab_color("green"); ws.hide()
Multiple sheets, colored tabs, hidden sheets. ws.activate()/select() to set the opening view.

10Big files & pandasscale

wb = xlsxwriter.Workbook("big.xlsx", {"constant_memory": True})★
constant_memory mode flushes each row as it's written — write millions of rows in flat memory. ⚠️ You must then write row by row, top-to-bottom (no going back).
df.to_excel("out.xlsx", engine="xlsxwriter", sheet_name="Data")★
pandas uses XlsxWriter as an Excel engine — the easiest path for tabular data.
with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as xw: df.to_excel(xw, sheet_name="D") ws = xw.sheets["D"]; ws.set_column("A:A", 20)★
Get the underlying Workbook/Worksheet from an ExcelWriter to add formats, charts, and formatting to a pandas export.

11Gotchas & vs openpyxlchoosing

# XlsxWriter is WRITE-ONLY — it cannot open/edit an existing .xlsxwrite-only
To read or modify existing workbooks, use openpyxl. XlsxWriter always creates a fresh file.
# XlsxWriter: fastest writer, most features, charts/cond-fmt # openpyxl: read + write, but slower & fewer chart features★
Rule of thumb: generating a new richly-formatted report → XlsxWriter; editing/reading an existing file → openpyxl.
# .xlsx only — no .xls (old format) and no .xlsm macros authoring beyond add_vba_project
Writes the modern .xlsx. Add a pre-extracted vbaProject.bin via wb.add_vba_project() for macro workbooks.
# Rust port: rust_xlsxwriter / the `polars` Excel writer use it
A high-performance Rust port (rust_xlsxwriter) exists and backs Polars' write_excel.

XlsxWriter cheat sheet

Outline

Write & format

Formulas & visuals

Layout & ecosystem

Write & Format

Formulas & Conditional Formatting

Charts & Visuals

Layout & Ecosystem

Worth memorizing