Sheet Generation API: Structured JSON In, Formatted Spreadsheet Out

查看原文 / View Original 编辑 / Edit

Sheet Generation API: Structured JSON In, Formatted Spreadsheet Out

Sheet Generation API：输入结构化 JSON，输出格式化电子表格

Spreadsheet Generation Is Still Painful

电子表格生成依然令人头疼

Every application eventually needs to export data as a spreadsheet. Financial reports, inventory lists, invoices, analytics dashboards — at some point, someone asks for an Excel file and you start searching for libraries. If you work in JavaScript, you find ExcelJS or SheetJS. Python has openpyxl. Ruby has caxlsx. Java has Apache POI. Each one has a different API surface, different quirks, and different failure modes. The common thread: they’re all verbose. 几乎每个应用程序最终都需要将数据导出为电子表格。无论是财务报表、库存清单、发票还是分析仪表板，总有一天会有人要求你提供 Excel 文件，于是你便开始寻找各种库。如果你使用 JavaScript，你会找到 ExcelJS 或 SheetJS；Python 有 openpyxl；Ruby 有 caxlsx；Java 则有 Apache POI。每一个库都有不同的 API 接口、不同的特性以及不同的故障模式。它们的共同点是：代码都非常冗长。

Generating a simple XLSX with headers, currency formatting, and a SUM formula takes dozens of lines of boilerplate — cell references, style objects, merge ranges, format strings. CSV is simpler but limited. No formatting, no formulas, no multiple sheets. You get a flat text file and hope the recipient’s spreadsheet app guesses the delimiter correctly. And if you need both formats? You maintain two separate code paths. One for XLSX with its style objects and cell references. One for CSV with its string concatenation. Both doing the same conceptual thing — turning rows of data into a spreadsheet — in completely different ways. 生成一个包含标题、货币格式和 SUM 公式的简单 XLSX 文件，往往需要编写几十行样板代码——涉及单元格引用、样式对象、合并范围和格式字符串等。CSV 虽然简单，但局限性很大：没有格式、没有公式、不支持多工作表。你只能得到一个扁平的文本文件，并祈祷接收者的电子表格软件能正确识别分隔符。如果你需要同时支持这两种格式呢？你就得维护两套独立的代码路径：一套用于处理 XLSX 的样式对象和单元格引用，另一套用于处理 CSV 的字符串拼接。两者在概念上做的是同一件事——将数据行转换为电子表格——但实现方式却截然不同。

One API Call, Three Formats

一次 API 调用，三种输出格式

The Iteration Layer’s Sheet Generation API takes a single JSON payload describing your spreadsheet and returns a formatted file. The same payload produces XLSX, CSV, or Markdown — you just change the format field. Iteration Layer 的 Sheet Generation API 只需接收一个描述电子表格的 JSON 数据包，即可返回一个格式化文件。同一个数据包可以生成 XLSX、CSV 或 Markdown 格式——你只需更改 format 字段即可。

import { IterationLayer } from "iterationlayer";

const client = new IterationLayer({ apiKey: "YOUR_API_KEY", });

