XLSX

数据透视表

基于源数据创建数据透视表，支持 11 种聚合函数

基本透视表

从源工作表的数据区域创建透视表，按行字段分组并聚合数据：

{
  "worksheets": [
    {
      "name": "Data",
      "rows": [
        { "cells": [{ "value": "Region" }, { "value": "Product" }, { "value": "Sales" }] },
        { "cells": [{ "value": "East" }, { "value": "Widget" }, { "value": 150 }] },
        { "cells": [{ "value": "East" }, { "value": "Gadget" }, { "value": 200 }] },
        { "cells": [{ "value": "West" }, { "value": "Widget" }, { "value": 300 }] },
        { "cells": [{ "value": "West" }, { "value": "Gadget" }, { "value": 100 }] },
        { "cells": [{ "value": "North" }, { "value": "Widget" }, { "value": 250 }] },
        { "cells": [{ "value": "North" }, { "value": "Gadget" }, { "value": 175 }] }
      ]
    },
    {
      "name": "Pivot",
      "rows": [],
      "pivotTables": [
        {
          "source": "A1:C7",
          "sourceSheet": "Data",
          "location": "A3",
          "rows": ["Region"],
          "data": [{ "field": "Sales", "summarize": "sum" }]
        }
      ]
    }
  ]
}

import { generateWorkbook } from "@office-open/xlsx";

const buffer = await generateWorkbook({
  worksheets: [
    {
      name: "Data",
      rows: [
        { cells: [{ value: "Region" }, { value: "Product" }, { value: "Sales" }] },
        { cells: [{ value: "East" }, { value: "Widget" }, { value: 150 }] },
        { cells: [{ value: "East" }, { value: "Gadget" }, { value: 200 }] },
        { cells: [{ value: "West" }, { value: "Widget" }, { value: 300 }] },
        { cells: [{ value: "West" }, { value: "Gadget" }, { value: 100 }] },
        { cells: [{ value: "North" }, { value: "Widget" }, { value: 250 }] },
        { cells: [{ value: "North" }, { value: "Gadget" }, { value: 175 }] },
      ],
    },
    {
      name: "Pivot",
      rows: [],
      pivotTables: [
        {
          source: "A1:C7",
          sourceSheet: "Data",
          location: "A3",
          rows: ["Region"],
          data: [{ field: "Sales", summarize: "sum" }],
        },
      ],
    },
  ],
});

聚合函数

支持 OOXML ST_DataConsolidateFunction 定义的全部 11 种聚合类型：

函数	`summarize` 值	说明
求和	`"sum"`	数值总和（默认）
平均值	`"average"`	算术平均值
计数	`"count"`	非空值数量
数值计数	`"countNums"`	数值数量
最大值	`"max"`	最大值
最小值	`"min"`	最小值
乘积	`"product"`	所有值的乘积
样本标准差	`"stdDev"`	STDEV.S
总体标准差	`"stdDevp"`	STDEV.P
样本方差	`"var"`	VAR.S
总体方差	`"varp"`	VAR.P

多个数据字段

在同一透视表中添加多个聚合：

{
  "pivotTables": [
    {
      "source": "A1:C7",
      "sourceSheet": "Data",
      "location": "A3",
      "rows": ["Region"],
      "data": [
        { "field": "Sales", "summarize": "sum", "name": "Total Sales" },
        { "field": "Sales", "summarize": "average", "name": "Avg Sales" }
      ]
    }
  ]
}

pivotTables: [
  {
    source: "A1:C7",
    sourceSheet: "Data",
    location: "A3",
    rows: ["Region"],
    data: [
      { field: "Sales", summarize: "sum", name: "Total Sales" },
      { field: "Sales", summarize: "average", name: "Avg Sales" },
    ],
  },
],

筛选器

使用透视筛选器来限制透视表中显示的数据。使用 PivotFilterTypeValue 枚举指定筛选类型：

import { PivotFilterTypeValue } from "@office-open/xlsx";

pivotTables: [
  {
    source: "A1:C9",
    sourceSheet: "Data",
    location: "A3",
    rows: ["City"],
    data: [{ field: "Revenue", summarize: "sum" }],
    filters: [
      {
        fld: 0,
        type: PivotFilterTypeValue.CAPTION_NOT_EQUAL,
        id: 1,
        stringValue1: "Guangzhou",
        name: "ExcludeGuangzhou",
      },
    ],
  },
],

