# spreadsheet

> Read, create, edit, and review spreadsheets; manage formulas, formatting, and comments. Use when users mention Excel, xlsx, spreadsheets, or spreadsheet automation.

- Author: Levi Lian
- Repository: 7th-ave-labs/docagent-skills
- Version: 20260120235513
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/7th-ave-labs/docagent-skills
- Web: https://mule.run/skillshub/@@7th-ave-labs/docagent-skills~spreadsheet:20260120235513

---

---
name: spreadsheet
description: Read, create, edit, and review spreadsheets; manage formulas, formatting, and comments. Use when users mention Excel, xlsx, spreadsheets, or spreadsheet automation.
---

# Spreadsheet Reading, Creation, and Editing

## Overview

Use `openpyxl` for Excel files with formulas/formatting, `pandas` for data analysis. After creating/editing spreadsheets with formulas, use `recalc.py` to recalculate values.

## Reading Spreadsheets

### With pandas (data analysis)
```python
import pandas as pd

df = pd.read_excel('file.xlsx')           # First sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # All sheets

df.head()       # Preview
df.describe()   # Statistics
```

### With openpyxl (formulas/formatting)
```python
from openpyxl import load_workbook

wb = load_workbook('file.xlsx')
sheet = wb.active

# Read cell values
for row in sheet.iter_rows(min_row=1, max_row=10, values_only=True):
    print(row)
```

## Creating Spreadsheets

```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
sheet = wb.active

# Add data
sheet['A1'] = 'Revenue'
sheet['B1'] = 'Cost'
sheet['C1'] = 'Profit'

# Add values
sheet['A2'] = 1000
sheet['B2'] = 600

# CRITICAL: Use formulas, not hardcoded calculations
sheet['C2'] = '=A2-B2'  # Good: Excel formula
# sheet['C2'] = 400     # Bad: Hardcoded value

# Basic formatting
sheet['A1'].font = Font(bold=True)
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet.column_dimensions['A'].width = 15

wb.save('/root/workspace/output.xlsx')
```

**After creating**: Save the XLSX under `/root/workspace` so it syncs automatically.

---

## Formatting for Readability (Optional)

Use `/root/skills/spreadsheet/scripts/format.py` to apply readable formatting (text wrapping, column widths, row heights) to spreadsheets with long text content. Skip for data exports or when users will format themselves.

### Command Line

```bash
# Apply all readable formatting defaults (wrap + auto-height)
python /root/skills/spreadsheet/scripts/format.py file.xlsx --readable

# Apply readable formatting with header styling
python /root/skills/spreadsheet/scripts/format.py file.xlsx --readable --header-style

# Use shrink-to-fit instead of wrap
python /root/skills/spreadsheet/scripts/format.py file.xlsx --readable --wrap-mode shrink

# Enable text wrapping only
python /root/skills/spreadsheet/scripts/format.py file.xlsx --wrap

# Set specific column widths
python /root/skills/spreadsheet/scripts/format.py file.xlsx --columns A=25,B=50,C=60

# Set uniform row height for data rows
python /root/skills/spreadsheet/scripts/format.py file.xlsx --row-height 60

# Auto-estimate row heights based on content
python /root/skills/spreadsheet/scripts/format.py file.xlsx --wrap --auto-height

# Full control
python /root/skills/spreadsheet/scripts/format.py file.xlsx --columns A=25,B=50 --wrap --auto-height --header-height 35 --header-style
```

### Python API

For Python API usage, see [reference.md](reference.md).


**When to use:** Reports, requirements tables, regulatory documents, anything with long text cells.
**When to skip:** Data exports, files for programmatic processing, when preserving existing formatting.
**Default post-process:** `recalc.py` then `format.py --readable` for long-text outputs.

## Editing Spreadsheets

```python
from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active

# Modify cells
sheet['A1'] = 'New Value'
sheet.insert_rows(2)
sheet.delete_cols(3)

# Add new sheet
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'

wb.save('modified.xlsx')
```

---

## Cell Comments (Review Workflow)

Use `/root/skills/spreadsheet/scripts/comments.py` to add, list, update, and delete cell comments for spreadsheet review.

### Command Line

