xlsx-generator

Original source / Raw SKILL.md

---
name: xlsx-generator
description: "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."
license: MIT
compatibility: Requires Deno with --allow-read, --allow-write permissions
metadata:
  author: agent-skills
  version: "1.0"
---

# 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


---

## Referenced Files

> The following files are referenced in this skill and included for context.

### scripts/analyze-template.ts

```typescript
#!/usr/bin/env -S deno run --allow-read

/**
 * analyze-template.ts - Extract structure and content from XLSX files
 *
 * Extracts sheets, cells, formulas, named ranges, and placeholders from Excel
 * spreadsheets for template analysis and content replacement planning.
 *
 * Usage:
 *   deno run --allow-read scripts/analyze-template.ts <input.xlsx> [options]
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *   --pretty         Pretty-print JSON output
 *   --sheet <name>   Analyze only specific sheet
 *
 * Permissions:
 *   --allow-read: Read XLSX file
 */

import { parseArgs } from "jsr:@std/[email protected]/parse-args";
import { basename } from "jsr:@std/[email protected]";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:[email protected]" as any;

// === Types ===

export interface CellInfo {
  address: string;
  row: number;
  col: number;
  value: string | number | boolean | null;
  formula?: string;
  type: "string" | "number" | "boolean" | "date" | "error" | "empty";
  format?: string;
}

export interface PlaceholderInfo {
  tag: string;
  location: string;
  sheet: string;
  address: string;
}

export interface SheetInfo {
  name: string;
  rowCount: number;
  colCount: number;
  usedRange: string;
  cells: CellInfo[];
  mergedCells: string[];
}

export interface NamedRange {
  name: string;
  ref: string;
  scope?: string;
}

export interface SpreadsheetInventory {
  filename: string;
  sheetCount: number;
  sheets: SheetInfo[];
  namedRanges: NamedRange[];
  placeholders: PlaceholderInfo[];
  hasFormulas: boolean;
  hasMacros: boolean;
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  pretty: boolean;
  sheet?: string;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "analyze-template";

// Placeholder patterns: {{PLACEHOLDER}} or ${placeholder}
const PLACEHOLDER_REGEX = /\{\{([^}]+)\}\}|\$\{([^}]+)\}/g;

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Extract structure from XLSX templates

Usage:
  deno run --allow-read scripts/${SCRIPT_NAME}.ts <input.xlsx> [options]

Arguments:
  <input.xlsx>     Path to the Excel spreadsheet to analyze

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output (to stderr)
  --pretty         Pretty-print JSON output (default: compact)
  --sheet <name>   Analyze only specific sheet

Examples:
  # Analyze spreadsheet
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx > inventory.json

  # With verbose output
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx -v --pretty

  # Analyze specific sheet
  deno run --allow-read scripts/${SCRIPT_NAME}.ts template.xlsx --sheet "Data"
`);
}

// === Utility Functions ===

function getCellType(cell: XLSX.CellObject | undefined): CellInfo["type"] {
  if (!cell) return "empty";
  switch (cell.t) {
    case "s": return "string";
    case "n": return "number";
    case "b": return "boolean";
    case "d": return "date";
    case "e": return "error";
    default: return "empty";
  }
}

function getCellValue(cell: XLSX.CellObject | undefined): string | number | boolean | null {
  if (!cell) return null;
  if (cell.v === undefined || cell.v === null) return null;
  return cell.v;
}

function decodeRange(range: string): { startRow: number; startCol: number; endRow: number; endCol: number } {
  const decoded = XLSX.utils.decode_range(range);
  return {
    startRow: decoded.s.r,
    startCol: decoded.s.c,
    endRow: decoded.e.r,
    endCol: decoded.e.c,
  };
}

function findPlaceholders(
  text: string,
  sheet: string,
  address: string
): PlaceholderInfo[] {
  const placeholders: PlaceholderInfo[] = [];
  let match;

  const regex = new RegExp(PLACEHOLDER_REGEX.source, "g");
  while ((match = regex.exec(text)) !== null) {
    placeholders.push({
      tag: match[0],
      location: `${sheet}!${address}`,
      sheet,
      address,
    });
  }

  return placeholders;
}

