Back to Details

xlsx-generator

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

XLSX Generator

XLSX 生成器

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
在以下场景使用此技能：
  • 根据数据或规范以编程方式创建Excel电子表格
  • 在保留格式的同时，用动态内容填充品牌化模板
  • 从现有XLSX文件中提取单元格数据、公式和结构
  • 在单元格中查找并替换
    {{TITLE}}
    或
    ${date}
    之类的占位符文本
  • 自动化电子表格生成工作流（报表、数据导出、财务报表）
请勿在以下场景使用此技能：
  • 用户想要打开/查看电子表格（使用原生Excel或查看器）
  • 需要复杂数据透视表或图表（支持有限）
  • 处理旧版.xls格式（仅支持XLSX）
  • 需要实时协作编辑

Prerequisites

前提条件

  • Deno installed (https://deno.land/)
  • Input XLSX files for template-based operations
  • JSON specification for scratch generation
  • 已安装Deno（https://deno.land/）
  • 用于模板化操作的输入XLSX文件
  • 用于从零开始生成的JSON规范

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
  1. 模板模式：修改现有品牌化模板
    • 分析模板以查找占位符和结构
    • 将
      {{PLACEHOLDERS}}
      替换为实际值
  2. 从零开始模式：使用JSON规范从头创建电子表格

Instructions

操作说明

Mode 1: Template-Based Generation

模式1：基于模板的生成

Step 1a: Analyze the Template

步骤1a：分析模板

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
}
提取单元格清单，了解可替换内容：
bash
deno run --allow-read scripts/analyze-template.ts financial-template.xlsx > inventory.json
输出（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

步骤1b：创建替换规范

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 }
  ]
}
创建
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

步骤1c：生成输出文件

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

Mode 2: From-Scratch Generation

模式2：从零开始生成

Step 2a: Create Specification

步骤2a：创建规范

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"
    }
  ]
}
创建
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

步骤2b：生成电子表格

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

Examples

示例

Example 1: Monthly Sales Report

示例1：月度销售报表

Scenario: Generate a monthly sales report from template.
Steps:
bash
undefined
场景：从模板生成月度销售报表。
步骤：
bash
undefined

1. Analyze template for replaceable content

1. 分析模板以获取可替换内容

deno run --allow-read scripts/analyze-template.ts sales-template.xlsx --pretty
deno run --allow-read scripts/analyze-template.ts sales-template.xlsx --pretty

2. Create replacements.json with monthly data

2. 创建包含月度数据的replacements.json

3. Generate report

3. 生成报表

deno run --allow-read --allow-write scripts/generate-from-template.ts
sales-template.xlsx replacements.json November-Sales.xlsx
undefined
deno run --allow-read --allow-write scripts/generate-from-template.ts
sales-template.xlsx replacements.json November-Sales.xlsx
undefined

Example 2: Data Export with Formulas

示例2：带公式的数据导出

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)" }
    ]
  }]
}
场景：创建包含计算总计的电子表格。
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

示例3：多工作表工作簿

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" }
      ]
    }
  ]
}
场景：创建包含摘要和明细工作表的工作簿。
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

脚本参考

ScriptPurposePermissions
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
脚本用途权限
analyze-template.ts
从XLSX中提取单元格、公式、占位符
--allow-read
generate-from-template.ts
替换模板中的占位符
--allow-read --allow-write
generate-scratch.ts
从JSON规范创建XLSX
--allow-read --allow-write

Specification Reference

规范参考

Sheet Options

工作表选项

PropertyTypeDescription
name
stringSheet name
data
array2D array of cell values starting at A1
cells
arrayIndividual cell specifications
rows
arrayRow-based data specifications
columns
arrayColumn width and visibility settings
merges
arrayMerged cell ranges
freezePane
stringFreeze panes at this cell (e.g., "A2")
autoFilter
stringAuto-filter range (e.g., "A1:F10")
属性类型描述
name
string工作表名称
data
array从A1开始的单元格值二维数组
cells
array单个单元格规范
rows
array基于行的数据规范
columns
array列宽和可见性设置
merges
array合并单元格范围
freezePane
string在此单元格冻结窗格（例如："A2"）
autoFilter
string自动筛选范围（例如："A1:F10"）

Cell Options

单元格选项

PropertyTypeDescription
address
stringCell address (e.g., "A1", "B2")
value
mixedCell value (string, number, boolean, null)
formula
stringFormula without = sign
format
stringNumber format (e.g., "#,##0.00")
type
stringForce type: "string", "number", "boolean", "date"
属性类型描述
address
string单元格地址（例如："A1"、"B2"）
value
mixed单元格值（字符串、数字、布尔值、null）
formula
string不带=号的公式
format
string数字格式（例如："#,##0.00"）
type
string强制类型："string"、"number"、"boolean"、"date"

Column Options

列选项

PropertyTypeDescription
col
stringColumn letter (e.g., "A", "B", "AA")
width
numberColumn width in characters
hidden
booleanHide column
属性类型描述
col
string列字母（例如："A"、"B"、"AA"）
width
number列宽（字符数）
hidden
boolean隐藏列

Template Replacement Options

模板替换选项

PropertyTypeDescription
tag
stringPlaceholder to find (e.g., "{{TITLE}}")
value
mixedReplacement value
sheets
arrayLimit to specific sheets
range
stringLimit to cell range (e.g., "A1:D10")
属性类型描述
tag
string要查找的占位符（例如："{{TITLE}}"）
value
mixed替换值
sheets
array限制为特定工作表
range
string限制为单元格范围（例如："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
症状：输出的XLSX仍然包含
{{PLACEHOLDER}}
标签。
解决方案：
  1. 运行
    analyze-template.ts
    以验证标签的确切文本和位置
  2. 检查占位符是否在字符串单元格中，而非公式中
  3. 验证替换规范中的工作表筛选条件

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"
症状：公式显示为文本而非计算结果。
解决方案：
  • 确保规范中的公式不以"="开头（会自动添加）
  • 检查单元格类型未被强制设置为"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)
  • 仅支持XLSX：不支持旧版.xls或.xlsb格式
  • 无宏支持：无法创建或保留VBA宏
  • 有限的图表支持：无原生图表创建功能
  • 无数据透视表：无法以编程方式创建数据透视表
  • 基础样式：单元格格式选项有限
  • 无条件格式：无法设置条件格式规则
  • 公式重计算：公式会被存储但不会自动重计算（Excel打开时会重新计算）

Related Skills

相关技能

  • pptx-generator: For creating PowerPoint presentations
  • docx-generator: For creating Word documents
  • csv-processor: For simpler CSV data processing
  • pptx-generator：用于创建PowerPoint演示文稿
  • docx-generator：用于创建Word文档
  • csv-processor：用于更简单的CSV数据处理