在数据透视表中，有11种计算类型。 每种类型都定义了在数据区域中计算值的方式。
枚举值
描述
average (平均值))
所有值的平均值。
count (计数)
所有值的数量。 计数合并功能的结果与工作表COUNT功能的结果相同。
countNums (数值计数)
所有数值类型值的数量，数值计数合并功能的结果与工作表COUNT功能的结果相同。
max (最大值)
所有值中的最大值。
min (最小值)
所有值中的最小值。
product (乘积)
所有值的乘积。
stdDev (总体标准偏差)
对总体标准偏差的估计，其中样本是整个总体的子集。
stdDevp (标准偏差))
总体的标准偏差，其中总体是要汇总的所有数据。
sum (求和)
所有值的加和。
var (总体方差)
对总体方差的估计，其中样本是整个总体的子集。
varp (方差)
总体的方差，其中总体是要汇总的所有数据。
如果有多个值字段，∑数值字段将自动添加到列区域。 ∑数值字段用作行/列字段，以定义数据透视表中行/列标题的数据分布。
用户可以通过以下API获取或设置数据透视表字段的计算类型：
在设置subTotalType之前，用户必须已将该字段添加到当前的数据透视表中。 例如：
所有支持的计算类型都在以下枚举中：
用户可以设置或者获取∑数值的位置信息:
用户可以获得或设置单个文件的小计位置（顶部/底部）：
const SubtotalType = ["Average of total", "Count of total", "Count numbers of total", "Max of total", "Min of total",
"Product of total", "StdDev of total", "StdDevp of total", "Sum of total", "Varr of total", "Varp of total"];
var currentFieldName = "Sum of total";
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 });
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
var pt = initPivotTable(pivotLayoutSheet);
bindEvent(spread, pt);
};
function initSpread(spread) {
spread.suspendPaint();
let 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");
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"]);
sheet.setArray(0, 0, pivotSales);
var sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
sheet0.setColumnCount(50);
spread.resumePaint();
}
function initPivotTable(sheet) {
let 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);
myPivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
myPivotTable.group(groupInfo);
myPivotTable.add("total", this.currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField);
myPivotTable.subTotalType(this.currentFieldName, GC.Pivot.SubtotalType.sum);
myPivotTable.add("total", "Average of total", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.average);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
return myPivotTable;
}
function getUniqueName(pt, name, index = 0) {
let realName = index === 0 ? name : name + (index + "");
if (pt.getField(realName)) {
return getUniqueName(pt, name, index + 1);
} else {
return realName;
}
}
function bindEvent (spread, pt) {
_getElementById("subtotalType").addEventListener("change", () => {
if (pt) {
var type = parseInt(_getElementById("subtotalType").value, 10);
var newFieldName = SubtotalType[type];
pt.subTotalType(currentFieldName, type);
newFieldName = getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
currentFieldName = newFieldName;
}
});
_getElementById("subtotalType2").addEventListener("change", () => {
if (pt) {
var type = parseInt(_getElementById("subtotalType2").value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
});
_getElementById("positionType").addEventListener("change", () => {
updateDataPosition(pt);
});
_getElementById("positionIndex").addEventListener("change", () => {
updateDataPosition(pt);
});
_getElementById("pt_subtotalsPosition").addEventListener("change", () => {
if (pt) {
var subtotalPosition = parseInt(_getElementById("pt_subtotalsPosition").value, 10);
pt.options.subtotalsPosition = subtotalPosition;
pt.autoFitColumn();
}
});
_getElementById("field_subtotalsPosition").addEventListener("change", () => {
if (pt) {
var subtotalPosition = parseInt(_getElementById("field_subtotalsPosition").value, 10);
pt.subtotalPosition("Cars", subtotalPosition);
pt.autoFitColumn();
}
});
}
function updateDataPosition (pt) {
if (pt) {
var positionType = parseInt(_getElementById("positionType").value, 10);
var positionIndex = parseInt(_getElementById("positionIndex").value, 10);
pt.dataPosition(positionType, positionIndex);
}
}
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 class="options-container">
<div class="option-row">
<label><b>Settings</b>
</label>
</div>
<hr />
<div class="option-row">
Change the subtotal type for the <b>Sum of Total</b> fields (col D-G):
<select id="subtotalType">
<option value="0">average (Average)</option>
<option value="1">count (Count)</option>
<option value="2">countNums (CountNums)</option>
<option value="3">max (Maximum)</option>
<option value="4">min (Minimum)</option>
<option value="5">product (Product)</option>
<option value="6">stdDev (StdDev)</option>
<option value="7">stdDevp (StdDevP)</option>
<option value="8" selected>sum (Sum)</option>
<option value="9">var (Variance)</option>
<option value="10">varp (VarP)</option>
</select>
</div>
<div class="option-row">
Changes the subtotal type for the <b>Average of total</b> fields (col H-M):
<select id="subtotalType2">
<option value="0" selected>average (Average)</option>
<option value="1">count (Count)</option>
<option value="2">countNums (CountNums)</option>
<option value="3">max (Maximum)</option>
<option value="4">min (Minimum)</option>
<option value="5">product (Product)</option>
<option value="6">stdDev (StdDev)</option>
<option value="7">stdDevp (StdDevP)</option>
<option value="8">sum (Sum)</option>
<option value="9">var (Variance)</option>
<option value="10">varp (VarP)</option>
</select>
</div>
<div class="option-row">
Changes the data position.
<div class="option-item">
Position Type:
<select id="positionType">
<option value="0">row</option>
<option value="1" selected>col</option>
</select>
</div>
<div class="option-item">
Position Index:
<input type="number" value="0" id="positionIndex" min="0" max="2" />
</div>
</div>
<div class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select name="" id="pt_subtotalsPosition" class="select-option-select">
<option value="0">none</option>
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
<div class="option-row">
Change subtotals position for the <b>Cars</b> fields:
<div class="option-item">
Subtotals Position:
<select name="" id="field_subtotalsPosition" class="select-option-select">
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: calc(100% - 220px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 220px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
.option-item {
margin-top: 5px;
}
.option-item #positionType {
float: right;
width: 80px;
}
.option-item #positionIndex {
float: right;
width: 72px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
