From Office Documents
Creates professional Excel spreadsheets with formatting, charts, conditional highlighting, multi-sheet workbooks, and formulas using openpyxl, pandas, and xlsxwriter.
How this skill is triggered — by the user, by Claude, or both
Slash command
/office-docs:excel-xlsxThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Expert skill for creating professional Excel spreadsheets. Core principle: enrich visuals while ensuring content clarity. Every visual element should serve the content, not decorate it.
Expert skill for creating professional Excel spreadsheets. Core principle: enrich visuals while ensuring content clarity. Every visual element should serve the content, not decorate it.
pip install openpyxl pandas xlsxwriter
Before creating any Excel, think through what the user needs and match features to user value.
| Feature | User Value | When to Use |
|---|---|---|
| Bar/Column Chart | Compare values across categories | Categorical comparisons (sales by region, scores by team) |
| Line Chart | See trends over time | Time series data (monthly revenue, daily users) |
| Pie Chart | See proportions of a whole | 3-6 categories that sum to 100% |
| Data Bars | Quick visual comparison within cells | Numeric columns where relative size matters |
| Color Scale | Spot highs and lows at a glance | Dense numeric data (heatmap effect) |
| Sparklines | See per-row trends without a full chart | When each row has its own time series |
| Feature | User Value | When to Use |
|---|---|---|
| Pre-sorting | Most important data visible first | Sort by key metric descending |
| Conditional Highlighting | Attention drawn to outliers | Values above/below thresholds |
| Icon Sets | Quick status assessment | Traffic lights for KPI status |
| Bold/Color Emphasis | Key rows stand out | Totals, subtotals, summary rows |
| KEY INSIGHTS Section | Immediate takeaways without analysis | Always include on Overview sheet |
| Feature | User Value | When to Use |
|---|---|---|
| Overview Sheet | Grasp everything in 30 seconds | Always (first sheet in workbook) |
| Pre-calculated Summaries | No manual calculation needed | Totals, averages, percentages |
| Consistent Number Formats | No mental parsing of formats | Apply to every numeric cell |
| Freeze Panes | Headers visible while scrolling | Tables with >10 rows |
| Sheet Index with Links | Navigate quickly between sheets | Workbooks with 3+ sheets |
| Feature | User Value | When to Use |
|---|---|---|
| Filters | Slice data without editing | Tables with >20 rows |
| Hyperlinks | Navigate to related content | Cross-sheet references, URLs |
| Print-friendly Layout | Clean output on paper | Reports intended for printing |
| Formulas not hardcoded | Users can update inputs | Calculations, projections |
| Data Validation Dropdowns | Guided data entry | Input cells with known options |
| Feature | User Value | When to Use |
|---|---|---|
| Data Source Attribution | Know where data came from | Always |
| Generation Date | Know how current data is | Always |
| Data Time Range | Know what period is covered | Time-based data |
| Professional Formatting | Perceive quality and care | Always |
| Consistent Precision | No confusing decimal variation | All numeric columns |
| Feature | User Value | When to Use |
|---|---|---|
| Comparison Columns | See differences/changes | Period-over-period data |
| Rank Column | Know relative position | Performance data |
| Grouped Summaries | See patterns by category | Data with natural groupings |
| Trend Indicators | Quick up/down/flat signal | Change metrics |
| Insight Text | Narrative explanation of data | Complex or surprising results |
| Guideline | Rule |
|---|---|
| Ideal sheet count | 3-5 sheets |
| Maximum sheets | 7 (more becomes hard to navigate) |
| First sheet | Always "Overview" or "Summary" |
| Sheet naming | Short, descriptive, no special characters |
| Sheet order | Overview > Detail > Charts > Reference |
| Element | Rule |
|---|---|
| Left margin | Column A, width 3 (visual breathing room) |
| Content start | Cell B2 (never A1) |
| Section spacing | 1 empty row between sections |
| Table spacing | 2 empty rows between tables |
| Right margin | 1 empty column after last data column |
Use standalone text rows (outside tables) for:
Do NOT put these inside data tables. They break filters and sorting.
For workbooks with 3+ sheets, add a clickable index on the Overview sheet:
def add_sheet_index(wb, overview_ws, start_row=5, col=2):
"""Add clickable sheet index to Overview sheet."""
overview_ws.cell(row=start_row, column=col, value="Sheet Index")
overview_ws.cell(row=start_row, column=col).font = Font(
name='Source Serif Pro', bold=True, size=13
)
for i, sheet_name in enumerate(wb.sheetnames):
row = start_row + 1 + i
cell = overview_ws.cell(row=row, column=col, value=sheet_name)
cell.hyperlink = f"#'{sheet_name}'!A1"
cell.font = Font(name='Source Sans Pro', color="0563C1", underline="single", size=11)
| Type | Format | Example |
|---|---|---|
| Integer | #,##0 | 1,234 |
| Decimal | #,##0.00 | 1,234.56 |
| Percentage | 0.0% | 12.3% |
| Currency (USD) | $#,##0.00 | $1,234.56 |
| Currency (EUR) | #,##0.00 "€" | 1,234.56 € |
| Date | YYYY-MM-DD | (see git history) |
| Accounting | _($* #,##0.00_) | $1,234.56 |
CRITICAL: Every numeric cell MUST have number_format set, including formula result cells.
# WRONG: formula cell has no format
cell = ws.cell(row=10, column=3)
cell.value = "=SUM(C2:C9)"
# CORRECT: formula cell has explicit format
cell = ws.cell(row=10, column=3)
cell.value = "=SUM(C2:C9)"
cell.number_format = '#,##0.00'
Always include these metadata elements, typically on the Overview sheet:
| Element | Where | Example |
|---|---|---|
| Data Source | Below title | "Source: CRM Export, Salesforce" |
| Time Range | Below source | "Period: Jan 2025 - Dec 2025" |
| Generation Date | Footer or below time range | "Generated: (see git history)" |
| Definitions | Footnotes or separate sheet | "MRR = Monthly Recurring Revenue" |
Add to the Overview sheet, below the title and before detailed data:
def add_key_insights(ws, insights: list[str], start_row=3, col=2):
"""Add KEY INSIGHTS section to a worksheet."""
cell = ws.cell(row=start_row, column=col, value="KEY INSIGHTS")
cell.font = Font(name='Source Serif Pro', bold=True, size=12, color="1A1A1A")
for i, insight in enumerate(insights):
row = start_row + 1 + i
cell = ws.cell(row=row, column=col, value=f" {insight}")
cell.font = Font(name='Source Sans Pro', size=11, color="333333")
Before finalizing, verify:
Apply to every worksheet before adding content:
def setup_worksheet(ws):
"""Essential setup for every worksheet."""
ws.sheet_view.showGridLines = False # Hide gridlines
ws.column_dimensions['A'].width = 3 # Left margin
12 built-in themes. Default: Elegant Black. Choose based on context.
| Theme | Header BG | Header Text | Accent | Best For |
|---|---|---|---|---|
| Elegant Black | 1A1A1A | FFFFFF | 2C2C2C | Default, formal reports |
| Corporate Blue | 1F4E79 | FFFFFF | 2E75B6 | Business, corporate |
| Forest Green | 1B5E20 | FFFFFF | 2E7D32 | Environmental, growth |
| Burgundy | 7B1F3A | FFFFFF | A0153E | Premium, executive |
| Slate Gray | 37474F | FFFFFF | 546E7A | Technical, engineering |
| Navy | 0D1B2A | FFFFFF | 1B3A5C | Finance, government |
| Charcoal | 2C2C2C | FFFFFF | 404040 | Minimalist |
| Deep Purple | 311B92 | FFFFFF | 4527A0 | Creative, marketing |
| Teal | 004D40 | FFFFFF | 00695C | Healthcare, wellness |
| Warm Brown | 3E2723 | FFFFFF | 5D4037 | Legal, traditional |
| Royal Blue | 1A237E | FFFFFF | 283593 | Academic, research |
| Olive | 33691E | FFFFFF | 558B2F | Agriculture, sustainability |
THEMES = {
"elegant_black": {
"header_bg": "1A1A1A",
"header_text": "FFFFFF",
"accent": "2C2C2C",
"alt_row": "F5F5F5",
"border": "D0D0D0",
"chart_colors": ["1A1A1A", "5B5B5B", "8C8C8C", "B0B0B0", "D4D4D4", "E8E8E8"],
},
"corporate_blue": {
"header_bg": "1F4E79",
"header_text": "FFFFFF",
"accent": "2E75B6",
"alt_row": "D6E4F0",
"border": "9DC3E6",
"chart_colors": ["1F4E79", "2E75B6", "5B9BD5", "9DC3E6", "BDD7EE", "DEEBF7"],
},
"forest_green": {
"header_bg": "1B5E20",
"header_text": "FFFFFF",
"accent": "2E7D32",
"alt_row": "E8F5E9",
"border": "A5D6A7",
"chart_colors": ["1B5E20", "2E7D32", "43A047", "66BB6A", "81C784", "A5D6A7"],
},
"burgundy": {
"header_bg": "7B1F3A",
"header_text": "FFFFFF",
"accent": "A0153E",
"alt_row": "FCE4EC",
"border": "E57373",
"chart_colors": ["7B1F3A", "A0153E", "C62828", "E53935", "EF5350", "E57373"],
},
"slate_gray": {
"header_bg": "37474F",
"header_text": "FFFFFF",
"accent": "546E7A",
"alt_row": "ECEFF1",
"border": "B0BEC5",
"chart_colors": ["37474F", "546E7A", "78909C", "90A4AE", "B0BEC5", "CFD8DC"],
},
"navy": {
"header_bg": "0D1B2A",
"header_text": "FFFFFF",
"accent": "1B3A5C",
"alt_row": "E3F2FD",
"border": "90CAF9",
"chart_colors": ["0D1B2A", "1B3A5C", "1565C0", "1E88E5", "42A5F5", "90CAF9"],
},
"charcoal": {
"header_bg": "2C2C2C",
"header_text": "FFFFFF",
"accent": "404040",
"alt_row": "F5F5F5",
"border": "BDBDBD",
"chart_colors": ["2C2C2C", "404040", "616161", "757575", "9E9E9E", "BDBDBD"],
},
"deep_purple": {
"header_bg": "311B92",
"header_text": "FFFFFF",
"accent": "4527A0",
"alt_row": "EDE7F6",
"border": "B39DDB",
"chart_colors": ["311B92", "4527A0", "512DA8", "673AB7", "7E57C2", "B39DDB"],
},
"teal": {
"header_bg": "004D40",
"header_text": "FFFFFF",
"accent": "00695C",
"alt_row": "E0F2F1",
"border": "80CBC4",
"chart_colors": ["004D40", "00695C", "00897B", "009688", "26A69A", "80CBC4"],
},
"warm_brown": {
"header_bg": "3E2723",
"header_text": "FFFFFF",
"accent": "5D4037",
"alt_row": "EFEBE9",
"border": "BCAAA4",
"chart_colors": ["3E2723", "5D4037", "6D4C41", "795548", "8D6E63", "BCAAA4"],
},
"royal_blue": {
"header_bg": "1A237E",
"header_text": "FFFFFF",
"accent": "283593",
"alt_row": "E8EAF6",
"border": "9FA8DA",
"chart_colors": ["1A237E", "283593", "303F9F", "3949AB", "5C6BC0", "9FA8DA"],
},
"olive": {
"header_bg": "33691E",
"header_text": "FFFFFF",
"accent": "558B2F",
"alt_row": "F1F8E9",
"border": "AED581",
"chart_colors": ["33691E", "558B2F", "689F38", "7CB342", "8BC34A", "AED581"],
},
}
def get_theme(name: str = "elegant_black") -> dict:
"""Get theme configuration by name."""
return THEMES.get(name, THEMES["elegant_black"])
| Element | Color Source |
|---|---|
| Table header background | header_bg |
| Table header text | header_text |
| Section header text | header_bg |
| Alternating row fill | alt_row |
| Borders | border |
| Chart series colors | chart_colors list |
| Accent elements (subtotals, highlights) | accent |
| Meaning | Hex | Usage |
|---|---|---|
| Positive / Growth | 2E7D32 | Positive changes, profits, growth |
| Negative / Decline | C62828 | Negative changes, losses, decline |
| Warning / Attention | F57C00 | Thresholds, warnings |
| Purpose | Hex | When to Use |
|---|---|---|
| Emphasis row | E6F3FF | Important data rows |
| Section divider | FFF3E0 | Category group headers |
| Input cell | FFFDE7 | Cells users should edit |
| Special note | FFF9C4 | Footnotes, annotations |
| Success | E8F5E9 | Achieved targets |
| Warning | FFCCBC | Below-threshold values |
Use a serif + sans-serif font pairing. Serif for headers and titles (authority, structure), sans-serif for data and body text (readability).
Recommended Pairings:
| Serif (Titles/Headers) | Sans-Serif (Data/Body) | Character |
|---|---|---|
| Source Serif Pro | Source Sans Pro | Modern professional |
| IBM Plex Serif | IBM Plex Sans | Technical, clean |
| Georgia | Calibri | Safe fallback (installed everywhere) |
Typography Hierarchy:
| Element | Font | Weight | Size | Color |
|---|---|---|---|---|
| Document title | Serif | Bold | 18-22 | header_bg from theme |
| Section header | Serif | Bold | 12-14 | header_bg from theme |
| Table header | Serif | Bold | 10-11 | header_text (on header_bg fill) |
| Data cells | Sans-Serif | Normal | 11 | 1A1A1A |
| Notes / footnotes | Sans-Serif | Normal | 9-10 | 666666 |
A "Data Block" is any rectangular range of cells that forms a logical unit: a table, a summary block, a key metrics panel. Identify data blocks by:
Use horizontal-only borders within data blocks. This creates a clean, modern look.
| Border Element | Style | Where |
|---|---|---|
| Outer frame | Thin, all 4 sides | Around the entire data block |
| Header bottom | Medium, bottom only | Below the header row |
| Internal horizontal | Thin, bottom only | Between data rows |
| Internal vertical | NONE | Never use vertical borders between columns |
from openpyxl.styles import Border, Side
def apply_data_block_borders(ws, min_row, max_row, min_col, max_col, theme_name="elegant_black"):
"""Apply horizontal-only border style to a data block.
Args:
ws: Worksheet object
min_row: First row of the block (header row)
max_row: Last row of the block
min_col: First column of the block
max_col: Last column of the block
theme_name: Theme to use for border color
"""
theme = get_theme(theme_name)
border_color = theme["border"]
thin = Side(style='thin', color=border_color)
medium = Side(style='medium', color=border_color)
no_side = Side(style=None)
for row in range(min_row, max_row + 1):
for col in range(min_col, max_col + 1):
cell = ws.cell(row=row, column=col)
# Determine border sides
top = thin if row == min_row else no_side
left = thin if col == min_col else no_side
right = thin if col == max_col else no_side
if row == min_row:
# Header row: medium bottom
bottom = medium
elif row == max_row:
# Last row: thin bottom (outer frame)
bottom = thin
else:
# Internal rows: thin bottom only
bottom = thin
cell.border = Border(top=top, bottom=bottom, left=left, right=right)
| Content Type | Horizontal | Indent | Example |
|---|---|---|---|
| Short text (< 20 chars) | Center | 0 | Status, Category |
| Long text (>= 20 chars) | Left | 1 | Description, Notes |
| Numbers | Right | 0 | 1,234.56 |
| Dates | Center | 0 | (see git history) |
| Headers | Center | 0 | Column headers |
| Percentages | Right | 0 | 12.3% |
Calculate based on content length with padding:
def calculate_column_width(ws, col_idx, min_width=8, max_width=50, padding=3):
"""Calculate optimal column width based on content.
Formula: max_content_length + padding, clamped to [min_width, max_width].
"""
max_length = 0
for row in ws.iter_rows(min_col=col_idx, max_col=col_idx):
for cell in row:
if cell.value:
cell_length = len(str(cell.value))
# Bold text needs ~10% more space
if cell.font and cell.font.bold:
cell_length = int(cell_length * 1.1)
max_length = max(max_length, cell_length)
width = max_length + padding
return max(min_width, min(width, max_width))
| Element | Height (points) |
|---|---|
| Document title | 35 |
| Section header | 25 |
| Table header | 30 |
| Standard data row | 18 |
| Wrapped text | lines x 15 + 10 |
Data Bars: Add to numeric columns for in-cell comparison:
from openpyxl.formatting.rule import DataBarRule
rule = DataBarRule(start_type='min', end_type='max',
color="5B9BD5", showValue=True)
ws.conditional_formatting.add("C2:C100", rule)
Color Scale: For heatmap effect on dense data:
from openpyxl.formatting.rule import ColorScaleRule
rule = ColorScaleRule(
start_type='min', start_color='63BE7B',
mid_type='percentile', mid_value=50, mid_color='FFEB84',
end_type='max', end_color='F8696B'
)
ws.conditional_formatting.add("B2:F20", rule)
Charts: Always use theme chart_colors for series:
from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice
theme = get_theme("corporate_blue")
# Apply chart_colors to chart series
for i, series in enumerate(chart.series):
color_idx = i % len(theme["chart_colors"])
series.graphicalProperties.solidFill = theme["chart_colors"][color_idx]
Apply when table has more than 10 rows:
# Freeze below header row (row 1 = header)
ws.freeze_panes = 'A2'
# If content starts at B5 with header in row 5
ws.freeze_panes = 'B6'
Apply when table has more than 20 rows:
ws.auto_filter.ref = f"B5:{get_column_letter(max_col)}{max_row}"
# Internal link (to another sheet)
cell.hyperlink = "#'Detail Sheet'!A1"
cell.font = Font(color="0563C1", underline="single")
# External link
cell.hyperlink = "https://example.com/report"
cell.font = Font(color="0563C1", underline="single")
| Data Type | Default Sort |
|---|---|
| Financial/performance | By key metric, descending |
| Time series | Chronological (ascending) |
| Categorical | Alphabetical or by rank |
| Mixed | By importance/relevance |
FFFDE7)from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import BarChart, PieChart, LineChart, Reference
import pandas as pd
def create_styled_workbook(data: dict, output_path: str, theme_name: str = "elegant_black"):
"""Create a professionally styled Excel workbook using the theme system."""
theme = get_theme(theme_name)
wb = Workbook()
ws = wb.active
ws.title = "Report"
setup_worksheet(ws)
# Styles from theme
header_font = Font(
name='Source Serif Pro', bold=True,
color=theme["header_text"], size=11
)
header_fill = PatternFill(
start_color=theme["header_bg"],
end_color=theme["header_bg"],
fill_type="solid"
)
data_font = Font(name='Source Sans Pro', size=11, color="1A1A1A")
# Write headers
headers = list(data.keys())
for col, header in enumerate(headers, 2): # Start at col B
cell = ws.cell(row=2, column=col, value=header)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
# Write data
max_rows = max(len(v) for v in data.values())
alt_fill = PatternFill(start_color=theme["alt_row"], end_color=theme["alt_row"], fill_type="solid")
for row in range(max_rows):
for col, header in enumerate(headers, 2):
values = data[header]
value = values[row] if row < len(values) else ""
cell = ws.cell(row=row + 3, column=col, value=value)
cell.font = data_font
if row % 2 == 1:
cell.fill = alt_fill
# Apply borders
apply_data_block_borders(ws, 2, max_rows + 2, 2, len(headers) + 1, theme_name)
# Auto-adjust column widths
for col_idx in range(2, len(headers) + 2):
width = calculate_column_width(ws, col_idx)
ws.column_dimensions[ws.cell(row=2, column=col_idx).column_letter].width = width
wb.save(output_path)
return output_path
import pandas as pd
def read_excel_smart(file_path: str, sheet_name=None):
"""Read Excel file with automatic type detection."""
if sheet_name is None:
xl = pd.ExcelFile(file_path)
sheets = {name: pd.read_excel(xl, sheet_name=name) for name in xl.sheet_names}
return sheets
return pd.read_excel(file_path, sheet_name=sheet_name)
def read_excel_with_headers(file_path: str, header_row: int = 0):
"""Read Excel with specified header row."""
return pd.read_excel(file_path, header=header_row)
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
def add_bar_chart(ws, data_range: str, title: str, position: str = "E2"):
"""Add a bar chart to worksheet."""
chart = BarChart()
chart.type = "col"
chart.title = title
chart.style = 10
data = Reference(ws, range_string=data_range)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, position)
def add_pie_chart(ws, labels_col: int, data_col: int,
min_row: int, max_row: int, title: str, position: str = "E2"):
"""Add a pie chart."""
chart = PieChart()
chart.title = title
labels = Reference(ws, min_col=labels_col, min_row=min_row, max_row=max_row)
data = Reference(ws, min_col=data_col, min_row=min_row-1, max_row=max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, position)
def add_line_chart(ws, data_range: str, title: str, position: str = "E2"):
"""Add a line chart."""
chart = LineChart()
chart.title = title
chart.style = 10
chart.y_axis.title = "Value"
chart.x_axis.title = "Period"
data = Reference(ws, range_string=data_range)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, position)
def add_formulas(ws, start_row: int, end_row: int, formula_col: int,
data_col: int, formula_type: str = "SUM"):
"""Add formulas to a column. Always set number_format on formula cells."""
col_letter = ws.cell(row=1, column=data_col).column_letter
if formula_type == "SUM":
cell = ws.cell(row=end_row+1, column=formula_col)
cell.value = f"=SUM({col_letter}{start_row}:{col_letter}{end_row})"
cell.number_format = '#,##0.00'
elif formula_type == "AVERAGE":
cell = ws.cell(row=end_row+1, column=formula_col)
cell.value = f"=AVERAGE({col_letter}{start_row}:{col_letter}{end_row})"
cell.number_format = '#,##0.00'
elif formula_type == "ROW_CALC":
for row in range(start_row, end_row+1):
cell = ws.cell(row=row, column=formula_col)
cell.value = f"=A{row}*B{row}" # Customize formula
cell.number_format = '#,##0.00'
def add_conditional_formatting(ws, cell_range: str, threshold: float):
"""Add conditional formatting with color scale and formula-based rules."""
from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill
# Color scale (green to red)
rule = ColorScaleRule(
start_type='min', start_color='63BE7B',
mid_type='percentile', mid_value=50, mid_color='FFEB84',
end_type='max', end_color='F8696B'
)
ws.conditional_formatting.add(cell_range, rule)
# Formula-based rule
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
rule = FormulaRule(formula=[f'A1>{threshold}'], fill=red_fill)
ws.conditional_formatting.add(cell_range, rule)
def dataframe_to_styled_excel(df: pd.DataFrame, output_path: str,
sheet_name: str = "Data",
theme_name: str = "elegant_black"):
"""Convert DataFrame to styled Excel using the theme system."""
theme = get_theme(theme_name)
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=1, startcol=1)
workbook = writer.book
worksheet = writer.sheets[sheet_name]
setup_worksheet(worksheet)
# Style headers
header_font = Font(name='Source Serif Pro', bold=True, color=theme["header_text"], size=11)
header_fill = PatternFill(start_color=theme["header_bg"], fill_type="solid")
for col_num, column_title in enumerate(df.columns, 2):
cell = worksheet.cell(row=2, column=col_num)
cell.font = header_font
cell.fill = header_fill
# Style data cells
data_font = Font(name='Source Sans Pro', size=11)
for row in range(3, len(df) + 3):
for col in range(2, len(df.columns) + 2):
cell = worksheet.cell(row=row, column=col)
cell.font = data_font
# Auto-fit columns
for col_idx in range(2, len(df.columns) + 2):
width = calculate_column_width(worksheet, col_idx)
col_letter = worksheet.cell(row=2, column=col_idx).column_letter
worksheet.column_dimensions[col_letter].width = width
return output_path
def create_multi_sheet_report(data_dict: dict, output_path: str,
theme_name: str = "elegant_black"):
"""Create workbook with multiple themed sheets and an Overview index."""
theme = get_theme(theme_name)
wb = Workbook()
# Create Overview sheet
overview = wb.active
overview.title = "Overview"
setup_worksheet(overview)
overview.cell(row=2, column=2, value="Report Overview")
overview.cell(row=2, column=2).font = Font(
name='Source Serif Pro', bold=True, size=18, color=theme["header_bg"]
)
# Create data sheets
for sheet_name, df in data_dict.items():
ws = wb.create_sheet(title=sheet_name[:31]) # Excel limit: 31 chars
setup_worksheet(ws)
# Write DataFrame
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 2):
for c_idx, value in enumerate(row, 2):
ws.cell(row=r_idx, column=c_idx, value=value)
# Style header row
for col in range(2, len(df.columns) + 2):
cell = ws.cell(row=2, column=col)
cell.font = Font(name='Source Serif Pro', bold=True, color=theme["header_text"])
cell.fill = PatternFill(start_color=theme["header_bg"], fill_type="solid")
# Style data with sans-serif font
for row in range(3, len(df) + 3):
for col in range(2, len(df.columns) + 2):
ws.cell(row=row, column=col).font = Font(name='Source Sans Pro', size=11)
# Add sheet index to Overview
add_sheet_index(wb, overview, start_row=5, col=2)
wb.save(output_path)
return output_path
| Task | Code |
|---|---|
| Create workbook | wb = Workbook() |
| Get active sheet | ws = wb.active |
| Create sheet | ws = wb.create_sheet("Name") |
| Set cell value | ws['A1'] = "Value" or ws.cell(1, 1, "Value") |
| Read cell | value = ws['A1'].value |
| Merge cells | ws.merge_cells('A1:D1') |
| Set column width | ws.column_dimensions['A'].width = 20 |
| Set row height | ws.row_dimensions[1].height = 30 |
| Freeze panes | ws.freeze_panes = 'A2' |
| Add filter | ws.auto_filter.ref = "A1:D100" |
| Hide gridlines | ws.sheet_view.showGridLines = False |
| Number format | cell.number_format = '#,##0.00' |
| Apply theme | theme = get_theme("corporate_blue") |
| Save | wb.save('file.xlsx') |
# Create P&L statement with theme
data = {
"Category": ["Revenue", "COGS", "Gross Profit", "OpEx", "Net Income"],
"Q1": [100000, 40000, 60000, 30000, 30000],
"Q2": [120000, 48000, 72000, 35000, 37000],
"Q3": [110000, 44000, 66000, 32000, 34000],
"Q4": [130000, 52000, 78000, 38000, 40000]
}
create_styled_workbook(data, "financial_report.xlsx", theme_name="navy")
# Summary statistics sheet + detail sheet + charts
sheets = {
"Summary": summary_df,
"Details": detail_df,
}
create_multi_sheet_report(sheets, "dashboard.xlsx", theme_name="corporate_blue")
with statement for ExcelWriteropenpyxl for .xlsx, xlrd for .xls (read-only)write_only modeelegant_black for formal reportsapply_data_block_borders() on every data table for consistent horizontal-only bordersnumber_format on EVERY numeric cell, including formula resultsnpx claudepluginhub jhamidun/claude-code-config-pack --plugin office-docsBuilds a throwaway prototype to answer a design question about UI appearance or state/logic behavior. Guides you through two branches: interactive terminal app for logic validation, or multiple UI variations for visual exploration.