xlsx-generator

What this skill does

# XLSX Generator

## When to Use This Skill

Use this skill when:
- Creating Excel spreadsheets programmatically from data or specifications
- Populating branded templates with dynamic content while preserving formatting
- Extracting cell data, formulas, and structure from existing XLSX files
- Finding and replacing placeholder text like `{{TITLE}}` or `${date}` in cells
- Automating spreadsheet generation workflows (reports, data exports, financial statements)

Do NOT use this skill when:
- User wants to open/view spreadsheets (use native Excel or viewer)
- Complex pivot tables or charts are required (limited support)
- Working with older .xls format (XLSX only)
- Real-time collaborative editing is needed

## Prerequisites

- Deno installed (https://deno.land/)
- Input XLSX files for template-based operations
- JSON specification for scratch generation

## Quick Start

### Two Modes of Operation

1. **Template Mode**: Modify existing branded templates
   - Analyze template to find placeholders and structure
   - Replace `{{PLACEHOLDERS}}` with actual values

2. **Scratch Mode**: Create spreadsheets from nothing using JSON specifications

## Instructions

### Mode 1: Template-Based Generation

#### Step 1a: Analyze the Template

Extract cell inventory to understand what can be replaced:

```bash
deno run --allow-read scripts/analyze-template.ts financial-template.xlsx > inventory.json
```

**Output** (inventory.json):
```json
{
  "filename": "financial-template.xlsx",
  "sheetCount": 3,
  "sheets": [
    {
      "name": "Summary",
      "rowCount": 25,
      "colCount": 8,
      "usedRange": "A1:H25",
      "cells": [
        { "address": "A1", "row": 1, "col": 1, "value": "{{REPORT_TITLE}}", "type": "string" },
        { "address": "B3", "row": 3, "col": 2, "value": "{{DATE}}", "type": "string" },
        { "address": "C5", "row": 5, "col": 3, "value": null, "type": "number", "formula": "SUM(C6:C20)" }
      ]
    }
  ],
  "placeholders": [
    { "tag": "{{REPORT_TITLE}}", "location": "Summary!A1", "sheet": "Summary", "address": "A1" },
    { "tag": "{{DATE}}", "location": "Summary!B3", "sheet": "Summary", "address": "B3" }
  ],
  "hasFormulas": true
}
```

#### Step 1b: Create Replacement Specification

Create `replacements.json`:
```json
{
  "textReplacements": [
    { "tag": "{{REPORT_TITLE}}", "value": "Q4 2024 Financial Report" },
    { "tag": "{{DATE}}", "value": "December 15, 2024" },
    { "tag": "{{COMPANY}}", "value": "Acme Corporation", "sheets": ["Summary", "Cover"] }
  ],
  "cellUpdates": [
    { "sheet": "Data", "address": "B5", "value": 1250000 },
    { "sheet": "Data", "address": "B6", "value": 750000 }
  ]
}
```

#### Step 1c: Generate Output

```bash
deno run --allow-read --allow-write scripts/generate-from-template.ts \
  financial-template.xlsx replacements.json output.xlsx
```

### Mode 2: From-Scratch Generation

#### Step 2a: Create Specification

Create `spec.json`:
```json
{
  "title": "Sales Report",
  "author": "Finance Team",
  "sheets": [
    {
      "name": "Sales Data",
      "data": [
        ["Product", "Q1", "Q2", "Q3", "Q4", "Total"],
        ["Widget A", 10000, 12000, 15000, 18000, null],
        ["Widget B", 8000, 9000, 11000, 13000, null],
        ["Widget C", 5000, 6000, 7000, 8000, null]
      ],
      "cells": [
        { "address": "F2", "formula": "SUM(B2:E2)" },
        { "address": "F3", "formula": "SUM(B3:E3)" },
        { "address": "F4", "formula": "SUM(B4:E4)" }
      ],
      "columns": [
        { "col": "A", "width": 15 },
        { "col": "B", "width": 10 },
        { "col": "C", "width": 10 },
        { "col": "D", "width": 10 },
        { "col": "E", "width": 10 },
        { "col": "F", "width": 12 }
      ],
      "freezePane": "A2",
      "autoFilter": "A1:F4"
    }
  ]
}
```

#### Step 2b: Generate Spreadsheet

```bash
deno run --allow-read --allow-write scripts/generate-scratch.ts spec.json output.xlsx
```

## Examples

### Example 1: Monthly Sales Report

**Scenario**: Generate a monthly sales report from template.

**Steps**:
```bash
# 1. Analyze template for replaceable content
deno run --allow-read scripts/analyze-template.ts sales-template.xlsx --pretty

# 2. Create replacements.json with monthly data
# 3. Generate report
deno run --allow-read --allow-write scripts/generate-from-template.ts \
  sales-template.xlsx replacements.json November-Sales.xlsx
```

### Example 2: Data Export with Formulas

**Scenario**: Create a spreadsheet with calculated totals.

**spec.json**:
```json
{
  "sheets": [{
    "name": "Expenses",
    "data": [
      ["Category", "January", "February", "March", "Total"],
      ["Office", 1500, 1600, 1400, null],
      ["Travel", 3000, 2500, 4000, null],
      ["Software", 500, 500, 500, null],
      ["Total", null, null, null, null]
    ],
    "cells": [
      { "address": "E2", "formula": "SUM(B2:D2)" },
      { "address": "E3", "formula": "SUM(B3:D3)" },
      { "address": "E4", "formula": "SUM(B4:D4)" },
      { "address": "B5", "formula": "SUM(B2:B4)" },
      { "address": "C5", "formula": "SUM(C2:C4)" },
      { "address": "D5", "formula": "SUM(D2:D4)" },
      { "address": "E5", "formula": "SUM(E2:E4)" }
    ]
  }]
}
```

### Example 3: Multi-Sheet Workbook

**Scenario**: Create a workbook with summary and detail sheets.

**spec.json**:
```json
{
  "title": "Q4 Report",
  "sheets": [
    {
      "name": "Summary",
      "data": [
        ["Department", "Budget", "Actual", "Variance"],
        ["Sales", 500000, 520000, null],
        ["Marketing", 200000, 195000, null]
      ],
      "cells": [
        { "address": "D2", "formula": "C2-B2" },
        { "address": "D3", "formula": "C3-B3" }
      ]
    },
    {
      "name": "Sales Detail",
      "data": [
        ["Month", "Revenue", "Cost", "Profit"],
        ["October", 180000, 120000, null],
        ["November", 170000, 115000, null],
        ["December", 170000, 110000, null]
      ],
      "cells": [
        { "address": "D2", "formula": "B2-C2" },
        { "address": "D3", "formula": "B3-C3" },
        { "address": "D4", "formula": "B4-C4" }
      ]
    }
  ]
}
```

## Script Reference

| Script | Purpose | Permissions |
|--------|---------|-------------|
| `analyze-template.ts` | Extract cells, formulas, placeholders from XLSX | `--allow-read` |
| `generate-from-template.ts` | Replace placeholders in templates | `--allow-read --allow-write` |
| `generate-scratch.ts` | Create XLSX from JSON specification | `--allow-read --allow-write` |

## Specification Reference

### Sheet Options

| Property | Type | Description |
|----------|------|-------------|
| `name` | string | Sheet name |
| `data` | array | 2D array of cell values starting at A1 |
| `cells` | array | Individual cell specifications |
| `rows` | array | Row-based data specifications |
| `columns` | array | Column width and visibility settings |
| `merges` | array | Merged cell ranges |
| `freezePane` | string | Freeze panes at this cell (e.g., "A2") |
| `autoFilter` | string | Auto-filter range (e.g., "A1:F10") |

### Cell Options

| Property | Type | Description |
|----------|------|-------------|
| `address` | string | Cell address (e.g., "A1", "B2") |
| `value` | mixed | Cell value (string, number, boolean, null) |
| `formula` | string | Formula without = sign |
| `format` | string | Number format (e.g., "#,##0.00") |
| `type` | string | Force type: "string", "number", "boolean", "date" |

### Column Options

| Property | Type | Description |
|----------|------|-------------|
| `col` | string | Column letter (e.g., "A", "B", "AA") |
| `width` | number | Column width in characters |
| `hidden` | boolean | Hide column |

### Template Replacement Options

| Property | Type | Description |
|----------|------|-------------|
| `tag` | string | Placeholder to find (e.g., "{{TITLE}}") |
| `value` | mixed | Replacement value |
| `sheets` | array | Limit to specific sheets |
| `range` | string | Limit to cell range (e.g., "A1:D10") |

## Common Iss