Skill GLOBAL es
Excel Spreadsheet Processing
Crea, lee, edita y analiza hojas de cálculo Excel (.xlsx): fórmulas, formato profesional, gráficos, limpieza de datos, modelos financieros con convenciones de color, ceros como guiones y validación de errores.
Contenido completo
---
name: xlsx
description: Use this skill for any spreadsheet task — creating, reading, editing, formatting, or analyzing .xlsx/.xlsm/.csv/.tsv files. Trigger on any mention of Excel, spreadsheets, or tabular data where the deliverable must be a spreadsheet file. Do NOT trigger when the deliverable is a Word document, HTML report, or standalone script.
---
# XLSX Processing
## Critical Rules
- **ZERO formula errors** — every deliverable must be error-free (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
- **Use Excel formulas, not hardcoded values** — always let Excel calculate
- **Preserve existing templates** — match format/style exactly; never override established conventions
- **Recalculate after writing formulas** — use `scripts/recalc.py` or LibreOffice
## Quick Reference
| Task | Tool | Key API |
|------|------|---------|
| Read/analyze data | pandas | `pd.read_excel()` |
| Create/edit with formulas | openpyxl | `wb = Workbook()` / `load_workbook()` |
| Format cells | openpyxl | `Font`, `PatternFill`, `Alignment` |
| Recalculate formulas | LibreOffice | `scripts/recalc.py` |
| CLI convert | LibreOffice | `libreoffice --headless --convert-to` |
## pandas — Data Analysis
```python
import pandas as pd
# Read
df = pd.read_excel('file.xlsx') # first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None) # all sheets
# Analyze
df.head() # preview
df.info() # column types
df.describe() # statistics
# Write
df.to_excel('output.xlsx', index=False)
# Tips
pd.read_excel('f.xlsx', dtype={'id': str}) # force types
pd.read_excel('f.xlsx', usecols=['A', 'C', 'E']) # specific cols
pd.read_excel('f.xlsx', parse_dates=['date_col']) # parse dates
pd.read_excel('f.xlsx', na_values=['-', 'N/A', '']) # custom NaN
```
## openpyxl — Create, Edit & Format
```python
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# Create
wb = Workbook()
ws = wb.active
ws['A1'] = 'Header'
ws.append(['Col1', 'Col2', 'Col3'])
ws['A3'] = '=SUM(A1:A2)'
# Format
ws['A1'].font = Font(bold=True, color='FF0000', name='Arial', size=11)
ws['A1'].fill = PatternFill('solid', start_color='FFFF00')
ws['A1'].alignment = Alignment(horizontal='center')
ws.column_dimensions['A'].width = 20
# Borders
thin = Side(style='thin', color='CCCCCC')
ws['A1'].border = Border(top=thin, bottom=thin, left=thin, right=thin)
# Save
wb.save('output.xlsx')
```
### Edit Existing
```python
wb = load_workbook('existing.xlsx')
ws = wb.active # or wb['SheetName']
# Modify
ws['A1'] = 'New value'
ws.insert_rows(2) # insert at row 2
ws.delete_cols(3) # delete col C
# Multiple sheets
for name in wb.sheetnames:
print(f"Sheet: {name}")
# New sheet
new = wb.create_sheet('Summary')
new['A1'] = '=SUM(Data!A2:A100)'
wb.save('modified.xlsx')
# Read calculated values (WARNING: saves formulas as values!)
wb = load_workbook('f.xlsx', data_only=True)
```
## Formula Best Practices
### ✅ CORRECT — Use formulas
```python
ws['B10'] = '=SUM(B2:B9)'
ws['C5'] = '=(C4-C2)/C2'
ws['D20'] = '=AVERAGE(D2:D19)'
ws['E1'] = '=IFERROR(VLOOKUP(A1,Data!A:B,2,FALSE),"-")'
```
### ❌ WRONG — Hardcode calculated values
```python
ws['B10'] = 5000 # Don't do this
ws['C5'] = 0.15 # Don't do this
```
### Assumptions in Separate Cells
```python
# Put assumptions in a dedicated section
ws['B1'] = 'Growth Rate'
ws['C1'] = 0.05 # hardcoded assumption
# Reference in formulas
ws['B5'] = '=B4*(1+$C$1)'
```
## Financial Model Conventions
### Color Coding
| Color | RGB | Use |
|-------|-----|-----|
| Blue | (0,0,255) | Hardcoded inputs / scenario variables |
| Black | (0,0,0) | All formulas |
| Green | (0,128,0) | Cross-sheet links |
| Red | (255,0,0) | External file links |
| Yellow bg | (255,255,0) | Key assumptions |
### Number Formatting
| Item | Format | Example |
|------|--------|---------|
| Years | Text | "2024" (not 2,024) |
| Currency | `$#,##0;($#,##0);-` | $1,500 or "-" |
| Percentages | `0.0%` | 15.3% |
| Multiples | `0.0x` | 12.5x |
| Negatives | `(123)` not -123 | (1,500) |
### Source Documentation
```python
# In adjacent cell
ws['D1'] = "Source: 10-K FY2024, p.45, [SEC EDGAR URL]"
```
## Recalculate Formulas
After writing formulas with openpyxl, values are not computed. Recalculate:
```bash
python scripts/recalc.py output.xlsx 30
```
The script returns JSON:
```json
{
"status": "success",
"total_errors": 0,
"total_formulas": 42
}
```
If `status: "errors_found"`, fix errors listed in `error_summary` and rerun.
## Verification Checklist
- [ ] Test 2-3 sample references before scaling
- [ ] Verify column mapping (col 64 = BL)
- [ ] Row offset: Excel is 1-indexed (df row 5 → Excel row 6)
- [ ] Check NaN values with `pd.notna()`
- [ ] Guard against #DIV/0! — use `=IFERROR(A/B, "-")`
- [ ] Cross-sheet refs: `Sheet1!A1` format
- [ ] No circular references
- [ ] Recalculate all formulas before delivery
- [ ] Scan for zero formula errors