Blog / AI / Claude Code for openpyxl: Python Excel Automation

Claude Code for openpyxl: Python Excel Automation

Published: February 1, 2028

•

Read time: 5 min read

•

By: Claude Skills 360

openpyxl reads and writes Excel xlsx files. pip install openpyxl. Create: from openpyxl import Workbook; wb = Workbook(). ws = wb.active. ws.title = "Sheet1". Cell: ws["A1"] = "Hello". ws.cell(row=1, column=2, value=42). Save: wb.save("out.xlsx"). Load: from openpyxl import load_workbook; wb = load_workbook("file.xlsx"). ws = wb["Sheet1"]. Read: ws["A1"].value. ws.cell(row=1, column=1).value. Iterate: for row in ws.iter_rows(min_row=2, values_only=True): print(row). for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3): .... Dimensions: ws.max_row, ws.max_column. Append: ws.append([1, "Alice", 99.5]). Font: from openpyxl.styles import Font, PatternFill, Alignment, Border, Side. ws["A1"].font = Font(bold=True, size=14, color="FF0000"). Fill: ws["A1"].fill = PatternFill(start_color="FFFF00", fill_type="solid"). Align: ws["A1"].alignment = Alignment(horizontal="center"). Border: ws["A1"].border = Border(left=Side(style="thin")). Number format: ws["B2"].number_format = "#,##0.00". Date: ws["C2"].number_format = "YYYY-MM-DD". Column width: ws.column_dimensions["A"].width = 20. Row height: ws.row_dimensions[1].height = 25. Merge: ws.merge_cells("A1:D1"). Freeze: ws.freeze_panes = "A2". Filter: ws.auto_filter.ref = ws.dimensions. Named sheet: ws2 = wb.create_sheet("Summary"). Copy: wb.copy_worksheet(ws). Data only: load_workbook("f.xlsx", data_only=True) — formulas as values. Claude Code generates openpyxl report templates, data exports, and formatted workbooks.

CLAUDE.md for openpyxl

## openpyxl Stack
- Version: openpyxl >= 3.1 | pip install openpyxl
- Create: Workbook(); ws = wb.active; ws["A1"] = value; wb.save("out.xlsx")
- Read: load_workbook("file.xlsx"); ws.iter_rows(values_only=True)
- Style: Font(bold=True) | PatternFill(fgColor="FFFF00") | Alignment(horizontal="center")
- Format: ws["B"].number_format = "#,##0.00" | "YYYY-MM-DD" for dates
- Layout: column_dimensions["A"].width | freeze_panes = "A2" | auto_filter.ref
- Charts: BarChart() + Reference + Series + ws.add_chart(chart, "E2")

openpyxl Excel Automation Pipeline

# app/excel.py — openpyxl workbook creation, reading, and formatting
from __future__ import annotations

from datetime import date, datetime
from pathlib import Path
from typing import Any

from openpyxl import Workbook, load_workbook
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.series import SeriesLabel
from openpyxl.styles import (
    Alignment,
    Border,
    Font,
    GradientFill,
    PatternFill,
    Side,
)
from openpyxl.utils import get_column_letter


# ─────────────────────────────────────────────────────────────────────────────
# Style helpers
# ─────────────────────────────────────────────────────────────────────────────

HEADER_FONT    = Font(bold=True, size=11, color="FFFFFF")
HEADER_FILL    = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
HEADER_ALIGN   = Alignment(horizontal="center", vertical="center", wrap_text=True)
THIN_BORDER    = Border(
    left=Side(style="thin"), right=Side(style="thin"),
    top=Side(style="thin"),  bottom=Side(style="thin"),
)
ALT_FILL       = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid")
CURRENCY_FMT   = '"$"#,##0.00'
DATE_FMT       = "YYYY-MM-DD"
PCT_FMT        = "0.00%"
NUMBER_FMT     = "#,##0"


def _apply_header(ws, row: int, columns: list[str]) -> None:
    """Apply bold blue header row styling."""
    for col_idx, label in enumerate(columns, start=1):
        cell = ws.cell(row=row, column=col_idx, value=label)
        cell.font      = HEADER_FONT
        cell.fill      = HEADER_FILL
        cell.alignment = HEADER_ALIGN
        cell.border    = THIN_BORDER


def _set_column_widths(ws, widths: dict[str, float]) -> None:
    """Set column widths by letter: {"A": 20, "B": 15}."""
    for col, width in widths.items():
        ws.column_dimensions[col].width = width


# ─────────────────────────────────────────────────────────────────────────────
# 1. Sales report workbook
# ─────────────────────────────────────────────────────────────────────────────

