xlsx-builder

XLSX Builder: NAPA-Branded Excel Spreadsheets

Create professional Excel workbooks with NAPA brand compliance using openpyxl.

Before You Start

1. Read brand-standards skill for colors, fonts, and tone
2. Check references/template-catalog.md for spreadsheet concept templates
3. Choose a spreadsheet type from the concept catalog below

Spreadsheet Type Concepts

Concept	Use When	Key Sheets/Features
Project Toolkit	Comprehensive project management	Plan, RASCI, Stakeholders, Risk Register, Status
RAID Log	Risk/Assumption/Issue/Decision tracking	Risks, Assumptions, Issues, Decisions (each a tab)
Gap Analysis	Current vs. desired state comparison	Categories, Current State, Desired State, Gap, Actions
SWOT Analysis	Strategic assessment	Strengths, Weaknesses, Opportunities, Threats matrix
Fishbone Diagram	Root cause analysis	Categories (People, Process, Technology, Policy), Causes
Process Map	Workflow documentation	Steps, Owners, Inputs, Outputs, Systems, SLAs
Test Script (UAT)	User acceptance testing	Test Cases, Steps, Expected/Actual Results, Pass/Fail
Business Requirements	Requirements gathering	ID, Requirement, Priority, Source, Status, Notes
Training Plan	Enablement and rollout	Audience, Topics, Schedule, Materials, Completion Status
Catalog Fieldsheet	ServiceNow catalog item design	Fields, Types, Mandatory, Default Values, Dependencies
Tracker/Dashboard	Progress and KPI tracking	Metrics, Targets, Actuals, Trends, Charts
Issue Chart	Problem tracking	Issues, Severity, Owner, Status, Resolution, Dates

NAPA Spreadsheet Standards

Color Constants

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter

BLUE_FILL = PatternFill(start_color='005195', end_color='005195', fill_type='solid')
ORANGE_FILL = PatternFill(start_color='FFA300', end_color='FFA300', fill_type='solid')
LIGHT_GRAY_FILL = PatternFill(start_color='F5F5F5', end_color='F5F5F5', fill_type='solid')
LIGHT_BLUE_FILL = PatternFill(start_color='E8F0FE', end_color='E8F0FE', fill_type='solid')
WHITE_FILL = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')
GREEN_FILL = PatternFill(start_color='2E8B57', end_color='2E8B57', fill_type='solid')
RED_FILL = PatternFill(start_color='CC0000', end_color='CC0000', fill_type='solid')

HEADER_FONT = Font(name='Arial', size=11, bold=True, color='FFFFFF')
BODY_FONT = Font(name='Arial', size=10, color='333333')
TITLE_FONT = Font(name='Arial', size=14, bold=True, color='005195')
KPI_FONT = Font(name='Arial', size=18, bold=True, color='005195')
ACCENT_FONT = Font(name='Arial', size=11, bold=True, color='FFA300')
LINK_FONT = Font(name='Arial', size=10, color='005195', underline='single')

THIN_BORDER = Border(
    left=Side(style='thin', color='CCCCCC'),
    right=Side(style='thin', color='CCCCCC'),
    top=Side(style='thin', color='CCCCCC'),
    bottom=Side(style='thin', color='CCCCCC')
)

Workbook Skeleton

wb = Workbook()
ws = wb.active
ws.title = "Main"

# Set column widths
for col in range(1, 10):
    ws.column_dimensions[get_column_letter(col)].width = 18

# Freeze header row
ws.freeze_panes = 'A2'

# Page setup
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.fitToWidth = 1
ws.print_title_rows = '1:1'

Helper Functions

Core Styling

def style_header_row(ws, row=1, max_col=None):
    """Apply NAPA Blue header styling to a row."""
    if max_col is None:
        max_col = ws.max_column
    for col in range(1, max_col + 1):
        cell = ws.cell(row=row, column=col)
        cell.font = HEADER_FONT
        cell.fill = BLUE_FILL
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        cell.border = THIN_BORDER

def style_data_rows(ws, start_row=2, max_col=None):
    """Apply alternating row styling with borders."""
    if max_col is None:
        max_col = ws.max_column
    for row in range(start_row, ws.max_row + 1):
        for col in range(1, max_col + 1):
            cell = ws.cell(row=row, column=col)
            cell.font = BODY_FONT
            cell.border = THIN_BORDER
            cell.alignment = Alignment(vertical='center', wrap_text=True)
            if (row - start_row) % 2 == 1:
                cell.fill = LIGHT_GRAY_FILL

Sheet and Title Helpers

def create_sheet_with_headers(wb, name, headers):
    """Create a new sheet with styled headers and frozen panes."""
    ws = wb.create_sheet(title=name)
    for col, header in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=header)
    ws.freeze_panes = 'A2'
    style_header_row(ws, max_col=len(headers))
    for col in range(1, len(headers) + 1):
        ws.column_dimensions[get_column_letter(col)].width = 20
    # Page setup
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
    ws.page_setup.fitToWidth = 1
    ws.print_title_rows = '1:1'
    return ws

