toolsets/xlsx-toolkit/doc.md

# xlsx‑toolkit

Spreadsheet creation, editing, inspection, and conversion — with code bindings
for the operations that agents previously had to write throw‑away scripts for.

---

## When to Use This Toolset

- Reading data from an existing `.xlsx` or `.xlsm` file
- Inspecting sheet structure before editing
- Creating a new spreadsheet from structured data (list of dicts → `.xlsx`)
- Converting spreadsheet data to CSV for further processing
- Editing values, formulas, or formatting in an existing workbook
- Working with multiple sheets in a single file

---

## Process

### Creating a Spreadsheet

```
Understand → Design → Build → Analyze → Finalize
```

1. **Understand** the data: what are the columns? What type of data (numbers, dates, text)?
   What will the consumer do with this spreadsheet?
2. **Design** the layout: columns, sheet names, formatting, any formulas.
   Use `xlsx_create` with clear column names and typed data.
3. **Build** the spreadsheet with `xlsx_create`. The function auto‑formats headers
   and auto‑widths columns.
4. **Analyze** with `xlsx_read` to verify the data reads back correctly.
5. **Finalize**: write to the target path and verify with `xlsx_sheets`.

### Editing a Spreadsheet

```
Open → Understand → Change → Verify
```

1. **Open** (or list sheets with `xlsx_sheets` to understand structure)
2. **Understand** dependencies: read the sheet with `xlsx_read`, check for formulas,
   merged cells, and data validation that might break
3. **Change** values carefully — the functions in this toolset handle data read/write.
   For formula editing, `openpyxl` preserves existing formulas when you modify cells.
4. **Verify** by reading back the changed cells/sheets

---

## Function Reference

### `xlsx_read`

Read an Excel sheet and return structured JSON.

**When to use:** Whenever you need to inspect or extract data from a spreadsheet.
Use this before editing so you understand the content.

**Args:**
- `filepath` (str) — Path to the `.xlsx` file
- `sheet` (str, optional) — Sheet name; defaults to the first sheet
- `max_rows` (int, optional) — Limit rows returned (0 = all)

**Returns:** `{sheet, columns, rows, count}`

**Gotcha:** For very large files (100k+ rows), use `max_rows` to avoid timeouts.
Open in chunks if you need everything.

### `xlsx_sheets`

List all sheets with row and column counts.

**When to use:** Before editing a workbook you haven't seen before.
Gives you a quick overview without reading all data.

**Args:**
- `filepath` (str) — Path to the `.xlsx` file

**Returns:** `{filename, sheets: [{name, rows, cols}]}`

### `xlsx_to_csv`

Convert a sheet to CSV text.

**When to use:** When you need to pipe spreadsheet data into another tool
that doesn't understand Excel format (e.g., command-line utilities, text processing).

**Args:**
- `filepath` (str) — Path to the `.xlsx` file
- `sheet` (str, optional) — Sheet name
- `delimiter` (str, optional) — Field delimiter (default comma)

**Returns:** `{csv, sheet, rows, columns}`

### `xlsx_create`

Create a new `.xlsx` from structured data.

**When to use:** When you need to generate a spreadsheet as output — report generation,
data exports, structured deliverables.

**Args:**
- `filepath` (str) — Where to write the new `.xlsx`
- `sheet` (str, optional) — Sheet name (default "Sheet1")
- `columns` (list) — Column header strings
- `rows` (list) — List of dicts keyed by column name, or list of lists

**Returns:** `{written, sheet, columns, rows}`

**Design tip:** Use descriptive column names. The header row will be styled
(bold white text on indigo background) for readability.

---

## Common Patterns

### Pattern 1: Inspect, Extract, Process

```
xlsx_sheets → understand structure
xlsx_read   → extract data (optionally filtered to specific sheet)
Process the JSON in your application logic
```

### Pattern 2: Data Export (CSV → XLSX)

```
Parse CSV → list of dicts
xlsx_create → formatted .xlsx output with styled headers
```

### Pattern 3: Multi‑Sheet Report

```
Call xlsx_create multiple times with different sheet names
(on the same file — openpyxl supports this)
```

---

## Formula Guidelines

When working with spreadsheets that contain formulas:

1. **Preserve formulas.** The `xlsx_read` function returns computed values (data_only=True).
   When editing with openpyxl directly, write to cells with formulas intact.
2. **Check dependencies.** Before changing a cell, check if other cells reference it.
3. **Document complex formulas.** If you're creating formulas, include a comment explaining the logic.
4. **Validate.** After editing formulas, read back the computed values to ensure correctness.
5. **Use named ranges** for complex spreadsheets to make formulas readable.

---

## Guidelines

### Do
- Always inspect with `xlsx_sheets` before editing an unfamiliar file
- Use `xlsx_read` with `max_rows` for previewing large files
- Verify data after writing — read it back to confirm
- Use descriptive column names when creating spreadsheets
- Handle empty cells explicitly (they come back as `None` in JSON)

### Don't
- Don't modify sheets with complex macros unless you understand them
- Don't assume the first row is always headers — inspect first
- Don't write to files that are open in Excel (you'll get a lock error)
- Don't use delimiters that appear in your data without proper quoting

### Edge Cases
- **Empty sheets**: `xlsx_read` returns `columns: [], rows: [], count: 0`
- **Missing files**: All functions return `{error: "File not found: ..."}`
- **Encrypted files**: Cannot be read by openpyxl in read-only mode
- **Very wide columns**: Auto-width is capped at 60 characters in `xlsx_create`