Excel Generator

Excel Generator Skill

Goal

Make the user able to use the Excel immediately and gain insights upon opening.

Core Principle

Enrich visuals as much as possible, while ensuring content clarity and not adding cognitive burden. Every visual element should be meaningful and purposeful—serving the content, not decorating it.

Part 1: User Needs & Feature Matching

Before creating any Excel, think through:

1. What does the user need? — Not "an Excel file", but what problem are they solving?
2. What can I provide? — Which features will help them?
3. How to match? — Select the right combination for this specific scenario.

Feature ↔ User Value Pairs

Help Users「Understand Data」

Help Users「Find What Matters」

Help Users「Save Time」

Help Users「Use Directly」

Help Users「Trust the Data」

Help Users「Gain Insights」

Part 2: Four-Layer Implementation

Layer 1: Structure (How It's Organized)

Goal: Logical, easy to navigate, user finds what they need immediately.

Sheet Organization

Information Architecture

• Overview sheet must stand alone: User should understand the main message without opening other sheets
• Progressive disclosure: Summary first, details available for those who want to dig deeper
• Consistent structure across sheets: Same layout patterns, same starting positions

Layout Rules

Chart placement:

• Default: below all tables, left-aligned with content
• Alternative: right of a single related table
• Charts must never overlap each other or tables

Standalone Text Rows

For rows with a single text cell (titles, descriptions, notes, bullet points), text will naturally extend into empty cells to the right. However, text is clipped if right cells contain any content (including spaces).

Decision logic:

Technical note: Fill and border alone do NOT block text overflow—only actual cell content (including space characters) blocks it.

Navigation

For files with 3+ sheets, include a Sheet Index on Overview:

# Sheet Index with hyperlinks
ws['B5'] = "CONTENTS"
ws['B5'].font = Font(name=SERIF_FONT, size=14, bold=True, color=THEME['accent'])

sheets = ["Overview", "Data", "Analysis"]
for i, sheet_name in enumerate(sheets, start=6):
    cell = ws.cell(row=i, column=2, value=sheet_name)
    cell.hyperlink = f"#'{sheet_name}'!A1"
    cell.font = Font(color=THEME['accent'], underline='single')

Layer 2: Information (What They Learn)

Goal: Accurate, complete, insightful—user gains knowledge, not just data.

Number Formats

Critical rules:

1. Every numeric cell must have number_format set — both input values AND formula results
2. Same column = same precision — never mix 0.1074 and 1.0 in one column
3. Formula results have no default format — they display raw precision unless explicitly formatted

Common mistake: Setting format only for input cells, forgetting formula cells.

# WRONG: Formula cell without number_format
ws['C10'] = '=C7-C9'  # Will display raw precision like 14.123456789

# CORRECT: Always set number_format for formula cells
ws['C10'] = '=C7-C9'
ws['C10'].number_format = '#,##0.0'  # Displays as 14.1

# Best practice: Define format by column/data type, apply to ALL cells
for row in range(data_start, data_end + 1):
    cell = ws.cell(row=row, column=value_col)
    cell.number_format = '#,##0.0'  # Applies to both values and formulas

Data Context

Every data set needs context:

Key Insights

For analytical content, don't just present data—tell the user what it means:

ws['B20'] = "KEY INSIGHTS"
ws['B20'].font = Font(name=SERIF_FONT, size=14, bold=True, color=THEME['accent'])

insights = [
    "• Revenue grew 23% YoY, driven primarily by APAC expansion",
    "• Top 3 customers account for 45% of total revenue",
    "• Q4 showed strongest performance across all metrics"
]
for i, insight in enumerate(insights, start=21):
    ws.cell(row=i, column=2, value=insight)

Content Completeness

Layer 3: Visual (What They See)

Goal: Professional appearance, immediate sense of value, visuals serve content.

Essential Setup

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

# Hide gridlines for cleaner look
ws.sheet_view.showGridLines = False

# Left margin
ws.column_dimensions['A'].width = 3

Theme System

Choose ONE theme per workbook. All visual elements derive from the theme color.

Available Themes (select based on context, or let user specify):

Theme Configuration:

# === THEME CONFIGURATION ===
THEMES = {
    'elegant_black': {
        'primary': '2D2D2D',
        'light': 'E5E5E5',
        'accent': '2D2D2D',
        'chart_colors': ['2D2D2D', '4A4A4A', '6B6B6B', '8C8C8C', 'ADADAD', 'CFCFCF'],
    },
    'corporate_blue': {
        'primary': '1F4E79',
        'light': 'D6E3F0',
        'accent': '1F4E79',
        'chart_colors': ['1F4E79', '2E75B6', '5B9BD5', '9DC3E6', 'BDD7EE', 'DEEBF7'],
    },
    # ... other themes follow same pattern
}

THEME = THEMES['elegant_black']  # Default
SERIF_FONT = 'Source Serif Pro'   # or 'Georgia' as fallback
SANS_FONT = 'Source Sans Pro'     # or 'Calibri' as fallback

How Theme Colors Apply:

Semantic Colors

For data meaning (independent of theme):

Row Highlight Colors

For row-level emphasis. Use high-lightness tints (85-95% lightness) for subtle distinction.

Rule: Same semantic = same color. Different semantic = different color.

HIGHLIGHT = {
    'emphasis': 'E6F3FF',
    'section': 'FFF3E0',
    'input': 'FFFDE7',
    'special': 'FFF9C4',
    'success': 'E8F5E9',
    'warning': 'FFCCBC',
}

Typography

Use serif + sans-serif pairing. Serif for hierarchy (titles, headers); sans-serif for data (tabular figures).

Font Pairings (in preference order):

Hierarchy:

# Document title
ws['B2'].font = Font(name=SERIF_FONT, size=18, bold=True, color=THEME['primary'])

# Section header
ws['B6'].font = Font(name=SERIF_FONT, size=14, bold=True, color=THEME['primary'])

# Table header
header_font = Font(name=SERIF_FONT, size=10, bold=True, color='FFFFFF')

# Data cells
data_font = Font(name=SANS_FONT, size=11)

# Notes
notes_font = Font(name=SANS_FONT, size=10, italic=True, color='666666')

Data Block Definition

A Data Block is a group of continuous, non-empty rows that form a visual unit requiring borders. Data Blocks are separated by empty rows.

Identification rules:

1. Scan from Section Header downward
2. Non-empty row starts a Data Block
3. Empty row ends the current Data Block
4. Each Data Block gets its own outer frame

Data Block types:

Example recognition:

Row 5: Section Header "INCOME STATEMENT"   → No border (not a Data Block)
Row 6: Empty                               → Separator
Row 7: Header (Item, Year1, Year2...)      → Data Block 1 starts
Row 8: Revenue, 47061, 48943...            
Row 9: Growth Rate, 4.0%, 3.5%...          → Data Block 1 ends
Row 10: Empty                              → Separator
Row 11: EBITDA, 12121, 12627...            → Data Block 2 starts (no header)
Row 12: EBITDA Margin, 25.8%, 25.8%...     → Data Block 2 ends
Row 13: Empty                              → Separator
Row 14: D&A, 1200, 1224...                 → Data Block 3 starts (no header)
Row 15: EBIT, 10921, 11404...              
Row 16: EBIT Margin, 23.2%, 23.3%...       → Data Block 3 ends

Result: 3 separate Data Blocks, each with its own outer frame.

Border Rules

Recommended style: Horizontal-only — cleaner, more modern.

Each Data Block must have:

Critical: Every cell in the Data Block must have its border set. Do not only set header and label cells—data cells need borders too.

# Border definitions
outer_border = Side(style='thin', color='D1D1D1')
header_bottom = Side(style='medium', color=THEME['primary'])
inner_horizontal = Side(style='thin', color='D1D1D1')
no_border = Side(style=None)

def apply_data_block_borders(ws, start_row, end_row, start_col, end_col, has_header=True):
    """
    Apply borders to a Data Block.
    Every cell must be processed—not just headers and labels.
    """
    for row in range(start_row, end_row + 1):
        for col in range(start_col, end_col + 1):
            cell = ws.cell(row=row, column=col)
            
            # Left/Right: outer frame
            left = outer_border if col == start_col else no_border
            right = outer_border if col == end_col else no_border
            
            # Top: outer for first row, inner horizontal for others
            top = outer_border if row == start_row else inner_horizontal
            
            # Bottom: header_bottom for header, outer for last row, inner for middle
            if has_header and row == start_row:
                bottom = header_bottom
            elif row == end_row:
                bottom = outer_border
            else:
                bottom = inner_horizontal
            
            cell.border = Border(left=left, right=right, top=top, bottom=bottom)

Non-Data-Block elements (titles, section headers, standalone text, notes): No border.

Alignment

Headers: Center.

Data cells:

Vertical: Always center (including wrapped text).

# Left-aligned text with padding
cell.alignment = Alignment(horizontal='left', vertical='center', indent=1)

# Numbers
cell.alignment = Alignment(horizontal='right', vertical='center')

# Wrapped text
cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True, indent=1)