// === Core Logic ===

export async function analyzeSpreadsheet(
  xlsxPath: string,
  options: { verbose?: boolean; sheetFilter?: string } = {}
): Promise<SpreadsheetInventory> {
  const { verbose = false, sheetFilter } = options;

  // Read the XLSX file
  const data = await Deno.readFile(xlsxPath);
  const workbook = XLSX.read(data, { type: "array", cellFormula: true, cellStyles: true });

  const filename = basename(xlsxPath);

  if (verbose) {
    console.error(`Analyzing: ${filename}`);
    console.error(`Sheets: ${workbook.SheetNames.join(", ")}`);
  }

  const sheets: SheetInfo[] = [];
  const allPlaceholders: PlaceholderInfo[] = [];
  let hasFormulas = false;

  // Process each sheet
  for (const sheetName of workbook.SheetNames) {
    if (sheetFilter && sheetName !== sheetFilter) {
      continue;
    }

    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) continue;

    const ref = worksheet["!ref"];
    if (!ref) {
      // Empty sheet
      sheets.push({
        name: sheetName,
        rowCount: 0,
        colCount: 0,
        usedRange: "",
        cells: [],
        mergedCells: [],
      });
      continue;
    }

    const range = decodeRange(ref);
    const cells: CellInfo[] = [];

    // Iterate through all cells in range
    for (let row = range.startRow; row <= range.endRow; row++) {
      for (let col = range.startCol; col <= range.endCol; col++) {
        const address = XLSX.utils.encode_cell({ r: row, c: col });
        const cell = worksheet[address] as XLSX.CellObject | undefined;

        if (cell && cell.v !== undefined) {
          const cellInfo: CellInfo = {
            address,
            row: row + 1,  // 1-indexed for display
            col: col + 1,
            value: getCellValue(cell),
            type: getCellType(cell),
          };

          if (cell.f) {
            cellInfo.formula = cell.f;
            hasFormulas = true;
          }

          if (cell.z) {
            cellInfo.format = cell.z;
          }

          cells.push(cellInfo);

          // Check for placeholders in string values
          if (cellInfo.type === "string" && typeof cellInfo.value === "string") {
            const placeholders = findPlaceholders(cellInfo.value, sheetName, address);
            allPlaceholders.push(...placeholders);
          }
        }
      }
    }

    // Get merged cells
    const mergedCells: string[] = [];
    if (worksheet["!merges"]) {
      for (const merge of worksheet["!merges"]) {
        const startCell = XLSX.utils.encode_cell(merge.s);
        const endCell = XLSX.utils.encode_cell(merge.e);
        mergedCells.push(`${startCell}:${endCell}`);
      }
    }

    sheets.push({
      name: sheetName,
      rowCount: range.endRow - range.startRow + 1,
      colCount: range.endCol - range.startCol + 1,
      usedRange: ref,
      cells,
      mergedCells,
    });

    if (verbose) {
      console.error(`Sheet "${sheetName}": ${cells.length} cells, ${mergedCells.length} merged regions`);
    }
  }

  // Get named ranges
  const namedRanges: NamedRange[] = [];
  if (workbook.Workbook?.Names) {
    for (const name of workbook.Workbook.Names) {
      namedRanges.push({
        name: name.Name,
        ref: name.Ref,
        scope: name.Sheet !== undefined ? workbook.SheetNames[name.Sheet] : undefined,
      });
    }
  }

  // Check for macros
  const hasMacros = workbook.vbaraw !== undefined;

  if (verbose) {
    console.error(`Named ranges: ${namedRanges.length}`);
    console.error(`Placeholders found: ${allPlaceholders.length}`);
    console.error(`Has formulas: ${hasFormulas}`);
    console.error(`Has macros: ${hasMacros}`);
  }

  return {
    filename,
    sheetCount: sheets.length,
    sheets,
    namedRanges,
    placeholders: allPlaceholders,
    hasFormulas,
    hasMacros,
  };
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose", "pretty"],
    string: ["sheet"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false, pretty: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length === 0) {
    console.error("Error: No input file provided\n");
    printHelp();
    Deno.exit(1);
  }

  const inputPath = positionalArgs[0];

  try {
    const inventory = await analyzeSpreadsheet(inputPath, {
      verbose: parsed.verbose,
      sheetFilter: parsed.sheet,
    });

    const output = parsed.pretty
      ? JSON.stringify(inventory, null, 2)
      : JSON.stringify(inventory);
    console.log(output);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}

