Spreadsheet System

A comprehensive skill for creating, editing, analyzing, and visualizing spreadsheet data. Covers Excel (XLSX) file operations, formula creation, formatting, chart generation, and pivot-table-like analysis using Python libraries.

When to Use This Skill

Choose this skill when:

• Creating formatted Excel workbooks with formulas and charts
• Analyzing spreadsheet data with pivot tables and aggregations
• Building automated report generation that outputs XLSX files
• Reading and processing existing Excel files
• Creating dashboards and visualizations in spreadsheet format

Consider alternatives when:

• Working with CSV data → use pandas directly
• Creating PDF reports → use a PDF creation skill
• Building web dashboards → use a chart/visualization library
• Working with Google Sheets → use Google Sheets API

Quick Start

Copy
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.chart import BarChart, Reference

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Sales Report"

# Header styling
header_font = Font(bold=True, size=12, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

headers = ['Product', 'Q1', 'Q2', 'Q3', 'Q4', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')

# Data with formulas
data = [
    ['Widget A', 1200, 1500, 1800, 2100],
    ['Widget B', 800, 900, 1100, 1300],
    ['Widget C', 500, 600, 700, 850],
]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)
    # SUM formula for total
    ws.cell(row=row_idx, column=6, value=f'=SUM(B{row_idx}:E{row_idx})')

# Add chart
chart = BarChart()
chart.title = "Quarterly Sales"
chart.y_axis.title = "Units"
data_ref = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'A7')

wb.save('sales_report.xlsx')

Core Concepts

Spreadsheet Operations

Operation	Library	Method
Create workbook	openpyxl	Workbook()
Read workbook	openpyxl	load_workbook()
Cell formatting	openpyxl.styles	Font, Fill, Alignment, Border
Formulas	openpyxl	Set cell value to formula string
Charts	openpyxl.chart	BarChart, LineChart, PieChart
Conditional formatting	openpyxl	ws.conditional_formatting.add()
Data analysis	pandas	pd.read_excel(), pivot_table()
Merging cells	openpyxl	ws.merge_cells('A1:D1')

Data Analysis with pandas

Copy
import pandas as pd

# Read and analyze
df = pd.read_excel('data.xlsx', sheet_name='Sales')

# Pivot table
pivot = df.pivot_table(
    values='Revenue',
    index='Region',
    columns='Quarter',
    aggfunc='sum',
    margins=True
)

# Write formatted output
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
    pivot.to_excel(writer, sheet_name='Pivot')
    df.describe().to_excel(writer, sheet_name='Summary')

Conditional Formatting

Copy
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule

# Highlight cells above threshold
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws.conditional_formatting.add(
    'B2:E4',
    CellIsRule(operator='greaterThan', formula=['1500'], fill=red_fill)
)

# Color scale (green to red)
ws.conditional_formatting.add(
    'B2:E4',
    ColorScaleRule(
        start_type='min', start_color='63BE7B',
        end_type='max', end_color='F8696B'
    )
)

Configuration

Parameter	Type	Default	Description
engine	string	'openpyxl'	Library: openpyxl or xlsxwriter
defaultFont	string	'Calibri'	Default workbook font
defaultFontSize	number	11	Default font size
dateFormat	string	'YYYY-MM-DD'	Date cell format
numberFormat	string	'#,##0.00'	Number cell format
chartStyle	number	10	Default chart style

Best Practices

1. Use openpyxl for reading/writing, xlsxwriter for creation-only — openpyxl supports both reading and writing existing files. xlsxwriter is write-only but produces smaller files and supports more chart types.

2. Apply formatting through styles, not individual cell properties — Define named styles and apply them to cells. This ensures consistency and makes style changes easy: update the style definition, not every cell.

3. Use pandas for data analysis, openpyxl for formatting — Read data with pd.read_excel(), analyze with pandas methods, then write back to Excel with openpyxl for formatting that pandas can't provide.

4. Always set column widths based on content — Auto-fit column widths by measuring the longest value in each column. Default column widths make spreadsheets hard to read.

5. Test formulas in Excel after generation — Formula syntax must match Excel's requirements exactly. Test generated files in Excel to verify formulas calculate correctly and don't show #REF! or #VALUE! errors.

Common Issues

Formulas show as text instead of calculating — Formulas must start with = and use Excel function names (not Python). Set the cell's data_type to formula or ensure the value string starts with =.

Large files slow to generate — openpyxl keeps the entire workbook in memory. For very large files, use write_only mode: Workbook(write_only=True). This streams data to disk instead of buffering.

Date values appear as numbers — Excel stores dates as serial numbers. Apply a date format to cells containing dates: cell.number_format = 'YYYY-MM-DD'. Without explicit formatting, dates display as integers.