xlsx

Spreadsheet Creation, Editing, and Analysis

You have access to multiple tools and workflows for working with .xlsx files — from reading and analysing data, through programmatic creation and editing, to formula recalculation and error checking.

Tooling Primer

Library	Best for
pandas	Bulk data manipulation, statistical analysis, quick CSV↔XLSX conversion
openpyxl	Cell-level formatting, Excel formulas, charts, conditional formatting

⛔ CRITICAL — Use Formulas, Never Hardcode Calculations

🚨 MANDATORY RULE — ZERO EXCEPTIONS 🚨

Every computed value must be an Excel formula, not a Python-calculated literal. This keeps the workbook dynamic and self-updating. Violations will produce stale, non-updating spreadsheets.

# ── WRONG — baking Python results into cells ──
total = df['Sales'].sum()
ws['B10'] = total          # static 5000

growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
ws['C5'] = growth          # static 0.15

avg = sum(vals) / len(vals)
ws['D20'] = avg            # static 42.5

# ── RIGHT — let Excel do the maths ──
ws['B10'] = '=SUM(B2:B9)'
ws['C5']  = '=(C4-C2)/C2'
ws['D20'] = '=AVERAGE(D2:D19)'

This rule applies to all calculations — sums, ratios, percentages, differences, etc.

Step-by-Step Workflow

1. Pick a library — pandas for data; openpyxl for formatting / formulas.
2. Open or create the workbook.
3. Modify — add/edit data, formulas, and styles.
4. Save to disk.
5. Recalculate (mandatory when formulas are present):

   python scripts/recalc.py output.xlsx
   

6. Inspect the JSON output and fix any errors:
   • status: "errors_found" → see error_summary for types and locations.
   • Common errors: #REF! (bad reference), #DIV/0! (zero denominator), #VALUE! (type mismatch), #NAME? (unknown function).

Reading & Analysing Data

import pandas as pd

df = pd.read_excel('file.xlsx')                        # first sheet
sheets = pd.read_excel('file.xlsx', sheet_name=None)   # all sheets → dict

df.head(); df.info(); df.describe()                    # quick overview

df.to_excel('result.xlsx', index=False)                # write back