PivotFilter 选项

选项	类型	说明
`fld`	`number`	要筛选的字段索引（必填）
`type`	`string`	筛选类型，来自 `PivotFilterTypeValue`（必填）
`id`	`number`	透视表内唯一筛选器 ID（必填）
`name`	`string`	筛选器名称
`description`	`string`	筛选器描述
`stringValue1`	`string`	标题/日期筛选的第一个值
`stringValue2`	`string`	区间筛选的第二个值
`mpFld`	`number`	OLAP 筛选的度量字段索引
`evalOrder`	`number`	评估顺序
`iMeasureHier`	`number`	度量层级
`iMeasureFld`	`number`	度量字段

可用筛选类型（`PivotFilterTypeValue`）

类别	值
标题	`CAPTION_EQUAL`、`CAPTION_NOT_EQUAL`、`CAPTION_BEGINS_WITH`、`CAPTION_NOT_BEGINS_WITH`、`CAPTION_ENDS_WITH`、`CAPTION_NOT_ENDS_WITH`、`CAPTION_CONTAINS`、`CAPTION_NOT_CONTAINS`、`CAPTION_GREATER_THAN`、`CAPTION_GREATER_THAN_OR_EQUAL`、`CAPTION_LESS_THAN`、`CAPTION_LESS_THAN_OR_EQUAL`、`CAPTION_BETWEEN`、`CAPTION_NOT_BETWEEN`
数值	`VALUE_EQUAL`、`VALUE_NOT_EQUAL`、`VALUE_GREATER_THAN`、`VALUE_GREATER_THAN_OR_EQUAL`、`VALUE_LESS_THAN`、`VALUE_LESS_THAN_OR_EQUAL`、`VALUE_BETWEEN`、`VALUE_NOT_BETWEEN`
日期	`DATE_EQUAL`、`DATE_NOT_EQUAL`、`DATE_OLDER_THAN`、`DATE_OLDER_THAN_OR_EQUAL`、`DATE_NEWER_THAN`、`DATE_NEWER_THAN_OR_EQUAL`、`DATE_BETWEEN`、`DATE_NOT_BETWEEN`
相对	`TOMORROW`、`TODAY`、`YESTERDAY`、`NEXT_WEEK`、`THIS_WEEK`、`LAST_WEEK`、`NEXT_MONTH`、`THIS_MONTH`、`LAST_MONTH`、`NEXT_QUARTER`、`THIS_QUARTER`、`LAST_QUARTER`、`NEXT_YEAR`、`THIS_YEAR`、`LAST_YEAR`、`YEAR_TO_DATE`
季度	`Q1`、`Q2`、`Q3`、`Q4`
月份	`M1` 至 `M12`
其他	`UNKNOWN`、`COUNT`、`PERCENT`、`SUM`

条件格式

为透视表区域应用条件格式规则：

pivotTables: [
  {
    source: "A1:C9",
    sourceSheet: "Data",
    location: "A3",
    rows: ["City"],
    columns: ["Category"],
    data: [
      {
        field: "Revenue",
        summarize: "sum",
        name: "Total Revenue",
        sortByTupleItems: [0],
      },
    ],
    pivotConditionalFormats: [
      {
        priority: 1,
        scope: "data",
        type: "all",
        pivotAreas: [
          {
            field: 2,
            type: "data",
            outline: true,
          },
        ],
      },
    ],
  },
],

高级功能

页面/报表筛选

使用 pages 添加报表筛选字段，显示在透视表上方：

pivotTables: [
  {
    source: "A1:D11",
    sourceSheet: "Data",
    location: "A3",
    rows: ["Region"],
    data: [{ field: "Sales", summarize: "sum" }],
    pages: ["Product"],
  },
],

列字段

添加 columns 创建交叉分析表：

pivotTables: [
  {
    source: "A1:C9",
    sourceSheet: "Data",
    location: "A3",
    rows: ["City"],
    columns: ["Category"],
    data: [{ field: "Revenue", summarize: "sum" }],
  },
],

透视层级

使用 pivotHierarchies 控制字段行为：

