anthropics / anthropics/skills

xlsx skill missing critical Excel compatibility patterns

Aberta
#584 0 comentários 2 reações 0 responsáveis Ver no GitHub
Linguagem predominante
Python
Estrelas
176k
Forks
20.8k
Merge médio
7h 21min
PRs com merge (30d)
5

Descrição

## Summary

The `xlsx` skill is missing several critical patterns for building formula-heavy Excel workbooks with openpyxl. These gaps cause "Excel needs to repair this file" dialogs, silently broken formulas, and hours of debugging. Discovered while building a multi-tab computational workbook (11 tabs, ~3000 formulas, cross-sheet INDEX/MATCH, LET formulas, MAXIFS, data validation across 500+ cells).

## Gap 1: OOXML Function Prefixes (Critical)

Post-2010 Excel functions require XML namespace prefixes when written via openpyxl. Without them, Excel flags the file as corrupted and strips/repairs formulas silently.

The skill doesn't mention this at all. Key functions affected:

| Function | Required Prefix |
|----------|----------------|
| `LET` | `_xlfn.LET` |
| LET variables | `_xlpm.` (every variable name) |
| `MAXIFS` | `_xlfn.MAXIFS` |
| `TEXTJOIN` | `_xlfn.TEXTJOIN` |
| `XLOOKUP` | `_xlfn.XLOOKUP` |
| `FILTER` | `_xlfn._xlws.FILTER` |
| `SORT` | `_xlfn._xlws.SORT` |

Example — this silently breaks:
```python
ws['A1'] = '=LET(x, B1, IF(x>0, x, 0))'
```

Must be:
```python
ws['A1'] = '=_xlfn.LET(_xlpm.x, B1, IF(_xlpm.x>0, _xlpm.x, 0))'
```

The `_xlpm.` prefix on LET variable names is especially non-obvious and is the most commonly missed requirement.

## Gap 2: Data Validation sqref Bloat (Critical)

When applying data validation to large ranges (100+ cells), openpyxl's `.add()` method writes individual cell references (`C159 C160 C161 ...`) into the XML sqref attribute. This bloats the XML past Excel's internal length limit and triggers the "repair needed" dialog.

The skill shows the `.add()` pattern without warning:
```python
# This triggers repair for large ranges
dv.add(ws.cell(row=row, column=3))
```

Fix is to use range-based sqref:
```python
dv.sqref = 'C5:C504' # Single range string — no bloat
```

## Gap 3: Conditional Formatting (ColorScaleRule)

The skill doesn't cover `ColorScaleRule` (3-color gradient heat maps), which is essential for scoring/metric workbooks. The polarity pattern (green-high for positive metrics, red-high for warning signals) and the correct parameter structure are non-trivial to get right.

## Gap 4: Empty Cell Guards

Formulas referencing potentially empty cells (e.g., `=Setup!B88`) return `0` instead of blank. This causes false data to appear in unused rows. The fix is an IF guard pattern: `=IF(Setup!B88="","",Setup!B88)` and cascading guards for dependent rows. Not mentioned in the skill.

## Gap 5: Multi-Letter Column String Comparison

When applying formatting conditionally by column letter, `'AA' >= 'E'` evaluates to `False` (string comparison: `'A' < 'E'`). This silently skips columns AA onwards. Must use index-based comparison instead. A subtle bug that wastes significant debugging time.

## Gap 6: Cross-Sheet Formula Patterns

The skill doesn't cover the most common formula patterns for multi-tab workbooks:
- Name-based INDEX/MATCH lookups between tabs
- Two-dimensional INDEX/MATCH (row + column lookup)
- MAXIFS across grouped columns with a reference row

## Gap 7: Formula Debugging

No guidance on inspecting formulas in built workbooks (`load_workbook` without `data_only=True`), scanning for missing prefixes, or inspecting xlsx XML structure when troubleshooting repair prompts.

## Suggestion

The skill is well-structured for basic read/write/format operations but doesn't cover the Excel compatibility layer where complex workbook builds actually break. Adding sections on OOXML prefixes and sqref handling alone would prevent the two most common failure modes.

Guia de contribuição

Nenhum guia de contribuição indexado para este repositório

Avaliação

Esta issue ainda não foi avaliada.

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.