xlsx

name	xlsx
description	Use for spreadsheet tasks where the workbook itself matters: read, repair, populate, reconcile, calculate, format, or export XLSX/XLSM/CSV/TSV artifacts while preserving sheets, formulas, number formats, styles, tables, named ranges, and deterministic cell-level validation.

Spreadsheet Workbooks

When To Use

Use this skill when the deliverable is a spreadsheet or when correctness is judged through workbook structure: formulas, sheets, styles, named ranges, tables, hidden data, or visible number formats. If tabular data is only an intermediate for a database or PDF deliverable, use the primary artifact skill and this skill only for workbook internals.

First Pass

1. Open the workbook with data_only=False and inventory sheets, dimensions, formulas, merged cells, tables, named ranges, validations, and styles.
2. Reopen with data_only=True only to inspect cached values. OpenPyXL does not calculate formulas.
3. Separate inputs, formulas, outputs, presentation ranges, and cells that tests will inspect.
4. Decide whether the task wants formula behavior, computed numeric values, or both. Do not replace formulas with values unless required.

Implementation Patterns

Workbook Inventory

from openpyxl import load_workbook

wb = load_workbook("input.xlsx", data_only=False)
for ws in wb.worksheets:
    formulas = []
    for row in ws.iter_rows():
        for cell in row:
            if isinstance(cell.value, str) and cell.value.startswith("="):
                formulas.append(cell.coordinate)
    print(ws.title, ws.max_row, ws.max_column, formulas[:20])
    print("merged", list(ws.merged_cells.ranges))
    print("tables", list(ws.tables))
print("named ranges", [dn.name for dn in wb.defined_names.values()])

Preserve Styles While Writing

from copy import copy

def copy_cell_style(src, dst):
    dst.font = copy(src.font)
    dst.fill = copy(src.fill)
    dst.border = copy(src.border)
    dst.alignment = copy(src.alignment)
    dst.number_format = src.number_format
    dst.protection = copy(src.protection)

For merged cells, write only to the top-left anchor. If you extend an Excel table, update its .ref so readers include appended rows.

Formula Construction

for row in range(2, ws.max_row + 1):
    ws[f"E{row}"] = f"=C{row}*D{row}"
    ws[f"E{row}"].number_format = '#,##0.00;(#,##0.00);-'

Use absolute references when a copied formula must keep the same assumption cell and relative references when each row should point at its own inputs.

Missing Value Recovery

Recover missing cells from workbook relationships, not hardcoded examples:

for row in range(2, ws.max_row + 1):
    if ws[f"D{row}"].value == "???":
        ws[f"D{row}"] = f"=B{row}*C{row}"

If tests require numeric values and there is no recalculation engine, compute the numeric answer independently and write it only for cells that should be values rather than formulas.

Financial Model Formatting

Use existing template conventions first. If no convention exists:

• blue font for hardcoded inputs;
• black font for formulas;
• green font for links to another worksheet;
• red font for external links;
• zeros displayed as -;
• negative numbers in parentheses.

Validation

• Save, reopen, and inspect the saved workbook, not the in-memory object.
• Confirm required sheet names, order, formulas, number formats, widths, hidden sheets, tables, validations, and named ranges.
• Independently recompute key totals in Python and compare to workbook outputs.
• Confirm there are no unresolved placeholders such as ??? when recovery is requested.
• If formulas must recalculate, use LibreOffice or another recalculation step before checking cached values.

Common Failures

• Flattening through pandas and losing formulas, styles, tables, or merged cells.
• Trusting cached formula values after editing with OpenPyXL.
• Writing display strings into numeric cells.
• Breaking formula ranges after inserting rows.
• Hardcoding answers into cells that should remain formula-driven.