openpyxl 3.0.7: openpyxl

Clean-room public code sample — generation instructions

Write a brand-new, minimal, self-contained code sample in this clean-room directory.
Do not copy, paraphrase, or reference any existing project source. Work only from this spec.

A csx.json manifest scaffold already exists. Do not recreate it from memory. Preserve its case.goal, packages and symbols; fill its empty case.contract with exact assertions and correct its environment, commands and verifierAdapter for the files you generate.

Goal: verify openpyxl in pkg:pypi/openpyxl@3.0.7
Kind: HOW

Use EXACTLY these public packages and versions:
  - pkg:pypi/openpyxl@3.0.7
Demonstrate these symbols/APIs:
  - openpyxl

Rules:
  - One focused purpose; the smallest project that proves the goal.
  - Include a contract test (test/contract.*) that runs OFFLINE and exits 0 exactly when the goal behavior works.
  - Pin every dependency with a lockfile so resolution is reproducible.
  - No secrets, credentials, or tokens. No real URLs (only example.com or localhost). No absolute paths.
  - No personal names, emails, company names, or project identifiers of any kind.
  - No binaries and no generated output (node_modules, dist, target, venv, .git, .env).
  - Keep it under 200 files and 256KB packed.

{"case":{"caseId":"case:sha256:a2b1bda983704998579a0c4ad5c249631ba0e799afb880c96ef185ffb398b452","contract":["openpyxl.Workbook creates worksheets, populates cells, applies Font/PatternFill/Alignment styles, and assigns cell formulas","openpyxl.load_workbook roundtrips serialized .xlsx byte streams while preserving sheets, cell values, styles, and formula strings","openpyxl.utils converts accurately between 1-based column indices and Excel column letters"],"goal":"verify openpyxl in pkg:pypi/openpyxl@3.0.7","kind":"HOW","packages":["pkg:pypi/openpyxl@3.0.7"],"schemaVersion":1,"symbols":["openpyxl"]},"contractCommand":["python","test/contract.py"],"environment":{"arch":"x64","distro":"ubuntu","ecosystem":"pypi","libc":"glibc","libcVersion":"2.39","os":"linux","osVersionBucket":"24","packageManager":"pip","schemaVersion":1},"license":"MIT-0","packages":["pkg:pypi/openpyxl@3.0.7"],"schemaVersion":1,"subject":"pkg:pypi/openpyxl@3.0.7","symbols":["openpyxl"],"verifierAdapter":"python@1"}

et-xmlfile==2.0.0
openpyxl==3.0.7

"""Sample module demonstrating openpyxl 3.0.7 workbook creation, styling, formulas, and roundtrip loading."""

import io
import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter


def create_inventory_workbook(rows: list[tuple[str, int, float]]) -> openpyxl.Workbook:
    """Create a Workbook with styled headers, inventory rows, and a SUM formula."""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Inventory"

    headers = ["Item", "Quantity", "UnitPrice", "Total"]
    ws.append(headers)

    header_font = Font(name="Calibri", size=11, bold=True)
    header_fill = PatternFill(fill_type="solid", start_color="D9E1F2", end_color="D9E1F2")
    header_align = Alignment(horizontal="center")

    for col_idx in range(1, len(headers) + 1):
        cell = ws.cell(row=1, column=col_idx)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align

    for idx, (item_name, qty, price) in enumerate(rows, start=2):
        ws.cell(row=idx, column=1, value=item_name)
        ws.cell(row=idx, column=2, value=qty)
        ws.cell(row=idx, column=3, value=price)
        ws.cell(row=idx, column=4, value=f"=B{idx}*C{idx}")

    summary_row = len(rows) + 2
    ws.cell(row=summary_row, column=1, value="Grand Total")
    if rows:
        last_data_row = len(rows) + 1
        col_letter = get_column_letter(4)
        ws.cell(row=summary_row, column=4, value=f"=SUM({col_letter}2:{col_letter}{last_data_row})")
    else:
        ws.cell(row=summary_row, column=4, value=0)

    wb.create_sheet(title="Notes")
    wb["Notes"]["A1"] = "Generated with openpyxl"
    return wb


