XLSX

Excel Spreadsheet Generation (xlsx)

Overview

Generate .xlsx files programmatically to export data, build reports, and create dashboards. Two primary libraries:

• openpyxl — read/write/modify existing Excel files, formula support, full feature set
• xlsxwriter — write-only, high-performance for large datasets, richer chart/format API

When to Invoke

Skill({ skill: 'xlsx' }) when:

• User asks to "export to Excel", "create a spreadsheet", or "generate a report in Excel"
• Converting tabular data, query results, or analysis output to .xlsx
• Building financial models, dashboards, or structured reports
• Creating templated Excel outputs for recurring workflows

Installation

pip install openpyxl xlsxwriter
# or
uv add openpyxl xlsxwriter

Quick Start (openpyxl)

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# Headers
headers = ["Product", "Q1", "Q2", "Q3", "Q4", "Total"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill(start_color="1F497D", fill_type="solid")
    cell.alignment = Alignment(horizontal="center")

# Data rows
data = [
    ("Widget A", 45000, 52000, 61000, 58000),
    ("Widget B", 32000, 38000, 41000, 44000),
    ("Widget C", 18000, 21000, 19000, 23000),
]
for row_idx, row in enumerate(data, 2):
    for col_idx, value in enumerate(row, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)
    # Add SUM formula for Total column
    ws.cell(row=row_idx, column=6, value=f"=SUM(B{row_idx}:E{row_idx})")

# Auto-fit column widths
for col in ws.columns:
    max_len = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[get_column_letter(col[0].column)].width = max_len + 4

wb.save("sales-report.xlsx")

Named Styles

from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, Alignment

def create_styles(wb):
    # Header style
    header_style = NamedStyle(name="header")
    header_style.font = Font(bold=True, color="FFFFFF", size=12)
    header_style.fill = PatternFill(start_color="2E4057", fill_type="solid")
    header_style.alignment = Alignment(horizontal="center", vertical="center")
    thin = Side(border_style="thin", color="000000")
    header_style.border = Border(bottom=thin)
    wb.add_named_style(header_style)

    # Currency style
    currency_style = NamedStyle(name="currency")
    currency_style.number_format = '"$"#,##0.00'
    wb.add_named_style(currency_style)

    # Percent style
    percent_style = NamedStyle(name="percent")
    percent_style.number_format = '0.0%'
    wb.add_named_style(percent_style)

    return wb

wb = create_styles(Workbook())
ws = wb.active
ws["A1"].style = "header"
ws["B2"].style = "currency"

Multiple Sheets

def build_multi_sheet_report(data_by_region: dict, output_path: str):
    wb = Workbook()
    wb.remove(wb.active)  # Remove default sheet

    summary_ws = wb.create_sheet("Summary", 0)

    for region, data in data_by_region.items():
        ws = wb.create_sheet(region)
        # Write region data
        for row_idx, row in enumerate(data, 1):
            for col_idx, value in enumerate(row, 1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        # Add cross-sheet reference to summary
        summary_ws.append([region, f"='{region}'!B2"])

    wb.save(output_path)

Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, CellIsRule
from openpyxl.styles import PatternFill

# Color scale (green-yellow-red)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",
    mid_type="percentile", mid_value=50, mid_color="FFEB84",
    end_type="max", end_color="F8696B"
)
ws.conditional_formatting.add("B2:E10", color_scale)

# Highlight negative values in red
red_fill = PatternFill(start_color="FFC7CE", fill_type="solid")
negative_rule = CellIsRule(operator="lessThan", formula=["0"], fill=red_fill)
ws.conditional_formatting.add("B2:E10", negative_rule)

Charts (openpyxl)

from openpyxl.chart import BarChart, Reference

def add_bar_chart(ws, title, data_range, categories_range, position="G1"):
    chart = BarChart()
    chart.type = "col"
    chart.title = title
    chart.style = 10
    chart.y_axis.title = "Value"
    chart.x_axis.title = "Category"
    chart.width = 20
    chart.height = 12

    data = Reference(ws, **data_range)  # e.g., min_col=2, min_row=1, max_col=5, max_row=5
    cats = Reference(ws, **categories_range)  # e.g., min_col=1, min_row=2, max_row=5

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, position)
    return chart

