XLSX

Pivot Tables

Create pivot tables from source data with 11 aggregation functions

Basic Pivot Table

Create a pivot table from a source data range, grouping by row fields and aggregating data:

{
  "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" }],
        },
      ],
    },
  ],
});

Aggregation Functions

All 11 ST_DataConsolidateFunction types from OOXML are supported:

Function	`summarize` value	Description
Sum	`"sum"`	Sum of values (default)
Average	`"average"`	Arithmetic mean
Count	`"count"`	Count of non-empty
Count Numbers	`"countNums"`	Count of numbers
Max	`"max"`	Maximum value
Min	`"min"`	Minimum value
Product	`"product"`	Product of values
StdDev (sample)	`"stdDev"`	STDEV.S
StdDevP (population)	`"stdDevp"`	STDEV.P
Var (sample)	`"var"`	VAR.S
VarP (population)	`"varp"`	VAR.P

Multiple Data Fields

Add multiple aggregations in the same pivot table:

{
  "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" },
    ],
  },
],

Filters

Apply pivot filters to restrict which data appears in the pivot table. Use the PivotFilterTypeValue enum for filter types:

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 options

Option	Type	Description
`fld`	`number`	Field index to filter on (required)
`type`	`string`	Filter type from `PivotFilterTypeValue` (required)
`id`	`number`	Unique filter ID within the pivot table (required)
`name`	`string`	Filter name
`description`	`string`	Filter description
`stringValue1`	`string`	First value for caption/date filters
`stringValue2`	`string`	Second value for between filters
`mpFld`	`number`	Measure field index for OLAP filters
`evalOrder`	`number`	Evaluation order
`iMeasureHier`	`number`	Measure hierarchy
`iMeasureFld`	`number`	Measure field

Available filter types (`PivotFilterTypeValue`)

Category	Values
Caption	`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	`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	`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`
Relative	`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`
Quarter	`Q1`, `Q2`, `Q3`, `Q4`
Month	`M1` through `M12`
Other	`UNKNOWN`, `COUNT`, `PERCENT`, `SUM`

Conditional Formatting

Apply conditional formatting rules to pivot table areas:

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,
          },
        ],
      },
    ],
  },
],

Advanced Features

Page/Report Filters

Use pages to add report filter fields that appear above the pivot table:

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

Column Fields

Add columns to create cross-tabulations:

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

Pivot Hierarchies

Control field behavior with 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",
      },
    ],
  },
],

Pivot Table Options Reference

Option	Type	Description
`source`	`string`	Source data range, e.g. `"A1:C11"`
`sourceSheet`	`string`	Source sheet name (default: current sheet)
`location`	`string`	Output start cell (default: `"A3"`)
`rows`	`string[]`	Row field names
`columns`	`string[]`	Column field names
`data`	`DataField[]`	Data field configurations
`pages`	`string[]`	Page/report filter field names
`name`	`string`	Pivot table name (default: `"PivotTable1"`)
`style`	`string`	Pivot style name (default: `"PivotStyleLight16"`)
`filters`	`PivotFilterOptions[]`	Pivot filters
`pivotConditionalFormats`	`PivotConditionalFormat[]`	Conditional format rules for pivot areas
`pivotHierarchies`	`PivotHierarchyOptions[]`	Pivot hierarchy configurations
`calculatedItems`	`CalculatedItemOptions[]`	Calculated items
`calculatedMembers`	`CalculatedMemberOptions[]`	Calculated members (MDX)
`chartFormats`	`ChartFormatOptions[]`	Chart format associations
`formats`	`PivotFormatOptions[]`	Pivot format areas
`autoSortScope`	`PivotAreaOptions`	Auto sort scope definition
`memberProperties`	`MemberPropertyOptions[]`	Member properties per field
`rowHierarchiesUsage`	`HierarchyUsageOptions[]`	Row hierarchy usage
`colHierarchiesUsage`	`HierarchyUsageOptions[]`	Column hierarchy usage
`dataOnRows`	`boolean`	Data fields on rows instead of columns
`grandTotalCaption`	`string`	Grand total caption text
`errorCaption`	`string`	Error caption text
`showError`	`boolean`	Show error messages
`missingCaption`	`string`	Missing caption text
`showMissing`	`boolean`	Show missing items
`pageStyle`	`string`	Page style name
`pivotTableStyle`	`string`	Custom pivot table style name
`tag`	`string`	Tag string
`showItems`	`boolean`	Show items with no data
`editData`	`boolean`	Edit data in-place
`disableFieldList`	`boolean`	Disable field list
`showCalcMbrs`	`boolean`	Show calculated members
`visualTotals`	`boolean`	Visual totals
`showMultipleLabel`	`boolean`	Show multiple labels
`showDataDropDown`	`boolean`	Show data drop-down
`showDrill`	`boolean`	Show drill indicators
`printDrill`	`boolean`	Print drill indicators
`showMemberPropertyTips`	`boolean`	Show member property tips
`showDataTips`	`boolean`	Show data tips
`enableWizard`	`boolean`	Enable layout wizard
`enableDrill`	`boolean`	Enable drill-down
`enableFieldProperties`	`boolean`	Enable field properties
`pageWrap`	`number`	Page fields per row/column
`pageOverThenDown`	`boolean`	Page layout over then down
`subtotalHiddenItems`	`boolean`	Subtotal hidden items
`fieldPrintTitles`	`boolean`	Field print titles
`mergeItem`	`boolean`	Merge item labels
`showDropZones`	`boolean`	Show drop zones
`showEmptyRow`	`boolean`	Show empty row
`showEmptyCol`	`boolean`	Show empty column
`showHeaders`	`boolean`	Show headers
`published`	`boolean`	Published to server
`gridDropZones`	`boolean`	Grid drop zones
`multipleFieldFilters`	`boolean`	Multiple field filters
`rowHeaderCaption`	`string`	Row header caption
`colHeaderCaption`	`string`	Column header caption
`fieldListSortAscending`	`boolean`	Sort field list ascending
`mdxSubqueries`	`boolean`	MDX subqueries enabled
`customListSort`	`boolean`	Custom list sort

Edit this pageorReport an issue

Hyperlinks

External links and internal sheet navigation

Images

Embed PNG and JPEG images in worksheets