Spreadsheet Builder

电子表格构建器

Spreadsheet Builder 技能支持创建专业的 Excel（.xlsx）和 CSV 文件，并提供高级格式设置、公式、图表和数据分析功能。借助 exceljs 和 xlsx 等库，该技能可处理从简单的数据导出到复杂的财务模型和仪表板等各类需求。

生成数据报告、财务报表、库存清单、分析仪表板以及各种表格数据可视化内容。支持多工作表、单元格样式、条件格式、公式、数据透视表和图表，是一套全面的电子表格自动化解决方案。

核心工作流

工作流 1：创建基本 Excel 工作簿

目的： 使用格式化数据创建简单的 Excel 文件

步骤：

1. 导入 exceljs 并创建 Workbook 实例
2. 添加工作表并命名
3. 定义列标题和列宽
4. 添加数据行
5. 应用基本格式设置（字体、颜色、对齐方式）
6. 设置列宽和行高
7. 写入 .xlsx 文件

实现：

const ExcelJS = require('exceljs');

async function createBasicWorkbook(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sales Data');

  // Define columns
  worksheet.columns = [
    { header: 'Date', key: 'date', width: 12 },
    { header: 'Product', key: 'product', width: 25 },
    { header: 'Quantity', key: 'quantity', width: 10 },
    { header: 'Price', key: 'price', width: 12 },
    { header: 'Total', key: 'total', width: 12 }
  ];

  // Style header row
  worksheet.getRow(1).font = { bold: true, size: 12 };
  worksheet.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF4472C4' }
  };
  worksheet.getRow(1).font = { color: { argb: 'FFFFFFFF' }, bold: true };

  // Add data
  data.forEach(row => {
    worksheet.addRow(row);
  });

  // Format currency columns
  worksheet.getColumn('price').numFmt = '$#,##0.00';
  worksheet.getColumn('total').numFmt = '$#,##0.00';

  await workbook.xlsx.writeFile(outputPath);
}

工作流 2：添加公式和计算

目的： 创建包含自动计算和公式的电子表格

步骤：

1. 创建工作簿和工作表
2. 添加数据列
3. 插入公式单元格（SUM、AVERAGE、IF、VLOOKUP 等）
4. 使用单元格引用进行动态计算
5. 添加条件公式
6. 创建计算列
7. 添加总计和小计

实现：

async function createWithFormulas(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Financial Report');

  worksheet.columns = [
    { header: 'Month', key: 'month', width: 12 },
    { header: 'Revenue', key: 'revenue', width: 15 },
    { header: 'Expenses', key: 'expenses', width: 15 },
    { header: 'Profit', key: 'profit', width: 15 },
    { header: 'Margin %', key: 'margin', width: 12 }
  ];

  // Add data rows
  data.forEach((row, index) => {
    const rowIndex = index + 2; // Account for header row
    worksheet.addRow({
      month: row.month,
      revenue: row.revenue,
      expenses: row.expenses,
      profit: { formula: `B${rowIndex}-C${rowIndex}` }, // Revenue - Expenses
      margin: { formula: `D${rowIndex}/B${rowIndex}` }  // Profit / Revenue
    });
  });

  // Add totals row
  const lastRow = data.length + 2;
  worksheet.addRow({
    month: 'TOTAL',
    revenue: { formula: `SUM(B2:B${lastRow - 1})` },
    expenses: { formula: `SUM(C2:C${lastRow - 1})` },
    profit: { formula: `SUM(D2:D${lastRow - 1})` },
    margin: { formula: `D${lastRow}/B${lastRow}` }
  });

  // Format totals row
  worksheet.getRow(lastRow).font = { bold: true };
  worksheet.getRow(lastRow).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE7E6E6' }
  };

  // Number formatting
  worksheet.getColumn('revenue').numFmt = '$#,##0.00';
  worksheet.getColumn('expenses').numFmt = '$#,##0.00';
  worksheet.getColumn('profit').numFmt = '$#,##0.00';
  worksheet.getColumn('margin').numFmt = '0.00%';

  await workbook.xlsx.writeFile(outputPath);
}

工作流 3：应用条件格式

目的： 根据规则和阈值突出显示单元格

步骤：

1. 创建包含数据的工作簿
2. 定义条件格式规则
3. 为数值范围应用色阶
4. 使用数据条进行可视化比较
5. 添加用于状态指示的图标集
6. 突出显示最大值和最小值
7. 应用基于自定义公式的规则

实现：