pivotTables: [
  {
    source: "A1:C9",
    sourceSheet: "Data",
    location: "A3",
    rows: ["City"],
    data: [{ field: "Revenue", summarize: "sum" }],
    pivotHierarchies: [
      {
        outline: true,
        dragToRow: true,
        dragToCol: true,
        showInFieldList: true,
        caption: "City",
      },
    ],
  },
],

透视表选项参考

选项	类型	说明
`source`	`string`	源数据区域，如 `"A1:C11"`
`sourceSheet`	`string`	源工作表名称（默认：当前工作表）
`location`	`string`	输出起始单元格（默认：`"A3"`）
`rows`	`string[]`	行字段名称
`columns`	`string[]`	列字段名称
`data`	`DataField[]`	数据字段配置
`pages`	`string[]`	页面/报表筛选字段名称
`name`	`string`	透视表名称（默认：`"PivotTable1"`）
`style`	`string`	透视表样式名（默认：`"PivotStyleLight16"`）
`filters`	`PivotFilterOptions[]`	透视筛选器
`pivotConditionalFormats`	`PivotConditionalFormat[]`	透视区域条件格式规则
`pivotHierarchies`	`PivotHierarchyOptions[]`	透视层级配置
`calculatedItems`	`CalculatedItemOptions[]`	计算项
`calculatedMembers`	`CalculatedMemberOptions[]`	计算成员（MDX）
`chartFormats`	`ChartFormatOptions[]`	图表格式关联
`formats`	`PivotFormatOptions[]`	透视格式区域
`autoSortScope`	`PivotAreaOptions`	自动排序范围定义
`memberProperties`	`MemberPropertyOptions[]`	每字段的成员属性
`rowHierarchiesUsage`	`HierarchyUsageOptions[]`	行层级使用情况
`colHierarchiesUsage`	`HierarchyUsageOptions[]`	列层级使用情况
`dataOnRows`	`boolean`	数据字段显示在行而非列
`grandTotalCaption`	`string`	总计标题文本
`errorCaption`	`string`	错误标题文本
`showError`	`boolean`	显示错误信息
`missingCaption`	`string`	缺失标题文本
`showMissing`	`boolean`	显示缺失项
`pageStyle`	`string`	页面样式名称
`pivotTableStyle`	`string`	自定义透视表样式名称
`tag`	`string`	标签字符串
`showItems`	`boolean`	显示无数据的项目
`editData`	`boolean`	原地编辑数据
`disableFieldList`	`boolean`	禁用字段列表
`showCalcMbrs`	`boolean`	显示计算成员
`visualTotals`	`boolean`	视觉总计
`showMultipleLabel`	`boolean`	显示多个标签
`showDataDropDown`	`boolean`	显示数据下拉菜单
`showDrill`	`boolean`	显示展开/折叠指示器
`printDrill`	`boolean`	打印展开/折叠指示器
`showMemberPropertyTips`	`boolean`	显示成员属性提示
`showDataTips`	`boolean`	显示数据提示
`enableWizard`	`boolean`	启用布局向导
`enableDrill`	`boolean`	启用向下钻取
`enableFieldProperties`	`boolean`	启用字段属性
`pageWrap`	`number`	每行/列的页字段数
`pageOverThenDown`	`boolean`	页面布局先横向后纵向
`subtotalHiddenItems`	`boolean`	对隐藏项分类汇总
`fieldPrintTitles`	`boolean`	字段打印标题
`mergeItem`	`boolean`	合并项目标签
`showDropZones`	`boolean`	显示拖放区域
`showEmptyRow`	`boolean`	显示空行
`showEmptyCol`	`boolean`	显示空列
`showHeaders`	`boolean`	显示标题
`published`	`boolean`	发布到服务器
`gridDropZones`	`boolean`	网格拖放区域
`multipleFieldFilters`	`boolean`	多字段筛选
`rowHeaderCaption`	`string`	行标题
`colHeaderCaption`	`string`	列标题
`fieldListSortAscending`	`boolean`	字段列表升序排序
`mdxSubqueries`	`boolean`	启用 MDX 子查询
`customListSort`	`boolean`	自定义序列排序

编辑此页面或提交问题报告

超链接

外部链接和内部工作表跳转

图片

在工作表中嵌入 PNG 和 JPEG 图片