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" }]
}
]
}
]
}
{
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" },
],
},
],
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 |
name | string | Pivot table name (default: "PivotTable1") |
style | string | Pivot style name (default: "PivotStyleLight16") |
PivotDataField options
| Option | Type | Description |
|---|---|---|
field | string | Source field name to aggregate |
summarize | string | Aggregation function (default: "sum") |
name | string | Display name (default: "Sum of {field}") |