```

### scripts/generate-from-template.ts

```typescript
#!/usr/bin/env -S deno run --allow-read --allow-write

/**
 * generate-from-template.ts - Generate XLSX from existing templates
 *
 * Modifies existing Excel templates using placeholder replacement.
 * Finds and replaces tagged content (e.g., {{TITLE}}, ${date}) in cells
 * across all sheets. Preserves formulas, formatting, and structure.
 *
 * Usage:
 *   deno run --allow-read --allow-write scripts/generate-from-template.ts <template.xlsx> <spec.json> <output.xlsx>
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *
 * Permissions:
 *   --allow-read: Read template and specification files
 *   --allow-write: Write output XLSX file
 */

import { parseArgs } from "jsr:@std/[email protected]/parse-args";
import { basename } from "jsr:@std/[email protected]";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:[email protected]" as any;

// === Types ===

export interface TextReplacement {
  /** The tag to find and replace (e.g., "{{TITLE}}" or "${date}") */
  tag: string;
  /** The replacement value */
  value: string | number | boolean;
  /** Optional: only apply to specific sheets */
  sheets?: string[];
  /** Optional: only apply to specific cell range (e.g., "A1:D10") */
  range?: string;
}

export interface CellUpdate {
  /** Sheet name */
  sheet: string;
  /** Cell address (e.g., "A1") */
  address: string;
  /** New value */
  value: string | number | boolean | null;
  /** Optional formula (without = sign) */
  formula?: string;
}

export interface TemplateSpec {
  /** Text replacements to apply */
  textReplacements?: TextReplacement[];
  /** Direct cell updates */
  cellUpdates?: CellUpdate[];
  /** Sheets to include (all if omitted) */
  includeSheets?: string[];
  /** Sheets to exclude */
  excludeSheets?: string[];
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "generate-from-template";

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Generate XLSX from existing templates

Usage:
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts <template.xlsx> <spec.json> <output.xlsx>

Arguments:
  <template.xlsx>  Path to the template Excel file
  <spec.json>      Path to JSON specification for replacements
  <output.xlsx>    Path for output Excel file

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output

Specification Format:
  {
    "textReplacements": [
      { "tag": "{{TITLE}}", "value": "Q4 2024 Report" },
      { "tag": "{{DATE}}", "value": "2024-12-15" },
      { "tag": "\${author}", "value": "Finance Team", "sheets": ["Summary"] }
    ],
    "cellUpdates": [
      { "sheet": "Data", "address": "B5", "value": 1250000 },
      { "sheet": "Data", "address": "C5", "formula": "B5*1.1" }
    ]
  }

Examples:
  # Replace text in template
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts \\
    template.xlsx replacements.json output.xlsx