Column Width

Calculation scope: Only Data Block cells. Exclude standalone text rows, section headers, and notes.

Formula: max(max_content_length_in_data_blocks + padding, minimum)

Design constraint: Same column across different Data Blocks should serve similar roles. Column width is determined by the widest content across all Data Blocks in that column.

Standalone text rows: Do NOT include in column width calculation. Let text extend naturally or use wrap/merge.

def calculate_column_width(ws, col, data_block_ranges):
    """
    Calculate column width based only on Data Block content.
    Standalone text rows are excluded.
    
    data_block_ranges: list of (start_row, end_row) tuples
    """
    max_len = 0
    is_numeric = True
    
    for start_row, end_row in data_block_ranges:
        for row in range(start_row, end_row + 1):
            cell = ws.cell(row=row, column=col)
            if cell.value:
                # Get formatted display length
                display_value = str(cell.value)
                max_len = max(max_len, len(display_value))
                if not isinstance(cell.value, (int, float)):
                    is_numeric = False
    
    padding = 6 if is_numeric else 4
    minimum = 14 if is_numeric else 15
    
    return max(max_len + padding, minimum)

Row Height

Must set manually—openpyxl does not auto-adjust.

ws.row_dimensions[2].height = 35   # Title
ws.row_dimensions[5].height = 25   # Section header
ws.row_dimensions[7].height = 30   # Table header
ws.row_dimensions[8].height = 18   # Data row

