In PivotTable, there are 11 kinds of subtotal types. Each type defines the way that values are calculated in the data area.
Enumeration Value
Description
average (Average)
The average of the values.
count (Count)
The number of data values. The Count consolidation function works the same as the COUNTA worksheet function.
countNums (CountNums)
The number of data values that are numbers. The Count Nums consolidation function works the same as the COUNT worksheet function.
max (Maximum)
The largest value.
min (Minimum)
The smallest value.
product (Product)
The product of the values.
stdDev (StdDev)
An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
stdDevp (StdDevP)
The standard deviation of a population, where the population is all of the data to be summarized.
sum (Sum)
The sum of the values.
var (Variance)
An estimate of the variance of a population, where the sample is a subset of the entire population.
varp (VarP)
The variance of a population, where the population is all of the data to be summarized.
In multiple value fields, the ∑Value field will be added to column area automatically.
The ∑Value field worked as the row / column field to define the data distribution in PivotTable, row / column header.
Users can get or set SubTotalType of the PivotTable field with the below API:
Before setting the subTotalType, a user must have added the field to the current PivotTable. For example:
All of the supported subtotal types are in the below enumeration:
The user can get or set the position of ∑Value:
The user can get or set the subtotal position (top/bottom) for a single field:
The can get or set whether subtotals for individual fields are displayed:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet> </gc-worksheet>
<gc-worksheet> </gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<label><b>Settings</b> </label>
<hr />
<div class="option-row">
Change the subtotal type for the <b>Sum of Total</b> fields (col D-G):
<select id="subtotalType" @change="switchSubtotalType($event)">
<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" @change="switchSubtotalType2($event)">
<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" @change="switchPositionType($event)">
<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" @change="updatePositionIndex($event)" />
</div>
</div>
<div class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select id="pt_subtotalsPosition" @change="updatePtSubtotalPosition($event)">
<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 id="field_subtotalsPosition" @change="updateFieldSubtotalPosition($event)">
<option value="1">top</option>
<option value="2" selected>bottom</option>
</select>
</div>
</div>
<div class="option-row">
Sets whether the <b>Cars</b> field displays subtotals:
<div class="option-item">
Subtotal Visible:
<select id="field_subtotalsVisible" @change="updateFieldSubtotalVisible($event)">
<option value="0">none</option>
<option value="1" selected>show</option>
</select>
</div>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-vue";
import { shallowRef } from "vue";
import "@mescius/spread-sheets-pivot-addon";
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",
];
let spreadRef = shallowRef(null);
let ptRef = shallowRef(null);
let currentFieldNameRef = shallowRef("Sum of total");
let positionTypeRef = shallowRef(1);
let positionIndexRef = shallowRef(0);
let pt_subtotalPositionRef = shallowRef(2);
let field_subtotalPositionRef = shallowRef(2);
let field_subtotalsVisibleRef = shallowRef(1);
function initSpread (spread) {
spreadRef.value = spread;
initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
let pt = initPivotTable(pivotLayoutSheet);
ptRef.value = pt;
}
function initSheets (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);
let 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);
let groupInfo = {
originFieldName: "date",
dateGroups: [{
by: GC.Pivot.DateGroupType.quarters
}],
};
myPivotTable.group(groupInfo);
myPivotTable.add(
"Quarters (date)",
"Quarters (date)",
GC.Spread.Pivot.PivotTableFieldType.columnField
);
myPivotTable.add(
"total",
currentFieldNameRef.value,
GC.Spread.Pivot.PivotTableFieldType.valueField
);
myPivotTable.subTotalType(
currentFieldNameRef.value,
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 switchSubtotalType (e) {
let pt = ptRef.value;
if (pt) {
let type = parseInt(e.target.value, 10);
let newFieldName = SubtotalType[type];
let currentFieldName = currentFieldNameRef.value;
pt.subTotalType(currentFieldName, type);
newFieldName = getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
currentFieldNameRef.value = newFieldName;
}
}
function switchSubtotalType2(e) {
let pt = ptRef.value;
if (pt) {
let type = parseInt(e.target.value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
}
function switchPositionType(e) {
let pt = ptRef.value;
if (pt) {
let positionType = parseInt(e.target.value, 10);
positionTypeRef.value = positionType;
pt.dataPosition(positionType, positionIndexRef.value);
}
}
function updatePositionIndex(e) {
let pt = ptRef.value;
if (pt) {
let positionIndex = parseInt(e.target.value, 10);
positionIndexRef.value = positionIndex;
pt.dataPosition(positionTypeRef.value, positionIndex);
}
}
function updatePtSubtotalPosition(e) {
let pt = ptRef.value;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt_subtotalPositionRef.value = subtotalPosition;
pt.options.subtotalsPosition = subtotalPosition;
}
}
function updateFieldSubtotalPosition(e) {
let pt = ptRef.value;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
field_subtotalPositionRef.value = subtotalPosition;
pt.subtotalPosition("Cars", subtotalPosition);
}
}
function updateFieldSubtotalVisible(e) {
let pt = ptRef.value;
if (pt) {
var subtotalVisible = parseInt(e.target.value, 10);
field_subtotalsVisibleRef.value = subtotalVisible;
pt.subtotalVisible("Cars", !!subtotalVisible);
}
}
</script>
<style scoped>
.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;
scrollbar-width: none;
-ms-overflow-style: none;
}
.options-container::-webkit-scrollbar {
display: none;
}
.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%;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'../node_modules/*/package.json',
"../node_modules/@mescius/*/package.json",
"../node_modules/@babel/*/package.json",
"../node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