async function addConditionalFormatting(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Performance');

  // Add data...
  worksheet.columns = [
    { header: 'Employee', key: 'name', width: 20 },
    { header: 'Sales', key: 'sales', width: 15 },
    { header: 'Target', key: 'target', width: 15 },
    { header: 'Performance', key: 'performance', width: 15 }
  ];

  data.forEach(row => worksheet.addRow(row));

  // Color scale: Green (high) to Red (low)
  worksheet.addConditionalFormatting({
    ref: 'B2:B100',
    rules: [
      {
        type: 'colorScale',
        cfvo: [
          { type: 'min' },
          { type: 'percentile', value: 50 },
          { type: 'max' }
        ],
        color: [
          { argb: 'FFF8696B' }, // Red
          { argb: 'FFFFEB84' }, // Yellow
          { argb: 'FF63BE7B' }  // Green
        ]
      }
    ]
  });

  // Data bars for performance column
  worksheet.addConditionalFormatting({
    ref: 'D2:D100',
    rules: [
      {
        type: 'dataBar',
        minLength: 0,
        maxLength: 100,
        color: { argb: 'FF638EC6' }
      }
    ]
  });

  // Highlight values above target
  worksheet.addConditionalFormatting({
    ref: 'B2:B100',
    rules: [
      {
        type: 'expression',
        formulae: ['B2>C2'], // Sales > Target
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FFC6EFCE' }
          }
        }
      }
    ]
  });

  await workbook.xlsx.writeFile(outputPath);
}

工作流 4：创建图表和可视化内容

目的： 添加图表，以可视化数据趋势和比较结果

步骤：

1. 创建包含数据的工作簿
2. 添加数据工作表
3. 创建图表工作表，或将图表嵌入数据工作表
4. 定义图表类型（柱形图、折线图、饼图、散点图等）
5. 设置数据系列的数据范围
6. 配置图表标题、坐标轴和图例
7. 应用样式和颜色
8. 在工作表中放置图表

实现：

async function createWithChart(data, outputPath) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sales');

  // Add data
  worksheet.columns = [
    { header: 'Month', key: 'month', width: 12 },
    { header: 'Sales', key: 'sales', width: 15 }
  ];

  data.forEach(row => worksheet.addRow(row));

  // Create chart (Note: exceljs has limited chart support, consider using xlsx-chart)
  // For full chart support, you may need to use Excel templates or officegen library

  // Alternative: Add chart using worksheet image
  // Or use a charting library to generate image, then embed

  await workbook.xlsx.writeFile(outputPath);
}

// For advanced charts, consider using officegen or generating chart images

工作流 5：创建带链接的多工作表工作簿

目的： 创建包含多个相关工作表的复杂工作簿

步骤：

1. 创建工作簿
2. 添加多个工作表（Summary、Details、Raw Data 等）
3. 创建跨工作表公式和引用
4. 在工作表之间添加超链接
5. 使用密码保护工作表
6. 根据需要隐藏或显示工作表
7. 设置活动工作表并冻结窗格

实现：

async function createMultiSheetWorkbook(data, outputPath) {
  const workbook = new ExcelJS.Workbook();

  // Summary sheet
  const summary = workbook.addWorksheet('Summary');
  summary.columns = [
    { header: 'Metric', key: 'metric', width: 25 },
    { header: 'Value', key: 'value', width: 15 }
  ];

  summary.addRow({ metric: 'Total Sales', value: { formula: "SUM(Details!B:B)" } });
  summary.addRow({ metric: 'Average Order', value: { formula: "AVERAGE(Details!B:B)" } });
  summary.addRow({ metric: 'Total Orders', value: { formula: "COUNTA(Details!A:A)-1" } });

  // Details sheet
  const details = workbook.addWorksheet('Details');
  details.columns = [
    { header: 'Order ID', key: 'id', width: 12 },
    { header: 'Amount', key: 'amount', width: 15 },
    { header: 'Date', key: 'date', width: 12 }
  ];

  data.forEach(row => details.addRow(row));

  // Freeze header row
  details.views = [
    { state: 'frozen', xSplit: 0, ySplit: 1 }
  ];

  // Add hyperlink from summary to details
  summary.getCell('A1').value = {
    text: 'View Details',
    hyperlink: '#Details!A1',
    tooltip: 'Jump to Details sheet'
  };
  summary.getCell('A1').font = { color: { argb: 'FF0000FF' }, underline: true };

  // Set Summary as active sheet
  summary.state = 'visible';
  details.state = 'visible';

  await workbook.xlsx.writeFile(outputPath);
}

工作流 6：导出为 CSV

目的： 创建用于数据交换的简单 CSV 文件

