vasilyu1983/ai-agents-public · GitHub
Create/edit .xlsx spreadsheets with tables, formulas, charts, validation, and workbook automation. Use when asked to generate Excel reports, models, exports, or audit spreadsheets.
프로젝트 폴더에서 아래 명령어를 실행하고, 설치할 에이전트를 선택하세요.
npx skills add vasilyu1983/ai-agents-public --skill document-xlsx설치 명령을 직접 실행해야 적용됩니다. 지원 에이전트와 필요한 권한·라이선스는 제작자의 안내를 확인하세요.
agents/openai.yamlinterface: display_name: "Document XLSX Skill - Quick Reference" short_description: "Create/edit .xlsx spreadsheets with tables, formulas, charts, validation, and" default_prompt: "Use $document-xlsx for Create/edit .xlsx spreadsheets with tables, formulas, charts, validation, and workbook automation. Use when asked to generate Excel reports, models, exports, or audit spreadsheets."
assets/data-dashboard.md# Data Dashboard Template
Copy-paste template for generating Excel dashboards with charts, KPIs, and data tables.
---
## KPI Dashboard
```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.utils import get_column_letter
def create_kpi_dashboard(data: dict, output_path: str = 'dashboard.xlsx'):
"""
Generate a KPI dashboard with charts and metrics.
Args:
data: Dictionary with KPIs, trends, and breakdowns
output_path: Output file path
Example data:
{
'title': 'Q4 2024 Sales Dashboard',
'kpis': [
{'name': 'Total Revenue', 'value': 1250000, 'target': 1200000, 'format': 'currency'},
{'name': 'Orders', 'value': 3420, 'target': 3000, 'format': 'number'},
{'name': 'Conversion Rate', 'value': 0.032, 'target': 0.03, 'format': 'percent'},
{'name': 'Avg Order Value', 'value': 365.50, 'target': 350, 'format': 'currency'},
],
'monthly_trend': {
'labels': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
'revenue': [85000, 92000, 88000, 95000, 102000, 98000,
105000, 112000, 108000, 115000, 125000, 125000],
'orders': [240, 260, 250, 270, 290, 280, 300, 320, 310, 330, 360, 360],
},
'category_breakdown': [
('Electronics', 450000),
('Clothing', 380000),
('Home & Garden', 250000),
('Sports', 170000),
],
'top_products': [
('Widget Pro X', 2500, 125000),
('Smart Watch Elite', 1800, 89000),
('Wireless Earbuds', 3200, 64000),
('Laptop Stand', 2100, 52000),
('Phone Case Premium', 4500, 45000),
]
}
"""
wb = Workbook()
ws = wb.active
ws.title = 'Dashboard'
# Styles
title_font = Font(bold=True, size=16, color='FFFFFF')
header_font = Font(bold=True, size=11)
kpi_value_font = Font(bold=True, size=24)
kpi_label_font = Font(size=10, color='666666')
section_fill = PatternFill(start_color='4472C4', fill_type='solid')
kpi_positive_fill = PatternFill(start_color='C6EFCE', fill_type='solid')
kpi_negative_fill = PatternFill(start_color='FFC7CE', fill_type='solid')
# Page setup
ws.sheet_view.showGridLines = False
for col in range(1, 15):
ws.column_dimensions[get_column_letter(col)].width = 12
row = 1
# ═══════════════════════════════════════════════════════════
# TITLE SECTION
# ═══════════════════════════════════════════════════════════
ws.merge_cells('A1:N1')
ws['A1'] = data['title']
ws['A1'].font = title_font
ws['A1'].fill = section_fill
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws.row_dimensions[1].height = 35
row = 3
# ═══════════════════════════════════════════════════════════
# KPI CARDS
# ═══════════════════════════════════════════════════════════
kpi_start_col = 1
for i, kpi in enumerate(data['kpis']):
col = kpi_start_col + (i * 3)
# KPI Card background
for r in range(row, row + 4):
for c in range(col, col + 3):
cell = ws.cell(row=r, column=c)
cell.fill = PatternFill(start_color='F2F2F2', fill_type='solid')
# KPI Name
ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col+2)
cell = ws.cell(row=row, column=col)
cell.value = kpi['name']
cell.font = kpi_label_font
cell.alignment = Alignment(horizontal='center')
# KPI Value
ws.merge_cells(start_row=row+1, start_column=col, end_row=row+1, end_column=col+2)
cell = ws.cell(row=row+1, column=col)
if kpi['format'] == 'currency':
cell.value = kpi['value']
cell.number_format = '$#,##0'
elif kpi['format'] == 'percent':
cell.value = kpi['value']
cell.number_format = '0.0%'
else:
cell.value = kpi['value']
cell.number_format = '#,##0'
cell.font = kpi_value_font
cell.alignment = Alignment(horizontal='center')
# Target comparison
ws.merge_cells(start_row=row+2, start_column=col, end_row=row+2, end_column=col+2)
cell = ws.cell(row=row+2, column=col)
if kpi['value'] >= kpi['target']:
variance = (kpi['value'] - kpi['target']) / kpi['target']
cell.value = f"+{variance:.1%} vs target"
cell.font = Font(color='006400', size=10)
else:
variance = (kpi['target'] - kpi['value']) / kpi['target']
cell.value = f"-{variance:.1%} vs target"
cell.font = Font(color='8B0000', size=10)
cell.alignment = Alignment(horizontal='center')
row += 5
# ═══════════════════════════════════════════════════════════
# TREND CHART (Line)
# ═══════════════════════════════════════════════════════════
# Write trend data
trend_data_row = row
ws.cell(row=row, column=1, value='Month')
ws.cell(row=row, column=2, value='Revenue')
ws.cell(row=row, column=3, value='Orders')
for i, label in enumerate(data['monthly_trend']['labels']):
ws.cell(row=row+1+i, column=1, value=label)
ws.cell(row=row+1+i, column=2, value=data['monthly_trend']['revenue'][i])
ws.cell(row=row+1+i, column=3, value=data['monthly_trend']['orders'][i])
# Create line chart
chart = LineChart()
chart.title = 'Monthly Revenue Trend'
chart.style = 10
chart.y_axis.title = 'Revenue ($)'
chart.x_axis.title = 'Month'
data_ref = Reference(ws, min_col=2, min_row=trend_data_row,
max_row=trend_data_row+12, max_col=2)
cats = Reference(ws, min_col=1, min_row=trend_data_row+1,
max_row=trend_data_row+12)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)
chart.series[0].smooth = True
chart.width = 18
chart.height = 10
ws.add_chart(chart, 'E8')
row += 15
# ═══════════════════════════════════════════════════════════
# CATEGORY BREAKDOWN (Pie Chart)
# ═══════════════════════════════════════════════════════════
cat_data_row = row
ws.cell(row=row, column=1, value='Category')
ws.cell(row=row, column=2, value='Revenue')
for i, (category, revenue) in enumerate(data['category_breakdown']):
ws.cell(row=row+1+i, column=1, value=category)
ws.cell(row=row+1+i, column=2, value=revenue)
pie_chart = PieChart()
pie_chart.title = 'Revenue by Category'
data_ref = Reference(ws, min_col=2, min_row=cat_data_row,
max_row=cat_data_row+len(data['category_breakdown']))
labels = Reference(ws, min_col=1, min_row=cat_data_row+1,
max_row=cat_data_row+len(data['category_breakdown']))
pie_chart.add_data(data_ref, titles_from_data=True)
pie_chart.set_categories(labels)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showPercent = True
pie_chart.dataLabels.showCatName = True
pie_chart.dataLabels.showVal = False
pie_chart.width = 10
pie_chart.height = 10
ws.add_chart(pie_chart, 'A23')
# ═══════════════════════════════════════════════════════════
# TOP PRODUCTS TABLE
# ═══════════════════════════════════════════════════════════
table_row = row
table_col = 5
# Headers
headers = ['Product', 'Units Sold', 'Revenue']
for i, header in enumerate(headers):
cell = ws.cell(row=table_row, column=table_col+i)
cell.value = header
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(start_color='4472C4', fill_type='solid')
cell.alignment = Alignment(horizontal='center')
# Data rows
for i, (product, units, revenue) in enumerate(data['top_products']):
row_num = table_row + 1 + i
ws.cell(row=row_num, column=table_col, value=product)
ws.cell(row=row_num, column=table_col+1, value=units).number_format = '#,##0'
ws.cell(row=row_num, column=table_col+2, value=revenue).number_format = '$#,##0'
# Alternating row colors
if i % 2 == 0:
for c in range(table_col, table_col+3):
ws.cell(row=row_num, column=c).fill = PatternFill(
start_color='F2F2F2', fill_type='solid'
)
# Adjust column widths for table
ws.column_dimensions[get_column_letter(table_col)].width = 20
ws.column_dimensions[get_column_letter(table_col+1)].width = 12
ws.column_dimensions[get_column_letter(table_col+2)].width = 12
# Hide raw data columns
ws.column_dimensions['A'].hidden = False
ws.column_dimensions['B'].hidden = False
ws.column_dimensions['C'].hidden = False
wb.save(output_path)
return output_path
# Example usage
if __name__ == '__main__':
sample_data = {
'title': 'Q4 2024 Sales Dashboard',
'kpis': [
{'name': 'Total Revenue', 'value': 1250000, 'target': 1200000, 'format': 'currency'},
{'name': 'Orders', 'value': 3420, 'target': 3000, 'format': 'number'},
{'name': 'Conversion Rate', 'value': 0.032, 'target': 0.03, 'format': 'percent'},
{'name': 'Avg Order Value', 'value': 365.50, 'target': 350, 'format': 'currency'},
],
'monthly_trend': {
'labels': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
'revenue': [85000, 92000, 88000, 95000, 102000, 98000,
105000, 112000, 108000, 115000, 125000, 125000],
'orders': [240, 260, 250, 270, 290, 280, 300, 320, 310, 330, 360, 360],
},
'category_breakdown': [
('Electronics', 450000),
('Clothing', 380000),
('Home & Garden', 250000),
('Sports', 170000),
],
'top_products': [
('Widget Pro X', 2500, 125000),
('Smart Watch Elite', 1800, 89000),
('Wireless Earbuds', 3200, 64000),
('Laptop Stand', 2100, 52000),
('Phone Case Premium', 4500, 45000),
]
}
create_kpi_dashboard(sample_data)
```
---
## Sales Report Dashboard
```python
def create_sales_dashboard(sales_data: list, output_path: str = 'sales_dashboard.xlsx'):
"""
Generate sales dashboard from transaction data.
Args:
sales_data: List of sales records
output_path: Output file path
Example sales_data:
[
{'date': '2024-01-15', 'product': 'Widget A', 'category': 'Electronics',
'quantity': 5, 'unit_price': 99.99, 'region': 'North'},
...
]
"""
import pandas as pd
from datetime import datetime
# Convert to DataFrame for analysis
df = pd.DataFrame(sales_data)
df['date'] = pd.to_datetime(df['date'])
df['revenue'] = df['quantity'] * df['unit_price']
df['month'] = df['date'].dt.strftime('%Y-%m')
wb = Workbook()
# ═══════════════════════════════════════════════════════════
# SUMMARY SHEET
# ═══════════════════════════════════════════════════════════
ws_summary = wb.active
ws_summary.title = 'Summary'
# KPIs
total_revenue = df['revenue'].sum()
total_orders = len(df)
avg_order_value = df['revenue'].mean()
top_category = df.groupby('category')['revenue'].sum().idxmax()
kpis = [
('Total Revenue', total_revenue, '$#,##0.00'),
('Total Orders', total_orders, '#,##0'),
('Avg Order Value', avg_order_value, '$#,##0.00'),
('Top Category', top_category, '@'),
]
ws_summary['A1'] = 'Sales Dashboard Summary'
ws_summary['A1'].font = Font(bold=True, size=16)
for i, (label, value, fmt) in enumerate(kpis):
ws_summary.cell(row=3+i, column=1, value=label)
cell = ws_summary.cell(row=3+i, column=2, value=value)
if fmt != '@':
cell.number_format = fmt
# Monthly trend
monthly = df.groupby('month')['revenue'].sum().reset_index()
row = 10
ws_summary.cell(row=row, column=1, value='Month')
ws_summary.cell(row=row, column=2, value='Revenue')
for i, (_, month_row) in enumerate(monthly.iterrows()):
ws_summary.cell(row=row+1+i, column=1, value=month_row['month'])
ws_summary.cell(row=row+1+i, column=2, value=month_row['revenue'])
# Add trend chart
chart = BarChart()
chart.title = 'Monthly Revenue'
chart.type = 'col'
data_ref = Reference(ws_summary, min_col=2, min_row=row,
max_row=row+len(monthly))
cats = Reference(ws_summary, min_col=1, min_row=row+1,
max_row=row+len(monthly))
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)
chart.width = 15
chart.height = 8
ws_summary.add_chart(chart, 'D3')
# ═══════════════════════════════════════════════════════════
# RAW DATA SHEET
# ═══════════════════════════════════════════════════════════
ws_data = wb.create_sheet('Raw Data')
# Headers
headers = ['Date', 'Product', 'Category', 'Quantity', 'Unit Price', 'Revenue', 'Region']
for i, header in enumerate(headers, 1):
cell = ws_data.cell(row=1, column=i, value=header)
cell.font = Font(bold=True)
cell.fill = PatternFill(start_color='4472C4', fill_type='solid')
cell.font = Font(bold=True, color='FFFFFF')
# Data
for i, record in enumerate(sales_data, 2):
ws_data.cell(row=i, column=1, value=record['date'])
ws_data.cell(row=i, column=2, value=record['product'])
ws_data.cell(row=i, column=3, value=record['category'])
ws_data.cell(row=i, column=4, value=record['quantity'])
ws_data.cell(row=i, column=5, value=record['unit_price']).number_format = '$#,##0.00'
revenue = record['quantity'] * record['unit_price']
ws_data.cell(row=i, column=6, value=revenue).number_format = '$#,##0.00'
ws_data.cell(row=i, column=7, value=record['region'])
# Auto-filter
ws_data.auto_filter.ref = f'A1:G{len(sales_data)+1}'
# Freeze header row
ws_data.freeze_panes = 'A2'
wb.save(output_path)
return output_path
```
---
## Project Status Dashboard
```python
def create_project_dashboard(projects: list, output_path: str = 'project_dashboard.xlsx'):
"""
Generate project status dashboard.
Args:
projects: List of project dictionaries
Example:
[
{
'name': 'Website Redesign',
'status': 'In Progress',
'progress': 0.65,
'budget': 50000,
'spent': 32000,
'due_date': '2024-03-15',
'owner': 'John Smith'
},
...
]
"""
from openpyxl.formatting.rule import DataBarRule, FormulaRule
wb = Workbook()
ws = wb.active
ws.title = 'Projects'
# Styles
header_fill = PatternFill(start_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')
# Status colors
status_colors = {
'Completed': 'C6EFCE',
'In Progress': 'FFEB9C',
'At Risk': 'FFC7CE',
'Not Started': 'F2F2F2',
}
# Headers
headers = ['Project', 'Status', 'Progress', 'Budget', 'Spent', 'Remaining', 'Due Date', 'Owner']
ws.column_dimensions['A'].width = 25
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12
ws.column_dimensions['E'].width = 12
ws.column_dimensions['F'].width = 12
ws.column_dimensions['G'].width = 12
ws.column_dimensions['H'].width = 15
for i, header in enumerate(headers, 1):
cell = ws.cell(row=1, column=i, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
# Data
for i, project in enumerate(projects, 2):
ws.cell(row=i, column=1, value=project['name'])
# Status with color
status_cell = ws.cell(row=i, column=2, value=project['status'])
status_cell.fill = PatternFill(
start_color=status_colors.get(project['status'], 'FFFFFF'),
fill_type='solid'
)
status_cell.alignment = Alignment(horizontal='center')
# Progress
ws.cell(row=i, column=3, value=project['progress']).number_format = '0%'
# Budget
ws.cell(row=i, column=4, value=project['budget']).number_format = '$#,##0'
ws.cell(row=i, column=5, value=project['spent']).number_format = '$#,##0'
# Remaining (formula)
ws.cell(row=i, column=6, value=f'=D{i}-E{i}').number_format = '$#,##0'
# Due date
ws.cell(row=i, column=7, value=project['due_date'])
# Owner
ws.cell(row=i, column=8, value=project['owner'])
# Add data bars for progress
ws.conditional_formatting.add(
f'C2:C{len(projects)+1}',
DataBarRule(
start_type='num', start_value=0,
end_type='num', end_value=1,
color='4472C4'
)
)
# Highlight overbudget projects
over_budget_fill = PatternFill(start_color='FFC7CE', fill_type='solid')
ws.conditional_formatting.add(
f'F2:F{len(projects)+1}',
FormulaRule(formula=['F2<0'], fill=over_budget_fill)
)
# Freeze header
ws.freeze_panes = 'A2'
# Auto-filter
ws.auto_filter.ref = f'A1:H{len(projects)+1}'
wb.save(output_path)
return output_path
```
---
## Usage Pattern
```python
# Import templates
from dashboard_templates import (
create_kpi_dashboard,
create_sales_dashboard,
create_project_dashboard
)
# Generate KPI dashboard
kpi_data = fetch_kpi_data() # From your data source
create_kpi_dashboard(kpi_data, 'reports/kpi_dashboard.xlsx')
# Generate sales dashboard from transactions
sales_records = fetch_sales_data()
create_sales_dashboard(sales_records, 'reports/sales_dashboard.xlsx')
# Generate project status
projects = fetch_project_status()
create_project_dashboard(projects, 'reports/project_status.xlsx')
```
assets/financial-report.md# Financial Report Template
Copy-paste template for generating financial statements and reports in Excel.
---
## Income Statement
```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
def create_income_statement(data: dict, output_path: str = 'income_statement.xlsx'):
"""
Generate a professional income statement.
Args:
data: Dictionary with revenue, expenses, and metadata
output_path: Output file path
Example data:
{
'company': 'Acme Corp',
'period': 'Q4 2024',
'revenue': [
('Product Sales', 150000),
('Service Revenue', 50000),
('Other Income', 5000),
],
'cogs': [
('Cost of Goods Sold', 80000),
],
'operating_expenses': [
('Salaries & Wages', 45000),
('Rent', 12000),
('Utilities', 3000),
('Marketing', 8000),
('Depreciation', 5000),
],
'other_expenses': [
('Interest Expense', 2000),
],
'tax_rate': 0.25
}
"""
wb = Workbook()
ws = wb.active
ws.title = 'Income Statement'
# Styles
title_font = Font(bold=True, size=14)
header_font = Font(bold=True, size=11)
section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
currency_format = '$#,##0.00'
border = Border(bottom=Side(style='thin'))
double_border = Border(bottom=Side(style='double'))
row = 1
# Title
ws.merge_cells(f'A{row}:C{row}')
ws[f'A{row}'] = f"{data['company']} - Income Statement"
ws[f'A{row}'].font = title_font
row += 1
ws.merge_cells(f'A{row}:C{row}')
ws[f'A{row}'] = f"Period: {data['period']}"
row += 2
# Column widths
ws.column_dimensions['A'].width = 35
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
# Revenue Section
ws[f'A{row}'] = 'REVENUE'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_revenue = 0
for item, amount in data['revenue']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_revenue += amount
row += 1
ws[f'A{row}'] = 'Total Revenue'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_revenue
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = header_font
ws[f'B{row}'].border = border
row += 2
# COGS
ws[f'A{row}'] = 'COST OF GOODS SOLD'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_cogs = 0
for item, amount in data['cogs']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_cogs += amount
row += 1
ws[f'A{row}'] = 'Total COGS'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_cogs
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = header_font
ws[f'B{row}'].border = border
row += 2
# Gross Profit
gross_profit = total_revenue - total_cogs
ws[f'A{row}'] = 'GROSS PROFIT'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = gross_profit
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
row += 2
# Operating Expenses
ws[f'A{row}'] = 'OPERATING EXPENSES'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_opex = 0
for item, amount in data['operating_expenses']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_opex += amount
row += 1
ws[f'A{row}'] = 'Total Operating Expenses'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_opex
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = header_font
ws[f'B{row}'].border = border
row += 2
# Operating Income
operating_income = gross_profit - total_opex
ws[f'A{row}'] = 'OPERATING INCOME'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = operating_income
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
row += 2
# Other Expenses
total_other = sum(amount for _, amount in data.get('other_expenses', []))
if total_other > 0:
ws[f'A{row}'] = 'OTHER EXPENSES'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
for item, amount in data['other_expenses']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
row += 1
row += 1
# Income Before Tax
income_before_tax = operating_income - total_other
ws[f'A{row}'] = 'INCOME BEFORE TAX'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = income_before_tax
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = header_font
row += 1
# Tax
tax = income_before_tax * data.get('tax_rate', 0.25)
ws[f'A{row}'] = f" Income Tax ({data.get('tax_rate', 0.25):.0%})"
ws[f'B{row}'] = tax
ws[f'B{row}'].number_format = currency_format
row += 2
# Net Income
net_income = income_before_tax - tax
ws[f'A{row}'] = 'NET INCOME'
ws[f'A{row}'].font = Font(bold=True, size=14)
ws[f'B{row}'] = net_income
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=14)
ws[f'B{row}'].border = double_border
wb.save(output_path)
return output_path
# Example usage
if __name__ == '__main__':
sample_data = {
'company': 'Acme Corporation',
'period': 'Q4 2024',
'revenue': [
('Product Sales', 150000),
('Service Revenue', 50000),
('Other Income', 5000),
],
'cogs': [
('Cost of Goods Sold', 80000),
],
'operating_expenses': [
('Salaries & Wages', 45000),
('Rent', 12000),
('Utilities', 3000),
('Marketing', 8000),
('Depreciation', 5000),
],
'other_expenses': [
('Interest Expense', 2000),
],
'tax_rate': 0.25
}
create_income_statement(sample_data)
```
---
## Balance Sheet
```python
def create_balance_sheet(data: dict, output_path: str = 'balance_sheet.xlsx'):
"""
Generate a professional balance sheet.
Args:
data: Dictionary with assets, liabilities, and equity
output_path: Output file path
Example data:
{
'company': 'Acme Corp',
'as_of': 'December 31, 2024',
'current_assets': [
('Cash & Equivalents', 50000),
('Accounts Receivable', 35000),
('Inventory', 25000),
('Prepaid Expenses', 5000),
],
'non_current_assets': [
('Property & Equipment', 150000),
('Less: Accumulated Depreciation', -30000),
('Intangible Assets', 20000),
],
'current_liabilities': [
('Accounts Payable', 25000),
('Accrued Expenses', 10000),
('Short-term Debt', 15000),
],
'non_current_liabilities': [
('Long-term Debt', 80000),
],
'equity': [
('Common Stock', 50000),
('Retained Earnings', 75000),
]
}
"""
wb = Workbook()
ws = wb.active
ws.title = 'Balance Sheet'
# Styles
title_font = Font(bold=True, size=14)
header_font = Font(bold=True, size=11)
section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
currency_format = '$#,##0.00'
border = Border(bottom=Side(style='thin'))
double_border = Border(bottom=Side(style='double'))
row = 1
# Title
ws.merge_cells(f'A{row}:C{row}')
ws[f'A{row}'] = f"{data['company']} - Balance Sheet"
ws[f'A{row}'].font = title_font
row += 1
ws.merge_cells(f'A{row}:C{row}')
ws[f'A{row}'] = f"As of: {data['as_of']}"
row += 2
# Column widths
ws.column_dimensions['A'].width = 35
ws.column_dimensions['B'].width = 18
# ASSETS
ws[f'A{row}'] = 'ASSETS'
ws[f'A{row}'].font = Font(bold=True, size=12)
row += 1
# Current Assets
ws[f'A{row}'] = 'Current Assets'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_current_assets = 0
for item, amount in data['current_assets']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_current_assets += amount
row += 1
ws[f'A{row}'] = 'Total Current Assets'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_current_assets
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Non-Current Assets
ws[f'A{row}'] = 'Non-Current Assets'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_non_current_assets = 0
for item, amount in data['non_current_assets']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_non_current_assets += amount
row += 1
ws[f'A{row}'] = 'Total Non-Current Assets'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_non_current_assets
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Total Assets
total_assets = total_current_assets + total_non_current_assets
ws[f'A{row}'] = 'TOTAL ASSETS'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = total_assets
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'].border = double_border
row += 3
# LIABILITIES
ws[f'A{row}'] = 'LIABILITIES'
ws[f'A{row}'].font = Font(bold=True, size=12)
row += 1
# Current Liabilities
ws[f'A{row}'] = 'Current Liabilities'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_current_liab = 0
for item, amount in data['current_liabilities']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_current_liab += amount
row += 1
ws[f'A{row}'] = 'Total Current Liabilities'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_current_liab
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Non-Current Liabilities
ws[f'A{row}'] = 'Non-Current Liabilities'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_non_current_liab = 0
for item, amount in data['non_current_liabilities']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_non_current_liab += amount
row += 1
ws[f'A{row}'] = 'Total Non-Current Liabilities'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_non_current_liab
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
total_liabilities = total_current_liab + total_non_current_liab
ws[f'A{row}'] = 'TOTAL LIABILITIES'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = total_liabilities
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'].border = border
row += 3
# EQUITY
ws[f'A{row}'] = "SHAREHOLDERS' EQUITY"
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'A{row}'].fill = section_fill
row += 1
total_equity = 0
for item, amount in data['equity']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_equity += amount
row += 1
ws[f'A{row}'] = 'TOTAL EQUITY'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = total_equity
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'].border = border
row += 2
# Total Liabilities + Equity
ws[f'A{row}'] = 'TOTAL LIABILITIES + EQUITY'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = total_liabilities + total_equity
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'].border = double_border
wb.save(output_path)
return output_path
```
---
## Cash Flow Statement
```python
def create_cash_flow_statement(data: dict, output_path: str = 'cash_flow.xlsx'):
"""
Generate cash flow statement.
Args:
data: Dictionary with operating, investing, financing activities
Example data:
{
'company': 'Acme Corp',
'period': 'Year Ended December 31, 2024',
'beginning_cash': 30000,
'operating': [
('Net Income', 45000),
('Depreciation', 5000),
('Changes in Receivables', -5000),
('Changes in Payables', 3000),
],
'investing': [
('Purchase of Equipment', -25000),
('Sale of Investments', 10000),
],
'financing': [
('Proceeds from Debt', 20000),
('Dividends Paid', -10000),
]
}
"""
wb = Workbook()
ws = wb.active
ws.title = 'Cash Flow'
# Styles
title_font = Font(bold=True, size=14)
header_font = Font(bold=True, size=11)
section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
currency_format = '$#,##0.00'
border = Border(bottom=Side(style='thin'))
double_border = Border(bottom=Side(style='double'))
row = 1
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 18
# Title
ws[f'A{row}'] = f"{data['company']} - Statement of Cash Flows"
ws[f'A{row}'].font = title_font
row += 1
ws[f'A{row}'] = data['period']
row += 2
# Operating Activities
ws[f'A{row}'] = 'OPERATING ACTIVITIES'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_operating = 0
for item, amount in data['operating']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_operating += amount
row += 1
ws[f'A{row}'] = 'Net Cash from Operating Activities'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_operating
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Investing Activities
ws[f'A{row}'] = 'INVESTING ACTIVITIES'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_investing = 0
for item, amount in data['investing']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_investing += amount
row += 1
ws[f'A{row}'] = 'Net Cash from Investing Activities'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_investing
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Financing Activities
ws[f'A{row}'] = 'FINANCING ACTIVITIES'
ws[f'A{row}'].font = header_font
ws[f'A{row}'].fill = section_fill
row += 1
total_financing = 0
for item, amount in data['financing']:
ws[f'A{row}'] = f' {item}'
ws[f'B{row}'] = amount
ws[f'B{row}'].number_format = currency_format
total_financing += amount
row += 1
ws[f'A{row}'] = 'Net Cash from Financing Activities'
ws[f'A{row}'].font = header_font
ws[f'B{row}'] = total_financing
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].border = border
row += 2
# Summary
net_change = total_operating + total_investing + total_financing
ws[f'A{row}'] = 'NET CHANGE IN CASH'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = net_change
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
row += 2
ws[f'A{row}'] = 'Beginning Cash Balance'
ws[f'B{row}'] = data['beginning_cash']
ws[f'B{row}'].number_format = currency_format
row += 1
ws[f'A{row}'] = 'ENDING CASH BALANCE'
ws[f'A{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'] = data['beginning_cash'] + net_change
ws[f'B{row}'].number_format = currency_format
ws[f'B{row}'].font = Font(bold=True, size=12)
ws[f'B{row}'].border = double_border
wb.save(output_path)
return output_path
```
---
## Usage Pattern
```python
# Generate all three financial statements
from financial_templates import (
create_income_statement,
create_balance_sheet,
create_cash_flow_statement
)
# Load your data from database/API
company_data = load_financial_data('ACME', '2024-Q4')
# Generate reports
create_income_statement(company_data['income'], 'reports/income_2024q4.xlsx')
create_balance_sheet(company_data['balance'], 'reports/balance_2024q4.xlsx')
create_cash_flow_statement(company_data['cashflow'], 'reports/cashflow_2024q4.xlsx')
```
assets/spreadsheet-model-review-checklist.md# Spreadsheet Model Review Checklist (Core, Non-AI) Purpose: review a spreadsheet model for correctness, traceability, and decision usefulness. ## Inputs - Spreadsheet file + change log (who changed what, when) - Source data references (exports, databases, reports) - Business question the model supports (decision + timeline) ## Outputs - Review findings (issues, severity, owner, fix-by date) - “Ship/No-ship” decision for using the model in decisions ## Core ### A) Structure and Readability - [ ] Clear separation: Inputs / Calculations / Outputs (tabs or sections) - [ ] Consistent units and time granularity (daily/weekly/monthly) - [ ] Named ranges or clearly labeled tables (avoid magic cells) - [ ] No hidden rows/columns that change meaning (or documented if used) - [ ] `A1` or the visible top-left area explains the purpose of the sheet - [ ] No blank worksheets in the delivered workbook ### B) Inputs and Assumptions - [ ] Every assumption is explicit (value + unit + source + date) - [ ] Assumptions are grouped in one place (single “Inputs” area) - [ ] Scenario controls are obvious (base/best/worst) and not duplicated ### C) Formula Integrity - [ ] No hardcoded constants inside formulas where an input should exist - [ ] No inconsistent formulas across a range (spot-check rows/columns) - [ ] Avoid volatile functions unless justified (INDIRECT, OFFSET, TODAY, RAND) - [ ] Error handling is intentional (IFERROR used only with a documented fallback) ### D) Traceability and Auditability - [ ] Key outputs can be traced to inputs in ≤ 3 clicks - [ ] Source links/notes exist for imported data (file, query, timestamp) - [ ] Complex logic has a short explanation (“why”, not “what”) - [ ] External links are removed, documented, or explicitly approved ### E) Data Quality Checks - [ ] Totals reconcile to known sources (control totals) - [ ] Duplicate/blank/outlier checks exist for key fields - [ ] Date ranges and filters are explicit (no silent exclusions) ### F) Charts and Outputs - [ ] Each chart has: title, units, timeframe, and data source note - [ ] Avoid misleading scales (truncated axes, mixed units) - [ ] Executive summary tab answers the decision question in 60 seconds ### G) Versioning and Change Control - [ ] File naming includes date/version (e.g., `Model_2025-12-18_v3.xlsx`) - [ ] Change log tab exists for material edits (assumptions, formulas, structure) - [ ] Review/approval owner is named ### H) Accessibility (baseline) - [ ] Meaning is not color-only (labels/legends present) - [ ] Sufficient contrast for key charts/tables - [ ] Sheet/tab names are descriptive - [ ] Primary data blocks use one clear header row (not merged multi-row headers) - [ ] Hyperlinks use meaningful text instead of raw URLs where possible - [ ] Charts or images include title/context and alt text if they carry meaning ## Decision Rules - No-ship if: key outputs are not traceable, assumptions are implicit, or formulas are inconsistent. - Re-review after: changing inputs structure, adding new tabs, or altering core logic. ## Risks - Silent errors (range drift, broken links, copy/paste mistakes) - Untraceable logic leads to unreviewable decisions - Data leakage (customer PII embedded in shared files) - Accessibility regressions caused by blank tabs, merged headers, or color-only meaning ## Optional: AI / Automation Use only if allowed by policy and data handling rules. - Generate a “model audit summary” (tabs, key formulas, dependencies); human spot-checks. - Suggest tests (control totals, anomaly checks); do not auto-fix formulas without review.
data/sources.json{
"metadata": {
"skill": "document-xlsx",
"updated": "2026-07-11",
"total_sources": 17,
"description": "Official spreadsheet automation libraries plus workbook security, accessibility, and cloud automation guidance.",
"version": "2.3",
"title": "Document XLSX - Sources",
"last_updated": "2026-07-11"
},
"categories": {
"python_libraries": [
{
"name": "openpyxl Documentation",
"url": "https://openpyxl.readthedocs.io/",
"type": "documentation",
"relevance": "Python library for reading and editing .xlsx/.xlsm files, including tables, validation, protection, and workbook loading options. Current stable release is 3.1.5; see the Optimised Modes page for write_only/read_only streaming behavior on large files.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"last_verified": "2026-07-11",
"tags": [
"python",
"xlsx",
"editing"
]
},
{
"name": "XlsxWriter Documentation",
"url": "https://xlsxwriter.readthedocs.io/",
"type": "documentation",
"relevance": "Write-focused Python library for table-first exports, formatting, formulas, charts, and polished report outputs. Write-only: cannot open or edit an existing workbook.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"last_verified": "2026-07-11",
"tags": [
"python",
"xlsx",
"reports"
]
},
{
"name": "pandas.DataFrame.to_excel",
"url": "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html",
"type": "reference",
"relevance": "DataFrame export reference for reproducible Excel outputs, engine selection, freeze panes, and styling workflows.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"python",
"pandas",
"xlsx"
]
},
{
"name": "xlwings Documentation",
"url": "https://docs.xlwings.org/en/latest/",
"type": "documentation",
"relevance": "Python-to-Excel automation when you need native Excel features on a machine with Excel installed.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"python",
"excel-automation",
"desktop"
]
},
{
"name": "calamine — GitHub",
"url": "https://github.com/tafia/calamine",
"type": "library",
"relevance": "Fast pure-Rust reader for xls, xlsx, xlsb, and ods; zero ML/GPU dependency. Use directly via Python bindings (python-calamine) for high-performance spreadsheet ingestion in LLM and data pipelines.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"last_verified": "2026-05-17",
"tags": [
"rust",
"xlsx",
"xls",
"ods",
"extraction",
"performance"
]
},
{
"name": "Docling (IBM Research) — GitHub",
"url": "https://github.com/docling-project/docling",
"type": "library",
"relevance": "Production document-extraction library (IBM Research) producing structured DoclingDocument from XLSX, PDF, DOCX, PPTX, HTML, and images. Primary source for releases, issue tracking, and integration examples.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"last_verified": "2026-05-17",
"tags": [
"docling",
"xlsx",
"extraction",
"llm",
"ibm"
]
},
{
"name": "Mistral OCR",
"url": "https://mistral.ai/news/mistral-ocr",
"type": "api",
"relevance": "OCR API understanding text, tables, equations, and embedded media across document formats; ~1000 pages per dollar. Relevant when Excel data is embedded in scanned or image-heavy documents. Docs at https://docs.mistral.ai/",
"update_frequency": "continuous",
"access": "paid",
"add_as_web_search": true,
"last_verified": "2026-05-17",
"tags": [
"ocr",
"mistral",
"extraction",
"tables",
"api"
]
}
],
"nodejs_libraries": [
{
"name": "ExcelJS",
"url": "https://github.com/exceljs/exceljs",
"type": "library",
"relevance": "Node.js library for generating and editing .xlsx files with styles, formulas, tables, and workbook structure operations. Native chart generation is not supported; pivot-table support is experimental as of the 4.4.x/4.5.x line.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"last_verified": "2026-07-11",
"tags": [
"nodejs",
"xlsx",
"typescript"
]
},
{
"name": "SheetJS Documentation",
"url": "https://docs.sheetjs.com/",
"type": "documentation",
"relevance": "Spreadsheet ingestion and export across formats; useful for parsing, interoperability, and metadata-aware workbook handling.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"nodejs",
"xlsx",
"ingestion"
]
},
{
"name": "SheetJS Security Notes",
"url": "https://docs.sheetjs.com/docs/miscellany/security/",
"type": "guide",
"relevance": "Security guidance for formula injection, hyperlinks, and workbook parsing risks in spreadsheet workflows.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"security",
"sheetjs",
"xlsx"
]
}
],
"cloud_automation": [
{
"name": "Office Scripts PivotTables",
"url": "https://learn.microsoft.com/en-us/office/dev/scripts/develop/pivottables",
"type": "documentation",
"relevance": "Official Excel on the web automation reference for creating and manipulating native PivotTables with Office Scripts.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"office-scripts",
"pivot-tables",
"excel-web"
]
},
{
"name": "Office Scripts Table Interface",
"url": "https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.table?view=office-scripts",
"type": "reference",
"relevance": "Reference for table operations in Office Scripts, including headers, totals, filters, and structured workbook automation.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"office-scripts",
"tables",
"excel-web"
]
},
{
"name": "Microsoft Graph Excel Workbook API",
"url": "https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0",
"type": "documentation",
"relevance": "Remote workbook sessions and workbook object model for Microsoft 365-hosted Excel files.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"graph",
"excel",
"api"
]
}
],
"accessibility_and_compliance": [
{
"name": "Accessibility Best Practices with Excel Spreadsheets",
"url": "https://support.microsoft.com/en-us/office/accessibility-best-practices-with-excel-spreadsheets-6cc05fc5-1314-48b5-8eb3-683e49b3e593",
"type": "guide",
"relevance": "Practical Microsoft guidance for descriptive worksheet names, accessible tables, meaningful links, and workbook checkers.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"accessibility",
"excel",
"microsoft"
]
},
{
"name": "Section 508 Accessible Spreadsheets",
"url": "https://www.section508.gov/create/spreadsheets/",
"type": "guide",
"relevance": "US federal accessibility guidance for spreadsheets, including workbook structure, A1 context, and blank worksheet avoidance.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"accessibility",
"section508",
"spreadsheets"
]
},
{
"name": "ECMA-376 Office Open XML",
"url": "https://ecma-international.org/publications-and-standards/standards/ecma-376/",
"type": "specification",
"relevance": "Underlying OOXML standard behind .xlsx; useful for interoperability edge cases and low-level workbook inspection.",
"update_frequency": "static",
"access": "free",
"add_as_web_search": false,
"tags": [
"spec",
"ooxml",
"xlsx"
]
},
{
"name": "Microsoft Open Specifications for OOXML",
"url": "https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oi29500/1fd4a662-8623-49c0-82f0-18fa91b413b8",
"type": "reference",
"relevance": "Stable entry point for Microsoft's published Office Open XML implementation notes and format details.",
"update_frequency": "continuous",
"access": "free",
"add_as_web_search": true,
"tags": [
"spec",
"office",
"ooxml"
]
}
]
}
}
learnings.consolidated.md# document-xlsx — Consolidated Learnings Curated, dated, committed memory for this skill. Pruned from raw `learnings.md` via `agents-skills-feedback-loop/scripts/consolidate.py`. Human-approved. Cap: 60 entries. When exceeded, promote durable rules to `references/`. ## Filter Override <!-- Add 2-4 bullets that sharpen what counts as a learning for this skill. Leave empty to use the default filter from agents-skills-feedback-loop/references/learnings-format.md. --> ## Patterns That Work ## Mistakes to Avoid ## Domain Knowledge ## Open Questions ## Consolidated Principles
learnings.md# document-xlsx — Learnings ## Patterns That Work ## Mistakes to Avoid ## Domain Knowledge - [2026-07-11] July 2026 audit: XlsxWriter is write-only (can't edit files); openpyxl data_only=True returns None until Excel recalculates; use write_only/read_only + lxml for large exports. ## Open Questions ## Consolidated Principles
references/excel-accessibility-compliance.md# Excel Accessibility and Compliance Use this reference when the workbook will be shared outside the authoring team or when accessibility requirements matter for procurement, regulated delivery, or public-sector use. --- ## Baseline Rules | Rule | Why it matters | |------|----------------| | Give every worksheet a descriptive name | Screen-reader and keyboard users rely on tab names | | Avoid blank worksheets | Blank tabs create noise and confusion | | Put workbook context in `A1` | Readers should know what the sheet is for immediately | | Use accessible tables with one header row | Tables are easier to navigate than arbitrary ranges | | Avoid merged or split header cells | They break navigation and header association | | Use meaningful hyperlink text | "View source export" is better than a raw URL | | Do not rely on color alone | Status and meaning need labels or icons too | | Add alt text where charts or images convey meaning | Non-text content needs a text equivalent | --- ## Authoring Checklist - `A1` explains the purpose of the sheet before the main table or content block - Workbook contains no blank worksheets - Sheet names are unique and descriptive - Primary data blocks are Excel Tables or clearly labeled bounded ranges - Header row is the first row of the table, not merged across multiple rows - Important formulas, assumptions, or controls are labeled in plain language - Hyperlinks describe the destination or purpose - Charts include a title, units, timeframe, and nearby explanatory text or alt text - Accessibility Checker is run before delivery --- ## Distribution Notes - If a workbook is customer-facing or part of an accessibility-sensitive process, expect requirements that align with Microsoft accessibility guidance and, in many enterprise or public-sector contexts, Section 508 or EN 301 549 expectations. - Accessibility review should happen before PDF export as well as before `.xlsx` distribution. - If the workbook is primarily a decision artifact, add an Instructions or Summary sheet that explains purpose, owner, version, and key assumptions. --- ## Do / Avoid **Do:** - Keep a simple reading order from top-left to bottom-right - Use tables, labels, and notes instead of layout tricks - Include units and timeframe on charts and summary cells - Run Excel's built-in Accessibility Checker before sharing **Avoid:** - Blank tabs - Merged multi-row headers - Hidden meaning behind colors only - Hyperlinks that expose raw tracking URLs when a readable label would work
references/excel-charts.md# Excel Charts Reference
Chart creation and customization for data visualization in spreadsheets.
---
## Table of Contents
- [Chart Types Overview](#chart-types-overview)
- [Bar/Column Charts](#barcolumn-charts)
- [Basic Bar Chart (Python)](#basic-bar-chart-python)
- [Sample data](#sample-data)
- [Create chart](#create-chart)
- [Data references](#data-references)
- [Position chart](#position-chart)
- [Stacked Bar Chart](#stacked-bar-chart)
- [Multiple data series](#multiple-data-series)
- [Clustered Bar with Custom Colors](#clustered-bar-with-custom-colors)
- [Custom series colors](#custom-series-colors)
- [Line Charts](#line-charts)
- [Basic Line Chart](#basic-line-chart)
- [Multi-Series Line Chart](#multi-series-line-chart)
- [Add multiple data columns](#add-multiple-data-columns)
- [Customize line styles](#customize-line-styles)
- [Line with Markers](#line-with-markers)
- [Pie Charts](#pie-charts)
- [Basic Pie Chart](#basic-pie-chart)
- [Pie Chart with Data Labels](#pie-chart-with-data-labels)
- [Data labels](#data-labels)
- [Exploded Pie](#exploded-pie)
- [Explode first slice](#explode-first-slice)
- [Doughnut Chart](#doughnut-chart)
- [Area Charts](#area-charts)
- [Scatter Charts](#scatter-charts)
- [Basic Scatter Plot](#basic-scatter-plot)
- [Scatter with Trendline](#scatter-with-trendline)
- [... add data ...](#add-data)
- [Add linear trendline](#add-linear-trendline)
- [Other types: 'exp', 'log', 'poly', 'power', 'movingAvg'](#other-types-exp-log-poly-power-movingavg)
- [Combo Charts](#combo-charts)
- [Column + Line Combination](#column-line-combination)
- [Primary chart (columns)](#primary-chart-columns)
- [Secondary chart (line)](#secondary-chart-line)
- [Use secondary Y axis](#use-secondary-y-axis)
- [Combine charts](#combine-charts)
- [Chart Customization](#chart-customization)
- [Size and Position](#size-and-position)
- [Anchor position](#anchor-position)
- [Alternative: absolute positioning](#alternative-absolute-positioning)
- [Legend Position](#legend-position)
- [Hide legend](#hide-legend)
- [Axis Formatting](#axis-formatting)
- [Number format](#number-format)
- [Axis bounds](#axis-bounds)
- [Axis title](#axis-title)
- [Hide axis](#hide-axis)
- [Gridlines](#gridlines)
- [Major gridlines](#major-gridlines)
- [Hide gridlines](#hide-gridlines)
- [Title Formatting](#title-formatting)
- [Styled title](#styled-title)
- [Chart Templates](#chart-templates)
- [Dashboard KPI Chart](#dashboard-kpi-chart)
- [Trend Analysis Chart](#trend-analysis-chart)
- [Comparison Pie Chart](#comparison-pie-chart)
- [ExcelJS Charts (Node.js)](#exceljs-charts-nodejs)
- [Chart Styles Reference](#chart-styles-reference)
## Chart Types Overview
| Chart Type | Best For | openpyxl Class |
|------------|----------|----------------|
| Bar/Column | Comparisons | `BarChart` |
| Line | Trends over time | `LineChart` |
| Pie | Part of whole | `PieChart` |
| Area | Cumulative trends | `AreaChart` |
| Scatter | Correlations | `ScatterChart` |
| Doughnut | Part of whole (variant) | `DoughnutChart` |
| Radar | Multi-variable comparison | `RadarChart` |
| Bubble | 3-variable relationships | `BubbleChart` |
| Stock | OHLC financial data | `StockChart` |
---
## Bar/Column Charts
### Basic Bar Chart (Python)
```python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
# Sample data
data = [
['Product', 'Sales'],
['Widget A', 1200],
['Widget B', 800],
['Widget C', 1500],
['Widget D', 950],
]
for row in data:
ws.append(row)
# Create chart
chart = BarChart()
chart.type = 'col' # 'col' for vertical, 'bar' for horizontal
chart.style = 10
chart.title = 'Product Sales'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales ($)'
# Data references
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4 # Rounded corners
# Position chart
ws.add_chart(chart, 'D2')
wb.save('bar_chart.xlsx')
```
### Stacked Bar Chart
```python
chart = BarChart()
chart.type = 'col'
chart.grouping = 'stacked' # 'standard', 'stacked', 'percentStacked'
# Multiple data series
data_ref = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=4)
chart.add_data(data_ref, titles_from_data=True)
```
### Clustered Bar with Custom Colors
```python
from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice
chart = BarChart()
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
# Custom series colors
colors = ['4472C4', 'ED7D31', '70AD47']
for i, series in enumerate(chart.series):
series.graphicalProperties.solidFill = colors[i % len(colors)]
```
---
## Line Charts
### Basic Line Chart
```python
from openpyxl.chart import LineChart, Reference
chart = LineChart()
chart.style = 10
chart.title = 'Monthly Trend'
chart.x_axis.title = 'Month'
chart.y_axis.title = 'Value'
data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=2)
categories = Reference(ws, min_col=1, min_row=2, max_row=13)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'D2')
```
### Multi-Series Line Chart
```python
chart = LineChart()
chart.title = 'Year over Year Comparison'
# Add multiple data columns
data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
# Customize line styles
for i, series in enumerate(chart.series):
series.graphicalProperties.line.width = 25000 # EMUs (25000 = ~2pt)
series.smooth = True # Smooth lines
```
### Line with Markers
```python
from openpyxl.chart.marker import Marker
chart = LineChart()
chart.add_data(data_ref, titles_from_data=True)
for series in chart.series:
series.marker = Marker(symbol='circle', size=7)
series.graphicalProperties.line.width = 20000
```
---
## Pie Charts
### Basic Pie Chart
```python
from openpyxl.chart import PieChart, Reference
chart = PieChart()
chart.title = 'Market Share'
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, 'D2')
```
### Pie Chart with Data Labels
```python
from openpyxl.chart.label import DataLabelList
chart = PieChart()
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)
# Data labels
chart.dataLabels = DataLabelList()
chart.dataLabels.showCatName = True
chart.dataLabels.showPercent = True
chart.dataLabels.showVal = False
```
### Exploded Pie
```python
from openpyxl.chart.series import DataPoint
chart = PieChart()
chart.add_data(data_ref, titles_from_data=True)
# Explode first slice
slice = DataPoint(idx=0, explosion=10) # 10% explosion
chart.series[0].data_points = [slice]
```
### Doughnut Chart
```python
from openpyxl.chart import DoughnutChart
chart = DoughnutChart()
chart.title = 'Budget Allocation'
chart.holeSize = 50 # Inner hole size (percentage)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)
```
---
## Area Charts
```python
from openpyxl.chart import AreaChart
chart = AreaChart()
chart.style = 10
chart.title = 'Cumulative Growth'
chart.grouping = 'stacked' # 'standard', 'stacked', 'percentStacked'
data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'D2')
```
---
## Scatter Charts
### Basic Scatter Plot
```python
from openpyxl.chart import ScatterChart, Reference, Series
chart = ScatterChart()
chart.style = 13
chart.title = 'Correlation Analysis'
chart.x_axis.title = 'Variable X'
chart.y_axis.title = 'Variable Y'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=50)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=50)
series = Series(yvalues, xvalues, title='Data Points')
chart.series.append(series)
ws.add_chart(chart, 'D2')
```
### Scatter with Trendline
```python
from openpyxl.chart.trendline import Trendline
chart = ScatterChart()
# ... add data ...
# Add linear trendline
trendline = Trendline(trendlineType='linear')
chart.series[0].trendline = trendline
# Other types: 'exp', 'log', 'poly', 'power', 'movingAvg'
```
---
## Combo Charts
### Column + Line Combination
```python
from openpyxl.chart import BarChart, LineChart, Reference
# Primary chart (columns)
bar_chart = BarChart()
bar_chart.type = 'col'
bar_data = Reference(ws, min_col=2, min_row=1, max_row=13)
bar_chart.add_data(bar_data, titles_from_data=True)
bar_chart.set_categories(categories)
# Secondary chart (line)
line_chart = LineChart()
line_data = Reference(ws, min_col=3, min_row=1, max_row=13)
line_chart.add_data(line_data, titles_from_data=True)
# Use secondary Y axis
line_chart.y_axis.axId = 200
line_chart.y_axis.crosses = 'max'
# Combine charts
bar_chart += line_chart
ws.add_chart(bar_chart, 'D2')
```
---
## Chart Customization
### Size and Position
```python
chart.width = 15 # Width in cm
chart.height = 10 # Height in cm
# Anchor position
ws.add_chart(chart, 'D2') # Top-left cell
# Alternative: absolute positioning
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker
chart.anchor = 'D2' # Or use TwoCellAnchor for resizing with cells
```
### Legend Position
```python
from openpyxl.chart.legend import Legend
chart.legend = Legend()
chart.legend.position = 'b' # 'b'=bottom, 't'=top, 'l'=left, 'r'=right, 'tr'=top-right
chart.legend.overlay = False
# Hide legend
chart.legend = None
```
### Axis Formatting
```python
# Number format
chart.y_axis.numFmt = '$#,##0'
# Axis bounds
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 10000
# Axis title
chart.x_axis.title = 'Quarter'
chart.y_axis.title = 'Revenue ($)'
# Hide axis
chart.x_axis.delete = True
```
### Gridlines
```python
from openpyxl.chart.axis import ChartLines
# Major gridlines
chart.y_axis.majorGridlines = ChartLines()
# Hide gridlines
chart.y_axis.majorGridlines = None
chart.y_axis.minorGridlines = None
```
### Title Formatting
```python
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
chart.title = 'Sales Report'
# Styled title
props = CharacterProperties(b=True, sz=1400) # Bold, 14pt
para = Paragraph(pPr=ParagraphProperties(defRPr=props), r=[])
chart.title.tx.rich.p = [para]
```
---
## Chart Templates
### Dashboard KPI Chart
```python
def create_kpi_chart(ws, data_range, title, position):
"""Create a clean KPI column chart."""
chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.title = title
data = Reference(ws, **data_range)
chart.add_data(data, titles_from_data=True)
# Clean styling
chart.legend = None
chart.y_axis.majorGridlines = ChartLines()
chart.y_axis.numFmt = '#,##0'
# Color scheme
chart.series[0].graphicalProperties.solidFill = '4472C4'
chart.width = 10
chart.height = 6
ws.add_chart(chart, position)
return chart
```
### Trend Analysis Chart
```python
def create_trend_chart(ws, data_range, categories_range, title, position):
"""Create line chart with trendline."""
chart = LineChart()
chart.style = 10
chart.title = title
data = Reference(ws, **data_range)
cats = Reference(ws, **categories_range)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
# Smooth lines with markers
for series in chart.series:
series.smooth = True
series.marker = Marker(symbol='circle', size=5)
# Add trendline
chart.series[0].trendline = Trendline(trendlineType='linear')
chart.width = 12
chart.height = 7
ws.add_chart(chart, position)
return chart
```
### Comparison Pie Chart
```python
def create_comparison_pie(ws, data_range, labels_range, title, position):
"""Create pie chart with percentage labels."""
chart = PieChart()
chart.title = title
data = Reference(ws, **data_range)
labels = Reference(ws, **labels_range)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
# Show percentages
chart.dataLabels = DataLabelList()
chart.dataLabels.showPercent = True
chart.dataLabels.showCatName = True
chart.dataLabels.showVal = False
chart.width = 10
chart.height = 8
ws.add_chart(chart, position)
return chart
```
---
## ExcelJS Charts (Node.js)
ExcelJS has limited native chart support. For complex charts, consider:
1. **Template approach**: Create chart in Excel, use as template
2. **Hybrid**: Generate data with ExcelJS, open in Excel for charts
3. **Alternative**: Use xlsx-chart or chart.js for image export
```typescript
// ExcelJS basic image embedding (for pre-rendered charts)
import ExcelJS from 'exceljs';
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Report');
// Add image (chart exported as PNG)
const imageId = workbook.addImage({
filename: 'chart.png',
extension: 'png',
});
sheet.addImage(imageId, {
tl: { col: 4, row: 1 },
ext: { width: 500, height: 300 }
});
```
---
## Chart Styles Reference
| Style # | Description |
|---------|-------------|
| 1 | Default colors |
| 2 | Outline style |
| 3 | Gradient fills |
| 10 | Clean, professional |
| 11 | Subtle colors |
| 12 | Bold colors |
| 13 | Two-tone |
Use `chart.style = N` to apply built-in Excel chart styles.
references/excel-cloud-automation.md# Excel Cloud Automation
Use cloud automation when the workbook lives in Microsoft 365 and you need native Excel features without shipping a local desktop file back and forth.
---
## Tool Selection
| Tool | Best For | Constraints |
|------|----------|-------------|
| Office Scripts | Excel on the web automation, tables, pivots, worksheet operations | Runs in Microsoft 365 contexts; TypeScript API |
| Microsoft Graph Excel | Remote workbook sessions, ranges, tables, charts, named items | Requires Graph auth and workbook location in OneDrive/SharePoint |
| xlwings | Desktop Excel automation with native feature access | Requires Excel installed on the machine |
---
## Office Scripts
Prefer Office Scripts when:
- The workbook is already in OneDrive or SharePoint
- The user needs native PivotTables or worksheet automation
- The workflow is initiated from Excel on the web, Power Automate, or Microsoft 365
### Create a Table
```typescript
function main(workbook: ExcelScript.Workbook) {
const ws = workbook.getWorksheet("Raw Data");
const table = ws.addTable(ws.getUsedRange(), true);
table.setName("SalesTable");
table.setShowTotals(true);
}
```
### Create a PivotTable
```typescript
function main(workbook: ExcelScript.Workbook) {
const sourceTable = workbook.getTable("SalesTable");
const pivotSheet = workbook.addWorksheet("Pivot");
const pivot = workbook.addPivotTable("SalesPivot", sourceTable, pivotSheet.getRange("A1"));
pivot.addRowHierarchy(pivot.getHierarchy("Region"));
pivot.addColumnHierarchy(pivot.getHierarchy("Product"));
pivot.addDataHierarchy(pivot.getHierarchy("Revenue"));
}
```
---
## Microsoft Graph Excel
Prefer Graph when:
- The workbook must be modified remotely from a service or backend job
- You need workbook sessions and object-model access over REST
- The workbook is stored in Microsoft 365 and human interaction is not required
Typical workflow:
1. Create or reuse a workbook session
2. Resolve the workbook item in OneDrive or SharePoint
3. Update ranges, tables, or named items
4. Close the session or persist changes
Graph is strong for remote workbook orchestration, but it is not a replacement for every desktop Excel feature.
---
## xlwings
Prefer xlwings when:
- You need native Excel behavior on a user or automation machine
- The workflow depends on Excel-specific rendering or a desktop add-in
- Office Scripts or Graph are not an option
Avoid xlwings for headless Linux CI or server environments without Excel.
---
## Decision Rules
- Workbook already lives in Microsoft 365:
prefer Office Scripts first, Graph second.
- Native pivots required without desktop Excel:
prefer Office Scripts.
- Service/backend job mutating hosted workbooks:
prefer Graph sessions.
- Desktop Excel is available and the workflow is local:
prefer xlwings.
- Pure export with no live workbook dependency:
stay local with `XlsxWriter`, `openpyxl`, or `ExcelJS`.
references/excel-data-validation.md# Excel Data Validation Reference
## Table of Contents
- [Contents](#contents)
- [Validation Types](#validation-types)
- [openpyxl Examples](#openpyxl-examples)
- [ExcelJS Example](#exceljs-example)
- [Named Ranges for Validation Lists](#named-ranges-for-validation-lists)
- [Cascading Dropdowns](#cascading-dropdowns)
- [Error Messages and Input Messages](#error-messages-and-input-messages)
- [Combining Validation with Sheet Protection](#combining-validation-with-sheet-protection)
- [Do / Avoid](#do--avoid)
- [Common Pitfalls](#common-pitfalls)
Patterns for enforcing input constraints in generated spreadsheets.
---
## Contents
- Validation types overview
- openpyxl and ExcelJS code examples
- Named ranges for maintainable lists
- Cascading (dependent) dropdowns
- Error/input messages and protection
- Do / Avoid and common pitfalls
---
## Validation Types
| Type | Use Case | openpyxl `type` value |
|------|----------|-----------------------|
| List (dropdown) | Constrain to predefined options | `list` |
| Whole number | Integer within range | `whole` |
| Decimal | Float within range | `decimal` |
| Date | Date within range | `date` |
| Text length | Min/max character count | `textLength` |
| Custom formula | Any boolean expression | `custom` |
---
## openpyxl Examples
```python
from openpyxl.worksheet.datavalidation import DataValidation
# Dropdown list
dv = DataValidation(type="list", formula1='"Open,In Progress,Closed"', allow_blank=True)
dv.error = "Pick a valid status."
dv.prompt = "Select a status from the list."
ws.add_data_validation(dv)
dv.add("B2:B500")
# Whole number range
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "Enter a number between 1 and 100."
ws.add_data_validation(dv_num)
dv_num.add("C2:C500")
# Custom formula (unique values only)
dv_uniq = DataValidation(type="custom", formula1="=COUNTIF($D:$D,D2)<=1")
dv_uniq.error = "Duplicate value."
ws.add_data_validation(dv_uniq)
dv_uniq.add("D2:D500")
```
## ExcelJS Example
```typescript
worksheet.getCell('B2').dataValidation = {
type: 'list',
allowBlank: true,
formulae: ['"Open,In Progress,Closed"'],
showErrorMessage: true,
error: 'Pick a valid status.',
showInputMessage: true,
prompt: 'Select a status from the list.'
};
```
ExcelJS requires setting `dataValidation` per-cell or looping over a range; there is no range-based add method.
---
## Named Ranges for Validation Lists
Hardcoded comma-separated strings break when lists exceed ~255 characters. Use named ranges instead.
```python
from openpyxl.workbook.defined_name import DefinedName
ws_lists = wb.create_sheet("Lists")
statuses = ["Open", "In Progress", "Closed", "Blocked"]
for i, val in enumerate(statuses, start=1):
ws_lists.cell(row=i, column=1, value=val)
ws_lists.sheet_state = "hidden"
ref = f"Lists!$A$1:$A${len(statuses)}"
defn = DefinedName("StatusList", attr_text=ref)
wb.defined_names.add(defn)
dv = DataValidation(type="list", formula1="StatusList")
ws.add_data_validation(dv)
dv.add("B2:B500")
```
---
## Cascading Dropdowns
Dependent validation (Country -> City) uses INDIRECT with named ranges per parent value.
```python
# Named ranges: "USA" -> Lists!$B$1:$B$3, "UK" -> Lists!$C$1:$C$2
dv_country = DataValidation(type="list", formula1='"USA,UK"')
ws.add_data_validation(dv_country)
dv_country.add("A2:A100")
dv_city = DataValidation(type="list", formula1="=INDIRECT(A2)")
ws.add_data_validation(dv_city)
dv_city.add("B2:B100")
```
Limitation: INDIRECT is volatile and only resolves in Excel. LibreOffice and Google Sheets have inconsistent support.
---
## Error Messages and Input Messages
| Property | Purpose |
|----------|---------|
| `prompt` / `promptTitle` | Tooltip when cell is selected |
| `error` / `errorTitle` | Dialog shown on invalid entry |
| `errorStyle` | `stop` (reject), `warning` (allow override), `information` (info only) |
Set `errorStyle` to `warning` when soft guidance is acceptable.
## Combining Validation with Sheet Protection
Validation alone does not prevent paste-over. Combine with protection:
```python
from openpyxl.styles import Protection
for row in ws.iter_rows(min_row=2, max_row=500, min_col=2, max_col=2):
for cell in row:
cell.protection = Protection(locked=False) # unlock input cells
ws.protection.sheet = True
ws.protection.password = "edit123"
```
---
## Do / Avoid
**Do:**
- Use named ranges on a hidden sheet for lists longer than 5 items
- Set both `prompt` and `error` messages for every validation rule
- Combine validation with sheet protection on template workbooks
- Test generated files in Excel, LibreOffice, and Google Sheets
**Avoid:**
- Comma-separated strings over ~200 characters (Excel truncates at 255)
- More than 65,534 validation objects per sheet (Excel hard limit)
- INDIRECT-based cascading dropdowns if the file will be consumed outside Excel
- Validating entire columns (`A:A`) -- use bounded ranges (`A2:A5000`)
---
## Common Pitfalls
| Pitfall | Detail |
|---------|--------|
| Hidden rows break list source | Filtered/hidden source rows cause blank dropdown entries |
| Copy-paste bypasses validation | Users can paste invalid data; sheet protection mitigates |
| Formula1 quoting | openpyxl lists need inner double quotes: `formula1='"A,B,C"'` |
| Validation invisible to pandas | `read_excel` ignores validation; it is UI metadata only |
| Max 255 chars in formula1 | Use a named range referencing cells instead of inline strings |
references/excel-formatting.md# Excel Formatting Reference
Styling, conditional formatting, and visual presentation for spreadsheets.
---
## Table of Contents
- [Cell Styling](#cell-styling)
- [Font Properties](#font-properties)
- [openpyxl](#openpyxl)
- [Fill (Background Color)](#fill-background-color)
- [openpyxl](#openpyxl)
- [Solid fill](#solid-fill)
- [Gradient fill](#gradient-fill)
- [Borders](#borders)
- [openpyxl](#openpyxl)
- [Border styles: 'thin', 'medium', 'thick', 'double', 'dotted', 'dashed'](#border-styles-thin-medium-thick-double-dotted-dashed)
- [Alignment](#alignment)
- [openpyxl](#openpyxl)
- [Number Formatting](#number-formatting)
- [Common Formats](#common-formats)
- [Implementation](#implementation)
- [openpyxl](#openpyxl)
- [Custom format with color](#custom-format-with-color)
- [Conditional Formatting](#conditional-formatting)
- [Color Scales (Heatmaps)](#color-scales-heatmaps)
- [2-color scale (red to green)](#2-color-scale-red-to-green)
- [3-color scale](#3-color-scale)
- [Data Bars](#data-bars)
- [Icon Sets](#icon-sets)
- [Traffic lights](#traffic-lights)
- [Icon styles: '3Arrows', '3TrafficLights1', '4Rating', '5Quarters'](#icon-styles-3arrows-3trafficlights1-4rating-5quarters)
- [Formula-Based Rules](#formula-based-rules)
- [Highlight rows where status = "Overdue"](#highlight-rows-where-status-=-overdue)
- [Highlight duplicates](#highlight-duplicates)
- [Cell Value Rules](#cell-value-rules)
- [Greater than](#greater-than)
- [Between](#between)
- [Operators: 'lessThan', 'lessThanOrEqual', 'greaterThan',](#operators-lessthan-lessthanorequal-greaterthan)
- ['greaterThanOrEqual', 'equal', 'notEqual', 'between'](#greaterthanorequal-equal-notequal-between)
- [Row and Column Formatting](#row-and-column-formatting)
- [Column Width](#column-width)
- [openpyxl](#openpyxl)
- [Auto-fit (approximate)](#auto-fit-approximate)
- [Row Height](#row-height)
- [openpyxl](#openpyxl)
- [All rows](#all-rows)
- [Freeze Panes](#freeze-panes)
- [openpyxl - Freeze first row](#openpyxl-freeze-first-row)
- [Freeze first column](#freeze-first-column)
- [Freeze both](#freeze-both)
- [Hide Rows/Columns](#hide-rowscolumns)
- [openpyxl](#openpyxl)
- [Merged Cells](#merged-cells)
- [openpyxl](#openpyxl)
- [Unmerge](#unmerge)
- [Named Styles](#named-styles)
- [Create reusable style](#create-reusable-style)
- [Register style](#register-style)
- [Apply to cells](#apply-to-cells)
- [Page Setup (Print)](#page-setup-print)
- [openpyxl](#openpyxl)
- [Print titles (repeat rows/columns)](#print-titles-repeat-rowscolumns)
- [Print area](#print-area)
- [Headers/footers](#headersfooters)
- [Style Presets](#style-presets)
- [Header Style](#header-style)
- [Alternating Row Colors](#alternating-row-colors)
- [Currency Column](#currency-column)
- [Color Reference](#color-reference)
- [Excel Theme Colors](#excel-theme-colors)
- [Status Colors](#status-colors)
## Cell Styling
### Font Properties
```python
# openpyxl
from openpyxl.styles import Font
cell.font = Font(
name='Calibri',
size=11,
bold=True,
italic=False,
underline='single', # 'single', 'double', 'singleAccounting', 'doubleAccounting'
strike=False,
color='FF0000' # ARGB hex (no #)
)
```
```typescript
// ExcelJS
cell.font = {
name: 'Calibri',
size: 11,
bold: true,
italic: false,
underline: true,
strike: false,
color: { argb: 'FFFF0000' }
};
```
### Fill (Background Color)
```python
# openpyxl
from openpyxl.styles import PatternFill
# Solid fill
cell.fill = PatternFill(
start_color='4472C4',
end_color='4472C4',
fill_type='solid'
)
# Gradient fill
from openpyxl.styles import GradientFill
cell.fill = GradientFill(
type='linear',
degree=90,
stop=['4472C4', 'FFFFFF']
)
```
```typescript
// ExcelJS
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FF4472C4' }
};
// Gradient
cell.fill = {
type: 'gradient',
gradient: 'angle',
degree: 90,
stops: [
{ position: 0, color: { argb: 'FF4472C4' } },
{ position: 1, color: { argb: 'FFFFFFFF' } }
]
};
```
### Borders
```python
# openpyxl
from openpyxl.styles import Border, Side
thin_border = Border(
left=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000'),
top=Side(style='thin', color='000000'),
bottom=Side(style='thin', color='000000')
)
cell.border = thin_border
# Border styles: 'thin', 'medium', 'thick', 'double', 'dotted', 'dashed'
```
```typescript
// ExcelJS
cell.border = {
top: { style: 'thin', color: { argb: 'FF000000' } },
left: { style: 'thin', color: { argb: 'FF000000' } },
bottom: { style: 'thin', color: { argb: 'FF000000' } },
right: { style: 'thin', color: { argb: 'FF000000' } }
};
```
### Alignment
```python
# openpyxl
from openpyxl.styles import Alignment
cell.alignment = Alignment(
horizontal='center', # 'left', 'center', 'right', 'justify'
vertical='center', # 'top', 'center', 'bottom'
wrap_text=True,
shrink_to_fit=False,
indent=0,
text_rotation=0 # -90 to 90 degrees
)
```
```typescript
// ExcelJS
cell.alignment = {
horizontal: 'center',
vertical: 'middle',
wrapText: true,
shrinkToFit: false,
indent: 0,
textRotation: 0
};
```
---
## Number Formatting
### Common Formats
| Format Code | Example Output | Use Case |
|-------------|----------------|----------|
| `General` | 1234.5 | Default |
| `0` | 1235 | Integer |
| `0.00` | 1234.50 | 2 decimals |
| `#,##0` | 1,235 | Thousands separator |
| `#,##0.00` | 1,234.50 | Currency without symbol |
| `$#,##0.00` | $1,234.50 | USD currency |
| `0%` | 50% | Percentage |
| `0.00%` | 50.00% | Percentage with decimals |
| `yyyy-mm-dd` | 2024-01-15 | ISO date |
| `mm/dd/yyyy` | 01/15/2024 | US date |
| `dd-mmm-yyyy` | 15-Jan-2024 | Readable date |
| `hh:mm:ss` | 14:30:00 | Time |
| `0.00E+00` | 1.23E+03 | Scientific |
### Implementation
```python
# openpyxl
cell.number_format = '$#,##0.00'
cell.number_format = 'yyyy-mm-dd'
cell.number_format = '0.00%'
# Custom format with color
cell.number_format = '[Green]$#,##0.00;[Red]-$#,##0.00'
```
```typescript
// ExcelJS
cell.numFmt = '$#,##0.00';
cell.numFmt = 'yyyy-mm-dd';
cell.numFmt = '0.00%';
```
---
## Conditional Formatting
### Color Scales (Heatmaps)
```python
from openpyxl.formatting.rule import ColorScaleRule
# 2-color scale (red to green)
rule = ColorScaleRule(
start_type='min', start_color='FF0000',
end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('B2:B100', rule)
# 3-color scale
rule = ColorScaleRule(
start_type='min', start_color='FF0000',
mid_type='percentile', mid_value=50, mid_color='FFFF00',
end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('C2:C100', rule)
```
```typescript
// ExcelJS
sheet.addConditionalFormatting({
ref: 'B2:B100',
rules: [{
type: 'colorScale',
cfvo: [
{ type: 'min' },
{ type: 'max' }
],
color: [
{ argb: 'FFFF0000' },
{ argb: 'FF00FF00' }
]
}]
});
```
### Data Bars
```python
from openpyxl.formatting.rule import DataBarRule
rule = DataBarRule(
start_type='min',
end_type='max',
color='4472C4',
showValue=True,
minLength=None,
maxLength=None
)
ws.conditional_formatting.add('D2:D100', rule)
```
### Icon Sets
```python
from openpyxl.formatting.rule import IconSetRule
# Traffic lights
rule = IconSetRule(
icon_style='3TrafficLights1',
type='percent',
values=[0, 33, 67],
showValue=True,
reverse=False
)
ws.conditional_formatting.add('E2:E100', rule)
# Icon styles: '3Arrows', '3TrafficLights1', '4Rating', '5Quarters'
```
### Formula-Based Rules
```python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill
# Highlight rows where status = "Overdue"
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
rule = FormulaRule(
formula=['$C2="Overdue"'],
fill=red_fill
)
ws.conditional_formatting.add('A2:E100', rule)
# Highlight duplicates
rule = FormulaRule(
formula=['COUNTIF($A:$A,A2)>1'],
fill=PatternFill(start_color='FFFFCC', fill_type='solid')
)
ws.conditional_formatting.add('A2:A100', rule)
```
### Cell Value Rules
```python
from openpyxl.formatting.rule import CellIsRule
# Greater than
rule = CellIsRule(
operator='greaterThan',
formula=['100'],
fill=PatternFill(start_color='C6EFCE', fill_type='solid')
)
ws.conditional_formatting.add('B2:B100', rule)
# Between
rule = CellIsRule(
operator='between',
formula=['50', '100'],
fill=PatternFill(start_color='FFEB9C', fill_type='solid')
)
ws.conditional_formatting.add('B2:B100', rule)
# Operators: 'lessThan', 'lessThanOrEqual', 'greaterThan',
# 'greaterThanOrEqual', 'equal', 'notEqual', 'between'
```
---
## Row and Column Formatting
### Column Width
```python
# openpyxl
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15
# Auto-fit (approximate)
for column in ws.columns:
max_length = max(len(str(cell.value or '')) for cell in column)
ws.column_dimensions[column[0].column_letter].width = max_length + 2
```
```typescript
// ExcelJS
sheet.getColumn('A').width = 20;
// Set via column definition
sheet.columns = [
{ header: 'Name', key: 'name', width: 20 },
{ header: 'Value', key: 'value', width: 15 }
];
```
### Row Height
```python
# openpyxl
ws.row_dimensions[1].height = 30 # Header row
# All rows
for row in range(1, 101):
ws.row_dimensions[row].height = 20
```
```typescript
// ExcelJS
sheet.getRow(1).height = 30;
```
### Freeze Panes
```python
# openpyxl - Freeze first row
ws.freeze_panes = 'A2'
# Freeze first column
ws.freeze_panes = 'B1'
# Freeze both
ws.freeze_panes = 'B2'
```
```typescript
// ExcelJS
sheet.views = [{ state: 'frozen', xSplit: 1, ySplit: 1 }];
```
### Hide Rows/Columns
```python
# openpyxl
ws.column_dimensions['C'].hidden = True
ws.row_dimensions[5].hidden = True
```
```typescript
// ExcelJS
sheet.getColumn('C').hidden = true;
sheet.getRow(5).hidden = true;
```
---
## Merged Cells
```python
# openpyxl
ws.merge_cells('A1:D1') # Merge range
ws['A1'] = 'Report Title'
ws['A1'].alignment = Alignment(horizontal='center')
# Unmerge
ws.unmerge_cells('A1:D1')
```
```typescript
// ExcelJS
sheet.mergeCells('A1:D1');
sheet.getCell('A1').value = 'Report Title';
sheet.getCell('A1').alignment = { horizontal: 'center' };
// Unmerge
sheet.unMergeCells('A1:D1');
```
---
## Named Styles
```python
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill
# Create reusable style
header_style = NamedStyle(name='header')
header_style.font = Font(bold=True, color='FFFFFF', size=12)
header_style.fill = PatternFill(start_color='4472C4', fill_type='solid')
header_style.border = Border(
bottom=Side(style='medium', color='000000')
)
# Register style
wb.add_named_style(header_style)
# Apply to cells
for cell in ws[1]:
cell.style = 'header'
```
---
## Page Setup (Print)
```python
# openpyxl
ws.page_setup.orientation = 'landscape'
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0 # Auto
# Print titles (repeat rows/columns)
ws.print_title_rows = '1:1' # Repeat row 1
ws.print_title_cols = 'A:A' # Repeat column A
# Print area
ws.print_area = 'A1:F50'
# Headers/footers
ws.oddHeader.center.text = 'Monthly Report'
ws.oddFooter.center.text = 'Page &P of &N'
```
---
## Style Presets
### Header Style
```python
def style_header_row(ws, row=1):
"""Apply professional header styling."""
header_fill = PatternFill(start_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF', size=11)
header_border = Border(bottom=Side(style='medium', color='000000'))
for cell in ws[row]:
cell.fill = header_fill
cell.font = header_font
cell.border = header_border
cell.alignment = Alignment(horizontal='center', vertical='center')
```
### Alternating Row Colors
```python
def style_alternating_rows(ws, start_row=2, end_row=100):
"""Apply zebra striping."""
light_fill = PatternFill(start_color='F2F2F2', fill_type='solid')
for row in range(start_row, end_row + 1):
if row % 2 == 0:
for cell in ws[row]:
cell.fill = light_fill
```
### Currency Column
```python
def style_currency_column(ws, col, start_row=2, end_row=100):
"""Format column as currency with conditional colors."""
for row in range(start_row, end_row + 1):
cell = ws.cell(row=row, column=col)
cell.number_format = '$#,##0.00'
if cell.value and cell.value < 0:
cell.font = Font(color='FF0000')
```
---
## Color Reference
### Excel Theme Colors
| Color Name | Hex Code | ARGB |
|------------|----------|------|
| Blue (Accent 1) | #4472C4 | FF4472C4 |
| Orange (Accent 2) | #ED7D31 | FFED7D31 |
| Gray (Accent 3) | #A5A5A5 | FFA5A5A5 |
| Yellow (Accent 4) | #FFC000 | FFFFC000 |
| Blue (Accent 5) | #5B9BD5 | FF5B9BD5 |
| Green (Accent 6) | #70AD47 | FF70AD47 |
### Status Colors
| Status | Hex | Usage |
|--------|-----|-------|
| Success | #C6EFCE | Light green background |
| Warning | #FFEB9C | Light yellow background |
| Error | #FFC7CE | Light red background |
| Info | #BDD7EE | Light blue background |
references/excel-formulas.md# Excel Formulas Reference
Comprehensive formula patterns for spreadsheet generation with openpyxl and ExcelJS.
---
## Table of Contents
- [Basic Formulas](#basic-formulas)
- [Conditional Formulas](#conditional-formulas)
- [SUMIF / SUMIFS](#sumif-sumifs)
- [openpyxl - Single condition](#openpyxl-single-condition)
- [Multiple conditions](#multiple-conditions)
- [COUNTIF / COUNTIFS](#countif-countifs)
- [AVERAGEIF](#averageif)
- [Average sales for specific product](#average-sales-for-specific-product)
- [Lookup Formulas](#lookup-formulas)
- [VLOOKUP](#vlookup)
- [Syntax: VLOOKUP(lookup_value, table_array, col_index, [range_lookup])](#syntax-vlookuplookupvalue-tablearray-colindex-rangelookup)
- [XLOOKUP (Excel 365+)](#xlookup-excel-365)
- [More flexible than VLOOKUP](#more-flexible-than-vlookup)
- [INDEX/MATCH (Most Flexible)](#indexmatch-most-flexible)
- [Syntax: INDEX(return_range, MATCH(lookup_value, lookup_range, 0))](#syntax-indexreturnrange-matchlookupvalue-lookuprange-0)
- [Date Formulas](#date-formulas)
- [Date Calculations](#date-calculations)
- [Days until deadline](#days-until-deadline)
- [Age calculation](#age-calculation)
- [Next month same day](#next-month-same-day)
- [Text Formulas](#text-formulas)
- [Text Extraction](#text-extraction)
- [Extract domain from email](#extract-domain-from-email)
- [First name from full name](#first-name-from-full-name)
- [Logical Formulas](#logical-formulas)
- [IF Statements](#if-statements)
- [Simple IF](#simple-if)
- [Nested IF](#nested-if)
- [IFS (Excel 365+)](#ifs-excel-365)
- [AND / OR](#and-or)
- [Multiple conditions](#multiple-conditions)
- [IFERROR](#iferror)
- [Handle division by zero, lookup failures](#handle-division-by-zero-lookup-failures)
- [Financial Formulas](#financial-formulas)
- [Array Formulas (Dynamic Arrays)](#array-formulas-dynamic-arrays)
- [FILTER](#filter)
- [Filter rows where column B > 100](#filter-rows-where-column-b-100)
- [UNIQUE](#unique)
- [Get unique values](#get-unique-values)
- [SORT](#sort)
- [Sort by column, descending](#sort-by-column-descending)
- [SEQUENCE](#sequence)
- [Generate number sequence](#generate-number-sequence)
- [Implementation Patterns](#implementation-patterns)
- [openpyxl (Python)](#openpyxl-python)
- [Add formula](#add-formula)
- [Named ranges in formulas](#named-ranges-in-formulas)
- [Array formula (legacy)](#array-formula-legacy)
- [ExcelJS (Node.js)](#exceljs-nodejs)
- [Common Pitfalls](#common-pitfalls)
- [Performance Tips](#performance-tips)
## Basic Formulas
| Category | Formula | Example | Description |
|----------|---------|---------|-------------|
| Sum | `=SUM(range)` | `=SUM(A1:A100)` | Total of range |
| Average | `=AVERAGE(range)` | `=AVERAGE(B2:B50)` | Mean value |
| Count | `=COUNT(range)` | `=COUNT(C:C)` | Count numbers |
| CountA | `=COUNTA(range)` | `=COUNTA(D:D)` | Count non-empty |
| Min/Max | `=MIN(range)` / `=MAX(range)` | `=MAX(E1:E100)` | Extremes |
---
## Conditional Formulas
### SUMIF / SUMIFS
```python
# openpyxl - Single condition
ws['F1'] = '=SUMIF(A:A,"Product A",B:B)'
# Multiple conditions
ws['F2'] = '=SUMIFS(C:C,A:A,"Region1",B:B,">100")'
```
```typescript
// ExcelJS
sheet.getCell('F1').value = { formula: 'SUMIF(A:A,"Product A",B:B)' };
```
### COUNTIF / COUNTIFS
| Pattern | Formula | Use Case |
|---------|---------|----------|
| Single condition | `=COUNTIF(A:A,"Completed")` | Count status |
| Date range | `=COUNTIFS(A:A,">=2024-01-01",A:A,"<=2024-12-31")` | Count by period |
| Multiple criteria | `=COUNTIFS(A:A,"Active",B:B,">1000")` | Filtered count |
### AVERAGEIF
```python
# Average sales for specific product
ws['G1'] = '=AVERAGEIF(A:A,"Widget",C:C)'
```
---
## Lookup Formulas
### VLOOKUP
```python
# Syntax: VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
ws['D2'] = '=VLOOKUP(A2,Products!A:C,3,FALSE)'
```
**Parameters:**
- `lookup_value`: Value to find
- `table_array`: Range containing data
- `col_index`: Column number to return (1-based)
- `range_lookup`: FALSE for exact match
### XLOOKUP (Excel 365+)
```python
# More flexible than VLOOKUP
ws['D2'] = '=XLOOKUP(A2,Products!A:A,Products!C:C,"Not Found")'
```
### INDEX/MATCH (Most Flexible)
```python
# Syntax: INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
ws['D2'] = '=INDEX(C:C,MATCH(A2,A:A,0))'
```
**Advantages over VLOOKUP:**
- Can look left (not just right)
- More performant on large datasets
- Column insertions don't break formula
---
## Date Formulas
| Formula | Example | Result |
|---------|---------|--------|
| `=TODAY()` | `=TODAY()` | Current date |
| `=NOW()` | `=NOW()` | Current date+time |
| `=YEAR(date)` | `=YEAR(A1)` | Extract year |
| `=MONTH(date)` | `=MONTH(A1)` | Extract month (1-12) |
| `=EOMONTH(date,months)` | `=EOMONTH(A1,0)` | End of month |
| `=NETWORKDAYS(start,end)` | `=NETWORKDAYS(A1,B1)` | Business days |
| `=DATEDIF(start,end,"Y")` | `=DATEDIF(A1,B1,"Y")` | Years between |
### Date Calculations
```python
# Days until deadline
ws['C2'] = '=B2-TODAY()'
# Age calculation
ws['D2'] = '=DATEDIF(A2,TODAY(),"Y")'
# Next month same day
ws['E2'] = '=EDATE(A2,1)'
```
---
## Text Formulas
| Formula | Example | Result |
|---------|---------|--------|
| `=CONCATENATE()` | `=A1&" "&B1` | Join text |
| `=LEFT(text,n)` | `=LEFT(A1,3)` | First n chars |
| `=RIGHT(text,n)` | `=RIGHT(A1,4)` | Last n chars |
| `=MID(text,start,n)` | `=MID(A1,2,5)` | Substring |
| `=TRIM(text)` | `=TRIM(A1)` | Remove spaces |
| `=UPPER/LOWER` | `=UPPER(A1)` | Case change |
| `=LEN(text)` | `=LEN(A1)` | Character count |
### Text Extraction
```python
# Extract domain from email
ws['B2'] = '=MID(A2,FIND("@",A2)+1,100)'
# First name from full name
ws['C2'] = '=LEFT(A2,FIND(" ",A2)-1)'
```
---
## Logical Formulas
### IF Statements
```python
# Simple IF
ws['C2'] = '=IF(B2>100,"High","Low")'
# Nested IF
ws['C2'] = '=IF(B2>100,"High",IF(B2>50,"Medium","Low"))'
# IFS (Excel 365+)
ws['C2'] = '=IFS(B2>100,"High",B2>50,"Medium",TRUE,"Low")'
```
### AND / OR
```python
# Multiple conditions
ws['D2'] = '=IF(AND(B2>100,C2="Active"),"Priority","Normal")'
ws['E2'] = '=IF(OR(B2>1000,C2="VIP"),"Premium","Standard")'
```
### IFERROR
```python
# Handle division by zero, lookup failures
ws['F2'] = '=IFERROR(A2/B2,0)'
ws['G2'] = '=IFERROR(VLOOKUP(A2,Data!A:B,2,FALSE),"Not Found")'
```
---
## Financial Formulas
| Formula | Purpose | Example |
|---------|---------|---------|
| `=PMT(rate,nper,pv)` | Loan payment | `=PMT(0.05/12,360,-250000)` |
| `=FV(rate,nper,pmt,pv)` | Future value | `=FV(0.07,10,-1000,0)` |
| `=PV(rate,nper,pmt)` | Present value | `=PV(0.05,5,-1000)` |
| `=NPV(rate,values)` | Net present value | `=NPV(0.1,B2:B10)` |
| `=IRR(values)` | Internal return | `=IRR(A1:A10)` |
---
## Array Formulas (Dynamic Arrays)
### FILTER
```python
# Filter rows where column B > 100
ws['E1'] = '=FILTER(A:C,B:B>100,"No results")'
```
### UNIQUE
```python
# Get unique values
ws['F1'] = '=UNIQUE(A:A)'
```
### SORT
```python
# Sort by column, descending
ws['G1'] = '=SORT(A1:C100,2,-1)'
```
### SEQUENCE
```python
# Generate number sequence
ws['A1'] = '=SEQUENCE(10,1,1,1)' # 1 to 10
```
---
## Implementation Patterns
### openpyxl (Python)
```python
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Add formula
ws['C2'] = '=A2*B2'
# Named ranges in formulas
wb.defined_names.add('SalesData', 'Sheet1!$A$1:$C$100')
ws['D1'] = '=SUM(SalesData)'
# Array formula (legacy)
ws['E1'] = '=SUM(A1:A10*B1:B10)'
ws['E1'].data_type = 'a' # Mark as array formula
```
### ExcelJS (Node.js)
```typescript
import ExcelJS from 'exceljs';
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Data');
// Simple formula
sheet.getCell('C2').value = { formula: 'A2*B2' };
// Formula with result hint
sheet.getCell('D2').value = {
formula: 'SUM(A:A)',
result: 1000 // Optional: cached result
};
// Shared formula (efficient for repeated formulas)
sheet.getCell('C2').value = { sharedFormula: 'A2*B2' };
for (let row = 3; row <= 100; row++) {
sheet.getCell(`C${row}`).value = { sharedFormula: 'C2' };
}
```
---
## Common Pitfalls
| Issue | Cause | Solution |
|-------|-------|----------|
| `#REF!` | Deleted reference | Use named ranges |
| `#VALUE!` | Type mismatch | Check data types |
| `#DIV/0!` | Division by zero | Wrap in IFERROR |
| `#N/A` | Lookup not found | IFERROR or IFNA |
| Circular reference | Self-referencing | Break the loop |
| Formula as text | Leading quote/space | Remove prefix |
| `openpyxl` reads formula cell as `None` with `data_only=True` | File was written by a library, never opened/recalculated by Excel or LibreOffice, so no cached value exists | Recalculate in Excel/LibreOffice headless before reading, or compute the value in code and write it as a literal alongside (or instead of) the formula |
| `#NAME?` on `XLOOKUP`/`FILTER`/`UNIQUE`/`SORT`/`IFS` | Opened in Excel 2019/2016 or an older non-365 build | Use `VLOOKUP`/`INDEX-MATCH`/nested `IF` when the recipient's Excel version is unknown or pre-365 |
---
## Performance Tips
1. **Avoid volatile functions** in large sheets: `NOW()`, `TODAY()`, `RAND()`, `INDIRECT()`
2. **Use structured references** with Tables instead of A1 notation
3. **Prefer XLOOKUP/INDEX-MATCH** over VLOOKUP for large datasets
4. **Limit whole-column references** (`A:A`) when possible
5. **Use helper columns** instead of complex nested formulas
6. **For large `openpyxl` exports**, install `lxml` and use `Workbook(write_only=True)` / `load_workbook(read_only=True)` — both stream instead of building a full in-memory tree, which is the difference between gigabytes and megabytes of RAM on multi-hundred-thousand-row workbooks. A write-only workbook can be saved exactly once.
references/excel-pivot-tables.md# Excel Pivot Tables and Summary Data Reference
## Table of Contents
- [Contents](#contents)
- [Runtime Support Matrix](#runtime-support-matrix)
- [pandas pivot_table to Excel](#pandas-pivottable-to-excel)
- [Office Scripts Native Pivot (Microsoft 365)](#office-scripts-native-pivot-microsoft-365)
- [xlwings Native Pivot (Requires Excel)](#xlwings-native-pivot-requires-excel)
- [Summary Table Patterns](#summary-table-patterns)
- [Structuring Data for Pivot-Readiness](#structuring-data-for-pivot-readiness)
- [When Native Pivots vs Pre-Computed](#when-native-pivots-vs-pre-computed)
- [Do / Avoid](#do--avoid)
Patterns for generating pivot-style summaries with local libraries, Excel automation, and Microsoft 365 workbook tooling.
---
## Contents
- Runtime-specific support for native pivot tables
- pandas pivot_table to Excel workflow
- Office Scripts native pivot creation
- xlwings native pivot table creation
- Summary table patterns (cross-tab, running totals, YoY)
- Structuring data for pivot-readiness
- Do / Avoid
---
## Runtime Support Matrix
| Library | Native Pivot Support | Notes |
|---------|---------------------|-------|
| openpyxl | No | Can read existing pivots, cannot create |
| XlsxWriter | No | Cannot create or modify pivot tables |
| ExcelJS | Limited | Recent pivot-table support exists, but validate carefully before production use |
| SheetJS | Read-only / transform | Good for ingestion and transformation, not native pivot creation |
| Office Scripts | Yes | Native Excel on the web pivots for Microsoft 365 workbooks |
| xlwings | Yes | Requires Excel installed on the machine |
| win32com | Yes | Windows + Excel only |
If the runtime has no Excel installation and no Microsoft 365 workbook context, generate pre-computed summary tables instead.
---
## pandas pivot_table to Excel
```python
import pandas as pd
df = pd.DataFrame({
"Region": ["East", "East", "West", "West", "East", "West"],
"Product": ["A", "B", "A", "B", "A", "B"],
"Revenue": [100, 200, 150, 250, 120, 180],
"Units": [10, 20, 15, 25, 12, 18],
})
# Single aggregation with grand totals
summary = pd.pivot_table(
df, values="Revenue", index="Region", columns="Product",
aggfunc="sum", margins=True, margins_name="Total"
)
# Multiple aggregations
detail = pd.pivot_table(
df, values=["Revenue", "Units"], index="Region", columns="Product",
aggfunc={"Revenue": "sum", "Units": "mean"}, fill_value=0
)
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
summary.to_excel(writer, sheet_name="Summary")
detail.to_excel(writer, sheet_name="Detail")
df.to_excel(writer, sheet_name="Raw Data", index=False)
```
---
## Office Scripts Native Pivot (Microsoft 365)
```typescript
function main(workbook: ExcelScript.Workbook) {
const raw = workbook.getWorksheet("Raw Data");
const sourceTable = raw.addTable(raw.getUsedRange(), true);
sourceTable.setName("SalesTable");
const pivotSheet = workbook.addWorksheet("Pivot");
const pivot = workbook.addPivotTable("SalesPivot", sourceTable, pivotSheet.getRange("A1"));
pivot.addRowHierarchy(pivot.getHierarchy("Region"));
pivot.addColumnHierarchy(pivot.getHierarchy("Product"));
pivot.addDataHierarchy(pivot.getHierarchy("Revenue"));
}
```
Use Office Scripts when the workbook already lives in OneDrive or SharePoint and native Excel pivot behavior is required.
---
## xlwings Native Pivot (Requires Excel)
```python
import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open("data.xlsx")
ws_data = wb.sheets["Raw Data"]
ws_pivot = wb.sheets.add("PivotReport")
src = ws_data.range("A1").expand()
pt = wb.api.PivotCaches().Create(
SourceType=1, SourceData=src.api
).CreatePivotTable(
TableDestination=ws_pivot.range("A3").api, TableName="SalesPivot"
)
pt.PivotFields("Region").Orientation = 1 # xlRowField
pt.PivotFields("Product").Orientation = 2 # xlColumnField
pt.AddDataField(pt.PivotFields("Revenue"), "Sum of Revenue", -4157)
wb.save("report_with_pivot.xlsx")
wb.close()
app.quit()
```
Not suitable for headless Linux CI -- COM/AppleScript bridge required.
---
## Summary Table Patterns
```python
# Cross-tab
cross = pd.crosstab(df["Region"], df["Product"],
values=df["Revenue"], aggfunc="sum", margins=True)
# Running totals
monthly = df.groupby("Month")["Revenue"].sum().reset_index()
monthly["Cumulative"] = monthly["Revenue"].cumsum()
# Year-over-year comparison
yoy = df.pivot_table(values="Revenue", index="Month", columns="Year", aggfunc="sum")
yoy["YoY Change"] = yoy[2025] - yoy[2024]
yoy["YoY %"] = ((yoy[2025] - yoy[2024]) / yoy[2024] * 100).round(1)
```
---
## Structuring Data for Pivot-Readiness
Pivots require tidy, flat data. Verify these properties before generating:
1. One header row -- no merged cells in the header
2. Every column has a unique, non-empty name
3. No blank rows or columns within the data block
4. Consistent types per column (no mixed text/numbers)
5. Dates stored as date objects, not strings
6. No subtotals or totals mixed into data rows
```python
df.columns = df.columns.str.strip()
df = df.dropna(how="all")
df["Date"] = pd.to_datetime(df["Date"], errors="coerce")
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce").fillna(0)
```
---
## When Native Pivots vs Pre-Computed
| Scenario | Recommendation |
|----------|---------------|
| Recipients need interactive slice/filter in Microsoft 365 | Native pivot (Office Scripts) |
| Recipients need interactive slice/filter on a desktop Excel workflow | Native pivot (xlwings) |
| Read-only report or email attachment | Pre-computed summary |
| Headless Linux CI | Pre-computed summary |
| File must open in Google Sheets / LibreOffice | Pre-computed summary |
| Strict audit trail required | Pre-computed (frozen values) |
---
## Do / Avoid
**Do:**
- Include a "Raw Data" sheet so recipients can build their own pivots
- Use `margins=True` in pandas to add Grand Total rows/columns
- Format summary output with openpyxl styles after writing
- Name the data range as an Excel Table for self-expanding pivot sources
- Validate aggregation results against source totals before saving
- Prefer pre-computed summaries when interoperability matters more than native Excel interactivity
**Avoid:**
- Merged cells in pivot source data (breaks field detection)
- Multi-level column headers from pandas MultiIndex without flattening
- Assuming openpyxl or XlsxWriter can create native pivot tables
- Assuming all viewers preserve native Excel pivots identically
- Leaving `NaN` in numeric columns (use `fill_value=0`)
- Running xlwings in CI without a licensed Excel installation
references/excel-security-protection.md# Excel Security and Protection Reference
## Table of Contents
- [Contents](#contents)
- [Sheet Protection](#sheet-protection)
- [Workbook Protection](#workbook-protection)
- [Cell Locking Patterns](#cell-locking-patterns)
- [Password Limitations](#password-limitations)
- [Formula Injection Prevention](#formula-injection-prevention)
- [Hyperlinks And External Links](#hyperlinks-and-external-links)
- [Hidden Sheets for Audit Trails](#hidden-sheets-for-audit-trails)
- [Do / Avoid](#do--avoid)
- [Checklist: Pre-Distribution Security Review](#checklist-pre-distribution-security-review)
Sheet protection, cell locking, external-link handling, and injection prevention for generated spreadsheets.
---
## Contents
- Sheet protection (openpyxl, ExcelJS) and workbook structure protection
- Cell locking patterns and password limitations
- Formula injection, hyperlinks, external links, and hidden sheets
- Do / Avoid and pre-distribution checklist
---
## Sheet Protection
### openpyxl
```python
from openpyxl.worksheet.protection import SheetProtection
ws.protection = SheetProtection(
sheet=True, password="review2025",
formatCells=False, insertRows=False, deleteRows=False,
sort=True, autoFilter=True,
selectLockedCells=True, selectUnlockedCells=True
)
```
### ExcelJS
```typescript
await worksheet.protect('review2025', {
selectLockedCells: true, selectUnlockedCells: true,
formatCells: false, insertRows: false, deleteRows: false,
sort: true, autoFilter: true
});
```
---
## Workbook Protection
Prevents adding, deleting, renaming, or reordering sheets. Does not protect cell contents.
```python
wb.security.workbookPassword = "struct2025"
wb.security.lockStructure = True
```
ExcelJS has no native workbook protection API. Use a pre-protected template.
---
## Cell Locking Patterns
All cells default to "locked" in Excel, but locking activates only when the sheet is protected.
```python
from openpyxl.styles import Protection
# Unlock input cells
for row in ws.iter_rows(min_row=2, max_row=200, min_col=2, max_col=4):
for cell in row:
cell.protection = Protection(locked=False)
# Lock and hide formula cells (hidden=True hides from formula bar)
for row in ws.iter_rows(min_row=2, max_row=200, min_col=5, max_col=8):
for cell in row:
cell.protection = Protection(locked=True, hidden=True)
ws.protection.sheet = True
ws.protection.password = "edit2025"
```
```typescript
// ExcelJS equivalent
for (let r = 2; r <= 200; r++) {
for (let c = 2; c <= 4; c++)
worksheet.getCell(r, c).protection = { locked: false };
for (let c = 5; c <= 8; c++)
worksheet.getCell(r, c).protection = { locked: true, hidden: true };
}
await worksheet.protect('edit2025');
```
---
## Password Limitations
Sheet/workbook protection passwords are **not encryption**. They are a UI deterrent only.
| Fact | Detail |
|------|--------|
| Hash algorithm | Legacy CRC / SHA-based hash in XML |
| Crack time | Seconds with freely available tools |
| Bypass | Unzip .xlsx, edit XML, remove password hash |
| Real encryption | AES-128/256 via `msoffcrypto-tool` or OS-level controls |
```python
import msoffcrypto
with open("report.xlsx", "rb") as f:
file = msoffcrypto.OfficeFile(f)
file.load_key(password="Str0ngP@ss!")
with open("report_encrypted.xlsx", "wb") as out:
file.encrypt("Str0ngP@ss!", out)
```
---
## Formula Injection Prevention
User-supplied strings can trigger formula execution when written to cells.
### Dangerous Prefixes
`=`, `+`, `-`, `@`, `\t` (tab), `\r` (carriage return)
### Sanitization
```python
DANGEROUS_PREFIXES = ("=", "+", "-", "@", "\t", "\r", "\n")
def sanitize_cell_value(value):
if isinstance(value, str) and value.startswith(DANGEROUS_PREFIXES):
return "'" + value # leading quote forces text interpretation
return value
```
```typescript
const DANGEROUS = /^[=+\-@\t\r\n]/;
function sanitize(v: unknown): unknown {
return typeof v === 'string' && DANGEROUS.test(v) ? "'" + v : v;
}
```
The leading single quote is not displayed in the cell.
---
## Hyperlinks And External Links
Hyperlinks and workbook external links need separate review.
### Hyperlink Review
- Reject or rewrite untrusted protocols such as `javascript:`
- Prefer readable hyperlink labels over raw tracking URLs
- Audit links before distribution when the workbook will leave the authoring team
### openpyxl Workbook Loading
When ingesting an untrusted workbook, do not preserve external links by default unless the workflow explicitly requires them.
```python
from openpyxl import load_workbook
wb = load_workbook("input.xlsx", keep_vba=False, keep_links=False)
```
### Strip External Links During Packaging
- Remove `xl/externalLinks/` parts and their relationships if the workbook should be self-contained
- Document intentional external links on an Instructions or Summary sheet
- Re-test formulas after link removal because some models expect external workbooks
---
## Hidden Sheets for Audit Trails
```python
ws_meta = wb.create_sheet("_Audit")
ws_meta.sheet_state = "veryHidden" # only accessible via VBA editor
ws_meta["A1"], ws_meta["B1"] = "Generated", datetime.now().isoformat()
ws_meta["A2"], ws_meta["B2"] = "Source Hash", data_hash
```
`hidden` = users can unhide via right-click. `veryHidden` = requires VBA or XML editing.
## Do / Avoid
**Do:** sanitize all user-supplied strings before cell writes. Review hyperlinks and external links before distribution. Use file-level AES encryption for sensitive data. Unlock only specific input ranges. Hide formulas in protected sheets. Document editable cells and intentional links on an Instructions sheet.
**Avoid:** relying on sheet protection passwords as a security boundary. Writing raw user input without injection checks. Preserving external links on untrusted ingest without a reason. Protecting sheets without setting locked/unlocked patterns first. Storing secrets or PII in cells, even on hidden sheets.
---
## Checklist: Pre-Distribution Security Review
- [ ] User-supplied values pass through injection sanitization
- [ ] Hyperlinks have been reviewed and unsafe protocols removed or rewritten
- [ ] Input cells unlocked; all others locked; sheet protection enabled
- [ ] Formula cells have `hidden=True` if logic is confidential
- [ ] Workbook structure protection is on
- [ ] External links are removed, documented, or explicitly approved
- [ ] File-level encryption applied if data is sensitive or regulated
- [ ] Hidden sheets contain no credentials or tokens
- [ ] Tested in Excel, LibreOffice, and Google Sheets
references/excel-tables-structured-references.md# Excel Tables and Structured References
Use Excel Tables as the default container for exported data blocks. Tables improve filtering, totals, formulas, readability, and downstream pivot/chart behavior.
---
## Table of Contents
- [Why Tables First](#why-tables-first)
- [XlsxWriter](#xlsxwriter)
- [openpyxl](#openpyxl)
- [ExcelJS](#exceljs)
- [Office Scripts](#office-scripts)
- [Structured Formula Patterns](#structured-formula-patterns)
- [Do / Avoid](#do-avoid)
## Why Tables First
| Benefit | Why it matters |
|---------|----------------|
| Stable ranges | Formulas, charts, and pivots expand with the table |
| Structured formulas | `=SUM(SalesTable[Revenue])` is easier to audit than `=SUM(D2:D5000)` |
| Built-in filters | Users get filters without ad hoc range guessing |
| Totals rows | Common aggregates can be exposed without hand-written footer formulas |
| Accessibility | Explicit headers and bounded data regions are easier to navigate |
---
## XlsxWriter
```python
import pandas as pd
df = pd.DataFrame(
[
{"region": "East", "product": "A", "revenue": 100},
{"region": "West", "product": "B", "revenue": 150},
]
)
with pd.ExcelWriter("table_report.xlsx", engine="xlsxwriter") as writer:
df.to_excel(writer, sheet_name="Raw Data", index=False, startrow=0)
workbook = writer.book
worksheet = writer.sheets["Raw Data"]
worksheet.add_table(
0,
0,
len(df),
len(df.columns) - 1,
{
"name": "SalesTable",
"style": "Table Style Medium 2",
"total_row": True,
"columns": [
{"header": "region"},
{"header": "product"},
{"header": "revenue", "total_function": "sum"},
],
},
)
worksheet.freeze_panes(1, 0)
```
---
## openpyxl
```python
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
ws.title = "Raw Data"
ws.append(["region", "product", "revenue"])
ws.append(["East", "A", 100])
ws.append(["West", "B", 150])
table = Table(displayName="SalesTable", ref="A1:C3")
table.tableStyleInfo = TableStyleInfo(
name="TableStyleMedium2",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False,
)
ws.add_table(table)
wb.save("table_report.xlsx")
```
---
## ExcelJS
```typescript
import ExcelJS from "exceljs";
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet("Raw Data");
worksheet.columns = [
{ header: "region", key: "region", width: 12 },
{ header: "product", key: "product", width: 12 },
{ header: "revenue", key: "revenue", width: 14 },
];
worksheet.addRows([
{ region: "East", product: "A", revenue: 100 },
{ region: "West", product: "B", revenue: 150 },
]);
worksheet.addTable({
name: "SalesTable",
ref: "A1",
headerRow: true,
totalsRow: true,
style: { theme: "TableStyleMedium2", showRowStripes: true },
columns: [
{ name: "region" },
{ name: "product" },
{ name: "revenue", totalsRowFunction: "sum" },
],
rows: [
["East", "A", 100],
["West", "B", 150],
],
});
await workbook.xlsx.writeFile("table_report.xlsx");
```
---
## Office Scripts
```typescript
function main(workbook: ExcelScript.Workbook) {
const ws = workbook.getWorksheet("Raw Data");
const range = ws.getUsedRange();
const table = ws.addTable(range, true);
table.setName("SalesTable");
table.setShowTotals(true);
}
```
---
## Structured Formula Patterns
| Goal | Formula |
|------|---------|
| Sum a column | `=SUM(SalesTable[Revenue])` |
| Current row math | `=[@qty]*[@price]` |
| Count non-empty items | `=COUNTA(SalesTable[product])` |
| Average by table column | `=AVERAGE(SalesTable[margin])` |
Use structured references for workbook models that will be reviewed by humans. They are more verbose than cell references, but far easier to audit.
---
## Do / Avoid
**Do:**
- Use one header row at the top of the table
- Give each table a descriptive, stable name
- Freeze the header row and keep totals rows explicit
- Build charts and pivots from tables instead of guessed ranges
**Avoid:**
- Merged cells in the header row
- Blank columns inside the table block
- Duplicate header names
- Hardcoded footer formulas that drift away from the data block
references/extraction-stack.md# 2026 Extraction Stack — XLSX Verified 2026-05-17. Use this note when selecting an XLSX extraction tool for LLM/RAG or data pipelines. ## Tier 1 — High-performance pure-Rust reading **calamine** — `github.com/tafia/calamine` Pure-Rust reader for xls, xlsx, xlsb, and ods; no dependencies; Python bindings via `python-calamine`. Preferred for high-throughput spreadsheet ingestion where openpyxl overhead is measurable. ## Tier 2 — Structured multi-format extraction **Docling** (IBM Research, MIT) — `github.com/docling-project/docling` Produces a typed `DoclingDocument` from XLSX, PDF, DOCX, PPTX, HTML, and images. Preferred when downstream consumers need consistent schema across multiple formats. ## Tier 3 — OCR for image-embedded or scanned spreadsheet content **Mistral OCR** — `mistral.ai/news/mistral-ocr` REST API; understands text, tables, equations, and embedded media; ~1000 pages/$. Use when Excel data is trapped in images or scanned documents rather than native cells. ## Decision rule calamine/openpyxl for native cells → Docling for cross-format schema → Mistral OCR for image-trapped data.
scripts/xlsx_audit.py#!/usr/bin/env python3
from __future__ import annotations
import argparse
import json
import posixpath
import sys
import zipfile
from dataclasses import asdict, dataclass
from pathlib import Path
from typing import Any
from xml.etree import ElementTree as ET
NS = {
"main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
"rel": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
"pkgrel": "http://schemas.openxmlformats.org/package/2006/relationships",
"cp": "http://schemas.openxmlformats.org/package/2006/metadata/core-properties",
"dc": "http://purl.org/dc/elements/1.1/",
"dcterms": "http://purl.org/dc/terms/",
"table": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
}
DANGEROUS_PREFIXES = ("=", "+", "-", "@", "\t", "\r", "\n")
@dataclass(frozen=True)
class SheetAudit:
name: str
path: str | None
state: str
formula_count: int
table_names: list[str]
inline_dangerous_strings: int
def _read_xml(zip_file: zipfile.ZipFile, name: str) -> ET.Element | None:
try:
with zip_file.open(name) as file:
return ET.parse(file).getroot()
except KeyError:
return None
def _read_text(zip_file: zipfile.ZipFile, name: str) -> bytes:
try:
with zip_file.open(name) as file:
return file.read()
except KeyError:
return b""
def _strip_namespace(tag: str) -> str:
if "}" in tag:
return tag.split("}", 1)[1]
return tag
def _package_target(base_part: str, target: str) -> str:
if target.startswith("/"):
return posixpath.normpath(target.lstrip("/"))
base = Path(base_part).parent
return posixpath.normpath((base / target).as_posix())
def _load_shared_strings(zip_file: zipfile.ZipFile) -> tuple[int, list[str]]:
root = _read_xml(zip_file, "xl/sharedStrings.xml")
if root is None:
return 0, []
count = 0
samples: list[str] = []
for si in root.findall("main:si", NS):
text = "".join(node.text or "" for node in si.iterfind(".//main:t", NS))
if text.startswith(DANGEROUS_PREFIXES):
count += 1
if len(samples) < 5:
samples.append(text[:120])
return count, samples
def _load_table_map(zip_file: zipfile.ZipFile) -> dict[str, str]:
table_map: dict[str, str] = {}
for name in zip_file.namelist():
if not name.startswith("xl/tables/") or not name.endswith(".xml"):
continue
root = _read_xml(zip_file, name)
if root is None:
continue
display_name = root.attrib.get("displayName") or root.attrib.get("name")
if display_name:
table_map[name] = display_name
return table_map
def _load_sheet_targets(zip_file: zipfile.ZipFile) -> list[tuple[str, str, str]]:
workbook_root = _read_xml(zip_file, "xl/workbook.xml")
rels_root = _read_xml(zip_file, "xl/_rels/workbook.xml.rels")
if workbook_root is None or rels_root is None:
return []
rels = {
rel.attrib.get("Id"): rel.attrib.get("Target")
for rel in rels_root.findall("pkgrel:Relationship", NS)
if rel.attrib.get("Id") and rel.attrib.get("Target")
}
sheets: list[tuple[str, str, str]] = []
for sheet in workbook_root.findall("main:sheets/main:sheet", NS):
rid = sheet.attrib.get(f"{{{NS['rel']}}}id")
if not rid or rid not in rels:
continue
target = _package_target("xl/workbook.xml", rels[rid])
sheets.append(
(
sheet.attrib.get("name", "Unnamed"),
target,
sheet.attrib.get("state", "visible"),
)
)
return sheets
def _sheet_table_names(zip_file: zipfile.ZipFile, sheet_path: str, table_map: dict[str, str]) -> list[str]:
rels_path = f"{Path(sheet_path).parent.as_posix()}/_rels/{Path(sheet_path).name}.rels"
rels_root = _read_xml(zip_file, rels_path)
if rels_root is None:
return []
tables: list[str] = []
for rel in rels_root.findall("pkgrel:Relationship", NS):
rel_type = rel.attrib.get("Type", "")
if not rel_type.endswith("/table"):
continue
target = rel.attrib.get("Target")
if not target:
continue
table_path = _package_target(sheet_path, target)
display_name = table_map.get(table_path)
if display_name:
tables.append(display_name)
return tables
def _sheet_formula_count(zip_file: zipfile.ZipFile, sheet_path: str) -> tuple[int, int]:
root = _read_xml(zip_file, sheet_path)
if root is None:
return 0, 0
formulas = sum(1 for _ in root.iterfind(".//main:f", NS))
dangerous_inline = 0
for inline_str in root.iterfind(".//main:is", NS):
text = "".join(node.text or "" for node in inline_str.iterfind(".//main:t", NS))
if text.startswith(DANGEROUS_PREFIXES):
dangerous_inline += 1
return formulas, dangerous_inline
def _defined_names(zip_file: zipfile.ZipFile) -> list[str]:
workbook_root = _read_xml(zip_file, "xl/workbook.xml")
if workbook_root is None:
return []
names: list[str] = []
for node in workbook_root.findall("main:definedNames/main:definedName", NS):
name = node.attrib.get("name")
if name:
names.append(name)
return names
def _core_properties(zip_file: zipfile.ZipFile) -> dict[str, str | None]:
root = _read_xml(zip_file, "docProps/core.xml")
if root is None:
return {}
return {
"title": root.findtext("dc:title", default=None, namespaces=NS),
"subject": root.findtext("dc:subject", default=None, namespaces=NS),
"creator": root.findtext("dc:creator", default=None, namespaces=NS),
"description": root.findtext("dc:description", default=None, namespaces=NS),
"created": root.findtext("dcterms:created", default=None, namespaces=NS),
"modified": root.findtext("dcterms:modified", default=None, namespaces=NS),
}
def audit_workbook(path: Path) -> dict[str, Any]:
if path.suffix.lower() not in {".xlsx", ".xlsm"}:
raise ValueError("Expected a .xlsx or .xlsm file.")
with zipfile.ZipFile(path) as zip_file:
shared_dangerous_count, shared_samples = _load_shared_strings(zip_file)
table_map = _load_table_map(zip_file)
sheet_targets = _load_sheet_targets(zip_file)
defined_names = _defined_names(zip_file)
core_props = _core_properties(zip_file)
sheets: list[SheetAudit] = []
total_formulas = 0
total_inline_dangerous = 0
hidden_sheets = 0
for sheet_name, sheet_path, state in sheet_targets:
formula_count, dangerous_inline = _sheet_formula_count(zip_file, sheet_path)
total_formulas += formula_count
total_inline_dangerous += dangerous_inline
if state != "visible":
hidden_sheets += 1
sheets.append(
SheetAudit(
name=sheet_name,
path=sheet_path,
state=state,
formula_count=formula_count,
table_names=_sheet_table_names(zip_file, sheet_path, table_map),
inline_dangerous_strings=dangerous_inline,
)
)
external_link_parts = sorted(
name for name in zip_file.namelist() if name.startswith("xl/externalLinks/")
)
calc_mode = None
workbook_root = _read_xml(zip_file, "xl/workbook.xml")
workbook_protection = False
if workbook_root is not None:
calc_pr = workbook_root.find("main:calcPr", NS)
if calc_pr is not None:
calc_mode = calc_pr.attrib.get("calcMode")
protection = workbook_root.find("main:workbookProtection", NS)
if protection is not None:
workbook_protection = any(
protection.attrib.get(key) in {"1", "true", "True"}
for key in ("lockStructure", "lockWindows", "lockRevision")
)
return {
"path": str(path),
"core_properties": core_props,
"macros_present": "xl/vbaProject.bin" in zip_file.namelist(),
"workbook_protection": workbook_protection,
"calc_mode": calc_mode,
"defined_names": defined_names,
"external_link_parts": external_link_parts,
"shared_string_dangerous_count": shared_dangerous_count,
"shared_string_samples": shared_samples,
"totals": {
"sheet_count": len(sheets),
"hidden_sheet_count": hidden_sheets,
"formula_count": total_formulas,
"table_count": sum(len(sheet.table_names) for sheet in sheets),
"dangerous_text_count": shared_dangerous_count + total_inline_dangerous,
},
"sheets": [asdict(sheet) for sheet in sheets],
}
def render_markdown(payload: dict[str, Any]) -> str:
lines = [
f"# XLSX Audit: {payload['path']}",
"",
"## Summary",
f"- Sheets: {payload['totals']['sheet_count']}",
f"- Hidden sheets: {payload['totals']['hidden_sheet_count']}",
f"- Formulas: {payload['totals']['formula_count']}",
f"- Tables: {payload['totals']['table_count']}",
f"- Dangerous text values: {payload['totals']['dangerous_text_count']}",
f"- Macros present: {'yes' if payload['macros_present'] else 'no'}",
f"- Workbook protection: {'yes' if payload['workbook_protection'] else 'no'}",
]
if payload.get("calc_mode"):
lines.append(f"- Calc mode: {payload['calc_mode']}")
lines.extend(["", "## Sheets"])
for sheet in payload["sheets"]:
tables = ", ".join(sheet["table_names"]) if sheet["table_names"] else "none"
lines.append(
f"- {sheet['name']}: state={sheet['state']}, formulas={sheet['formula_count']}, "
f"tables={tables}, dangerous_inline_strings={sheet['inline_dangerous_strings']}"
)
lines.extend(["", "## Names And Links"])
lines.append(
f"- Defined names: {', '.join(payload['defined_names']) if payload['defined_names'] else 'none'}"
)
lines.append(
f"- External link parts: {', '.join(payload['external_link_parts']) if payload['external_link_parts'] else 'none'}"
)
if payload["shared_string_samples"]:
lines.extend(["", "## Dangerous Text Samples"])
for sample in payload["shared_string_samples"]:
lines.append(f"- {sample}")
return "\n".join(lines)
def main(argv: list[str]) -> int:
parser = argparse.ArgumentParser(
description="Audit a .xlsx/.xlsm workbook for formulas, links, hidden sheets, tables, and risky strings."
)
parser.add_argument("workbook", type=Path, help="Path to a .xlsx or .xlsm workbook")
parser.add_argument("--format", choices=("md", "json"), default="md", help="Output format")
args = parser.parse_args(argv)
if not args.workbook.exists():
print(f"File not found: {args.workbook}", file=sys.stderr)
return 2
try:
payload = audit_workbook(args.workbook)
except (ValueError, zipfile.BadZipFile) as exc:
print(str(exc), file=sys.stderr)
return 2
if args.format == "json":
print(json.dumps(payload, indent=2, ensure_ascii=False))
else:
print(render_markdown(payload))
return 0
if __name__ == "__main__":
raise SystemExit(main(sys.argv[1:]))
scripts/xlsx_export_report.py#!/usr/bin/env python3
from __future__ import annotations
import argparse
import sys
from pathlib import Path
def _require_pandas():
try:
import pandas as pd # type: ignore
return pd
except ImportError as exc:
raise RuntimeError("Missing dependency: pandas. Install with: pip install pandas XlsxWriter") from exc
def load_dataframe(input_path: Path):
pd = _require_pandas()
suffix = input_path.suffix.lower()
if suffix == ".csv":
return pd.read_csv(input_path)
if suffix == ".json":
return pd.read_json(input_path)
if suffix == ".parquet":
return pd.read_parquet(input_path)
raise ValueError("Supported input formats: .csv, .json, .parquet")
def export_report(input_path: Path, output_path: Path, sheet_name: str, table_name: str, title: str | None) -> None:
pd = _require_pandas()
try:
import xlsxwriter # noqa: F401
except ImportError as exc:
raise RuntimeError("Missing dependency: XlsxWriter. Install with: pip install pandas XlsxWriter") from exc
df = load_dataframe(input_path)
startrow = 1 if title else 0
output_path.parent.mkdir(parents=True, exist_ok=True)
with pd.ExcelWriter(output_path, engine="xlsxwriter") as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=startrow)
workbook = writer.book
worksheet = writer.sheets[sheet_name]
header_format = workbook.add_format({"bold": True, "bg_color": "#D9E2F3"})
title_format = workbook.add_format({"bold": True, "font_size": 14})
if title:
worksheet.write("A1", title, title_format)
freeze_row = startrow + 1
worksheet.freeze_panes(freeze_row, 0)
last_col = max(len(df.columns) - 1, 0)
worksheet.autofilter(startrow, 0, max(startrow, startrow + len(df)), last_col)
for idx, column in enumerate(df.columns):
width = max(len(str(column)), 12)
if not df.empty:
width = min(40, max(width, df[column].astype(str).map(len).max()))
worksheet.set_column(idx, idx, width + 2)
if not df.empty:
worksheet.add_table(
startrow,
0,
startrow + len(df),
len(df.columns) - 1,
{
"name": table_name,
"style": "Table Style Medium 2",
"columns": [{"header": col, "header_format": header_format} for col in df.columns],
"autofilter": True,
},
)
def main(argv: list[str]) -> int:
parser = argparse.ArgumentParser(
description="Export CSV/JSON/Parquet data to a table-first .xlsx workbook."
)
parser.add_argument("input_path", type=Path, help="Input .csv, .json, or .parquet file")
parser.add_argument("output_path", type=Path, help="Output .xlsx path")
parser.add_argument("--sheet-name", default="Report", help="Worksheet name (default: Report)")
parser.add_argument("--table-name", default="ReportTable", help="Excel table name (default: ReportTable)")
parser.add_argument("--title", default=None, help="Optional title written to A1")
args = parser.parse_args(argv)
if not args.input_path.exists():
print(f"File not found: {args.input_path}", file=sys.stderr)
return 2
if args.output_path.suffix.lower() != ".xlsx":
print("Output path must end in .xlsx", file=sys.stderr)
return 2
try:
export_report(args.input_path, args.output_path, args.sheet_name, args.table_name, args.title)
except Exception as exc:
print(str(exc), file=sys.stderr)
return 2
return 0
if __name__ == "__main__":
raise SystemExit(main(sys.argv[1:]))
scripts/xlsx_sanitize.py#!/usr/bin/env python3
from __future__ import annotations
import argparse
import sys
import zipfile
from pathlib import Path
from xml.etree import ElementTree as ET
MAIN_NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
PKGREL_NS = "http://schemas.openxmlformats.org/package/2006/relationships"
CONTENT_NS = "http://schemas.openxmlformats.org/package/2006/content-types"
ET.register_namespace("", MAIN_NS)
ET.register_namespace("", CONTENT_NS)
ET.register_namespace("", PKGREL_NS)
DANGEROUS_PREFIXES = ("=", "+", "-", "@", "\t", "\r", "\n")
def _read_xml(zip_file: zipfile.ZipFile, name: str) -> ET.Element | None:
try:
with zip_file.open(name) as file:
return ET.parse(file).getroot()
except KeyError:
return None
def _sanitize_text(text: str) -> str:
if text.startswith(DANGEROUS_PREFIXES):
return "'" + text
return text
def _sanitize_shared_strings(data: bytes) -> tuple[bytes, int]:
root = ET.fromstring(data)
changed = 0
for si in root.findall(f"{{{MAIN_NS}}}si"):
text_nodes = list(si.iterfind(f".//{{{MAIN_NS}}}t"))
if not text_nodes:
continue
current = "".join(node.text or "" for node in text_nodes)
updated = _sanitize_text(current)
if updated == current:
continue
first_node = text_nodes[0]
first_node.text = "'" + (first_node.text or "")
changed += 1
return ET.tostring(root, encoding="utf-8", xml_declaration=True), changed
def _sanitize_inline_strings(data: bytes) -> tuple[bytes, int]:
root = ET.fromstring(data)
changed = 0
for inline_str in root.iterfind(f".//{{{MAIN_NS}}}is"):
text_nodes = list(inline_str.iterfind(f".//{{{MAIN_NS}}}t"))
if not text_nodes:
continue
current = "".join(node.text or "" for node in text_nodes)
updated = _sanitize_text(current)
if updated == current:
continue
text_nodes[0].text = "'" + (text_nodes[0].text or "")
changed += 1
return ET.tostring(root, encoding="utf-8", xml_declaration=True), changed
def _strip_external_links_from_workbook(data: bytes) -> bytes:
root = ET.fromstring(data)
for node in list(root):
if node.tag == f"{{{MAIN_NS}}}externalReferences":
root.remove(node)
return ET.tostring(root, encoding="utf-8", xml_declaration=True)
def _strip_external_link_relationships(data: bytes) -> bytes:
root = ET.fromstring(data)
for rel in list(root):
rel_type = rel.attrib.get("Type", "")
if rel_type.endswith("/externalLink"):
root.remove(rel)
return ET.tostring(root, encoding="utf-8", xml_declaration=True)
def _strip_external_link_content_types(data: bytes) -> bytes:
root = ET.fromstring(data)
for override in list(root):
part_name = override.attrib.get("PartName", "")
if part_name.startswith("/xl/externalLinks/"):
root.remove(override)
return ET.tostring(root, encoding="utf-8", xml_declaration=True)
def sanitize_workbook(input_path: Path, output_path: Path, strip_external_links: bool) -> tuple[int, int]:
dangerous_changes = 0
stripped_parts = 0
output_path.parent.mkdir(parents=True, exist_ok=True)
with zipfile.ZipFile(input_path) as source, zipfile.ZipFile(output_path, "w", compression=zipfile.ZIP_DEFLATED) as dest:
for info in source.infolist():
data = source.read(info.filename)
filename = info.filename
if strip_external_links and filename.startswith("xl/externalLinks/"):
stripped_parts += 1
continue
if filename == "xl/sharedStrings.xml":
data, changes = _sanitize_shared_strings(data)
dangerous_changes += changes
elif filename.startswith("xl/worksheets/") and filename.endswith(".xml"):
data, changes = _sanitize_inline_strings(data)
dangerous_changes += changes
elif strip_external_links and filename == "xl/workbook.xml":
data = _strip_external_links_from_workbook(data)
elif strip_external_links and filename == "xl/_rels/workbook.xml.rels":
data = _strip_external_link_relationships(data)
elif strip_external_links and filename == "[Content_Types].xml":
data = _strip_external_link_content_types(data)
dest.writestr(info, data)
return dangerous_changes, stripped_parts
def main(argv: list[str]) -> int:
parser = argparse.ArgumentParser(
description="Copy and sanitize a .xlsx/.xlsm workbook by quoting dangerous text prefixes and optionally stripping external links."
)
parser.add_argument("input_path", type=Path, help="Input .xlsx or .xlsm workbook")
parser.add_argument("output_path", type=Path, help="Output workbook path")
parser.add_argument(
"--strip-external-links",
action="store_true",
help="Remove workbook externalLinks parts and workbook references.",
)
args = parser.parse_args(argv)
if not args.input_path.exists():
print(f"File not found: {args.input_path}", file=sys.stderr)
return 2
if args.input_path.suffix.lower() not in {".xlsx", ".xlsm"}:
print("Expected a .xlsx or .xlsm file.", file=sys.stderr)
return 2
if args.output_path == args.input_path:
print("Output path must differ from input path.", file=sys.stderr)
return 2
try:
dangerous_changes, stripped_parts = sanitize_workbook(
args.input_path, args.output_path, args.strip_external_links
)
except (ET.ParseError, zipfile.BadZipFile, OSError) as exc:
print(str(exc), file=sys.stderr)
return 2
print(
f"Sanitized workbook written to {args.output_path} "
f"(dangerous text entries quoted: {dangerous_changes}, external link parts removed: {stripped_parts})"
)
return 0
if __name__ == "__main__":
raise SystemExit(main(sys.argv[1:]))
SKILL.md---
name: document-xlsx
description: "Create/edit .xlsx spreadsheets with tables, formulas, charts, validation, and workbook automation. Use when asked to generate Excel reports, models, exports, or audit spreadsheets."
allowed-tools: Bash, Read, Write, Glob, Grep
compatibility: Claude Code + Codex. Uses runtime-specific allowed-tools / argument-hint fields.
version: "1.1"
last_validated: 2026-07-11
---
# Document XLSX Skill - Quick Reference
This skill enables creation, editing, inspection, and safe distribution of `.xlsx` workbooks. Use it for report exports, spreadsheet models, spreadsheet QA, workbook automation, and Excel-compatible deliverables.
Modern best practices (July 2026):
- Prefer Excel Tables over loose ranges.
- Separate inputs, calculations, and outputs.
- Treat spreadsheets as software: checks, owners, change control, and review loops.
- Treat untrusted workbooks as hostile: formulas, hyperlinks, external links, hidden content, and macros all need review.
- If workbooks are shared externally, include accessibility hygiene and run Excel's Accessibility Checker.
## Core Decision Rules (2026)
- First decide the runtime:
local file generation, cloud workbook automation, or workbook audit/sanitization.
- Default to table-first exports:
headers in row 1, frozen header row, autofilter, named table, bounded ranges.
- For native pivots:
use Office Scripts or Excel automation; for headless exports prefer pre-computed summary tables.
- Libraries usually write formulas, but Excel calculates them when the file opens.
If server-side computed values are required, calculate them in code and write values.
- `XlsxWriter` is write-only: it cannot open, read, or edit an existing `.xlsx` file.
If the task is "edit this workbook" rather than "create a new one," reach for `openpyxl` (or ExcelJS in Node) instead — choosing `XlsxWriter` for an edit task is a common non-expert mistake that fails immediately.
- A formula written by `openpyxl` or `XlsxWriter` has no cached result until some calculation engine (Excel, LibreOffice headless, or a session-based tool such as xlwings) opens and recalculates the file.
Reading that same file back with `openpyxl(..., data_only=True)` before any recalculation returns `None`, not the computed value — this looks like a bug but is expected behavior. If a downstream step (pandas, another script, an LLM) needs the number immediately, compute it in Python and write the literal value, or write both the formula and a plausible cached value only if you can guarantee it matches.
- ExcelJS is strong for workbook structure and styling, but it does not provide native chart generation; ExcelJS pivot-table support shipped as an experimental, limited feature only in recent 4.x releases — treat it as unstable and verify round-trip fidelity before relying on it in production.
- `openpyxl` can preserve VBA with `keep_vba=True`, but this skill does not author or execute macros.
- If ingesting untrusted workbooks with `openpyxl`, default to `keep_links=False` unless external links must be preserved.
- For very large exports (hundreds of thousands of rows or more), default `openpyxl` usage can balloon memory (a ~150MB source DataFrame has been observed using 2GB+ RAM with the default XML parser). Install `lxml` and use `Workbook(write_only=True)` for writing or `load_workbook(read_only=True)` for reading — both stream rather than build a full in-memory tree, and `lxml` alone materially cuts memory even outside those modes. Write-only workbooks can be saved exactly once; a second `save()` call raises `WorkbookAlreadySaved`, so batch all writes before saving.
- Row/column ceilings are fixed by the file format, not the library: 1,048,576 rows and 16,384 columns per worksheet. Exports approaching this need a pagination or multi-sheet strategy decided up front, not discovered at write time.
## Quick Reference
| Task | Tool/Library | Language | When to Use |
|------|--------------|----------|-------------|
| Table-first exports | XlsxWriter | Python | New `.xlsx` reports with tables, formats, and charts |
| Edit existing workbook | openpyxl | Python | Modify sheets, formulas, tables, validation, and protection |
| DataFrame export | pandas + XlsxWriter/openpyxl | Python | Data pipeline to Excel with styling and reviewable outputs |
| DataFrame export | Polars + XlsxWriter | Python | Fast dataframe pipeline with Excel output |
| Server-side workbook generation | ExcelJS | Node.js | Typed Node/TS stacks, workbook structure, styles, tables |
| Workbook ingestion | SheetJS / pandas / openpyxl | Node.js / Python | Parse existing spreadsheet data and metadata |
| Cloud automation | Office Scripts | TypeScript | Excel on the web, OneDrive/SharePoint workbooks, native pivots/tables |
| Microsoft 365 workbook API | Microsoft Graph Excel | REST | Remote workbook sessions, ranges, tables, charts, named items |
| Desktop Excel automation | xlwings | Python | Native Excel features on a machine with Excel installed |
| Workbook review | `scripts/xlsx_audit.py` | Python | Read-only QA pass before sharing or refactoring |
| Safe distribution | `scripts/xlsx_sanitize.py` | Python | Sanitize dangerous text prefixes and strip external links |
| Repeatable export | `scripts/xlsx_export_report.py` | Python | Opinionated CSV/JSON/Parquet to `.xlsx` export helper |
## When To Use This Skill
Invoke this skill when a user requests:
- Generate `.xlsx` reports, dashboards, models, or exports
- Add formulas, validation, tables, conditional formatting, or protection
- Audit an existing workbook for formulas, links, hidden sheets, or risky content
- Prepare a workbook for distribution, accessibility review, or safer ingestion
- Automate Excel features that depend on Microsoft 365 or desktop Excel
## Default Workflow
- Create:
pick local generation (`XlsxWriter`, `openpyxl`, `ExcelJS`) or cloud automation (Office Scripts, Graph, xlwings), then start from a table-first layout.
- Review:
run `python3 scripts/xlsx_audit.py workbook.xlsx --format md` and compare the results against `assets/spreadsheet-model-review-checklist.md`.
- Ship:
sanitize exported text, review external links, run Accessibility Checker, and verify behavior in Excel plus the target secondary viewer if interoperability matters.
## ASCII Flow
```text
XLSX request
|
v
Classify workbook task
|-- new export / report
|-- edit existing workbook
|-- audit / sanitize
|-- cloud or desktop automation
|
v
Choose runtime
|-- Python data pipeline -----> pandas / Polars + XlsxWriter
|-- Python workbook edits ----> openpyxl
|-- Node / TS service --------> ExcelJS
|-- M365 live workbook -------> Office Scripts or Graph Excel
|-- desktop Excel ------------> xlwings
|
v
Apply table-first structure
|-- inputs
|-- calculations
|-- outputs
|-- instructions / summary
|
v
Review formulas, links, hidden content, and accessibility
|
v
Sanitize and verify in target viewers
```
## Known Limits And Caveats
- Native pivots remain runtime-specific.
`openpyxl` and `XlsxWriter` still do not create native pivot tables.
- Google Sheets and LibreOffice do not perfectly preserve all Excel features.
Validate if you rely on pivots, formulas, protection, or advanced formatting.
- Data validation is UI metadata, not a full security boundary.
Users can paste around it unless protection and process controls are in place.
- Workbook and sheet protection passwords are deterrents, not encryption.
Use file-level encryption or platform controls for sensitive data.
- External links and hyperlinks can be both a security and reproducibility problem.
Strip or document them before distribution.
- Dynamic-array and modern lookup formulas (`XLOOKUP`, `FILTER`, `UNIQUE`, `SORT`, `IFS`, `SEQUENCE`) require Microsoft 365 / current Excel.
Writing them into a workbook targeted at Excel 2019/2016, Google Sheets (partial support), or older LibreOffice will show `#NAME?` for recipients on those versions — confirm the audience's Excel channel before defaulting to these over `VLOOKUP`/`INDEX-MATCH`/nested `IF`.
- `pandas.read_excel()` picks its engine by file extension (`openpyxl` for `.xlsx`), not by what wrote the file. It never surfaces conditional formatting, data validation, protection, or charts — if the audit needs those, read the OOXML parts directly (see `scripts/xlsx_audit.py`) or use `openpyxl` directly instead of pandas.
## Decision Tree
```text
Excel Task: [What do you need?]
├─ New workbook export?
│ ├─ Python data/report pipeline → pandas/Polars + XlsxWriter
│ ├─ Edit-heavy workbook logic → openpyxl
│ └─ Node/TypeScript service → ExcelJS
│
├─ Existing workbook review?
│ ├─ Read-only audit → scripts/xlsx_audit.py
│ ├─ Data extraction → pandas or SheetJS
│ └─ Structural edits → openpyxl
│
├─ Native Excel features on a live workbook?
│ ├─ Web / M365 workbook → Office Scripts or Graph Excel
│ └─ Desktop Excel installed → xlwings
│
└─ Safe distribution?
├─ Sanitize text / strip links → scripts/xlsx_sanitize.py
├─ Accessibility review → Excel checker + accessibility reference
└─ Sensitive data → encryption + platform access controls
```
## Core Operations
### Table-First Export (Python - XlsxWriter)
```python
import pandas as pd
df = pd.DataFrame(
[
{"product": "Widget A", "qty": 100, "price": 10.0},
{"product": "Widget B", "qty": 50, "price": 25.0},
]
)
df["total"] = df["qty"] * df["price"]
with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
df.to_excel(writer, sheet_name="Sales", index=False, startrow=1)
workbook = writer.book
worksheet = writer.sheets["Sales"]
header_fmt = workbook.add_format({"bold": True, "bg_color": "#D9E2F3"})
money_fmt = workbook.add_format({"num_format": "$#,##0.00"})
worksheet.write("A1", "Sales report")
worksheet.freeze_panes(2, 0)
worksheet.autofilter(1, 0, len(df), len(df.columns) - 1)
worksheet.set_column("C:D", 14, money_fmt)
worksheet.add_table(
1,
0,
len(df) + 1,
len(df.columns) - 1,
{
"name": "SalesTable",
"style": "Table Style Medium 2",
"columns": [{"header": col, "header_format": header_fmt} for col in df.columns],
"total_row": True,
},
)
```
### Edit Existing Workbook Safely (Python - openpyxl)
```python
from openpyxl import load_workbook
wb = load_workbook("input.xlsx", keep_vba=False, keep_links=False)
ws = wb["Sales"]
ws["A1"] = "Sales report for Q1 2026"
ws.freeze_panes = "A2"
ws.sheet_view.showGridLines = True
wb.save("output.xlsx")
```
### Native Pivot Creation (Office Scripts)
```typescript
function main(workbook: ExcelScript.Workbook) {
const dataSheet = workbook.getWorksheet("Raw Data");
const sourceRange = dataSheet.getUsedRange();
const sourceTable = dataSheet.addTable(sourceRange, true);
sourceTable.setName("SalesTable");
const pivotSheet = workbook.addWorksheet("Pivot");
const pivot = workbook.addPivotTable("SalesPivot", sourceTable, pivotSheet.getRange("A1"));
pivot.addRowHierarchy(pivot.getHierarchy("Region"));
pivot.addColumnHierarchy(pivot.getHierarchy("Product"));
pivot.addDataHierarchy(pivot.getHierarchy("Revenue"));
}
```
## Do / Avoid (July 2026)
### Do
- Default to named tables, bounded ranges, and frozen headers.
- Keep assumptions explicit with value, unit, source, and date.
- Add control totals, duplicate checks, and fail-loud QA cells.
- Use descriptive sheet names and place workbook context in `A1`.
- Audit hidden sheets, external links, formulas, and named items before sharing.
### Avoid
- Raw cell-block exports when a table would work.
- Hardcoded constants buried in formulas.
- Blank worksheets, merged header cells, or color-only meaning in delivered reports.
- Preserving external links by default on untrusted ingest.
- Sharing workbooks with PII or secrets without explicit approval and controls.
## What Good Looks Like
- Structure:
clear Inputs, Calculations, Outputs, and Instructions or Summary tabs as needed.
- Data model:
named tables or ranges, no silent range drift, and no unexplained hidden sheets.
- Integrity:
no `#REF!`, broken names, stale links, or silent formula inconsistencies.
- Accessibility:
descriptive tabs, meaningful hyperlinks, proper table headers, alt text where applicable, and a clean Accessibility Checker run.
- Release hygiene:
owner named, review loop completed, and workbook sanitized or justified before distribution.
## Optional: AI / Automation
Use only when explicitly requested and policy-compliant.
- Generate first-pass formulas, charts, or summary tabs; humans verify results and edge cases.
- Produce a workbook audit summary from `scripts/xlsx_audit.py`; humans review the findings.
- Draft assumptions and glossary tabs from known source data; do not invent metrics or provenance.
## Navigation
**Resources**
- [references/excel-tables-structured-references.md](references/excel-tables-structured-references.md) - Excel Tables, totals rows, and structured formulas
- [references/excel-cloud-automation.md](references/excel-cloud-automation.md) - Office Scripts, Microsoft Graph Excel, xlwings
- [references/excel-accessibility-compliance.md](references/excel-accessibility-compliance.md) - Accessibility, Section 508, EN 301 549 considerations
- [references/excel-formulas.md](references/excel-formulas.md) - Formula reference and patterns
- [references/excel-formatting.md](references/excel-formatting.md) - Styling and conditional formatting
- [references/excel-charts.md](references/excel-charts.md) - Chart types and customization
- [references/excel-data-validation.md](references/excel-data-validation.md) - Dropdowns, input constraints, cascading validation
- [references/excel-pivot-tables.md](references/excel-pivot-tables.md) - Pivot workarounds and runtime-specific options
- [references/excel-security-protection.md](references/excel-security-protection.md) - Protection, links, injection prevention
- [data/sources.json](data/sources.json) - Current vendor and standards links
**Scripts**
- `python3 scripts/xlsx_audit.py workbook.xlsx --format md`
- `python3 scripts/xlsx_export_report.py input.csv output.xlsx`
- `python3 scripts/xlsx_sanitize.py input.xlsx output.xlsx --strip-external-links`
**Templates**
- [assets/financial-report.md](assets/financial-report.md) - Financial statement template
- [assets/data-dashboard.md](assets/data-dashboard.md) - Dashboard with charts and KPIs
- [assets/spreadsheet-model-review-checklist.md](assets/spreadsheet-model-review-checklist.md) - Workbook QA checklist
**Related Skills**
- [../document-pdf/SKILL.md](../document-pdf/SKILL.md) - PDF generation from spreadsheet data
- [../ai-ml-data-science/SKILL.md](../ai-ml-data-science/SKILL.md) - Data analysis and dataframe workflows
- [../data-sql-optimization/SKILL.md](../data-sql-optimization/SKILL.md) - Database-to-workbook pipelines
## Fact-Checking
- Use web search/web fetch to verify current external facts, versions, deadlines, regulations, or platform behavior before final answers.
- Prefer primary sources and stable vendor docs over blog posts.
- If a Microsoft Learn landing page is session-dependent, prefer a retrievable API/reference page for the source list.
## Learnings Loop
Before applying this skill on a non-trivial task, read `learnings.consolidated.md` in this directory (and `learnings.md` if present).
After applying it, if you encountered a pattern worth remembering, a mistake worth preventing, or a domain fact that surprised you, append one dated bullet to `learnings.md` via `agents-skills-feedback-loop/scripts/append_learning.py`. Do not modify `SKILL.md` itself.