Excel Xlwings Workflow

Standard Workflow

1. Confirm workbook path, required outputs, and minimal sheet design.
2. In analysis/ statistical or aggregation workflows, use .ipynb for exploration when needed.
3. Define table inventory first (table name, target sheet, start cell, and description).
4. Implement final Excel-generation logic in a .py script with xlwings when available; otherwise use an explicit openpyxl fallback and record why.
5. Create Excel Tables for outputs, and preserve formulas/formatting where needed.
6. Add sheet-level guide blocks for human-facing interpretation sheets.
7. Populate table_guide with per-table descriptions and concise column-reading help.
8. Save workbook and close handles (wb.close(), app context exit).
9. Reopen the saved workbook and run validation checks.
10. Open the saved workbook in Excel UI once for visual QA when the environment supports it. If not, record that the visual QA step was skipped because desktop Excel automation was unavailable.
11. If any issue is found, fix and rerun steps 4 to 10.

Sheet and Table Rules

• Prefer one consolidated data sheet unless data volume or user requirements justify more.
• Use stable table names, for example tbl_<domain>_<scope>.
• Avoid duplicate/empty sheets and scattered unstructured ranges.
• If a sheet exists primarily to expose one important table, place the table on that sheet and explain the sheet in-place instead of forcing the user to cross-reference another sheet first.
• If a workbook contains both summary and detail tables, the summary sheet should explain:
  • what each key selection field means
  • how to move from the summary table to the detailed evidence table
  • one concrete example row or scenario
• Required table_guide columns:
  • table_name
  • sheet_name
  • table_range
  • description
  • key_columns
  • column_guide (recommended for interpretation workbooks)
  • notes (optional)
• description should explain what the table represents and how to interpret it in one short sentence.
• column_guide should briefly explain what the main columns mean, using the exact column names where possible.

Required Post-Save Validation

Run every check after reopening the output workbook:

• Formula/function error check:

  • Fail if a cell value is one of #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.

• Broken formula check:

  • Fail if required formula cells are missing formulas.
  • Fail if formula text contains broken references (for example, #REF!).

• Blank-cell check:

  • Define business-critical required ranges first.
  • Fail if required cells are empty (None or empty string after trim).

• Table integrity check:

  • Fail if expected tables are missing.
  • Fail if table_guide is missing.
  • Fail if any table in workbook lacks a matching table_guide row.
  • Fail if description is blank in table_guide.
  • For interpretation workbooks, fail if the expected sheet-level guide labels (for example purpose, key columns, example) are missing on important sheets.

• Record a validation summary:

  • workbook path, sheet/range scanned, issue counts, pass/fail.

• Label safety check (xlwings write path):

  • Fail if any intended plain-label string starts with =.
  • Fix by rewriting label text to avoid a leading = before writing to cells.

• Engine disclosure check:

  • In WSL/headless environments, record that openpyxl was the default engine.
  • If xlwings was not used because desktop Excel automation was unavailable, say so explicitly.
  • Record which validation steps were still executed and which Excel UI checks were skipped.

Validation Snippet (openpyxl default)

from openpyxl import load_workbook

ERROR_TOKENS = {"#DIV/0!", "#N/A", "#NAME?", "#NULL!", "#NUM!", "#REF!", "#VALUE!"}

def is_blank(value):
    return value is None or (isinstance(value, str) and value.strip() == "")

def validate_book(path, required_ranges):
    issues = {"errors": [], "blanks": [], "tables": []}
    wb = load_workbook(path)
    try:
        for sheet_name, addr in required_ranges:
            sheet = wb[sheet_name]
            for row in sheet[addr]:
                for cell in row:
                    value = cell.value
                    if isinstance(value, str) and value in ERROR_TOKENS:
                        issues["errors"].append((sheet_name, cell.coordinate, value))
                    if is_blank(value):
                        issues["blanks"].append((sheet_name, cell.coordinate))

        if "table_guide" not in wb.sheetnames:
            issues["tables"].append(("table_guide", "missing"))
    finally:
        wb.close()
    return issues

Formatting Snippet: Force Text Format for Plain-Text Columns (openpyxl)

Use this when you are writing IDs/codes/labels (not formulas and not numeric analytics columns).

def write_text_cell(cell, value):
    cell.number_format = "@"
    cell.value = "" if value is None else str(value)

Command Templates (PowerShell/cmd)

NOTE: scripts/excel_job.py is a placeholder example path (this repo may not include that file). Replace it with your actual Excel-generation script under scripts/.

PowerShell:

• conda run -n cuda python -c "import sys; print(sys.executable)"
• conda run -n cuda python .\scripts\excel_job.py --input ".\input.xlsx" --output ".\output.xlsx"
• Start-Process ".\output.xlsx"