Summary
PivotTables can be sorted in the following ways:
Sort By Field Item Name
Sort By Value
Sort By Custom Field Item Value
Sort By Custom Callback
The sortType can be set in any way.
API
Interface
API
Sample Code
window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getSource(sheet2, pivotSales);
window.pivotTable = addPivotTable(sheet1, tableName);
bindEvent(spread);
initFormulaTextBox(spread);
spread.resumePaint();
spread.focus();
}
function getSource(sheet, tableSource) {
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('table', 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, tableSource);
return table.name();
}
function addPivotTable(sheet, source) {
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(10000);
let pivotTable = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
pivotTable.suspendLayout();
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.top;
pivotTable.resumeLayout();
sheet.resumePaint();
pivotTable.autoFitColumn();
return pivotTable;
}
function _isNullOrUndefined(obj) {
return obj === null || obj === undefined;
}
function bindEvent(spread) {
_getElementById("setSortInfo").addEventListener("click", function (e) {
let sortInfo = generateSortInfo();
if (sortInfo) {
pivotTable.sort(getSortFieldName(), sortInfo);
syncSortInfoToDOM();
_rangeSelector.endSelectMode();
spread.focus();
}
});
_getElementById("clearSortInfo").addEventListener("click", function (e) {
pivotTable.sort(getSortFieldName(), null);
syncSortInfoToDOM();
_rangeSelector.endSelectMode();
spread.focus();
});
_getElementById("sort-field").addEventListener("change", function (e) {
syncSortInfoToDOM();
});
}
function initFormulaTextBox(spread) {
let host = _getElementById("formulaTextBox");
window._rangeSelector = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(host, {
rangeSelectMode: true,
absoluteReference: true,
needSheetName: false
}, spread);
_rangeSelector.workbook(spread);
}
function getSortFieldName() {
return _getElementById("sort-field").value;
}
function getSortType() {
var obj = document.getElementsByName("sort-type");
for (var i in obj) {
if (obj[i].checked == true) {
return +obj[i].value;
}
}
}
function setSortType(type) {
var obj = document.getElementsByName("sort-type");
for (var i in obj) {
if (obj[i].value === type + '') {
obj[i].checked = "checked";
break;
}
}
}
function getValueFieldName() {
return _getElementById("value-field-name").value;
}
function setValueFieldName(name) {
_getElementById("value-field-name").value = name;
}
function initPivotReferences() {
var cellRef = _rangeSelector.text();
if (!cellRef || !getValueFieldName()) {
return;
}
var spread = _rangeSelector.workbook();
var sheet = spread.getActiveSheet();
var range = GC.Spread.Sheets.CalcEngine.formulaToRange(sheet, _rangeSelector.text());
if (range) {
var row = range.row, col = range.col;
if (sheet.pivotTables.findPivotTable(row, col)) {
var pivotInfo = pivotTable.getPivotInfo(row, col);
var fieldArea = pivotTable.getField(getSortFieldName()).pivotArea;
var infos;
if (fieldArea === GC.Spread.Pivot.PivotTableFieldType.rowField) {
infos = pivotInfo.colInfos;
}
if (fieldArea === GC.Spread.Pivot.PivotTableFieldType.columnField) {
infos = pivotInfo.rowInfos;
}
if (infos && infos.length > 0) {
var isGrandTotal = infos.length === 1 && infos[0].isGrandTotal;
if (!isGrandTotal) { // if is grand total, nothing to do.
return infos.map((info) => {
return {
fieldName: info.fieldName,
items: [info.itemName]
};
});
}
}
}
}
}
function setPivotReferences(pivotReferences) {
var rangeStr = '', resultRow, resultCol;
if (pivotReferences) {
var refSourceNames = pivotReferences.map(ref => ref.fieldName);
var allFields = pivotTable.getFieldsByArea(GC.Spread.Pivot.PivotTableFieldType.rowField).concat(pivotTable.getFieldsByArea(GC.Spread.Pivot.PivotTableFieldType.columnField));
var pivotArea = {
references: allFields.map(function (field) {
var index = refSourceNames.indexOf(field.sourceName);
if (index !== -1) {
return {
fieldName: field.fieldName,
items: pivotReferences[index].items
}
} else {
return {
fieldName: field.fieldName
}
}
})
}
var range = pivotTable.getPivotAreaRanges(pivotArea)[0];
resultRow = range.row;
resultCol = range.col;
rangeStr = GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(resultRow, resultCol, 1, 1));
}
_rangeSelector.text(rangeStr);
}
function generateSortInfo() {
var sortFieldName = getSortFieldName();
var sortType = getSortType();
var sortValueFieldName = getValueFieldName();
var pivotReferences = initPivotReferences();
if (_isNullOrUndefined(sortFieldName) || _isNullOrUndefined(sortType)) {
return;
}
var sortInfo = {
sortType: sortType
};
if (!_isNullOrUndefined(sortValueFieldName) && sortValueFieldName !== '') {
sortInfo.sortValueFieldName = sortValueFieldName;
}
if (!_isNullOrUndefined(pivotReferences)) {
sortInfo.sortByPivotReferences = pivotReferences;
}
return sortInfo;
}
function syncSortInfoToDOM() {
var sortInfo = pivotTable.sort(getSortFieldName());
var sortType = sortInfo && sortInfo.sortType;
if (_isNullOrUndefined(sortType)) {
sortType = GC.Spread.Pivot.SortType.asc;
}
setSortType(sortType);
var sortValueFieldName = sortInfo && sortInfo.sortValueFieldName;
if (_isNullOrUndefined(sortValueFieldName)) {
sortValueFieldName = '';
}
setValueFieldName(sortValueFieldName);
var pivotReferences = sortInfo && sortInfo.sortByPivotReferences;
setPivotReferences(pivotReferences);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="options-row">
<span>Sort Field:</span>
<select id="sort-field">
<option value="Salesperson" checked>Salesperson</option>
<option value="Cars">Cars</option>
<option value="Date">Date</option>
</select>
</div>
<div class="options-row">
<span>Sort Type:</span>
<label><input type="radio" name="sort-type" value="0" checked>Ascending</label>
<label><input type="radio" name="sort-type" value="1">Descending</label>
</div>
<div class="options-row">
<span>Value Field Name:</span>
<select id="value-field-name">
<option value="" checked></option>
<option value="Quantity">Quantity</option>
</select>
</div>
<div class="options-row">
<span>Cell Ref:</span>
<div id="formulaTextBox"></div>
</div>
<hr>
<div class="options-row">
<button id="setSortInfo">Set Sort Info</button>
</div>
<div class="options-row">
<button id="clearSortInfo">Clear Sort Info</button>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 400px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 380px;
padding: 10px;
font-family: Arial, sans-serif;
font-size: 14px;
}
.options-row {
display: flex;
align-items: center;
margin-bottom: 10px;
}
.options-row span {
flex: 1;
margin-right: 10px;
min-width: 100px;
text-align: left;
}
.options-row select,
.options-row input[type="text"] {
flex: 2;
padding: 5px;
border: 1px solid #ccc;
border-radius: 3px;
font-family: inherit;
font-size: inherit;
}
.options-row label {
flex: 2;
}
.options-row label:first-of-type{
padding-left: 15px;
}
.options-row button {
flex: 1;
background-color: #007bff;
color: #fff;
padding: 5px 10px;
border: none;
border-radius: 3px;
font-family: inherit;
font-size: inherit;
cursor: pointer;
}
.options-row button:hover {
background-color: #0069d9;
}
#formulaTextBox {
flex: 2;
padding: 2px 5px;
border: 1px solid #ccc;
border-radius: 3px;
font-family: inherit;
font-size: inherit;
background-color: #fff;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
