Excel Toolkit

Excel Toolkit

Setup (First Use)

Run the dependency installer before any Excel operation:

python3 scripts/setup_deps.py

Installs: openpyxl, pandas, xlsxwriter, matplotlib. Skip if already installed.

Workflow Selection

1. Inspect a file → Run scripts/inspect_excel.py
2. Analyze data / get insights → Run scripts/analyze_excel.py
3. Read data for processing → Use pandas in inline Python
4. Edit existing file → Use openpyxl (preserves formulas/formatting)
5. Create new file → Use openpyxl (formulas/formatting) or pandas (data export)
6. 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 rows

Returns 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 sheet

Returns 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 types

Creating / 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

1. Use Excel formulas, not hardcoded calculations

   • ❌ ws['B10'] = df['Sales'].sum()
   • ✅ ws['B10'] = '=SUM(B2:B9)'

2. Recalculate after writing formulas — openpyxl writes formula strings but doesn't evaluate:

   python3 scripts/recalc.py output.xlsx
   

3. Never save workbooks opened with data_only=True — destroys all formulas permanently.

4. Preserve existing formatting — use load_workbook() and match existing conventions.

Providing Insights

When analyzing data:

1. Run scripts/inspect_excel.py to understand structure
2. Run scripts/analyze_excel.py --correlations for numeric data
3. 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":

1. Use a running ROW counter — never hardcode row positions for sections/charts
2. Reserve 17-20 rows after each chart anchor for chart height
3. Leave 2 blank rows between tables and charts
4. Set chart dimensions explicitly — use the sizing guide for each chart type
5. Apply consistent styling — title/section/header fonts, zebra striping, thin borders
6. Use the standard color palette — BLUE for primary, ORANGE for secondary, RED for warnings
7. 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.