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

Template Mode: Modify existing branded templates

• Analyze template to find placeholders and structure

• Replace {{PLACEHOLDERS}} with actual values

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:

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

Output (inventory.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 :

{ "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

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 :

{ "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

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:

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:

{ "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:

{ "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:

• Run analyze-template.ts to verify exact tag text and location

• Check that placeholder is in a string cell, not a formula

• 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