Document XLSX Skill — Quick Reference
This skill enables creation, editing, and analysis of Excel spreadsheets programmatically. Claude should apply these patterns when users need to generate data reports, financial models, automate Excel workflows, or process spreadsheet data.
Modern Best Practices (Jan 2026):
- Treat spreadsheets as software: clear inputs/outputs, auditability, and versioning.
- Protect data integrity: control totals, validation, and traceability to sources.
- Accessibility: labels, contrast, structure; use Excel's Accessibility Checker; meet procurement/regulatory requirements when distributing externally.
- If distributing in the EU or regulated contexts, follow applicable accessibility requirements (often aligned with EN 301 549 / WCAG).
- Ship with a review loop and an owner (avoid "mystery models").
- Security: treat untrusted input/workbooks as hostile (formula injection, external links, hidden content, macros).
Quick Reference
| Task |
Tool/Library |
Language |
When to Use |
| Create XLSX |
ExcelJS |
Node.js |
Reports, data exports |
| Create XLSX |
openpyxl |
Python |
Read/write, modify existing files |
| Create XLSX |
XlsxWriter |
Python |
Write-only, rich formatting, charts |
| Data analysis |
pandas + openpyxl |
Python |
DataFrame to Excel with formatting |
| Read XLSX |
xlsx (SheetJS) |
Node.js |
Parse spreadsheets |
| Charts |
openpyxl/XlsxWriter |
Python |
Embedded visualizations |
| Styling |
ExcelJS/openpyxl |
Both |
Conditional formatting |
| Automation |
xlwings |
Python |
Excel installed, interactive workflows |
Guardrails and Caveats
- Formula calculation: libraries write formulas; Excel computes results when opened. If you need computed values server-side, calculate in code and write values (or use a dedicated formula engine).
- Pivot tables: programmatic creation is limited. Prefer pandas summaries (pivot tables as data) or Excel automation (xlwings/Office Scripts/VBA) if you truly need native pivots.
- Macros: openpyxl can preserve existing VBA (
keep_vba=True) but does not author macros; never generate or execute macros from untrusted input.
- Spreadsheet injection: never put untrusted strings into
formula fields; write them as text values and validate/sanitize user-provided data used in exports.
Core Operations
Create Spreadsheet (Node.js - exceljs)
import ExcelJS from 'exceljs';
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Sales Report');
// Headers with styling
sheet.columns = [
{ header: 'Product', key: 'product', width: 20 },
{ header: 'Quantity', key: 'qty', width: 12 },
{ header: 'Price', key: 'price', width: 12 },
{ header: 'Total', key: 'total', width: 15 },
];
// Style header row
sheet.getRow(1).font = { bold: true };
sheet.getRow(1).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FF4472C4' }
};
// Add data
const data = [
{ product: 'Widget A', qty: 100, price: 10 },
{ product: 'Widget B', qty: 50, price: 25 },
];
data.forEach((item, index) => {
sheet.addRow({
product: item.product,
qty: item.qty,
price: item.price,
total: { formula: `B${index + 2}*C${index + 2}` }
});
});
// Add totals row
const lastRow = sheet.rowCount + 1;
sheet.addRow({
product: 'TOTAL',
total: { formula: `SUM(D2:D${lastRow - 1})` }
});
// Currency formatting
sheet.getColumn('price').numFmt = '$#,##0.00';
sheet.getColumn('total').numFmt = '$#,##0.00';
await workbook.xlsx.writeFile('report.xlsx');
Create Spreadsheet (Python - openpyxl)
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
wb = Workbook()
ws = wb.active
ws.title = 'Sales Report'
# Headers
headers = ['Product', 'Quantity', 'Price', '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='4472C4', end_color='4472C4', fill_type='solid')
# Data
data = [
('Widget A', 100, 10),
('Widget B', 50, 25),
('Widget C', 75, 15),
]
for row_idx, (product, qty, price) in enumerate(data, 2):
ws.cell(row=row_idx, column=1, value=product)
ws.cell(row=row_idx, column=2, value=qty)
ws.cell(row=row_idx, column=3, value=price)
ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')
# Totals row
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value='TOTAL')
ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')
# Number formatting
for row in range(2, total_row + 1):
ws.cell(row=row, column=3).number_format = '$#,##0.00'
ws.cell(row=row, column=4).number_format = '$#,##0.00'
wb.save('report.xlsx')
Read and Analyze (Python - pandas)
import pandas as pd
# Read Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# Analysis
summary = df.groupby('Category').agg({
'Sales': 'sum',
'Quantity': 'mean'
}).round(2)
# Write to Excel with formatting
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Raw Data', index=False)
summary.to_excel(writer, sheet_name='Summary')
# Auto-adjust column widths
for sheet in writer.sheets.values():
for column in sheet.columns:
max_length = max(len(str(cell.value)) for cell in column)
sheet.column_dimensions[column[0].column_letter].width = max_length + 2
Add Charts (Python)
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.title = 'Sales by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'
# Data range (assumes column D contains the series and row 1 is headers)
max_row = ws.max_row
data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
ws.add_chart(chart, 'F2')
Conditional Formatting
from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill
# Color scale (heatmap)
ws.conditional_formatting.add(
'D2:D100',
ColorScaleRule(
start_type='min', start_color='FF0000',
end_type='max', end_color='00FF00'
)
)
# Highlight cells above threshold
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
ws.conditional_formatting.add(
'D2:D100',
FormulaRule(formula=['D2>1000'], fill=red_fill)
)
Common Formulas Reference
| Purpose |
Formula |
Example |
| Sum |
=SUM(range) |
=SUM(A1:A10) |
| Average |
=AVERAGE(range) |
=AVERAGE(B2:B100) |
| Count |
=COUNT(range) |
=COUNT(C:C) |
| Conditional sum |
=SUMIF(range,criteria,sum_range) |
=SUMIF(A:A,"Widget",B:B) |
| Lookup |
=VLOOKUP(value,range,col,FALSE) |
=VLOOKUP(A2,Data!A:C,3,FALSE) |
| If |
=IF(condition,true,false) |
=IF(B2>100,"High","Low") |
| Percentage |
=value/total |
=B2/SUM(B:B) |
Decision Tree
Excel Task: [What do you need?]
├─ Create new spreadsheet?
│ ├─ Simple data export → pandas to_excel()
│ ├─ Formatted report → exceljs or openpyxl
│ └─ With charts → openpyxl charts module
│
├─ Read/analyze existing?
│ ├─ Data analysis → pandas read_excel()
│ ├─ Preserve formatting → openpyxl load_workbook()
│ └─ Fast parsing → xlsx (SheetJS)
│
├─ Modify existing?
│ ├─ Add data → openpyxl (preserves formatting)
│ └─ Update formulas → openpyxl
│
└─ Complex features?
├─ Pivot tables → pandas summary tables or xlwings (native pivots)
├─ Data validation → openpyxl DataValidation
└─ Macros → preserve only; use xlwings for Excel automation
Do / Avoid (Jan 2026)
Do
- Separate Inputs / Calculations / Outputs (tabs or clear sections).
- Keep assumptions explicit (value + unit + source + date).
- Add control totals and reconciliation checks for imported data.
Avoid
- Hardcoded constants inside formulas without a documented assumption.
- Hidden rows/columns that change results without documentation.
- Sharing sheets with customer PII or secrets.
What Good Looks Like
- Structure: clear Inputs/Assumptions, Calculations, and Outputs separation (tabs or sections).
- Integrity: no
#REF!, broken named ranges, or hardcoded constants hidden in formulas.
- Traceability: every key output ties back to labeled inputs (units + source + date).
- Checks: control totals, reconciliations, and error flags that fail loudly.
- Review: independent review pass using
assets/spreadsheet-model-review-checklist.md.
Optional: AI / Automation
Use only when explicitly requested and policy-compliant.
- Generate first-pass formulas/charts; humans verify correctness and edge cases.
- Draft documentation tabs (assumptions, glossary); do not invent source data.
Navigation
Resources
- references/excel-formulas.md — Formula reference and patterns
- references/excel-formatting.md — Styling, conditional formatting
- references/excel-charts.md — Chart types and customization
- data/sources.json — Library documentation links
Templates
- assets/financial-report.md — Financial statement template
- assets/data-dashboard.md — Dashboard with charts
- assets/spreadsheet-model-review-checklist.md — Model QA checklist (assumptions, formulas, traceability)
Related Skills
1---
2name: document-xlsx
3description: "Create, edit, audit, and extract Excel spreadsheets (.xlsx): generate reports/exports, apply formulas/formatting/charts/data validation, parse existing workbooks, and avoid spreadsheet risks (formula injection, broken links, hidden rows). Supports ExcelJS, openpyxl, pandas, XlsxWriter, and SheetJS."
4metadata:
5 source: https://skillmd.com/skills/aibot88/document-xlsx
6---
7
8# Document XLSX Skill — Quick Reference
9
10This skill enables creation, editing, and analysis of Excel spreadsheets programmatically. Claude should apply these patterns when users need to generate data reports, financial models, automate Excel workflows, or process spreadsheet data.
11
12**Modern Best Practices (Jan 2026)**:
13- Treat spreadsheets as software: clear inputs/outputs, auditability, and versioning.
14- Protect data integrity: control totals, validation, and traceability to sources.
15- Accessibility: labels, contrast, structure; use Excel's Accessibility Checker; meet procurement/regulatory requirements when distributing externally.
16- If distributing in the EU or regulated contexts, follow applicable accessibility requirements (often aligned with EN 301 549 / WCAG).
17- Ship with a review loop and an owner (avoid "mystery models").
18- Security: treat untrusted input/workbooks as hostile (formula injection, external links, hidden content, macros).
19
20---
21
22## Quick Reference
23
24| Task | Tool/Library | Language | When to Use |
25|------|--------------|----------|-------------|
26| Create XLSX | ExcelJS | Node.js | Reports, data exports |
27| Create XLSX | openpyxl | Python | Read/write, modify existing files |
28| Create XLSX | XlsxWriter | Python | Write-only, rich formatting, charts |
29| Data analysis | pandas + openpyxl | Python | DataFrame to Excel with formatting |
30| Read XLSX | xlsx (SheetJS) | Node.js | Parse spreadsheets |
31| Charts | openpyxl/XlsxWriter | Python | Embedded visualizations |
32| Styling | ExcelJS/openpyxl | Both | Conditional formatting |
33| Automation | xlwings | Python | Excel installed, interactive workflows |
34
35## Guardrails and Caveats
36
37- Formula calculation: libraries write formulas; Excel computes results when opened. If you need computed values server-side, calculate in code and write values (or use a dedicated formula engine).
38- Pivot tables: programmatic creation is limited. Prefer pandas summaries (pivot tables as data) or Excel automation (xlwings/Office Scripts/VBA) if you truly need native pivots.
39- Macros: openpyxl can preserve existing VBA (`keep_vba=True`) but does not author macros; never generate or execute macros from untrusted input.
40- Spreadsheet injection: never put untrusted strings into `formula` fields; write them as text values and validate/sanitize user-provided data used in exports.
41
42---
43
44## Core Operations
45
46### Create Spreadsheet (Node.js - exceljs)
47
48```typescript
49import ExcelJS from 'exceljs';
50
51const workbook = new ExcelJS.Workbook();
52const sheet = workbook.addWorksheet('Sales Report');
53
54// Headers with styling
55sheet.columns = [
56 { header: 'Product', key: 'product', width: 20 },
57 { header: 'Quantity', key: 'qty', width: 12 },
58 { header: 'Price', key: 'price', width: 12 },
59 { header: 'Total', key: 'total', width: 15 },
60];
61
62// Style header row
63sheet.getRow(1).font = { bold: true };
64sheet.getRow(1).fill = {
65 type: 'pattern',
66 pattern: 'solid',
67 fgColor: { argb: 'FF4472C4' }
68};
69
70// Add data
71const data = [
72 { product: 'Widget A', qty: 100, price: 10 },
73 { product: 'Widget B', qty: 50, price: 25 },
74];
75
76data.forEach((item, index) => {
77 sheet.addRow({
78 product: item.product,
79 qty: item.qty,
80 price: item.price,
81 total: { formula: `B${index + 2}*C${index + 2}` }
82 });
83});
84
85// Add totals row
86const lastRow = sheet.rowCount + 1;
87sheet.addRow({
88 product: 'TOTAL',
89 total: { formula: `SUM(D2:D${lastRow - 1})` }
90});
91
92// Currency formatting
93sheet.getColumn('price').numFmt = '$#,##0.00';
94sheet.getColumn('total').numFmt = '$#,##0.00';
95
96await workbook.xlsx.writeFile('report.xlsx');
97```
98
99### Create Spreadsheet (Python - openpyxl)
100
101```python
102from openpyxl import Workbook
103from openpyxl.styles import Font, PatternFill
104
105wb = Workbook()
106ws = wb.active
107ws.title = 'Sales Report'
108
109# Headers
110headers = ['Product', 'Quantity', 'Price', 'Total']
111for col, header in enumerate(headers, 1):
112 cell = ws.cell(row=1, column=col, value=header)
113 cell.font = Font(bold=True, color='FFFFFF')
114 cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
115
116# Data
117data = [
118 ('Widget A', 100, 10),
119 ('Widget B', 50, 25),
120 ('Widget C', 75, 15),
121]
122
123for row_idx, (product, qty, price) in enumerate(data, 2):
124 ws.cell(row=row_idx, column=1, value=product)
125 ws.cell(row=row_idx, column=2, value=qty)
126 ws.cell(row=row_idx, column=3, value=price)
127 ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')
128
129# Totals row
130total_row = len(data) + 2
131ws.cell(row=total_row, column=1, value='TOTAL')
132ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')
133
134# Number formatting
135for row in range(2, total_row + 1):
136 ws.cell(row=row, column=3).number_format = '$#,##0.00'
137 ws.cell(row=row, column=4).number_format = '$#,##0.00'
138
139wb.save('report.xlsx')
140```
141
142### Read and Analyze (Python - pandas)
143
144```python
145import pandas as pd
146
147# Read Excel file
148df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
149
150# Analysis
151summary = df.groupby('Category').agg({
152 'Sales': 'sum',
153 'Quantity': 'mean'
154}).round(2)
155
156# Write to Excel with formatting
157with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
158 df.to_excel(writer, sheet_name='Raw Data', index=False)
159 summary.to_excel(writer, sheet_name='Summary')
160
161 # Auto-adjust column widths
162 for sheet in writer.sheets.values():
163 for column in sheet.columns:
164 max_length = max(len(str(cell.value)) for cell in column)
165 sheet.column_dimensions[column[0].column_letter].width = max_length + 2
166```
167
168### Add Charts (Python)
169
170```python
171from openpyxl.chart import BarChart, Reference
172
173chart = BarChart()
174chart.title = 'Sales by Product'
175chart.x_axis.title = 'Product'
176chart.y_axis.title = 'Sales'
177
178# Data range (assumes column D contains the series and row 1 is headers)
179max_row = ws.max_row
180data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
181categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)
182
183chart.add_data(data_ref, titles_from_data=True)
184chart.set_categories(categories)
185chart.shape = 4
186
187ws.add_chart(chart, 'F2')
188```
189
190### Conditional Formatting
191
192```python
193from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
194from openpyxl.styles import PatternFill
195
196# Color scale (heatmap)
197ws.conditional_formatting.add(
198 'D2:D100',
199 ColorScaleRule(
200 start_type='min', start_color='FF0000',
201 end_type='max', end_color='00FF00'
202 )
203)
204
205# Highlight cells above threshold
206red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
207ws.conditional_formatting.add(
208 'D2:D100',
209 FormulaRule(formula=['D2>1000'], fill=red_fill)
210)
211```
212
213---
214
215## Common Formulas Reference
216
217| Purpose | Formula | Example |
218|---------|---------|---------|
219| Sum | `=SUM(range)` | `=SUM(A1:A10)` |
220| Average | `=AVERAGE(range)` | `=AVERAGE(B2:B100)` |
221| Count | `=COUNT(range)` | `=COUNT(C:C)` |
222| Conditional sum | `=SUMIF(range,criteria,sum_range)` | `=SUMIF(A:A,"Widget",B:B)` |
223| Lookup | `=VLOOKUP(value,range,col,FALSE)` | `=VLOOKUP(A2,Data!A:C,3,FALSE)` |
224| If | `=IF(condition,true,false)` | `=IF(B2>100,"High","Low")` |
225| Percentage | `=value/total` | `=B2/SUM(B:B)` |
226
227---
228
229## Decision Tree
230
231```text
232Excel Task: [What do you need?]
233 ├─ Create new spreadsheet?
234 │ ├─ Simple data export → pandas to_excel()
235 │ ├─ Formatted report → exceljs or openpyxl
236 │ └─ With charts → openpyxl charts module
237 │
238 ├─ Read/analyze existing?
239 │ ├─ Data analysis → pandas read_excel()
240 │ ├─ Preserve formatting → openpyxl load_workbook()
241 │ └─ Fast parsing → xlsx (SheetJS)
242 │
243 ├─ Modify existing?
244 │ ├─ Add data → openpyxl (preserves formatting)
245 │ └─ Update formulas → openpyxl
246 │
247 └─ Complex features?
248 ├─ Pivot tables → pandas summary tables or xlwings (native pivots)
249 ├─ Data validation → openpyxl DataValidation
250 └─ Macros → preserve only; use xlwings for Excel automation
251```
252
253---
254
255## Do / Avoid (Jan 2026)
256
257### Do
258
259- Separate Inputs / Calculations / Outputs (tabs or clear sections).
260- Keep assumptions explicit (value + unit + source + date).
261- Add control totals and reconciliation checks for imported data.
262
263### Avoid
264
265- Hardcoded constants inside formulas without a documented assumption.
266- Hidden rows/columns that change results without documentation.
267- Sharing sheets with customer PII or secrets.
268
269## What Good Looks Like
270
271- Structure: clear Inputs/Assumptions, Calculations, and Outputs separation (tabs or sections).
272- Integrity: no `#REF!`, broken named ranges, or hardcoded constants hidden in formulas.
273- Traceability: every key output ties back to labeled inputs (units + source + date).
274- Checks: control totals, reconciliations, and error flags that fail loudly.
275- Review: independent review pass using `assets/spreadsheet-model-review-checklist.md`.
276
277## Optional: AI / Automation
278
279Use only when explicitly requested and policy-compliant.
280
281- Generate first-pass formulas/charts; humans verify correctness and edge cases.
282- Draft documentation tabs (assumptions, glossary); do not invent source data.
283
284## Navigation
285
286**Resources**
287- [references/excel-formulas.md](references/excel-formulas.md) — Formula reference and patterns
288- [references/excel-formatting.md](references/excel-formatting.md) — Styling, conditional formatting
289- [references/excel-charts.md](references/excel-charts.md) — Chart types and customization
290- [data/sources.json](data/sources.json) — Library documentation links
291
292**Templates**
293- [assets/financial-report.md](assets/financial-report.md) — Financial statement template
294- [assets/data-dashboard.md](assets/data-dashboard.md) — Dashboard with charts
295- [assets/spreadsheet-model-review-checklist.md](assets/spreadsheet-model-review-checklist.md) — Model QA checklist (assumptions, formulas, traceability)
296
297**Related Skills**
298- [../document-pdf/SKILL.md](../document-pdf/SKILL.md) — PDF generation from data
299- [../ai-ml-data-science/SKILL.md](../ai-ml-data-science/SKILL.md) — Data analysis patterns
300- [../data-sql-optimization/SKILL.md](../data-sql-optimization/SKILL.md) — Database to Excel workflows