# XLSX > Excel spreadsheet generation using openpyxl and xlsxwriter. Convert Claude output, data analysis results, and tabular data into formatted .xlsx files with charts, formulas, conditional formatting, and multiple sheets. Use for generating Excel reports programmatically. - Skill: `oimiragieo/xlsx` (Agent Skill, multi-file: 12 files) - Install (CLI): `npx skillmds@latest add oimiragieo/xlsx` - Raw SKILL.md: https://api.skillmd.com/api/skills/oimiragieo/xlsx/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: oimiragieo (https://skillmd.com/u/oimiragieo) - Updated: 2026-09-10 - Page: https://skillmd.com/skills/oimiragieo/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 ```bash pip install openpyxl xlsxwriter # or uv add openpyxl xlsxwriter ``` ## Quick Start (openpyxl) ```python 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 ```python 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 ```python 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 ```python 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) ```python 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) ```python 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 ```python 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 | Feature | openpyxl | xlsxwriter | | ------------------------ | -------- | ---------------- | | Read existing files | YES | No | | Modify existing files | YES | No | | Write new files | YES | YES | | Performance (large data) | Moderate | Excellent | | Formula support | YES | YES | | Charts | YES | YES (better API) | | Conditional formatting | YES | YES | | Tables (ListObject) | YES | YES | | Max rows (practical) | ~100K | Millions | **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.