```bash
# Add a comment to a cell
python /root/skills/spreadsheet/scripts/comments.py add file.xlsx B5 "Check this formula" --author "Reviewer"

# List all comments
python /root/skills/spreadsheet/scripts/comments.py list file.xlsx

# Get a specific comment
python /root/skills/spreadsheet/scripts/comments.py get file.xlsx B5

# Update a comment
python /root/skills/spreadsheet/scripts/comments.py update file.xlsx B5 "Updated comment text"

# Delete a comment
python /root/skills/spreadsheet/scripts/comments.py delete file.xlsx B5

# Delete all comments
python /root/skills/spreadsheet/scripts/comments.py delete-all file.xlsx
```

### Python API

For Python API usage, see [reference.md](reference.md).


## Formula Recalculation (REQUIRED)

openpyxl saves formulas as strings but doesn't calculate values. Use `recalc.py`:

```bash
python /root/skills/spreadsheet/scripts/recalc.py /root/workspace/output.xlsx
```

The script returns JSON with error details:
```json
{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42
}
```

If errors found, fix and recalculate again:
- `#REF!`: Invalid cell references
- `#DIV/0!`: Division by zero
- `#VALUE!`: Wrong data type
- `#NAME?`: Unrecognized formula name

## Visual Verification (REQUIRED)

After creating or modifying a spreadsheet, you must render it to verify the layout, column widths, and formatting.

```bash
python /root/skills/spreadsheet/scripts/render.py file.xlsx --output-dir /root/tmp/render
# Then call: view { path: "/root/tmp/render/page-1.png" }
```

Inspect the image to ensure:
- Columns are wide enough to show all text (no "#####")
- Headers are styled correctly
- Number formatting is applied (e.g., currency symbols, decimals)

## Quality Expectations

### Zero Formula Errors
Every spreadsheet MUST have zero formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

### Use Formulas, Not Hardcoded Values
```python
# ❌ WRONG
total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

# ✅ CORRECT
sheet['B10'] = '=SUM(B2:B9)'
```

### No AI Citation Tokens
Never include `[145036110387964†L158-L160]` or `【turn...】` in spreadsheets

### Preserve Existing Formatting
When editing existing spreadsheets, EXACTLY match existing format and style

---

## Formula Best Practices

### Do's
- Use cell references instead of hardcoded values: `=B5*(1+$B$3)` not `=B5*1.05`
- Use absolute (`$B$4`) or relative (`B4`) references appropriately
- Use helper cells for intermediate calculations
- Cite sources in cell comments for hardcoded inputs

### Don'ts
- DO NOT use dynamic array formulas (FILTER, XLOOKUP, SORT, SEQUENCE)
- DO NOT use volatile functions (INDIRECT, OFFSET) unless necessary
- DO NOT use `=TABLE` or data tables

---

## Financial Model Standards

### Color Conventions
- **Blue text (0,0,255)**: Hardcoded inputs
- **Black text (0,0,0)**: Formulas and calculations
- **Green text (0,128,0)**: Links from other worksheets
- **Red text (255,0,0)**: External links
- **Yellow background (255,255,0)**: Key assumptions

### Number Formatting
- Years: Text strings ("2024" not "2,024")
- Currency: $#,##0 with units in headers ("Revenue ($mm)")
- Zeros: Format as "-"
- Percentages: 0.0% (one decimal)
- Multiples: 0.0x
- Negatives: Parentheses (123) not minus -123

### Documentation
Cite sources for hardcodes:
```
Source: Company 10-K, FY2024, Page 45, Revenue Note
Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity
```

---

## Formula Verification Checklist

### Essential Verification
- [ ] **Test 2-3 sample references**: Verify they pull correct values before building full model
- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL, not BK)
- [ ] **Row offset**: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)

### Common Pitfalls
- [ ] **NaN handling**: Check for null values with `pd.notna()`
- [ ] **Far-right columns**: FY data often in columns 50+
- [ ] **Multiple matches**: Search all occurrences, not just first
- [ ] **Division by zero**: Check denominators before using `/` in formulas (#DIV/0!)
- [ ] **Wrong references**: Verify all cell references point to intended cells (#REF!)
- [ ] **Cross-sheet references**: Use correct format (Sheet1!A1) for linking sheets

### Formula Testing Strategy
- [ ] **Start small**: Test formulas on 2-3 cells before applying broadly
- [ ] **Verify dependencies**: Check all cells referenced in formulas exist
- [ ] **Test edge cases**: Include zero, negative, and very large values