Merge Cells

When to merge: Merge when text would otherwise be clipped at the column boundary. If text fits within a single column, merging is optional.

Common cases requiring merge:

• Titles and subtitles (usually span full content width)
• Section headers (span width of related table)
• KEY INSIGHTS bullet points (long sentences)
• Notes and disclaimers (multi-sentence text)

Section header with background — merge width must match table width:

# Section header spans same width as table below
last_col = 8  # Must match table's last column
ws.merge_cells(f'B6:{get_column_letter(last_col)}6')
ws['B6'] = "KEY METRICS"
ws['B6'].font = Font(name=SERIF_FONT, size=14, bold=True, color=THEME['primary'])
ws['B6'].fill = PatternFill(start_color=THEME['light'], end_color=THEME['light'], fill_type='solid')
ws['B6'].alignment = Alignment(horizontal='left', vertical='center')

Data Visualization

Data Bars:

from openpyxl.formatting.rule import DataBarRule

rule = DataBarRule(start_type='min', end_type='max', color=THEME['primary'])
ws.conditional_formatting.add('C5:C50', rule)

Color Scale:

from openpyxl.formatting.rule import ColorScaleRule

rule = ColorScaleRule(
    start_type='min', start_color='FFFFFF',
    end_type='max', end_color=THEME['primary']
)
ws.conditional_formatting.add('D5:D50', rule)

Charts:

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Revenue by Region"
data = Reference(ws, min_col=3, min_row=4, max_row=10)
cats = Reference(ws, min_col=2, min_row=5, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# Apply theme colors to chart series
for i, series in enumerate(chart.series):
    series.graphicalProperties.solidFill = THEME['chart_colors'][i % len(THEME['chart_colors'])]

ws.add_chart(chart, "F5")

Layer 4: Interaction (How They Interact)

Goal: Usable, flexible, user can explore and work with the data.

Freeze Panes

For tables with >10 rows:

ws.freeze_panes = 'B5'  # Freeze below header row

Filters

For tables with >20 rows:

ws.auto_filter.ref = f"B4:{get_column_letter(last_col)}{last_row}"

Hyperlinks

# Internal link
cell.hyperlink = "#'Data'!A1"
cell.font = Font(color=THEME['accent'], underline='single')

# External link
cell.hyperlink = "https://example.com"
cell.font = Font(color=THEME['accent'], underline='single')

Sorting

Pre-sort by most meaningful dimension:

• Rankings → by value descending
• Time series → by date ascending
• Alphabetical → when no clear priority

df = df.sort_values('revenue', ascending=False)

Editability

• Use formulas when users may update inputs
• Use hardcoded values when data is final
• Keep formulas simple; document complex ones