anthropics / anthropics/skills
xlsx skill missing critical Excel compatibility patterns
- 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.