XlsxWriter creates Excel XLSX files in pure Python. pip install xlsxwriter. Basic: import xlsxwriter; wb = xlsxwriter.Workbook("out.xlsx"); ws = wb.add_worksheet(); ws.write(0, 0, "Hello"); wb.close(). Context manager: with xlsxwriter.Workbook("out.xlsx") as wb: ws = wb.add_worksheet(). In-memory: from io import BytesIO; buf = BytesIO(); wb = xlsxwriter.Workbook(buf, {"in_memory": True}). Cell: ws.write(row, col, data). ws.write("A1", "text"). Number: ws.write_number("B2", 42.5). Formula: ws.write_formula("C2", "=SUM(A1:A10)"). Format: fmt = wb.add_format({"bold": True, "bg_color": "#4472C4", "font_color": "white"}). Number fmt: wb.add_format({"num_format": "$#,##0.00"}). Apply: ws.write(0, 0, "Header", fmt). Column width: ws.set_column(0, 0, 20). ws.set_column("A:D", 15). Row height: ws.set_row(0, 30). Merge: ws.merge_range("A1:D1", "Title", fmt). Freeze: ws.freeze_panes(1, 0) — freeze row 0. Table: ws.add_table("A1:D10", {"style": "Table Style Medium 9", "columns": [{"header": "Name"}]}). Conditional: ws.conditional_format("B2:B100", {"type": "data_bar", "bar_color": "#63C384"}). Chart: chart = wb.add_chart({"type": "column"}); chart.add_series({...}); ws.insert_chart("F2", chart). Image: ws.insert_image("A1", "logo.png"). Claude Code generates XlsxWriter invoice reports, dashboard spreadsheets, and Excel export pipelines.
CLAUDE.md for XlsxWriter
## XlsxWriter Stack
- Version: xlsxwriter >= 3.1 | pip install xlsxwriter
- Init: wb = xlsxwriter.Workbook("out.xlsx") or Workbook(BytesIO(), {"in_memory": True})
- Format: fmt = wb.add_format({"bold":True, "bg_color":"#hex", "num_format":"$#,##0"})
- Write: ws.write(row, col, val, fmt) | ws.write("A1", val)
- Table: ws.add_table("A1:D10", {"style":"Table Style Medium 9", "columns":[...]})
- Chart: wb.add_chart({"type":"column/line/pie"}); chart.add_series({...})XlsxWriter Excel Generation Pipeline
# app/excel_gen.py — XlsxWriter workbook, formats, tables, charts, and pandas export
from __future__ import annotations
import io
from dataclasses import dataclass
from datetime import date
from typing import Any
import xlsxwriter
from xlsxwriter.workbook import Workbook
from xlsxwriter.worksheet import Worksheet
# ─────────────────────────────────────────────────────────────────────────────
# 1. Workbook factory helpers
# ─────────────────────────────────────────────────────────────────────────────
def make_workbook(path_or_buf=None) -> tuple[Workbook, bool]:
"""
Create a Workbook writing to a file path or BytesIO buffer.
Returns (workbook, is_memory).
If path_or_buf is None → in-memory BytesIO.
Usage:
wb, buf = make_workbook() # in-memory
wb, _ = make_workbook("out.xlsx") # file
"""
if path_or_buf is None:
buf = io.BytesIO()
wb = xlsxwriter.Workbook(buf, {"in_memory": True})
return wb, True
return xlsxwriter.Workbook(str(path_or_buf)), False
def get_bytes(wb: Workbook, buf: io.BytesIO | None) -> bytes:
"""Close workbook and return bytes (for in-memory workbooks)."""
wb.close()
if buf is not None:
return buf.getvalue()
return b""
# ─────────────────────────────────────────────────────────────────────────────
# 2. Common format factory
# ─────────────────────────────────────────────────────────────────────────────
class Formats:
"""
Pre-built XlsxWriter formats for common use cases.
Create once per Workbook.
Usage:
fmts = Formats(wb)
ws.write("A1", "Revenue", fmts.header)
ws.write("B1", 12500.00, fmts.money)
"""
def __init__(self, wb: Workbook, accent: str = "#4472C4"):
self.header = wb.add_format({
"bold": True, "bg_color": accent, "font_color": "white",
"border": 1, "align": "center", "valign": "vcenter",
})
self.subheader = wb.add_format({
"bold": True, "bg_color": "#D9E1F2", "border": 1,
"align": "center",
})
self.money = wb.add_format({"num_format": "$#,##0.00", "border": 1})
self.money_bold = wb.add_format({
"num_format": "$#,##0.00", "bold": True, "border": 1,
})
self.pct = wb.add_format({"num_format": "0.0%", "border": 1})
self.int_fmt = wb.add_format({"num_format": "#,##0", "border": 1})
self.cell = wb.add_format({"border": 1})
self.cell_alt = wb.add_format({"border": 1, "bg_color": "#EEF2F8"})
self.date_fmt = wb.add_format({
"num_format": "yyyy-mm-dd", "border": 1,
"align": "center",
})
self.title = wb.add_format({
"bold": True, "font_size": 16, "font_color": "#1F3864",
})
self.total = wb.add_format({
"bold": True, "bg_color": "#262626", "font_color": "white",
"num_format": "$#,##0.00", "border": 1,
})
# ─────────────────────────────────────────────────────────────────────────────
# 3. Table writer
# ─────────────────────────────────────────────────────────────────────────────
def write_table(
ws: Worksheet,
fmts: Formats,
headers: list[str],
rows: list[list[Any]],
start_row: int = 0,
start_col: int = 0,
col_widths: list[float] | None = None,
alternating: bool = True,
number_cols: set[int] | None = None,
money_cols: set[int] | None = None,
pct_cols: set[int] | None = None,
date_cols: set[int] | None = None,
table_style: str = "Table Style Medium 9",
) -> int:
"""
Write a header row + data rows with formatting.
Returns the last row index used.
number_cols / money_cols / pct_cols / date_cols: 0-indexed column sets.
"""
number_cols = number_cols or set()
money_cols = money_cols or set()
pct_cols = pct_cols or set()
date_cols = date_cols or set()
end_row = start_row + len(rows)
end_col = start_col + len(headers) - 1
# Column widths
for i, w in enumerate(col_widths or []):
ws.set_column(start_col + i, start_col + i, w)
# Write as Excel Table (enables autofilter + banded rows natively)
table_cols = [{"header": h} for h in headers]
ws.add_table(
start_row, start_col, end_row, end_col,
{"style": table_style, "columns": table_cols},
)
# Apply additional cell formatting
for r_idx, row in enumerate(rows):
row_fmt = fmts.cell_alt if (alternating and r_idx % 2 == 0) else fmts.cell
for c_idx, val in enumerate(row):
abs_row = start_row + 1 + r_idx
abs_col = start_col + c_idx
if c_idx in money_cols:
ws.write_number(abs_row, abs_col, float(val or 0), fmts.money)
elif c_idx in pct_cols:
ws.write_number(abs_row, abs_col, float(val or 0), fmts.pct)
elif c_idx in number_cols:
ws.write_number(abs_row, abs_col, float(val or 0), fmts.int_fmt)
elif c_idx in date_cols and isinstance(val, date):
ws.write_datetime(abs_row, abs_col, val, fmts.date_fmt) # type: ignore[arg-type]
else:
ws.write(abs_row, abs_col, val, row_fmt)
return end_row
# ─────────────────────────────────────────────────────────────────────────────
# 4. Chart helpers
# ─────────────────────────────────────────────────────────────────────────────
def add_column_chart(
wb: Workbook,
ws: Worksheet,
series_data: list[dict],
insert_cell: str = "F2",
title: str = "",
x_axis: str = "",
y_axis: str = "",
width: int = 480,
height: int = 288,
) -> None:
"""
Add a clustered column chart.
series_data: list of {
"name": (sheet, row, col) or str,
"categories": (sheet, row1, col1, row2, col2),
"values": (sheet, row1, col1, row2, col2),
"fill": {"color": "#hex"} (optional),
}
Example:
add_column_chart(wb, ws, [{
"name": "Revenue",
"categories": ["Sheet1", 1, 0, 6, 0], # A2:A7
"values": ["Sheet1", 1, 1, 6, 1], # B2:B7
}])
"""
chart = wb.add_chart({"type": "column"})
for s in series_data:
series = {k: v for k, v in s.items() if k != "fill"}
if "fill" in s:
series["fill"] = s["fill"]
chart.add_series(series)
if title:
chart.set_title({"name": title})
if x_axis:
chart.set_x_axis({"name": x_axis})
if y_axis:
chart.set_y_axis({"name": y_axis})
chart.set_size({"width": width, "height": height})
ws.insert_chart(insert_cell, chart)
# ─────────────────────────────────────────────────────────────────────────────
# 5. Sales report generator
# ─────────────────────────────────────────────────────────────────────────────
def generate_sales_report(
sales_data: list[dict],
title: str = "Sales Report",
) -> bytes:
"""
Generate a multi-column Excel sales report with chart.
sales_data: [{"name": str, "region": str, "revenue": float, "units": int}, ...]
Returns bytes.
"""
buf = io.BytesIO()
wb = xlsxwriter.Workbook(buf, {"in_memory": True})
ws = wb.add_worksheet("Sales")
fmts = Formats(wb)
# Title
ws.merge_range("A1:F1", title, fmts.title)
ws.set_row(0, 30)
ws.freeze_panes(2, 0)
headers = ["Name", "Region", "Revenue", "Units", "Avg Price", "% of Total"]
col_widths = [20, 14, 14, 10, 12, 12]
total_revenue = sum(r["revenue"] for r in sales_data) or 1
rows = [
[
r["name"],
r["region"],
r["revenue"],
r["units"],
r["revenue"] / r["units"] if r["units"] else 0,
r["revenue"] / total_revenue,
]
for r in sales_data
]
end_row = write_table(
ws, fmts, headers, rows,
start_row=1,
col_widths=col_widths,
money_cols={2, 4},
number_cols={3},
pct_cols={5},
)
# Totals row
ws.write(end_row + 1, 0, "TOTAL", fmts.subheader)
ws.write_formula(end_row + 1, 2, f"=SUM(C3:C{end_row + 1})", fmts.total)
ws.write_formula(end_row + 1, 3, f"=SUM(D3:D{end_row + 1})", fmts.total)
# Column chart of revenue by name
add_column_chart(
wb, ws,
[{"name": "Revenue",
"categories": ["Sales", 2, 0, end_row, 0],
"values": ["Sales", 2, 2, end_row, 2],
"fill": {"color": "#4472C4"}}],
insert_cell="H3",
title="Revenue by Salesperson",
y_axis="Revenue ($)",
)
# Conditional format — data bar on revenue column
ws.conditional_format(
f"C3:C{end_row + 1}",
{"type": "data_bar", "bar_color": "#63C384", "bar_border_color": "#63C384"},
)
wb.close()
return buf.getvalue()
# ─────────────────────────────────────────────────────────────────────────────
# Demo
# ─────────────────────────────────────────────────────────────────────────────
if __name__ == "__main__":
from pathlib import Path
sales = [
{"name": "Alice Johnson", "region": "West", "revenue": 142500.00, "units": 48},
{"name": "Bob Martinez", "region": "East", "revenue": 98750.00, "units": 35},
{"name": "Carol Williams", "region": "Central", "revenue": 175200.00, "units": 60},
{"name": "David Chen", "region": "North", "revenue": 61000.00, "units": 22},
{"name": "Eve Thompson", "region": "South", "revenue": 119400.00, "units": 41},
]
pdf = generate_sales_report(sales, title="Q4 2024 Sales Report")
Path("/tmp/sales_report.xlsx").write_bytes(pdf)
print(f"Excel report: {len(pdf):,} bytes → /tmp/sales_report.xlsx")For the openpyxl alternative — openpyxl can both read and write xlsx files and is ideal when you need to modify existing workbooks or read cell values back; XlsxWriter is write-only but has better performance for large files (streams to disk) and a cleaner API for charts, conditional formatting, and Excel Tables — use XlsxWriter when generating reports from scratch, openpyxl when you need to read or edit existing files. For the pandas.ExcelWriter alternative — df.to_excel(writer) uses either openpyxl or xlsxwriter as a backend; using XlsxWriter directly gives you full access to formatting, charts, and add_table() that the pandas abstraction does not expose — use to_excel for simple data dumps, XlsxWriter directly for styled reports with charts. The Claude Skills 360 bundle includes XlsxWriter skill sets covering make_workbook()/get_bytes() in-memory factory, Formats class with header/money/pct/date/total styles, write_table() with Excel Table and column type dispatch, add_column_chart() chart builder, generate_sales_report() with conditional format and chart, freeze_panes, merge_range, set_column width, and FastAPI BytesIO download pattern. Start with the free tier to try Excel report generation code generation.