def create_sales_report(
    data: list[dict],
    output_path: Path,
) -> Path:
    """
    Build a formatted sales report with:
    - Styled header row
    - Alternating row colors
    - Currency and percentage number formats
    - Auto-filter and freeze panes
    - SUM footer row
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales"

    columns   = ["Date", "Product", "Region", "Units", "Unit Price", "Revenue", "Margin %"]
    col_fmts  = [DATE_FMT, None, None, NUMBER_FMT, CURRENCY_FMT, CURRENCY_FMT, PCT_FMT]
    col_widths = {"A": 14, "B": 25, "C": 12, "D": 10, "E": 12, "F": 14, "G": 12}

    # Title row
    ws.merge_cells("A1:G1")
    title_cell = ws["A1"]
    title_cell.value     = "Sales Report"
    title_cell.font      = Font(bold=True, size=14)
    title_cell.alignment = Alignment(horizontal="center")
    ws.row_dimensions[1].height = 30

    # Header row
    _apply_header(ws, row=2, columns=columns)
    ws.freeze_panes = "A3"

    # Data rows
    for row_idx, record in enumerate(data, start=3):
        alt = (row_idx % 2 == 0)
        row_data = [
            record.get("date"),
            record.get("product"),
            record.get("region"),
            record.get("units", 0),
            record.get("unit_price", 0.0),
            record.get("revenue", 0.0),
            record.get("margin", 0.0),
        ]
        for col_idx, (value, fmt) in enumerate(zip(row_data, col_fmts), start=1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = THIN_BORDER
            if fmt:
                cell.number_format = fmt
            if alt:
                cell.fill = ALT_FILL

    # Footer / totals
    last_row = len(data) + 2
    footer_row = last_row + 1
    ws.cell(row=footer_row, column=1, value="TOTAL").font = Font(bold=True)
    for col_idx, label in enumerate(columns, start=1):
        cell = ws.cell(row=footer_row, column=col_idx)
        cell.border = THIN_BORDER
    # SUM formulas for numeric columns
    for col_idx in (4, 5, 6):
        col_letter = get_column_letter(col_idx)
        sum_cell = ws.cell(row=footer_row, column=col_idx)
        sum_cell.value         = f"=SUM({col_letter}3:{col_letter}{last_row})"
        sum_cell.font          = Font(bold=True)
        sum_cell.number_format = col_fmts[col_idx - 1]

    # Auto-filter on header row
    ws.auto_filter.ref = f"A2:{get_column_letter(len(columns))}{last_row}"

    _set_column_widths(ws, col_widths)
    wb.save(output_path)
    return output_path


# ─────────────────────────────────────────────────────────────────────────────
# 2. Read existing Excel file
# ─────────────────────────────────────────────────────────────────────────────

def read_excel(path: Path, sheet: str | None = None, skip_rows: int = 0) -> list[dict]:
    """
    Read an Excel file into a list of dicts.
    data_only=True returns formula results rather than formula strings.
    """
    wb = load_workbook(str(path), read_only=True, data_only=True)
    ws = wb[sheet] if sheet else wb.active

    rows = list(ws.iter_rows(min_row=skip_rows + 1, values_only=True))
    if not rows:
        return []

    headers = [str(h) if h is not None else f"col_{i}" for i, h in enumerate(rows[0])]
    return [
        {headers[i]: val for i, val in enumerate(row)}
        for row in rows[1:]
    ]


# ─────────────────────────────────────────────────────────────────────────────
# 3. Multi-sheet workbook
# ─────────────────────────────────────────────────────────────────────────────

def create_multi_sheet(
    sheets: dict[str, list[dict]],
    output_path: Path,
) -> Path:
    """Create a workbook with one sheet per data set."""
    wb = Workbook()
    first = True

    for sheet_name, records in sheets.items():
        if first:
            ws = wb.active
            ws.title = sheet_name
            first = False
        else:
            ws = wb.create_sheet(title=sheet_name)

        if not records:
            continue

        headers = list(records[0].keys())
        _apply_header(ws, row=1, columns=headers)
        ws.freeze_panes = "A2"

        for row_idx, record in enumerate(records, start=2):
            for col_idx, key in enumerate(headers, start=1):
                ws.cell(row=row_idx, column=col_idx, value=record.get(key))

        # Auto-width
        for col_idx, header in enumerate(headers, start=1):
            col_letter = get_column_letter(col_idx)
            max_len = max(
                len(str(header)),
                *(len(str(r.get(header, "") or "")) for r in records),
            )
            ws.column_dimensions[col_letter].width = min(max_len + 2, 50)

    wb.save(output_path)
    return output_path


# ─────────────────────────────────────────────────────────────────────────────
# 4. Bar chart from data
# ─────────────────────────────────────────────────────────────────────────────

def add_bar_chart(output_path: Path, sheet_name: str = "Sales") -> None:
    """Add a BarChart to an existing workbook sheet."""
    wb = load_workbook(str(output_path))
    ws = wb[sheet_name]

    chart = BarChart()
    chart.type          = "col"
    chart.title         = "Revenue by Product"
    chart.y_axis.title  = "Revenue ($)"
    chart.x_axis.title  = "Product"
    chart.width         = 18
    chart.height        = 10

    # Revenue column (F = col 6), rows 3 to max
    data_ref = Reference(ws, min_col=6, min_row=2, max_row=ws.max_row - 1)
    cats_ref = Reference(ws, min_col=2, min_row=3, max_row=ws.max_row - 1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)

    ws.add_chart(chart, "I3")
    wb.save(output_path)


# ─────────────────────────────────────────────────────────────────────────────
# 5. pandas DataFrame → Excel
# ─────────────────────────────────────────────────────────────────────────────

def dataframe_to_excel(df, output_path: Path, sheet_name: str = "Data") -> Path:
    """
    Export a pandas DataFrame to a formatted Excel file.
    Uses openpyxl as the engine — column widths are auto-fitted.
    """
    try:
        import pandas as pd

        with pd.ExcelWriter(str(output_path), engine="openpyxl") as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)
            ws = writer.sheets[sheet_name]

            # Style header
            for cell in ws[1]:
                cell.font  = Font(bold=True, color="FFFFFF")
                cell.fill  = PatternFill(start_color="2F5496", fill_type="solid")

            # Auto-fit columns
            for col in ws.columns:
                max_len = max(len(str(cell.value or "")) for cell in col) + 2
                ws.column_dimensions[col[0].column_letter].width = min(max_len, 50)

        return output_path

    except ImportError:
        raise ImportError("pip install pandas to use dataframe_to_excel")


# ─────────────────────────────────────────────────────────────────────────────
# Demo
# ─────────────────────────────────────────────────────────────────────────────

if __name__ == "__main__":
    import tempfile

    sample_data = [
        {"date": date(2024, 1, 1), "product": "Widget A", "region": "North",
         "units": 120, "unit_price": 29.99, "revenue": 3598.80, "margin": 0.42},
        {"date": date(2024, 1, 2), "product": "Widget B", "region": "South",
         "units": 85,  "unit_price": 49.99, "revenue": 4249.15, "margin": 0.38},
        {"date": date(2024, 1, 3), "product": "Gadget X", "region": "East",
         "units": 200, "unit_price": 14.99, "revenue": 2998.00, "margin": 0.55},
    ]

    with tempfile.TemporaryDirectory() as tmp:
        out = Path(tmp) / "sales_report.xlsx"
        create_sales_report(sample_data, out)
        print(f"Sales report: {out} ({out.stat().st_size} bytes)")

        add_bar_chart(out)
        print("Bar chart added.")

        rows = read_excel(out, skip_rows=1)   # skip title row
        print(f"Read back {len(rows)} rows")

        multi = Path(tmp) / "multi.xlsx"
        create_multi_sheet({"Q1": sample_data, "Q2": sample_data}, multi)
        print(f"Multi-sheet: {multi} ({multi.stat().st_size} bytes)")

For the xlwt / xlrd alternative — xlwt can only write the legacy .xls format (Excel 97–2003) and xlrd 2.x dropped support for .xlsx, making neither viable for modern Excel files, while openpyxl reads and writes the current .xlsx Open XML format, supports formulas, charts, conditional formatting, named ranges, and data validation, and is the engine used by pandas.DataFrame.to_excel() with engine="openpyxl". For the csv alternative — csv.DictWriter produces UTF-8 text files that open as flat data in Excel without formatting, column widths, number formats, freeze panes, or charts, while openpyxl’s PatternFill, Font, number_format, and add_chart produce a fully styled .xlsx file where opening in Excel shows blue header rows, alternating zebra stripes, currency-formatted revenue columns, and an embedded bar chart — the difference between a data dump and a deliverable report. The Claude Skills 360 bundle includes openpyxl skill sets covering Workbook/Worksheet creation, iter_rows with values_only for efficient reading, load_workbook with data_only=True, Font/PatternFill/Alignment/Border cell styling, number_format for currency/date/percent, column_dimensions width and row_dimensions height, merge_cells for spanning headers, freeze_panes and auto_filter, BarChart and LineChart with Reference/Series, multi-sheet workbooks, auto-fit column widths, and pandas DataFrame export via ExcelWriter. Start with the free tier to try Excel automation code generation.

Keep Reading

Claude Code for email.contentmanager: Python Email Content Accessors

Read and write EmailMessage body content with Python's email.contentmanager module and Claude Code — email contentmanager ContentManager for the class that maps content types to get and set handler functions allowing EmailMessage to support get_content and set_content with type-specific behaviour, email contentmanager raw_data_manager for the ContentManager instance that handles raw bytes and str payloads without any conversion, email contentmanager content_manager for the standard ContentManager instance used by email.policy.default that intelligently handles text plain text html multipart and binary content types, email contentmanager get_content_text for the handler that returns the decoded text payload of a text-star message part as a str, email contentmanager get_content_binary for the handler that returns the raw decoded bytes payload of a non-text message part, email contentmanager get_data_manager for the get-handler lookup used by EmailMessage get_content to find the right reader function for the content type, email contentmanager set_content text for the handler that creates and sets a text part correctly choosing charset and transfer encoding, email contentmanager set_content bytes for the handler that creates and sets a binary part with base64 encoding and optional filename Content-Disposition, email contentmanager EmailMessage get_content for the method that reads the message body using the registered content manager handlers, email contentmanager EmailMessage set_content for the method that sets the message body and MIME headers in one call, email contentmanager EmailMessage make_alternative make_mixed make_related for the methods that convert a simple message into a multipart container, email contentmanager EmailMessage add_attachment for the method that attaches a file or bytes to a multipart message, and email contentmanager integration with email.message and email.policy and email.mime and io for building high-level email readers attachment extractors text body accessors HTML readers and policy-aware MIME construction pipelines.

5 min read Feb 12, 2029

Claude Code for email.charset: Python Email Charset Encoding

Control header and body encoding for international email with Python's email.charset module and Claude Code — email charset Charset for the class that wraps a character set name with the encoding rules for header encoding and body encoding describing how to encode text for that charset in email messages, email charset Charset header_encoding for the attribute specifying whether headers using this charset should use QP quoted-printable encoding BASE64 encoding or no encoding, email charset Charset body_encoding for the attribute specifying the Content-Transfer-Encoding to use for message bodies in this charset such as QP or BASE64, email charset Charset output_codec for the attribute giving the Python codec name used to encode the string to bytes for the wire format, email charset Charset input_codec for the attribute giving the Python codec name used to decode incoming bytes to str, email charset Charset get_output_charset for returning the output charset name, email charset Charset header_encode for encoding a header string using the charset's header_encoding method, email charset Charset body_encode for encoding body content using the charset's body_encoding, email charset Charset convert for converting a string from the input_codec to the output_codec, email charset add_charset for registering a new charset with custom encoding rules in the global charset registry, email charset add_alias for adding an alias name that maps to an existing registered charset, email charset add_codec for registering a codec name mapping for use by the charset machinery, and email charset integration with email.message and email.mime and email.policy and email.encoders for building international email senders non-ASCII header encoders Content-Transfer-Encoding selectors charset-aware message constructors and MIME encoding pipelines.

5 min read Feb 11, 2029

Claude Code for email.utils: Python Email Address and Header Utilities

Parse and format RFC 2822 email addresses and dates with Python's email.utils module and Claude Code — email utils parseaddr for splitting a display-name plus angle-bracket address string into a realname and email address tuple, email utils formataddr for combining a realname and address string into a properly quoted RFC 2822 address with angle brackets, email utils getaddresses for parsing a list of raw address header strings each potentially containing multiple comma-separated addresses into a list of realname address tuples, email utils parsedate for parsing an RFC 2822 date string into a nine-tuple compatible with time.mktime, email utils parsedate_tz for parsing an RFC 2822 date string into a ten-tuple that includes the UTC offset timezone in seconds, email utils parsedate_to_datetime for parsing an RFC 2822 date string into an aware datetime object with timezone, email utils formatdate for formatting a POSIX timestamp or the current time as an RFC 2822 date string with optional usegmt and localtime flags, email utils format_datetime for formatting a datetime object as an RFC 2822 date string, email utils make_msgid for generating a globally unique Message-ID string with optional idstring and domain components, email utils decode_rfc2231 for decoding an RFC 2231 encoded parameter value into a tuple of charset language and value, email utils encode_rfc2231 for encoding a string as an RFC 2231 encoded parameter value, email utils collapse_rfc2231_value for collapsing a decoded RFC 2231 tuple to a Unicode string, and email utils integration with email.message and email.headerregistry and datetime and time for building address parsers date formatters message-id generators header extractors and RFC-compliant email construction utilities.

5 min read Feb 10, 2029

Put these ideas into practice

Claude Skills 360 gives you production-ready skills for everything in this article — and 2,350+ more. Start free or go all-in.

Get 360 skills free

Free $39