  # With verbose output
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts \\
    template.xlsx replacements.json output.xlsx -v
`);
}

// === Utility Functions ===

function escapeRegExp(string: string): string {
  return string.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
}

function getCellType(value: unknown): string {
  if (typeof value === "string") return "s";
  if (typeof value === "number") return "n";
  if (typeof value === "boolean") return "b";
  if (value instanceof Date) return "d";
  return "s";
}

function isInRange(address: string, range: string): boolean {
  const decoded = XLSX.utils.decode_range(range);
  const cell = XLSX.utils.decode_cell(address);
  return (
    cell.r >= decoded.s.r &&
    cell.r <= decoded.e.r &&
    cell.c >= decoded.s.c &&
    cell.c <= decoded.e.c
  );
}

// === Core Logic ===

interface ReplacementStats {
  replacements: number;
  updates: number;
}

export async function generateFromTemplate(
  templatePath: string,
  spec: TemplateSpec,
  outputPath: string,
  options: { verbose?: boolean } = {}
): Promise<ReplacementStats> {
  const { verbose = false } = options;

  // Read template
  const templateData = await Deno.readFile(templatePath);
  const workbook = XLSX.read(templateData, {
    type: "array",
    cellFormula: true,
    cellStyles: true,
    cellNF: true,
  });

  if (verbose) {
    console.error(`Loaded template: ${basename(templatePath)}`);
    console.error(`Sheets: ${workbook.SheetNames.join(", ")}`);
  }

  const textReplacements = spec.textReplacements || [];
  const cellUpdates = spec.cellUpdates || [];

  if (verbose) {
    console.error(`Text replacements: ${textReplacements.length}`);
    console.error(`Cell updates: ${cellUpdates.length}`);
  }

  let totalReplacements = 0;
  let totalUpdates = 0;

  // Determine which sheets to process
  let sheetsToProcess = [...workbook.SheetNames];
  if (spec.includeSheets && spec.includeSheets.length > 0) {
    sheetsToProcess = sheetsToProcess.filter((s) =>
      spec.includeSheets!.includes(s)
    );
  }
  if (spec.excludeSheets && spec.excludeSheets.length > 0) {
    sheetsToProcess = sheetsToProcess.filter(
      (s) => !spec.excludeSheets!.includes(s)
    );
  }

  // Process each sheet
  for (const sheetName of sheetsToProcess) {
    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) continue;

    const ref = worksheet["!ref"];
    if (!ref) continue;

    if (verbose) {
      console.error(`Processing sheet: ${sheetName}`);
    }

    let sheetReplacements = 0;

    // Process text replacements
    for (const key of Object.keys(worksheet)) {
      if (key.startsWith("!")) continue;

      const cell = worksheet[key];
      if (!cell || cell.t !== "s" || typeof cell.v !== "string") continue;

      let text = cell.v;
      const originalText = text;

      for (const replacement of textReplacements) {
        // Check sheet filter
        if (replacement.sheets && !replacement.sheets.includes(sheetName)) {
          continue;
        }

        // Check range filter
        if (replacement.range && !isInRange(key, replacement.range)) {
          continue;
        }

        // Perform replacement
        if (text.includes(replacement.tag)) {
          const regex = new RegExp(escapeRegExp(replacement.tag), "g");
          const matches = text.match(regex);
          if (matches) {
            sheetReplacements += matches.length;
          }
          text = text.replace(regex, String(replacement.value));
        }
      }

      if (text !== originalText) {
        // If the entire cell was a placeholder and replacement is a number, use number type
        if (
          originalText.trim() === textReplacements.find((r) => originalText.includes(r.tag))?.tag &&
          typeof textReplacements.find((r) => originalText.includes(r.tag))?.value === "number"
        ) {
          cell.t = "n";
          cell.v = Number(text);
        } else {
          cell.v = text;
        }
        // Clear any cached value
        delete cell.w;
      }
    }

    if (verbose && sheetReplacements > 0) {
      console.error(`  ${sheetReplacements} replacements`);
    }
    totalReplacements += sheetReplacements;
  }

  // Process direct cell updates
  for (const update of cellUpdates) {
    const worksheet = workbook.Sheets[update.sheet];
    if (!worksheet) {
      if (verbose) {
        console.error(`Warning: Sheet "${update.sheet}" not found`);
      }
      continue;
    }

    if (update.formula) {
      worksheet[update.address] = {
        t: "n",
        f: update.formula,
      };
    } else if (update.value !== null && update.value !== undefined) {
      worksheet[update.address] = {
        t: getCellType(update.value),
        v: update.value,
      };
    } else {
      // Clear the cell
      delete worksheet[update.address];
    }

    totalUpdates++;
  }

  if (verbose) {
    console.error(`Total replacements: ${totalReplacements}`);
    console.error(`Total updates: ${totalUpdates}`);
  }

  // Write output file
  const xlsxData = XLSX.write(workbook, {
    type: "buffer",
    bookType: "xlsx",
    compression: true,
  });

  await Deno.writeFile(outputPath, new Uint8Array(xlsxData));

  if (verbose) {
    console.error(`Wrote ${outputPath}`);
  }

  return { replacements: totalReplacements, updates: totalUpdates };
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length < 3) {
    console.error(
      "Error: template.xlsx, spec.json, and output.xlsx are required\n"
    );
    printHelp();
    Deno.exit(1);
  }

  const templatePath = positionalArgs[0];
  const specPath = positionalArgs[1];
  const outputPath = positionalArgs[2];

  try {
    // Read specification
    const specText = await Deno.readTextFile(specPath);
    const spec = JSON.parse(specText) as TemplateSpec;

    await generateFromTemplate(templatePath, spec, outputPath, {
      verbose: parsed.verbose,
    });

    console.log(`Created: ${outputPath}`);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}

```

### scripts/generate-scratch.ts

```typescript
#!/usr/bin/env -S deno run --allow-read --allow-write

/**
 * generate-scratch.ts - Create XLSX from scratch using JSON specification
 *
 * Creates Excel spreadsheets programmatically from a JSON specification
 * using the xlsx library. Supports sheets, cells, formulas, formatting, and charts.
 *
 * Usage:
 *   deno run --allow-read --allow-write scripts/generate-scratch.ts <spec.json> <output.xlsx>
 *
 * Options:
 *   -h, --help       Show help
 *   -v, --verbose    Enable verbose output
 *
 * Permissions:
 *   --allow-read: Read specification file
 *   --allow-write: Write output XLSX file
 */

import { parseArgs } from "jsr:@std/[email protected]/parse-args";
// deno-lint-ignore no-explicit-any
import * as XLSX from "npm:[email protected]" as any;

// === Types ===

export interface CellSpec {
  /** Cell address (e.g., "A1", "B2") */
  address: string;
  /** Cell value */
  value: string | number | boolean | null;
  /** Formula (without = sign) */
  formula?: string;
  /** Number format (e.g., "0.00", "#,##0", "yyyy-mm-dd") */
  format?: string;
  /** Cell type override */
  type?: "string" | "number" | "boolean" | "date";
}

export interface RowSpec {
  /** Row number (1-indexed) */
  row: number;
  /** Cell values starting from column A */
  values: (string | number | boolean | null)[];
  /** Optional formulas for cells (same index as values) */
  formulas?: (string | undefined)[];
}

export interface ColumnSpec {
  /** Column letter (e.g., "A", "B", "AA") */
  col: string;
  /** Column width in characters */
  width?: number;
  /** Column hidden */
  hidden?: boolean;
}

export interface MergeSpec {
  /** Start cell address */
  start: string;
  /** End cell address */
  end: string;
}

export interface SheetSpec {
  /** Sheet name */
  name: string;
  /** Individual cell specifications */
  cells?: CellSpec[];
  /** Row-based data (alternative to cells) */
  rows?: RowSpec[];
  /** 2D array data starting at A1 (alternative to cells/rows) */
  data?: (string | number | boolean | null)[][];
  /** Column specifications */
  columns?: ColumnSpec[];
  /** Merged cell ranges */
  merges?: MergeSpec[];
  /** Freeze panes at this cell (e.g., "A2" freezes first row) */
  freezePane?: string;
  /** Auto-filter range (e.g., "A1:D10") */
  autoFilter?: string;
  /** Tab color (hex, no #) */
  tabColor?: string;
}

export interface SpreadsheetSpec {
  /** Workbook title (metadata) */
  title?: string;
  /** Author (metadata) */
  author?: string;
  /** Company (metadata) */
  company?: string;
  /** Sheets to create */
  sheets: SheetSpec[];
}

interface ParsedArgs {
  help: boolean;
  verbose: boolean;
  _: (string | number)[];
}

// === Constants ===
const VERSION = "1.0.0";
const SCRIPT_NAME = "generate-scratch";

// === Help Text ===
function printHelp(): void {
  console.log(`
${SCRIPT_NAME} v${VERSION} - Create XLSX from scratch using JSON specification

Usage:
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts <spec.json> <output.xlsx>

Arguments:
  <spec.json>      Path to JSON specification file
  <output.xlsx>    Path for output Excel file

Options:
  -h, --help       Show this help message
  -v, --verbose    Enable verbose output

Specification Format:
  {
    "title": "Sales Report",
    "sheets": [
      {
        "name": "Data",
        "data": [
          ["Product", "Price", "Quantity", "Total"],
          ["Widget A", 10.99, 100, null],
          ["Widget B", 24.99, 50, null]
        ],
        "cells": [
          { "address": "D2", "formula": "B2*C2" },
          { "address": "D3", "formula": "B3*C3" }
        ],
        "columns": [
          { "col": "A", "width": 15 },
          { "col": "B", "width": 10 }
        ]
      }
    ]
  }

Examples:
  # Generate spreadsheet
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts spec.json output.xlsx