const result = await client.generateSheet({
  format: "xlsx",
  sheets: [
    {
      name: "Q1 Revenue",
      columns: [
        { name: "Company" },
        { name: "Region" },
        { name: "Revenue" },
      ],
      rows: [
        [
          { value: "Acme Corp" },
          { value: "North America" },
          { value: 48500.00, format: "currency", currency_code: "USD" },
        ],
        [
          { value: "Globex Ltd" },
          { value: "Europe" },
          { value: 37200.00, format: "currency", currency_code: "EUR" },
        ],
      ],
    },
  ],
});
{
  "success": true,
  "data": {
    "buffer": "UEsDBBQAAAAIAA...",
    "mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  }
}

The response contains the file as a base64-encoded buffer with the appropriate MIME type. Decode it, save it, send it to a client — the file is ready to open in Excel, Google Sheets, or Numbers. 响应内容包含一个 base64 编码的缓冲区文件以及相应的 MIME 类型。解码并保存后发送给客户端，该文件即可直接在 Excel、Google Sheets 或 Numbers 中打开。

The Data Model

数据模型

The API uses a positional model. You define columns, then provide rows as ordered arrays of cells. Each cell has a value and optional formatting. Columns define the header row. Each column has a name and an optional width: 该 API 使用位置模型。你先定义列，然后以有序的单元格数组形式提供行数据。每个单元格都有一个值和可选的格式。列定义了标题行，每一列都有一个名称和可选的宽度：

{
  "columns": [
    { "name": "Product", "width": 30 },
    { "name": "Units Sold" },
    { "name": "Unit Price" },
    { "name": "Total" }
  ]
}

Cells carry the data. At minimum, a cell has a value. Add format to control how it’s displayed: 单元格用于承载数据。最简单的情况下，单元格只需包含一个值。添加 format 即可控制其显示方式：

{
  "value": 1500.50,
  "format": "currency",
  "currency_code": "USD"
}

Supported cell formats: text, number, decimal, currency, percentage, date, datetime, time, and custom. 支持的单元格格式包括：text（文本）、number（数字）、decimal（小数）、currency（货币）、percentage（百分比）、date（日期）、datetime（日期时间）、time（时间）和 custom（自定义）。

Number styles control how digits are grouped. Four styles cover the major international conventions: 数字样式控制数字的分组方式。以下四种样式涵盖了主要的国际惯例：

  • comma_period — 1,234.56 (美国、英国、亚洲大部分地区)
  • period_comma — 1.234,56 (德国、巴西、欧洲大部分地区)
  • space_comma — 1 234,56 (法国、挪威、瑞典)
  • space_period — 1 234.56 (爱沙尼亚、南非)

Date styles control how date, datetime, and time values are formatted. Pass any Excel date format code — dd/mm/yyyy for European dates, mm/dd/yyyy for US, d mmmm yyyy for full month names, hh:mm for short times. The format code is applied natively in XLSX and rendered to the equivalent string in CSV and Markdown. 日期样式控制日期、日期时间和时间值的格式。你可以传入任何 Excel 日期格式代码——例如欧洲日期的 dd/mm/yyyy、美国的 mm/dd/yyyy、显示完整月份名称的 d mmmm yyyy，或简短时间的 hh:mm。该格式代码在 XLSX 中会原生应用，而在 CSV 和 Markdown 中则会渲染为相应的字符串。

Currency codes follow ISO 4217. The API supports 162 codes — from USD and EUR to THB and CLP — and maps each one to its correct symbol. 货币代码遵循 ISO 4217 标准。该 API 支持 162 种货币代码（从 USD、EUR 到 THB、CLP 等），并能将每种代码映射到正确的符号。

Styles: Base and Per-Cell

样式：基础样式与单元格级样式

Instead of styling every cell individually, you define base styles for headers and body rows, then override specific cells as needed. 你无需为每个单元格单独设置样式，只需为标题和正文行定义基础样式，然后根据需要覆盖特定单元格的样式即可。

const result = await client.generateSheet({
  format: "xlsx",
  styles: {
    header: { is_bold: true, font_size_in_pt: 12, background_color: "#1a1a2e", font_color: "#ffffff", horizontal_alignment: "center" },
    body: { font_size_in_pt: 10 },
  },
  sheets: [ /* ... */ ]
});

The styles.header and styles.body objects set defaults. The styles field on individual cells overrides them. Available style properties include font_family, font_size_in_pt, is_bold, is_italic, font_color, background_color, horizontal_alignment, and number_format. For XLSX output, you can also embed custom fonts by including a fonts array with base64-encoded font buffers. styles.header 和 styles.body 对象用于设置默认值。单个单元格上的 styles 字段会覆盖这些默认值。可用的样式属性包括 font_family、font_size_in_pt、is_bold、is_italic、font_color、background_color、horizontal_alignment 和 number_format。对于 XLSX 输出，你还可以通过包含一个带有 base64 编码字体缓冲区的 fonts 数组来嵌入自定义字体。

Formulas

公式

Cells whose value starts with = are treated as formulas. In XLSX output, they’re written as native Excel formulas — Excel evaluates them when the file opens. In CSV and Markdown output, the API evaluates them server-side and writes the computed result. The server-side evaluator supports SUM, AVERAGE, COUNT, MIN, and MAX on cell ranges. 值以 = 开头的单元格会被视为公式。在 XLSX 输出中，它们会被写入为原生的 Excel 公式，Excel 会在打开文件时进行计算。在 CSV 和 Markdown 输出中，API 会在服务器端进行计算并写入计算结果。服务器端计算器支持对单元格范围使用 SUM、AVERAGE、COUNT、MIN 和 MAX 函数。

{
  "rows": [
    [ { "value": "Total" }, { "value": "=SUM(B2:B4)", "format": "currency", "currency_code": "USD" } ]
  ]
}

In XLSX, the Total cell contains =SUM(B2:B4) and Excel computes it. In CSV, the API evaluates the formula and writes 13200. Same input, correct output in every format. 在 XLSX 中，Total 单元格包含 =SUM(B2:B4)，由 Excel 进行计算。在 CSV 中，API 会计算该公式并写入 13200。相同的输入，在每种格式下都能得到正确的输出。

← 返回新闻列表 / Back to news list