概述
本 Demo 展示了如何使用 GETPIVOTDATA 函数从数据透视表中查询数据。Demo 创建了一个包含销售人员、车型、季度和销售额的数据透视表，并演示了 GETPIVOTDATA 函数的多种用法：基础查询单个数据、多条件组合查询、以及使用动态数组实现批量查询。
实现思路
创建数据源工作表，设置销售数据（包括日期、销售人员、车型、单价、数量、总价）
在 PivotLayout 工作表中创建数据透视表，配置行字段、列字段和值字段
为数据透视表设置过滤器，筛选特定的销售人员和车型数据
设置排序规则，按升序排列销售人员和小车型
在工作表右侧区域演示 GETPIVOTDATA 函数的多种用法，包括基础查询、多条件查询和动态数组溢出
代码解析
创建数据透视表
这段代码创建了一个数据透视表，并配置了行字段（销售人员、车型）、列字段（季度）和值字段（总销售额）。pivotTables.add 方法的参数依次为：透视表名称、数据源、起始行、起始列、布局类型和主题样式。
设置过滤器
使用 labelFilter 方法过滤销售人员字段，只显示 Alan、John 和 Tess 三人的数据。然后通过 sort 方法对销售人员按升序排序。
基础 GETPIVOTDATA 查询
这是 GETPIVOTDATA 函数的基础用法。参数说明：
"Totals"：要查询的值字段名称
$B$2：数据透视表中任意单元格的引用
"Salesperson","Tess"：第一组字段名称和项目名称
"Cars","Audi"：第二组字段名称和项目名称
"Qt","季度2"：第三组字段名称和项目名称
这个公式返回 Tess 在第二季度销售 Audi 的总金额。
动态数组溢出查询
GETPIVOTDATA 支持动态数组溢出功能。在启用 allowDynamicArray 后，可以使用数组参数（如 {"Alan";"John";"Tess"} 或单元格范围引用 C10:C12）作为条件，函数会返回一个二维数组，包含所有匹配条件的结果。这个公式返回 Alan、John、Tess 三人在不同季度销售不同车型的总销售额，结果会溢出到一个 3x3 的单元格区域。
运行效果
工作表左侧显示数据透视表，按销售人员和车型分组，列显示各季度的销售额
工作表右侧显示多个 GETPIVOTDATA 公式示例及计算结果
第一个示例返回 Tess 在第二季度销售 Audi 的总金额
第二个示例返回 Alan 销售 BMW 的总金额
第三个示例返回第一季度的总销售额
第四个示例返回所有数据的总计
最后一个示例使用动态数组，一次性返回多个销售人员在多个季度的销售数据
API 参考
pivotTables.add 方法
name：透视表名称
source：数据源，可以是表格名称或范围公式
row、col：透视表起始位置
layout：布局类型（compact、outline、tabular）
theme：主题样式
GETPIVOTDATA 函数
data_field：要检索的值字段名称，需用双引号引起来
pivot_table：数据透视表中任意单元格的引用
field1, item1, ...：可选，字段名称和项目名称对，最多 126 对
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 });
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
initPivotTable(pivotLayoutSheet);
initStyles(spread);
setGetPivotDataFunction(pivotLayoutSheet);
};
function initSpread(spread) {
spread.suspendPaint();
spread.options.allowDynamicArray = true;
var sheet = spread.getSheet(1);
sheet.name("DataSource");
sheet.setRowCount(117);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,2).formatter("$ #,##0");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 0, 0, 117, 6);
for(let i=2;i<=117;i++)
{
sheet.setFormula(i-1,5,'=D'+i+'*E'+i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
var sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
spread.resumePaint();
}
function initPivotTable(sheet) {
var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
myPivotTable.suspendLayout();
myPivotTable.options.showRowHeader = true;
myPivotTable.options.showColumnHeader = true;
myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
myPivotTable.group(groupInfo);
myPivotTable.add("季度 (date)", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField);
myPivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let itemList = ["Alan","John", "Tess"];
myPivotTable.labelFilter("Salesperson", { textItem: { list: itemList, isAll: false } });
myPivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc });
let carList = ["Audi","BMW","Mercedes"];
myPivotTable.labelFilter("Cars", { textItem: { list: carList, isAll: false } });
myPivotTable.sort("Cars", { sortType: GC.Pivot.SortType.asc });
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
myPivotTable.setStyle({dataOnly: true}, style);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
}
function setGetPivotDataFunction(sheet) {
sheet.setColumnWidth(8, 20);
sheet.setValue(1, 9, 'GETPIVOTDATA 基础用法');
sheet.setStyle(1, 9, 'intro');
var formula1 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson","Tess","Cars","Audi","Qt","季度2")';
sheet.setValue(3, 9, '返回 Tess 在第二季度销售 Audi 的总金额');
sheet.setStyle(3, 9, 'introSec');
sheet.setValue(4, 9, formula1);
sheet.setStyle(4, 9, 'formula');
sheet.getCell(5,9).formatter("$ #,##0");
sheet.setFormula(5, 9, formula1);
sheet.setStyle(5, 9, 'result');
var formula2 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson","Alan","Cars","BMW")';
sheet.setValue(7, 9, '返回 Alan 销售 BMW 的总金额');
sheet.setStyle(7, 9, 'introSec');
sheet.setValue(8, 9, formula2);
sheet.setStyle(8, 9, 'formula');
sheet.getCell(9,9).formatter("$ #,##0");
sheet.setFormula(9, 9, formula2);
sheet.setStyle(9, 9, 'result');
// Invoked formula
var formula3 = '=GETPIVOTDATA("Totals",$B$2,"Qt","季度1")';
sheet.setValue(11, 9, '返回第一季度的总销售额');
sheet.setStyle(11, 9, 'introSec');
sheet.setValue(12, 9, formula3);
sheet.setStyle(12, 9, 'formula');
sheet.getCell(13,9).formatter("$ #,##0");
sheet.setFormula(13, 9, formula3);
sheet.setStyle(13, 9, 'result');
var formula4 = '=GETPIVOTDATA("Totals",$B$2)';
sheet.setValue(15, 9, '返回总计');
sheet.setStyle(15, 9, 'introSec');
sheet.setValue(16, 9, formula4);
sheet.setStyle(16, 9, 'formula');
sheet.getCell(17,9).formatter("$ #,##0");
sheet.setFormula(17, 9, formula4);
sheet.setStyle(17, 9, 'result');
var formula5 = '=GETPIVOTDATA("Totals",$B$2,"Salesperson",{"Alan";"John";"Tess"},"Cars",C10:C12,"Qt",E3:G3)';
sheet.setValue(19, 9, '返回 Alan/John/Tess 在 Qtr2/Qtr3/Qtr4 季度销售 Audi/BMW/Mercedes 的溢出范围总金额');
sheet.setStyle(19, 9, 'introSec');
sheet.setValue(20, 9, formula5);
sheet.setStyle(20, 9, 'formula');
sheet.setFormula(21, 9, formula5);
sheet.getRange(21, 9, 3, 3).hAlign(0);
}
function initStyles(spread) {
var introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 13px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
var introSecStyle = new GC.Spread.Sheets.Style();
introSecStyle.name = 'introSec';
introSecStyle.font = 'normal bold 12px Segoe UI';
introSecStyle.foreColor = "#000";
spread.addNamedStyle(introSecStyle);
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
spread.addNamedStyle(formulaStyle);
var resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
spread.addNamedStyle(resultStyle);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
