Xlsx Spreadsheet Processing

Problem

Excel delivery often fails in two ways: calculations are hardcoded after being computed in Python, so the workbook stops updating when source data changes; and formula references, cross-sheet links, number formats, and recalculation errors are not checked as one pipeline, letting #REF!, #DIV/0!, and #VALUE! surface late.

This skill turns .xlsx creation, editing, and analysis into a reproducible engineering workflow.

How It Works

• Data analysis: use pandas to read, filter, and aggregate workbook data for batch cleaning and quick exploration.
• Formulas and formatting: use openpyxl to write Excel formulas, styles, and cross-sheet references instead of static hardcoded values.
• Template first: when updating an existing file, preserve the existing font, color, number format, and model conventions before imposing a generic standard.
• Automatic recalculation: run scripts/recalc.py with LibreOffice to recalculate formulas and scan every worksheet for errors, returning error types and locations.
• Repair loop: use the error summary to fix invalid references, division by zero, #NAME?, and type mismatches, then revalidate until the result is stable.

Boundaries and Notes

Use this for financial models, operating analysis, working papers, and maintainable .xlsx deliverables; it is heavier than a one-time static export. Be careful that openpyxl with data_only=True can read calculated values and permanently replace formulas. For large files, prefer read_only or write_only. Cross-sheet references should be checked for Sheet1!A1 syntax, column mapping, and 1-based indexing.