xlsx

Spreadsheet Files

Overview

Use this skill whenever the primary deliverable is a spreadsheet file or the user explicitly wants work done on spreadsheet data in place. Prefer openpyxl when workbook structure, formulas, styling, or multiple sheets must be preserved. Use pandas for analysis and reshaping, then write the final workbook carefully instead of treating the spreadsheet as a disposable export.

Non-Negotiable Rules

• Use formulas in the workbook instead of calculating values in Python and hardcoding the result.
• Preserve existing workbook structure unless the user asked for destructive cleanup.
• Recalculate and verify formulas after edits with scripts/recalc.py.
• Keep formatting readable: sensible column widths, aligned headers, and explicit number formats.
• If editing a financial model, keep assumptions visually distinct from formulas and outputs.

Choose the Workflow

Existing workbook

Use openpyxl when the file already exists and you must preserve:

• formulas,
• formatting,
• named sheets,
• comments,
• workbook-level structure.

New workbook or pure analysis

Use pandas for:

• data cleaning,
• joins,
• aggregation,
• reshaping.

Then write the final spreadsheet with either:

• pandas if a simple flat export is enough, or
• openpyxl if formulas, formatting, or multiple sheets matter.

Core Workflow

1. Inspect the workbook or source data.
2. Decide whether the task is analysis-only, structure-preserving edit, or workbook creation.
3. Make the edits.
4. Recalculate formulas with python scripts/recalc.py <excel_file>.
5. Fix any reported workbook errors before handoff.

Reading and Analyzing Data

Pandas quick read

import pandas as pd

df = pd.read_excel("file.xlsx")
all_sheets = pd.read_excel("file.xlsx", sheet_name=None)

print(df.head())
print(df.info())
print(df.describe(include="all"))

Openpyxl structure-preserving read

from openpyxl import load_workbook

wb = load_workbook("existing.xlsx")
print(wb.sheetnames)

ws = wb["Sheet1"]
print(ws["A1"].value)