Andromeda
Note

Spreadsheet Automation Tactics (Python)

Definition

Advanced techniques for manipulating the visual and structural properties of Excel spreadsheets beyond simple data entry.

Why It Matters

Spreadsheet automation is the ‘force multiplier’ for data workers; by using Python to manipulate the visual and structural properties of Excel files, you can eliminate thousands of hours of manual ‘formatting toil’ and replace it with repeatable, error-free scripts.

Core Concepts

  • Styling: Use openpyxl.styles (e.g., Font, Alignment) to modify cell appearance (bold, italic, size).
  • Formulas: Assign Excel-style formulas as strings (e.g., sheet['C1'] = '=SUM(A1:B1)'). Python treats them as text; Excel evaluates them on open.
  • Structural Changes:
    • Merging: sheet.merge_cells('A1:D3').
    • Unmerging: sheet.unmerge_cells('A1:D3').
    • Freeze Panes: The sheet.freeze_panes = 'A2' property. The “Sticky Header” mental model: everything ABOVE and to the LEFT of the specified cell is frozen.
  • Layout: Setting sheet.row_dimensions[1].height and sheet.column_dimensions['A'].width.
import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()
sheet = wb.active

# Setting font style
bold_font = Font(bold=True)
sheet['A1'].font = bold_font
sheet['A1'] = 'Bold Title'

# Using formulas
sheet['B1'] = 10
sheet['B2'] = 20
sheet['B3'] = '=SUM(B1:B2)'

# Freezing panes
sheet.freeze_panes = 'A2' # Freeze the top row

Connected Concepts

Connected notes