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:
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState;
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"];
export function AppFunc() {
const [pt, setPt] = useState(null);
const [currentFieldName, setCurrentFieldName] = useState("Sum of total");
const [state, setState] = useState({
positionType: 1,
positionIndex: 0,
pt_subtotalPosition: 2,
field_subtotalPosition: 2,
field_subtotalsVisible: 1,
});
const initSpread = (spread) => {
initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
let pt = initPivotTable(pivotLayoutSheet);
setPt(pt);
}
const 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();
}
const 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", currentFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField);
myPivotTable.subTotalType(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;
}
const 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;
}
}
const switchSubtotalType = (e) => {
if (pt) {
let type = parseInt(e.target.value, 10);
let newFieldName = SubtotalType[type];
pt.subTotalType(currentFieldName, type);
newFieldName = getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
setCurrentFieldName(newFieldName);
}
}
const switchSubtotalType2 = (e) => {
if (pt) {
let type = parseInt(e.target.value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
}
const switchPositionType = (e) => {
if (pt) {
let positionType = parseInt(e.target.value, 10);
pt.dataPosition(positionType, positionIndex);
setState({ ...state, positionType: positionType });
}
}
const updatePositionIndex = (e) => {
if (pt) {
let positionIndex = parseInt(e.target.value, 10);
pt.dataPosition(positionType, positionIndex);
setState({ ...state, positionIndex: positionIndex });
}
}
const updatePtSubtotalPosition = (e) => {
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt.options.subtotalsPosition = subtotalPosition;
setState({ ...state, pt_subtotalPosition: subtotalPosition });
}
};
const updateFieldSubtotalPosition = (e) => {
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt.subtotalPosition("Cars", subtotalPosition);
setState({ ...state, field_subtotalPosition: subtotalPosition });
}
};
const updateFieldSubtotalVisible = (e) => {
if (pt) {
var subtotalVisible = parseInt(e.target.value, 10);
pt.subtotalVisible("Cars", !!subtotalVisible);
setState({ ...state, field_subtotalsVisible: subtotalVisible });
}
};
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
positionType={state.positionType}
positionIndex={state.positionIndex}
pt_subtotalPosition={state.pt_subtotalPosition}
field_subtotalPosition={state.field_subtotalPosition}
field_subtotalsVisible={state.field_subtotalsVisible}
switchSubtotalType={(e) => { switchSubtotalType(e) }}
switchSubtotalType2={(e) => { switchSubtotalType2(e) }}
switchPositionType={(e) => { switchPositionType(e) }}
updatePositionIndex={(e) => { updatePositionIndex(e) }}
updatePtSubtotalPosition={(e) => { updatePtSubtotalPosition(e) }}
updateFieldSubtotalPosition={(e) => { updateFieldSubtotalPosition(e) }}
updateFieldSubtotalVisible={(e) => { updateFieldSubtotalVisible(e) }}
/>
</div>
);
}
function Panel(props) {
return (
<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" onChange={(e) => { props.switchSubtotalType(e) }}>
<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" onChange={(e) => { props.switchSubtotalType2(e) }}>
<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" onChange={(e) => { props.switchPositionType(e) }}>
<option value="0">row</option>
<option value="1" selected>col</option>
</select>
</div>
<div class="option-item">
Position Index:
<input type="number" value={props.positionIndex} id="positionIndex" min="0" max="2" onChange={(e) => { props.updatePositionIndex(e) }} />
</div>
</div>
<div class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select id="pt_subtotalsPosition" onChange={(e) => { props.updatePtSubtotalPosition(e) }}>
<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" onChange={(e) => { props.updateFieldSubtotalPosition(e) }}>
<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" onChange={(e) => { props.updateFieldSubtotalVisible(e) }}>
<option value="0">none</option>
<option value="1" selected>show</option>
</select>
</div>
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
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"];
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.pt = null;
this.currentFieldName = "Sum of total";
this.state = {
positionType: 1,
positionIndex: 0,
pt_subtotalPosition: 2,
field_subtotalPosition: 2,
field_subtotalsVisible: 1,
};
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
positionType={this.state.positionType}
positionIndex={this.state.positionIndex}
pt_subtotalPosition={this.state.pt_subtotalPosition}
field_subtotalPosition={this.state.field_subtotalPosition}
field_subtotalsVisible={this.state.field_subtotalsVisible}
switchSubtotalType={(e) => { this.switchSubtotalType(e) }}
switchSubtotalType2={(e) => { this.switchSubtotalType2(e) }}
switchPositionType={(e) => { this.switchPositionType(e) }}
updatePositionIndex={(e) => { this.updatePositionIndex(e) }}
updatePtSubtotalPosition={(e) => { this.updatePtSubtotalPosition(e) }}
updateFieldSubtotalPosition={(e) => { this.updateFieldSubtotalPosition(e) }}
updateFieldSubtotalVisible={(e) => { this.updateFieldSubtotalVisible(e) }}
/>
</div>
);
}
initSpread(spread) {
this.spread = spread;
this.initSheets(spread);
let pivotLayoutSheet = spread.getSheet(0);
let pt = this.initPivotTable(pivotLayoutSheet);
this.pt = pt;
}
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();
}
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", 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;
}
getUniqueName(pt, name, index = 0) {
let realName = index === 0 ? name : name + (index + "");
if (pt.getField(realName)) {
return this.getUniqueName(pt, name, index + 1);
} else {
return realName;
}
}
switchSubtotalType(e) {
let pt = this.pt;
if (pt) {
let type = parseInt(e.target.value, 10);
let newFieldName = SubtotalType[type];
let currentFieldName = this.currentFieldName;
pt.subTotalType(currentFieldName, type);
newFieldName = this.getUniqueName(pt, newFieldName);
pt.updateFieldName(currentFieldName, newFieldName);
pt.autoFitColumn();
this.currentFieldName = newFieldName;
}
}
switchSubtotalType2(e) {
let pt = this.pt;
if (pt) {
let type = parseInt(e.target.value, 10);
pt.subTotalType("Average of total", type);
pt.autoFitColumn();
pt.refresh();
}
}
switchPositionType(e) {
let pt = this.pt;
if (pt) {
let positionType = parseInt(e.target.value, 10);
pt.dataPosition(positionType, this.positionIndex);
this.setState({ positionType: positionType });
}
}
updatePositionIndex(e) {
let pt = this.pt;
if (pt) {
let positionIndex = parseInt(e.target.value, 10);
pt.dataPosition(this.positionType, positionIndex);
this.setState({ positionIndex: positionIndex });
}
}
updatePtSubtotalPosition(e) {
let pt = this.pt;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt.options.subtotalsPosition = subtotalPosition;
this.setState({ pt_subtotalPosition: subtotalPosition });
}
};
updateFieldSubtotalPosition(e) {
let pt = this.pt;
if (pt) {
var subtotalPosition = parseInt(e.target.value, 10);
pt.subtotalPosition("Cars", subtotalPosition);
this.setState({ field_subtotalPosition: subtotalPosition });
}
};
updateFieldSubtotalVisible(e) {
let pt = this.pt;
if (pt) {
var subtotalVisible = parseInt(e.target.value, 10);
pt.subtotalVisible("Cars", !!subtotalVisible);
this.setState({ field_subtotalsVisible: subtotalVisible });
}
};
}
class Panel extends Component {
constructor(props) {
super(props);
}
render() {
return (
<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" onChange={(e) => { this.props.switchSubtotalType(e) }}>
<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" onChange={(e) => { this.props.switchSubtotalType2(e) }}>
<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" onChange={(e) => { this.props.switchPositionType(e) }}>
<option value="0">row</option>
<option value="1" selected>col</option>
</select>
</div>
<div class="option-item">
Position Index:
<input type="number" value={this.props.positionIndex} id="positionIndex" min="0" max="2" onChange={(e) => { this.props.updatePositionIndex(e) }} />
</div>
</div>
<div class="option-row">
Change pivotTable subtotals position:
<div class="option-item">
Subtotals Position:
<select id="pt_subtotalsPosition" onChange={(e) => { this.props.updatePtSubtotalPosition(e) }}>
<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" onChange={(e) => { this.props.updateFieldSubtotalPosition(e) }}>
<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" onChange={(e) => { this.props.updateFieldSubtotalVisible(e) }}>
<option value="0">none</option>
<option value="1" selected>show</option>
</select>
</div>
</div>
</div>
);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></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;
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%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
