Excel Toolkit
Setup (First Use)
Run the dependency installer before any Excel operation:
python3 scripts/setup_deps.pyInstalls: openpyxl, pandas, xlsxwriter, matplotlib. Skip if already installed.
Workflow Selection
- Inspect a file → Run
scripts/inspect_excel.py - Analyze data / get insights → Run
scripts/analyze_excel.py - Read data for processing → Use pandas in inline Python
- Edit existing file → Use openpyxl (preserves formulas/formatting)
- Create new file → Use openpyxl (formulas/formatting) or pandas (data export)
- Recalculate formulas → Run
scripts/recalc.py
Quick-Start Scripts
Inspect File Structure
python3 scripts/inspect_excel.py data.xlsx # Structure only
python3 scripts/inspect_excel.py data.xlsx --data # With data preview
python3 scripts/inspect_excel.py data.xlsx --sheet "Sales" # Specific sheet
python3 scripts/inspect_excel.py data.xlsx --data --rows 50 # More preview rowsReturns JSON: sheet names, dimensions, headers, column types, optional data preview.
Analyze Data
python3 scripts/analyze_excel.py data.xlsx # Basic stats
python3 scripts/analyze_excel.py data.xlsx --correlations # With correlations
python3 scripts/analyze_excel.py data.xlsx --sheet "Revenue" # Specific sheetReturns JSON: shape, dtypes, missing values, numeric stats, categorical summaries, duplicates, date ranges.
Recalculate Formulas
python3 scripts/recalc.py output.xlsx [timeout_seconds]Requires LibreOffice. Returns JSON with formula errors and locations.
Reading Data
import pandas as pd
df = pd.read_excel('file.xlsx') # First sheet
df = pd.read_excel('file.xlsx', sheet_name='Sales') # Named sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # All sheets as dict
df = pd.read_excel('file.xlsx', dtype={'id': str}) # Force typesCreating / Editing
Create New
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
ws = wb.active
ws.title = "Data"
ws['A1'] = 'Category'
ws['A1'].font = Font(bold=True)
ws.append(['Sales', 1500])
ws['B3'] = '=SUM(B2:B2)'
ws.column_dimensions['A'].width = 18
wb.save('output.xlsx')Edit Existing
from openpyxl import load_workbook
wb = load_workbook('existing.xlsx') # preserves formulas
ws = wb['Sheet1']
ws['A1'] = 'Updated'
ws.insert_rows(2)
wb.save('modified.xlsx')Critical Rules
- Use Excel formulas, not hardcoded calculations
- ❌ ws['B10'] = df['Sales'].sum() - ✅ ws['B10'] = '=SUM(B2:B9)'
- Recalculate after writing formulas — openpyxl writes formula strings but doesn't evaluate:
python3 scripts/recalc.py output.xlsx - Never save workbooks opened with
data_only=True— destroys all formulas permanently. - Preserve existing formatting — use
load_workbook()and match existing conventions.
Providing Insights
When analyzing data:
- Run
scripts/inspect_excel.pyto understand structure - Run
scripts/analyze_excel.py --correlationsfor numeric data - Present findings:
- Overview: rows, columns, data types - Key Statistics: means, medians, ranges - Data Quality: missing values, duplicates, anomalies - Patterns: correlations, trends, distributions - Actionable Insights: what stands out, recommendations
Building Dashboards & Insights Sheets
When creating dashboard/insights sheets with tables and charts, you MUST follow the layout rules in references/advanced-patterns.md → "Dashboard Layout & Spacing":
- Use a running ROW counter — never hardcode row positions for sections/charts
- Reserve 17-20 rows after each chart anchor for chart height
- Leave 2 blank rows between tables and charts
- Set chart dimensions explicitly — use the sizing guide for each chart type
- Apply consistent styling — title/section/header fonts, zebra striping, thin borders
- Use the standard color palette — BLUE for primary, ORANGE for secondary, RED for warnings
- Set column widths — use the defaults table for readable layouts
Advanced Features
For charts, conditional formatting, pivot tables, data validation, CSV conversion, dashboard layout, and large file handling → see references/advanced-patterns.md.