步骤：

1. 将数据格式化为对象数组或数组
2. 根据需要定义标题
3. 转换为 CSV 格式
4. 处理特殊字符和引号
5. 设置分隔符（逗号、分号、制表符）
6. 使用正确的编码写入文件

实现：

const fs = require('fs');

function createCSV(data, outputPath, options = {}) {
  const delimiter = options.delimiter || ',';
  const headers = options.headers || Object.keys(data[0]);

  // Create header row
  let csv = headers.join(delimiter) + '\n';

  // Add data rows
  data.forEach(row => {
    const values = headers.map(header => {
      let value = row[header] || '';
      // Escape quotes and wrap in quotes if contains delimiter or newline
      if (typeof value === 'string' && (value.includes(delimiter) || value.includes('\n') || value.includes('"'))) {
        value = '"' + value.replace(/"/g, '""') + '"';
      }
      return value;
    });
    csv += values.join(delimiter) + '\n';
  });

  fs.writeFileSync(outputPath, csv, 'utf8');
}

快速参考

操作	命令/触发语
创建 Excel 工作簿	“使用 [data] 创建 Excel 文件”
生成 CSV	“将 [data] 导出为 csv”
添加公式	“向电子表格添加公式”
应用格式设置	“设置 Excel 单元格格式 [style]”
创建图表	“向工作簿添加图表”
多工作表工作簿	“创建包含 [sheets] 的工作簿”
条件格式	“应用条件格式”
冻结窗格	“冻结标题行”
保护工作表	“使用密码保护 [sheet]”

最佳实践

• 数据验证： 将数据写入单元格前，先验证数据类型
• 数字格式： 应用适当的数字格式（货币、百分比、日期）
• 列宽： 根据内容设置列宽，以便阅读
• 冻结窗格： 冻结标题行，方便滚动查看数据
• 命名区域： 在复杂工作簿中使用命名区域编写公式
• 模板： 为重复使用的报告类型创建模板
• 内存管理： 对超大型数据集（>100k 行）使用流式处理
• 错误处理： 使用 try-catch 包裹公式创建过程，以处理无效引用
• CSV 编码： 使用 UTF-8 BOM 支持国际字符
• 性能： 批量操作单元格，而非逐个写入
• 测试： 创建文件后，验证公式是否能正确计算
• 文档： 在单元格内为复杂公式添加注释

常见模式

库存报告：

worksheet.columns = [
  { header: 'SKU', key: 'sku', width: 15 },
  { header: 'Product', key: 'product', width: 30 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Unit Price', key: 'price', width: 15 },
  { header: 'Total Value', key: 'value', width: 15 }
];

data.forEach((item, idx) => {
  const row = idx + 2;
  worksheet.addRow({
    sku: item.sku,
    product: item.product,
    qty: item.qty,
    price: item.price,
    value: { formula: `C${row}*D${row}` }
  });
});

财务仪表板：

// Summary sheet with KPIs
summary.addRow({ metric: 'Revenue', value: { formula: "SUM(Data!B:B)" } });
summary.addRow({ metric: 'Expenses', value: { formula: "SUM(Data!C:C)" } });
summary.addRow({ metric: 'Net Profit', value: { formula: "B2-B3" } });
summary.addRow({ metric: 'Profit Margin', value: { formula: "B4/B2" } });
summary.getColumn('value').numFmt = '$#,##0.00';

依赖项

安装所需的软件包：

npm install exceljs
npm install xlsx      # Alternative library
npm install csv-writer # For CSV generation

错误处理

• 公式无效： 赋值前验证公式语法
• 单元格引用： 确保引用的单元格存在
• 文件权限： 妥善处理写入错误
• 内存限制： 对 >50MB 的文件使用流式模式
• 数据类型： 将数据转换为适当的类型（数字、字符串、日期）
• 编码问题： 确保使用 UTF-8 编码以支持国际字符

性能提示

• 使用 worksheet.addRows()，而不是多次调用 addRow()
• 添加数据前设置列属性
• 避免不必要地读取单元格
• 对大型数据集使用流式写入
• 批量应用样式
• 对静态数据，尽可能预先计算值，而不是使用公式

高级功能

流式处理大型文件：

const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({ filename: outputPath });
const worksheet = workbook.addWorksheet('Large Data');
// Add data in chunks
worksheet.commit();
workbook.commit();

数据验证：

worksheet.getCell('A2').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['"Option1,Option2,Option3"']
};

保护：

await worksheet.protect('password', {
  selectLockedCells: true,
  selectUnlockedCells: true
});