--- name: spreadsheet-create-verify-gate description: Create or edit Excel workbooks with openpyxl-specific pitfall handling, anchored preflight audit, and deterministic post-write proof that every criterion is satisfied --- # 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 ```python 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 ```python 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 ` - `required-sheet: exists` - `required-column: Sheet has column ""` - `row-count: Sheet has exactly N data rows` - `data-populated: Column contains values for all data rows` - `formula: Column contains formulas` - `merged-region: Sheet has merged range ` **For editing tasks:** - All creation criteria, plus: - `preservation: Sheet still exists unchanged` - `unchanged-sheets: were not modified` - `macro-preservation: VBA macros preserved (if applicable)` ### Good criterion examples: - `required-sheet: Summary exists` - `required-column: Sheet Sample has column "Selected"` - `row-count: exactly 25 data rows in Sample` - `formula: column H contains formulas for all populated rows` - `merged-region: Summary A1:B1 is merged` ### Bad criterion examples: - `workbook looks right` - `sampling seems okay` - `most tabs present` ## Phase 4: Pre-edit go/no-go gate Proceed only if ALL are true: **For creation:** - Output directory is valid - Planned sheet names are valid (length, characters, uniqueness) - Planned structure is coherent - No conflicting file at target path **For editing:** - Workbook identity is confirmed - Target sheet is unambiguous - Required columns exist or can be added safely - Row counts are plausible - No unresolved inspection conflicts remain - Requested edit is possible from available source data **For both:** - Openpyxl pitfalls have been planned for (merged cells, etc.) - All required criteria are checkable Otherwise, stop and report the mismatch. ## Phase 5: Perform the creation/edit ### For creation: ```python from openpyxl import Workbook wb = Workbook() # Remove default sheet if creating custom structure if 'Sheet' in wb.sheetnames: del wb['Sheet'] # Create sheets with validated names for sheet_name in planned_sheets: ws = wb.create_sheet(title=sheet_name) # Populate headers for col_idx, header in enumerate(headers, start=1): ws.cell(row=1, column=col_idx, value=header) # Populate data rows for row_idx, row_data in enumerate(data_rows, start=2): for col_idx, value in enumerate(row_data, start=1): ws.cell(row=row_idx, column=col_idx, value=value) # Apply merges if planned for merge_range in planned_merges: ws.merge_cells(merge_range) wb.save(output_path) ``` ### For editing: ```python from openpyxl import load_workbook wb = load_workbook(target_path, data_only=False) ws = wb[sheet_name] # Handle merged cells when reading merged_ranges = list(ws.merged_cells.ranges) # When modifying, be careful with merged regions # Don't write to cells within merged ranges (except top-left) # Modify content ws.cell(row=row, column=col, value=new_value) # Add new sheet if needed if new_sheet_name not in wb.sheetnames: wb.create_sheet(title=new_sheet_name) wb.save(output_path) ``` ### Record what was done: - Workbook path edited/created - Output path written - Sheets modified or created - Columns added or populated - Rows added or updated - Formulas inserted - Merged regions applied - Any assumptions made ## Phase 6: Deterministic post-write proof **This phase is mandatory.** After saving, directly inspect the saved workbook from disk with Python and `openpyxl`. Do not finalize from memory, delegated prose, or a generic success message. ### Verification script procedure ```python from pathlib import Path from openpyxl import load_workbook WORKBOOK = Path("TARGET.xlsx") TRUTHY = {"x", "yes", "true", "1", "y"} def norm(v): if v is None: return "" return str(v).strip() def lower(v): return norm(v).lower() def first_nonempty_row(ws, max_scan=20): for r in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_scan), values_only=True): vals = list(r) if any(norm(v) != "" for v in vals): return vals return [] def data_rows(ws, header_row_idx=1): count = 0 for row in ws.iter_rows(min_row=header_row_idx + 1, values_only=True): if any(norm(v) != "" for v in row): count += 1 return count def safe_cell_value(ws, coord, merged_ranges=None): """Safely get cell value, handling merged cells""" cell = ws[coord] if merged_ranges: for mr in merged_ranges: if coord in mr: # Return value from top-left of merged range return ws[mr.min_row][mr.min_col - 1].value return cell.value print(f"WORKBOOK: {WORKBOOK}") print(f"EXISTS: {WORKBOOK.exists()}") if not WORKBOOK.exists(): print("CRITERION|output-exists|FAIL|Workbook file missing") raise SystemExit(0) wb = load_workbook(WORKBOOK, data_only=False) print(f"SHEETS: {wb.sheetnames}") for ws in wb.worksheets: merged_ranges = list(ws.merged_cells.ranges) headers = first_nonempty_row(ws) print(f"SHEET|{ws.title}|HEADERS|{headers}") print(f"SHEET|{ws.title}|DATA_ROWS|{data_rows(ws)}") print(f"SHEET|{ws.title}|MERGED_RANGES|{[str(mr) for mr in merged_ranges]}") # Check each criterion # Example: # required_sheets = ["Summary", "Data"] # for sheet in required_sheets: # status = "PASS" if sheet in wb.sheetnames else "FAIL" # print(f"CRITERION|required-sheet-{sheet}|{status}|Sheet {sheet} {'found' if status == 'PASS' else 'missing'}") # Print all criteria with status for criterion_line in criteria: print(criterion_line) ``` ### Required proof outputs The verification must report: - Inspected file path - Whether the file exists - Workbook sheet names - Per-sheet headers for relevant sheets - Per-sheet non-empty row counts - Merged cell ranges (if applicable) - Formula presence (if relevant) - Preservation of required source sheets (for editing) - Criterion checklist with status for each item ### Status vocabulary Each criterion must end in exactly one status: - `PASS` — requirement satisfied by direct inspection - `FAIL` — requirement not satisfied - `UNAVAILABLE-IN-SOURCE` — requirement could not be satisfied because required source information was absent Do not use softer wording like: - looks okay - appears complete - probably satisfied - seems unavailable ## Finalization gate You may finalize only when every required criterion is either: - `PASS`, or - `UNAVAILABLE-IN-SOURCE` with explicit reconciliation You must not finalize when: - Any mandatory criterion remains `FAIL` - A claimed output file was not verified from disk - The workbook path is uncertain - Post-write proof was not run - You only have delegated summary evidence - Requested counts differ from actual counts without reconciliation - Required sheet names or headers are missing - Openpyxl errors occurred during verification (indicates file corruption or structural issues) If direct proof says the workbook is incomplete, that proof is authoritative. ## Error handling and resilience ### Tool failure patterns If `execute_code_sandbox` or `run_shell` returns opaque errors: 1. **Retry once** with the same command (may be transient) 2. **If still failing**, try alternative approach: - If `execute_code_sandbox` fails, try `run_shell` with python script - If `run_shell` fails, try `execute_code_sandbox` - If both fail, use `shell_agent` for autonomous execution ### openpyxl-specific errors **MergedCell AttributeError:** - **Symptom**: Accessing `.value` on merged cells raises exception - **Fix**: Check `ws.merged_cells.ranges` before accessing; get value from top-left cell **Invalid sheet name:** - **Symptom**: ValueError on sheet creation - **Fix**: Validate names before creation (≤31 chars, no `\ / ? * [ ]`) **Formula parsing errors:** - **Symptom**: Formula not calculating correctly - **Fix**: Use `data_only=False` when loading; verify formula syntax is Excel-compatible **File lock/permission errors:** - **Symptom**: Cannot save or load workbook - **Fix**: Ensure file is not open in another process; verify directory permissions ### Escalation to shell_agent If spreadsheet operations fail repeatedly (>2 attempts): ```python shell_agent( task=""" Create/modify Excel workbook at . Required structure: - Sheets: - Columns per sheet: - Data to populate:

- Formulas: - Merged regions: Handle openpyxl pitfalls: - Check merged_cells.ranges before accessing cell values - Validate sheet names (31 chars max, no special chars) - Use data_only=False when loading to preserve formulas Provide: created file path, sheets created, row counts, any errors encountered. """, timeout=300 ) ``` Then verify shell_agent output with the Phase 6 proof procedure. **Tool requirement**: Write verification script to a .py file and execute via `run_shell`, NOT `execute_code_sandbox`. This ensures reliability when sandbox providers are unavailable. **Step 6.1: Write script to disk first** Create a Python file (e.g., `verify_workbook.py`) with the verification logic, then execute it via shell. **Step 6.2: Execute via run_shell** ```bash python verify_workbook.py ``` **Step 6.3: Parse verification output** The script must output explicit PASS/FAIL markers for each criterion.

spreadsheet-create-verify-gate

Install this skill

Comments