# Xlsx Generator

> Create and manipulate Excel XLSX files programmatically.

Create and manipulate Excel XLSX files programmatically. Use when the user needs to generate spreadsheets, modify XLSX templates, extract spreadsheet content, or automate Excel workflows. Supports both template-based generation (for branding compliance) and from-scratch creation. Keywords: Excel, XLSX, spreadsheet, workbook, worksheet, data, report, template, financial, analysis.

- Page: https://ss33.ai/skills/jwynia/xlsx-generator-48425988097
- Publisher: jwynia
- Category: [Data & Analytics](https://ss33.ai/skills/category/data-and-analytics-m1r3i9) › [Analysis & BI](https://ss33.ai/skills/category/data-and-analytics-m1r3i9/analysis-and-bi-b2nit8)
- License: MIT
- Source: https://github.com/jwynia/agent-skills/blob/e02ec7e226a6e4f8419fd3b88a1d8e472d421b32/skills/general/document-processing/spreadsheet/xlsx-generator/SKILL.md

## Skill

SKILL.md: https://ss33.ai/api/v1/ss33/download/jwynia/xlsx-generator-48425988097

# 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 Issues and Solutions

### Issue: Placeholders not being replaced

**Symptoms**: Output XLSX still contains `{{PLACEHOLDER}}` tags.

**Solution**:
1. Run `analyze-template.ts` to verify exact tag text and location
2. Check that placeholder is in a string cell, not a formula
3. Verify sheet filter in replacement spec

### Issue: Formulas showing as text

**Symptoms**: Formulas display as text instead of calculating.

**Solution**:
- Ensure formula doesn't start with "=" in spec (it's added automatically)
- Check cell type is not forced to "string"

### Issue: Numbers formatted as text

**Symptoms**: Numbers have green triangle indicating text storage.

**Solution**:
- Use numeric values in spec, not quoted strings
- For template replacement, if entire cell is placeholder and replacement is number, it converts automatically

### Issue: Column widths not applied

**Symptoms**: Columns have default width despite specification.

**Solution**:
- Ensure column letters are uppercase
- Verify column spec is in array format

## Limitations

- **XLSX only**: Does not support legacy .xls or .xlsb formats
- **No macros**: Cannot create or preserve VBA macros
- **Limited charting**: No native chart creation support
- **No pivot tables**: Cannot create pivot tables programmatically
- **Basic styling**: Limited cell formatting options
- **No conditional formatting**: Cannot set conditional format rules
- **Formula recalc**: Formulas are stored but not recalculated (Excel recalculates on open)

## Related Skills

- **pptx-generator**: For creating PowerPoint presentations
- **docx-generator**: For creating Word documents
- **csv-processor**: For simpler CSV data processing