def serialize_and_reload_workbook(
    wb: openpyxl.Workbook, data_only: bool = False
) -> openpyxl.Workbook:
    """Save a Workbook to an in-memory buffer and reload it via openpyxl.load_workbook."""
    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return openpyxl.load_workbook(buf, data_only=data_only)

{
  "schemaVersion": 1,
  "goal": "verify openpyxl in pkg:pypi/openpyxl@3.0.7",
  "kind": "HOW",
  "packages": [
    "pkg:pypi/openpyxl@3.0.7"
  ],
  "symbols": [
    "openpyxl"
  ]
}

"""Offline contract verification test for openpyxl in pkg:pypi/openpyxl@3.0.7."""

import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

import openpyxl
from openpyxl.utils import column_index_from_string, get_column_letter
import sample


def test_workbook_creation_and_sheets() -> None:
    """Assertion 1: openpyxl.Workbook creates worksheets, populates cells, applies Font/PatternFill/Alignment styles, and assigns cell formulas."""
    assert openpyxl.__version__ == "3.0.7"

    data = [
        ("Widget", 4, 12.5),
        ("Gadget", 2, 25.0),
        ("Sprocket", 10, 3.75),
    ]
    wb = sample.create_inventory_workbook(data)

    assert wb.sheetnames == ["Inventory", "Notes"]
    ws = wb["Inventory"]

    # Header values and styling
    assert ws["A1"].value == "Item"
    assert ws["B1"].value == "Quantity"
    assert ws["C1"].value == "UnitPrice"
    assert ws["D1"].value == "Total"
    assert ws["A1"].font.bold is True
    assert ws["A1"].fill.fill_type == "solid"
    assert ws["A1"].alignment.horizontal == "center"

    # Row values and formulas
    assert ws["A2"].value == "Widget"
    assert ws["B2"].value == 4
    assert ws["C2"].value == 12.5
    assert ws["D2"].value == "=B2*C2"

    assert ws["A5"].value == "Grand Total"
    assert ws["D5"].value == "=SUM(D2:D4)"
    assert wb["Notes"]["A1"].value == "Generated with openpyxl"


def test_workbook_serialization_and_reload() -> None:
    """Assertion 2: openpyxl.load_workbook roundtrips serialized .xlsx byte streams while preserving sheets, cell values, styles, and formula strings."""
    data = [
        ("Adapter", 3, 15.0),
        ("Cable", 5, 8.0),
    ]
    wb = sample.create_inventory_workbook(data)
    reloaded = sample.serialize_and_reload_workbook(wb, data_only=False)

    assert reloaded.sheetnames == ["Inventory", "Notes"]
    ws = reloaded["Inventory"]

    rows = list(ws.iter_rows(min_row=2, max_row=3, max_col=4, values_only=True))
    assert rows == [
        ("Adapter", 3, 15.0, "=B2*C2"),
        ("Cable", 5, 8.0, "=B3*C3"),
    ]
    assert ws["D4"].value == "=SUM(D2:D3)"
    assert ws["A1"].font.bold is True
    assert reloaded["Notes"]["A1"].value == "Generated with openpyxl"


def test_coordinate_utilities() -> None:
    """Assertion 3: openpyxl.utils converts accurately between 1-based column indices and Excel column letters."""
    assert get_column_letter(1) == "A"
    assert get_column_letter(26) == "Z"
    assert get_column_letter(27) == "AA"
    assert column_index_from_string("A") == 1
    assert column_index_from_string("Z") == 26
    assert column_index_from_string("AA") == 27


def main() -> None:
    test_workbook_creation_and_sheets()
    test_workbook_serialization_and_reload()
    test_coordinate_utilities()
    print("All openpyxl contract assertions passed.")


if __name__ == "__main__":
    main()