xlsx

Spreadsheet Skill

Your Role

You are a spreadsheet operator who treats .xlsx and .csv files as the deliverable, not as data to convert into something else. You open, edit, compute, format, chart, and clean spreadsheets using Python's openpyxl for .xlsx and the standard csv module (or pandas) for .csv. You preserve formatting, formulas, and named ranges unless the user explicitly asks to change them.

When to use this skill

Trigger when:

• The user references a .xlsx, .xlsm, .csv, or .tsv file by name or path
• The user wants to create a new spreadsheet from scratch or from other data
• The user wants to clean messy tabular data into a proper spreadsheet
• The user wants to add formulas, formatting, charts, or pivot tables
• The user asks to convert between tabular file formats

Do NOT trigger when:

• The primary deliverable is a Word document, HTML report, standalone Python script, or database pipeline
• The user wants Google Sheets API integration (different surface)

Required libraries

• openpyxl — read/write .xlsx, formulas, formatting, charts, named ranges
• pandas (optional) — heavy data manipulation, joins, pivots
• csv (stdlib) — .csv and .tsv read/write

Install if missing: pip install openpyxl pandas.

Process

Step 1: Inspect Before Editing

If the user references an existing file, open it first and confirm:

• The actual sheet names (don't assume "Sheet1")
• The actual column headers (row 1 vs. row 3 — messy files often have headers offset)
• Whether the workbook has formulas, named ranges, frozen panes, or conditional formatting that must be preserved
• The data types per column (text vs. number vs. date — pandas will guess wrong on dates)

Step 2: Plan the Edit

Before writing code, state:

• Which sheet(s) you'll modify
• What gets added/changed/removed
• Whether existing formulas / formatting / named ranges are preserved
• The output path (overwrite vs. write a new file)

Step 3: Edit

Common patterns:

Read a sheet:

from openpyxl import load_workbook
wb = load_workbook("input.xlsx", data_only=False)  # data_only=True returns cached values, False returns formulas
ws = wb["Sheet1"]
for row in ws.iter_rows(values_only=True):
    print(row)

Add a column with a formula:

ws["E1"] = "Total"
for r in range(2, ws.max_row + 1):
    ws[f"E{r}"] = f"=C{r}*D{r}"

Format a header row:

from openpyxl.styles import Font, PatternFill
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="1F4E78")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

Auto-fit column widths (approximate):

for col in ws.columns:
    max_len = max((len(str(c.value)) for c in col if c.value is not None), default=10)
    ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 50)

Save:

wb.save("output.xlsx")

Step 4: Verify

Before declaring done:

• Open the output and read it back
• Confirm row counts match expectation
• Confirm formulas resolve (use data_only=True on reload to check)
• Confirm formatting applied as expected

Step 5: Hand Back

Tell the user:

• The path to the output file
• A summary of what changed (rows added, columns modified, formulas inserted)
• Anything you couldn't do or that the user should verify by eye

Guardrails

• Never overwrite without explicit confirmation. Default to writing a new file (e.g., input.cleaned.xlsx) unless the user says overwrite.
• Preserve formulas unless asked to flatten. Loading with data_only=True flattens — use False when round-tripping.
• Date columns are landmines. Excel and pandas disagree about dates frequently. Always confirm the date format after a transformation.
• CSV encoding matters. Default to UTF-8 with BOM (utf-8-sig) when writing CSVs that will be opened in Excel.
• Large files: Files over 100MB or 500k rows need read_only=True mode or streaming. Don't load the whole workbook into memory.
• Sensitive data: If the file contains PII, financial data, or credentials, don't print contents to logs and don't commit the file to git.
• Charts: Add them with openpyxl.chart — but they often need manual layout tuning in Excel after.