  # With verbose output
  deno run --allow-read --allow-write scripts/${SCRIPT_NAME}.ts spec.json output.xlsx -v
`);
}

// === Utility Functions ===

function colNameToIndex(colName: string): number {
  let index = 0;
  for (let i = 0; i < colName.length; i++) {
    index = index * 26 + (colName.charCodeAt(i) - 64);
  }
  return index - 1;
}

function getCellType(value: unknown, specType?: string): string {
  if (specType) {
    switch (specType) {
      case "string": return "s";
      case "number": return "n";
      case "boolean": return "b";
      case "date": return "d";
    }
  }
  if (typeof value === "string") return "s";
  if (typeof value === "number") return "n";
  if (typeof value === "boolean") return "b";
  if (value instanceof Date) return "d";
  return "s";
}

// === Core Logic ===

export async function generateFromSpec(
  spec: SpreadsheetSpec,
  outputPath: string,
  options: { verbose?: boolean } = {}
): Promise<void> {
  const { verbose = false } = options;

  // Create workbook
  const workbook = XLSX.utils.book_new();

  // Set workbook properties
  if (spec.title || spec.author || spec.company) {
    workbook.Props = {
      Title: spec.title,
      Author: spec.author,
      Company: spec.company,
    };
  }

  if (verbose) {
    console.error(`Creating workbook with ${spec.sheets.length} sheet(s)`);
  }

  // Process each sheet
  for (const sheetSpec of spec.sheets) {
    if (verbose) {
      console.error(`Processing sheet: ${sheetSpec.name}`);
    }

    // deno-lint-ignore no-explicit-any
    let worksheet: any;

    // Create worksheet from data array
    if (sheetSpec.data && sheetSpec.data.length > 0) {
      worksheet = XLSX.utils.aoa_to_sheet(sheetSpec.data);
      if (verbose) {
        console.error(`  Created from ${sheetSpec.data.length} rows of data`);
      }
    } else {
      worksheet = {};
    }

    // Process row specifications
    if (sheetSpec.rows) {
      for (const rowSpec of sheetSpec.rows) {
        for (let colIdx = 0; colIdx < rowSpec.values.length; colIdx++) {
          const value = rowSpec.values[colIdx];
          const formula = rowSpec.formulas?.[colIdx];
          const address = XLSX.utils.encode_cell({ r: rowSpec.row - 1, c: colIdx });

          if (formula) {
            worksheet[address] = { t: "n", f: formula };
          } else if (value !== null && value !== undefined) {
            worksheet[address] = {
              t: getCellType(value),
              v: value,
            };
          }
        }
      }
      if (verbose) {
        console.error(`  Processed ${sheetSpec.rows.length} row specs`);
      }
    }

    // Process individual cell specifications
    if (sheetSpec.cells) {
      for (const cellSpec of sheetSpec.cells) {
        // deno-lint-ignore no-explicit-any
        const cell: any = {};

        if (cellSpec.formula) {
          cell.t = "n";
          cell.f = cellSpec.formula;
        } else if (cellSpec.value !== null && cellSpec.value !== undefined) {
          cell.t = getCellType(cellSpec.value, cellSpec.type);
          cell.v = cellSpec.value;
        }

        if (cellSpec.format) {
          cell.z = cellSpec.format;
        }

        worksheet[cellSpec.address] = cell;
      }
      if (verbose) {
        console.error(`  Applied ${sheetSpec.cells.length} cell specs`);
      }
    }

    // Calculate the used range
    const range = { s: { r: 0, c: 0 }, e: { r: 0, c: 0 } };
    let hasData = false;

    for (const key of Object.keys(worksheet)) {
      if (key.startsWith("!")) continue;
      hasData = true;
      const cell = XLSX.utils.decode_cell(key);
      range.e.r = Math.max(range.e.r, cell.r);
      range.e.c = Math.max(range.e.c, cell.c);
    }

    if (hasData) {
      worksheet["!ref"] = XLSX.utils.encode_range(range);
    }

    // Apply column specifications
    if (sheetSpec.columns) {
      worksheet["!cols"] = [];
      for (const colSpec of sheetSpec.columns) {
        const colIdx = colNameToIndex(colSpec.col);
        while (worksheet["!cols"].length <= colIdx) {
          worksheet["!cols"].push({});
        }
        worksheet["!cols"][colIdx] = {
          wch: colSpec.width,
          hidden: colSpec.hidden,
        };
      }
      if (verbose) {
        console.error(`  Applied ${sheetSpec.columns.length} column specs`);
      }
    }

    // Apply merged cells
    if (sheetSpec.merges && sheetSpec.merges.length > 0) {
      worksheet["!merges"] = sheetSpec.merges.map((merge) => ({
        s: XLSX.utils.decode_cell(merge.start),
        e: XLSX.utils.decode_cell(merge.end),
      }));
      if (verbose) {
        console.error(`  Applied ${sheetSpec.merges.length} merge specs`);
      }
    }

    // Apply freeze pane
    if (sheetSpec.freezePane) {
      const freezeCell = XLSX.utils.decode_cell(sheetSpec.freezePane);
      worksheet["!freeze"] = {
        xSplit: freezeCell.c,
        ySplit: freezeCell.r,
        topLeftCell: sheetSpec.freezePane,
      };
    }

    // Apply auto-filter
    if (sheetSpec.autoFilter) {
      worksheet["!autofilter"] = { ref: sheetSpec.autoFilter };
    }

    // Add sheet to workbook
    XLSX.utils.book_append_sheet(workbook, worksheet, sheetSpec.name);
  }

  // Write output file
  const xlsxData = XLSX.write(workbook, {
    type: "buffer",
    bookType: "xlsx",
    compression: true,
  });

  await Deno.writeFile(outputPath, new Uint8Array(xlsxData));

  if (verbose) {
    console.error(`Wrote ${outputPath}`);
  }
}

// === Main CLI Handler ===
async function main(args: string[]): Promise<void> {
  const parsed = parseArgs(args, {
    boolean: ["help", "verbose"],
    alias: { help: "h", verbose: "v" },
    default: { verbose: false },
  }) as ParsedArgs;

  if (parsed.help) {
    printHelp();
    Deno.exit(0);
  }

  const positionalArgs = parsed._.map(String);

  if (positionalArgs.length < 2) {
    console.error("Error: Both spec.json and output.xlsx are required\n");
    printHelp();
    Deno.exit(1);
  }

  const specPath = positionalArgs[0];
  const outputPath = positionalArgs[1];

  try {
    const specText = await Deno.readTextFile(specPath);
    const spec = JSON.parse(specText) as SpreadsheetSpec;

    await generateFromSpec(spec, outputPath, {
      verbose: parsed.verbose,
    });

    console.log(`Created: ${outputPath}`);
  } catch (error) {
    console.error(
      "Error:",
      error instanceof Error ? error.message : String(error)
    );
    Deno.exit(1);
  }
}

// === Entry Point ===
if (import.meta.main) {
  main(Deno.args);
}

```