Spreadsheet Create Verify Gate

Spreadsheet Create/Verify Gate

Use this skill when you must create from scratch or modify an Excel workbook and the result must be proven against a user specification rather than merely described.

This skill extends the spreadsheet-proof-gate methodology with explicit support for:

• Workbook creation from scratch (not just editing existing files)
• openpyxl-specific pitfall handling (MergedCell, worksheet creation, formula handling)
• Tool failure resilience with clear escalation paths
• Pre-edit audit phases that catch common openpyxl errors before they occur

This is a workflow skill requiring a direct Python verification pass with openpyxl before reporting success. The verification script must be saved to a .py file and executed via run_shell (NOT execute_code_sandbox).

When to use

Use this skill when ANY of the following are true:

Creation scenarios:

• You must create a new Excel workbook from scratch
• No existing template is provided
• You need to establish the initial workbook structure

Editing scenarios:

• The workbook path must be discovered
• Multiple similar Excel files may exist
• The user requires exact sheet names, columns, counts, or samples
• Prior summaries are inconsistent or not trustworthy
• Exact post-edit workbook state matters

General:

• You must prove that mandatory criteria were satisfied
• A delegated agent may perform spreadsheet work
• Tool instability has been observed in prior attempts

Core rules

Rule 1: Creation vs. Editing distinction

Before starting, determine which path applies:

Creation path (new workbook):

• No existing workbook at the target path
• You are establishing the initial structure
• Pre-edit audit focuses on workspace validation and structure planning

Editing path (existing workbook):

• Existing workbook at the target path
• You are modifying structure or content
• Pre-edit audit includes full workbook structure inspection

Rule 2: Openpyxl pitfall awareness

The following openpyxl-specific issues must be handled explicitly:

MergedCell errors:

• Accessing .value on merged cells raises MergedCell exception
• Solution: Check ws.merged_cells.ranges and handle merged regions specially
• Pre-edit check: Identify all merged ranges before accessing cell values

Worksheet creation patterns:

• wb.create_sheet() is the correct method, not direct assignment
• Sheet names must be unique and ≤31 characters
• Invalid characters (\ / ? * [ ]) are not allowed in sheet names

Formula handling:

• Set data_only=False when loading to preserve formulas
• Use wb.save() after modifications, not wb.close()
• Formula cells need explicit formula strings, not calculated values

Cell value type handling:

• Dates, numbers, and strings may need explicit type handling
• Use cell.value not direct cell access for safety

Rule 3: Never finalize from narrative summary alone

A workbook task is only complete when:

1. The target workbook path was identified (for editing) or chosen (for creation)
2. Pre-edit structure matched the intended operation
3. The edit/creation was performed on the correct file
4. The saved workbook was directly re-read from disk
5. Every requested criterion was checked in a deterministic verification report
6. Each criterion is marked as one of: PASS, FAIL, or UNAVAILABLE-IN-SOURCE
7. Any non-pass result is explicitly reconciled before finalizing

Rule 4: Workspace anchoring is absolute

All discovery, reads, writes, and verification must be anchored to the exact workspace path provided for the task.

• Treat the provided workspace path as the authoritative root
• Resolve workbooks to exact paths under that root
• Do not silently switch to similarly named files elsewhere
• If the anchored file cannot be found for editing, stop rather than guessing
• For creation, ensure the output directory exists

Outcome contract

Your output should be based on proof, not inference.

For every task using this workflow, maintain these artifacts internally:

1. Workbook inventory: path, existence status, sheet structure (for editing) OR planned structure (for creation)
2. Pre-edit audit checklist: workbook structure before modification
3. Post-write proof checklist: deterministic verification of every criterion

The post-write proof checklist is decisive.

Workflow

Phase 1: Determine creation vs. editing path

Step 1.1: Check for existing workbook

from pathlib import Path
target_path = Path("path/to/workbook.xlsx")
exists = target_path.exists()

If exists == False and user expects to create new workbook:

• Follow the creation path (Phase 2A)

If exists == True and user expects to edit:

• Follow the editing path (Phase 2B)

If exists == False but user expects to edit:

• STOP and report: workbook not found at anchored path
• Do not proceed with creation unless user explicitly requests it

Step 1.2: Validate workspace for creation

For creation path, confirm:

• Output directory exists or can be created
• No conflicting file at target path
• Sufficient permissions inferred from workspace context

Phase 2A: Creation path pre-audit

Step 2A.1: Plan workbook structure

Before writing any code, document:

• All sheet names to be created
• Column headers for each sheet
• Expected row counts
• Any formulas to be inserted
• Any merged cell regions
• Any special formatting requirements

Step 2A.2: Check for openpyxl pitfalls

Merged cell planning:

• If merged cells are required, plan the merge ranges explicitly
• Ensure merged ranges don't overlap
• Plan to populate only the top-left cell of merged regions

Sheet name validation:

• All names ≤31 characters
• No invalid characters: \ / ? * [ ]
• All names unique (case-sensitive in openpyxl)

Data type planning:

• Dates: use Python datetime objects, not strings
• Numbers: use numeric types, not formatted strings
• Formulas: use Excel formula syntax as strings

Phase 2B: Editing path pre-audit

Step 2B.1: Discover and confirm workbook identity

Search under the exact workspace root for plausible Excel files:

• .xlsx, .xlsm, .xls

Confirm:

• Exact anchored path
• File existence
• Workbook readability (can be loaded by openpyxl)

Step 2B.2: Openpyxl-safe structure inspection

from openpyxl import load_workbook

# Always use data_only=False to preserve formulas
wb = load_workbook(target_path, data_only=False)

# Get all sheet names
sheet_names = wb.sheetnames

# For each sheet, inspect safely
for ws in wb.worksheets:
    # Check for merged cells BEFORE accessing values
    merged_ranges = list(ws.merged_cells.ranges)
    
    # Get headers safely
    headers = []
    for cell in ws[1]:  # Row 1
        # Check if cell is part of a merged range
        is_merged = any(cell.coordinate in mr for mr in merged_ranges)
        if is_merged:
            # Get value from top-left of merged range
            for mr in merged_ranges:
                if cell.coordinate in mr:
                    headers.append(ws[mr.min_row][mr.min_col - 1].value)
                    break
        else:
            headers.append(cell.value)

Step 2B.3: Identity consistency check

If you inspect the workbook multiple times:

• Inspections must agree on: path, sheet names, row counts, headers
• If they disagree, treat workbook identity as unconfirmed
• Do not edit until reconciled

Phase 3: Convert requirements into explicit criteria

Before editing/creating, extract every verifiable requirement.

For creation tasks:

• output-exists: workbook created at <path>
• required-sheet: <name> exists
• required-column: Sheet <name> has column "<column>"
• row-count: Sheet <name> has exactly N data rows
• data-populated: Column <X> contains values for all data rows
• formula: Column <Y> contains formulas
• merged-region: Sheet <name> has merged range <range>

For editing tasks:

• All creation criteria, plus:
• preservation: Sheet <name> still exists unchanged
• unchanged-sheets: <list> were not modified
• macro-preservation: VBA macros preserved (if applicable)