High-Performance with xlsxwriter (Large Datasets)

import xlsxwriter

def export_large_dataset(rows: list[list], output_path: str, sheet_name="Data"):
    """Use xlsxwriter for 10K+ row exports — significantly faster than openpyxl."""
    workbook = xlsxwriter.Workbook(output_path)
    worksheet = workbook.add_worksheet(sheet_name)

    # Define formats
    header_fmt = workbook.add_format({
        "bold": True, "bg_color": "#1F497D", "font_color": "#FFFFFF",
        "border": 1, "align": "center"
    })
    money_fmt = workbook.add_format({"num_format": "$#,##0.00"})
    pct_fmt = workbook.add_format({"num_format": "0.0%"})
    date_fmt = workbook.add_format({"num_format": "yyyy-mm-dd"})

    # Write headers
    headers = rows[0] if rows else []
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_fmt)

    # Write data rows using write_row for performance
    for row_idx, row in enumerate(rows[1:], 1):
        worksheet.write_row(row_idx, 0, row)

    # Auto-filter on headers
    worksheet.autofilter(0, 0, len(rows) - 1, len(headers) - 1)

    # Freeze top row
    worksheet.freeze_panes(1, 0)

    workbook.close()
    return output_path

Agent Workflow: Claude Output → Excel

from dataclasses import dataclass
from typing import Any

@dataclass
class SheetSpec:
    name: str
    headers: list[str]
    rows: list[list[Any]]
    formats: dict[int, str] = None  # col_index -> format_name

def claude_output_to_excel(sheets: list[SheetSpec], output_path: str) -> str:
    """
    Convert Claude structured output to Excel file.

    sheets: List of SheetSpec objects describing each worksheet.
    """
    wb = Workbook()
    wb.remove(wb.active)

    for spec in sheets:
        ws = wb.create_sheet(spec.name)

        # Write headers
        for col, header in enumerate(spec.headers, 1):
            cell = ws.cell(row=1, column=col, value=header)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="D9E1F2", fill_type="solid")

        # Write rows
        for row_idx, row in enumerate(spec.rows, 2):
            for col_idx, value in enumerate(row, 1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        # Auto-width
        for col in ws.columns:
            max_len = max(len(str(cell.value or "")) for cell in col)
            ws.column_dimensions[get_column_letter(col[0].column)].width = min(max_len + 4, 50)

    wb.save(output_path)
    return output_path

# Example: Export analysis results
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
from openpyxl import Workbook

result = claude_output_to_excel([
    SheetSpec(
        name="Summary",
        headers=["Metric", "Value", "Change"],
        rows=[
            ["Revenue", "$2.1M", "+23%"],
            ["Users", "45,230", "+15%"],
            ["NPS", "72", "+8pts"],
        ]
    ),
    SheetSpec(
        name="Details",
        headers=["Date", "Product", "Amount", "Region"],
        rows=[
            ["2026-01-15", "Widget A", 12500, "North"],
            ["2026-01-16", "Widget B", 8900, "South"],
        ]
    ),
], "analysis-output.xlsx")

Choosing: openpyxl vs xlsxwriter

Rule: Use openpyxl when reading/modifying, xlsxwriter when creating large new files.

Output Location

Save generated .xlsx files to:

• User-specified path, or
• .claude/context/artifacts/ for agent-generated reports

Anti-Patterns

• Never use openpyxl write_only mode then try to read cells back
• Never forget workbook.close() with xlsxwriter — file is not flushed until close
• Never use string values for numbers in Excel cells — use native Python int/float
• Never build large spreadsheets cell-by-cell with openpyxl — use write_row with xlsxwriter
• Never leave merged cells in auto-width calculation — causes AttributeError

Related Skills

• pptx — PowerPoint generation (for visual slide presentations)
• data-expert — Data processing, pandas, transformation pipelines
• markitdown-converter — Convert existing Excel files to Markdown for Claude

Memory Protocol (MANDATORY)

Before starting: Read .claude/context/memory/learnings.md

After completing: Record library version gotchas or performance patterns.

ASSUME INTERRUPTION: Your context may reset. If it's not in memory, it didn't happen.