def add_title_row(ws, title, row=1):
    """Add a merged title row with NAPA styling."""
    ws.merge_cells(start_row=row, start_column=1,
                   end_row=row, end_column=ws.max_column or 5)
    cell = ws.cell(row=row, column=1)
    cell.value = title
    cell.font = TITLE_FONT
    cell.alignment = Alignment(horizontal='left', vertical='center')

KPI and Data Helpers

def add_kpi_cell(ws, row, col, value, label=None):
    """Add a large KPI number with optional label below."""
    cell = ws.cell(row=row, column=col)
    cell.value = value
    cell.font = KPI_FONT
    cell.alignment = Alignment(horizontal='center', vertical='center')
    if label:
        label_cell = ws.cell(row=row + 1, column=col)
        label_cell.value = label
        label_cell.font = Font(name='Arial', size=9, color='666666')
        label_cell.alignment = Alignment(horizontal='center')

def auto_fit_columns(ws, min_width=10, max_width=50, padding=2):
    """Auto-fit column widths based on content length."""
    for col in range(1, ws.max_column + 1):
        max_len = 0
        letter = get_column_letter(col)
        for row in range(1, ws.max_row + 1):
            cell = ws.cell(row=row, column=col)
            if cell.value:
                max_len = max(max_len, len(str(cell.value)))
        width = min(max(max_len + padding, min_width), max_width)
        ws.column_dimensions[letter].width = width

def format_number_column(ws, col, start_row=2, fmt='#,##0'):
    """Apply number formatting to a column (e.g., '#,##0', '0.0%', '$#,##0')."""
    for row in range(start_row, ws.max_row + 1):
        cell = ws.cell(row=row, column=col)
        cell.number_format = fmt

Conditional Formatting

def add_status_formatting(ws, col_letter, start_row=2, end_row=None):
    """Add conditional formatting for status columns."""
    from openpyxl.formatting.rule import CellIsRule
    if end_row is None:
        end_row = ws.max_row
    range_str = f'{col_letter}{start_row}:{col_letter}{end_row}'
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"Complete"'],
                   fill=PatternFill(start_color='D4EDDA', end_color='D4EDDA', fill_type='solid')))
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"In Progress"'],
                   fill=PatternFill(start_color='FFF3CD', end_color='FFF3CD', fill_type='solid')))
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"Blocked"'],
                   fill=PatternFill(start_color='F8D7DA', end_color='F8D7DA', fill_type='solid')))

def add_priority_formatting(ws, col_letter, start_row=2, end_row=None):
    """Add conditional formatting for priority columns."""
    from openpyxl.formatting.rule import CellIsRule
    if end_row is None:
        end_row = ws.max_row
    range_str = f'{col_letter}{start_row}:{col_letter}{end_row}'
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"Critical"'],
                   fill=PatternFill(start_color='F8D7DA', end_color='F8D7DA', fill_type='solid')))
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"High"'],
                   fill=PatternFill(start_color='FFE0B2', end_color='FFE0B2', fill_type='solid')))
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"Medium"'],
                   fill=PatternFill(start_color='FFF3CD', end_color='FFF3CD', fill_type='solid')))
    ws.conditional_formatting.add(range_str,
        CellIsRule(operator='equal', formula=['"Low"'],
                   fill=PatternFill(start_color='D4EDDA', end_color='D4EDDA', fill_type='solid')))

Multi-Sheet Workbook Pattern

wb = Workbook()
wb.remove(wb.active)  # Remove default sheet

# Create structured sheets
summary = create_sheet_with_headers(wb, "Summary", ["Metric", "Value", "Target", "Status"])
details = create_sheet_with_headers(wb, "Details", ["ID", "Item", "Category", "Owner", "Status", "Due Date", "Notes"])
risks = create_sheet_with_headers(wb, "Risks", ["Risk ID", "Description", "Probability", "Impact", "Mitigation", "Owner"])

# Add data, apply formatting, save
wb.save("output.xlsx")

On-the-Fly Template Swapping

To use a concept from the template library:

1. Check references/template-catalog.md for the spreadsheet type
2. Study the sheet structure, column headers, and data patterns
3. Apply NAPA branding (Blue headers, alternating rows, Arial font)
4. Adapt formulas and conditional formatting to fit the use case

Mandatory Rules

1. Font: Arial (never Calibri)
2. Header row: NAPA Blue fill, white bold 11pt text
3. Body rows: Arial 10pt, alternating white / Light Gray
4. Freeze panes on header row
5. Orange accent for KPIs and totals
6. Status columns get conditional formatting (green/yellow/red)
7. Print setup: landscape, fit to width, repeat header row
8. Auto-fit columns when content length varies significantly
9. Number formatting: Use #,##0 for counts, 0.0% for percentages, $#,##0 for currency

Changelog

v2.0.0 (2026-03-03)

• Added auto_fit_columns() helper for dynamic column sizing
• Added format_number_column() helper for consistent number formatting
• Added add_priority_formatting() for Critical/High/Medium/Low columns
• Added LINK_FONT constant for hyperlink styling
• create_sheet_with_headers() now includes page setup (landscape, fit-to-width, repeat headers)
• Aligned body text color to #333333 (consistent with docx-builder)

v1.0.0 (2026-02-16)

• Initial release