PivotTable Sort

API

Interface

interface GC.Pivot.ICustomSortCallBack { (fieldItemNameArray: any[], sortInfo: GC.Pivot.IPivotCustomSortInfo) : any[]; } // the usage here is a little different with pivot area, so the interface IPivotReference changes the display way. but actually they are same interface. interface GC.Spread.Pivot.IPivotReference { fieldName: string; items: string[]; } interface IPivotViewSortInfo { sortType: GC.Pivot.SortType; sortValueFieldName?: string; sortByPivotReferences?: GC.Spread.Pivot.IPivotReference[]; customSortCallback?: GC.Pivot.ICustomSortCallBack; }

API

/** * @description get or set sort for a field of pivot table. * @param {string} fieldName Indicates the target field name. * @param {GC.Pivot.IPivotViewSortInfo} sortInfo Indicates the sort info. * @returns {GC.Pivot.IPivotViewSortInfo | void} **/ function sort(fieldName: string, sortInfo: GC.Pivot.IPivotViewSortInfo): GC.Pivot.IPivotViewSortInfo | void

Sample Code

// Get sort info let sortInfo = pivotTable.sort("Country"); // Clear sort info pivotTable.sort("Country", null); // Sort By Field Item Name var sortInfo = { sortType: GC.Pivot.SortType.asc }; pivotTable.sort("Country", sortInfo); // Sort By Value var sortInfo = { sortType: GC.Pivot.SortType.asc, sortValueFieldName: 'DAMAGE' }; pivotTable.sort("Country", sortInfo) // Sort By Custom Field Item Value var sortInfo = { sortType: GC.Pivot.SortType.desc, sortValueFieldName: "DAMAGE", sortByPivotReferences: [ { fieldName: "Country", items: ["Germany"] }, { fieldName: "Type1", items: ["MT"] } ] }; pivotTable.sort("Level", sortInfo); // Sort By Custom Callback pivotTable.sort("Country", { customSortCallback: function (fieldItemNameArray) { return fieldItemNameArray.sort((a, b) => a.length - b.length); } });

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

import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { AppFunc } from './app-func';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, 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 } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState,
useEffect = React.useEffect;
const GCsheets = GC.Spread.Sheets;
function _getElementById(id) {
return document.getElementById(id);
}
export function AppFunc() {
const [selectedSortField, setSelectedSortField] = useState("Salesperson");
const [selectedSortType, setSelectedSortType] = useState("0");
const [selectedValueFieldName, setSelectedValueFieldName] = useState("");
const [spread, setSpread] = useState("");
const [pivotTable, setPivotTable] = useState("");
const [_rangeSelector, setRangeSelector] = useState("");
const sortFields = ["Salesperson", "Cars", "Date"];
const valueFieldNames = ["", "Quantity"];
const setSortInfo = () => {
spread.suspendPaint();
let sortInfo = generateSortInfo();
if (sortInfo) {
pivotTable.sort(selectedSortField, sortInfo);
syncSortInfo();
_rangeSelector.endSelectMode();
spread.focus();
}
spread.resumePaint();
};
const clearSortInfo = () => {
pivotTable.sort(selectedSortField, undefined);
syncSortInfo();
_rangeSelector.endSelectMode();
spread.focus();
};
useEffect(() => {
if (spread) {
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getSource(sheet2, pivotSales);
setPivotTable(addPivotTable(sheet1, tableName));
initFormulaTextBox();
spread.focus();
spread.resumePaint();
}
}, [spread]);
useEffect(() => {
if (pivotTable) {
syncSortInfo();
}
}, [selectedSortField]);
const initFormulaTextBox = () => {
let host = _getElementById("formulaTextBox");
let rangeSelector = new GCsheets.FormulaTextBox.FormulaTextBox(host, {
rangeSelectMode: true,
absoluteReference: true,
needSheetName: false
}, spread);
rangeSelector.workbook(spread);
setRangeSelector(rangeSelector);
}
const 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(GCsheets.Tables.TableThemes["none"]);
sheet.setArray(0, 0, tableSource);
return table.name();
}
const generateSortInfo = () => {
let pivotReferences = initPivotReferences();
let sortInfo = {
sortType: + selectedSortType
};
if (selectedValueFieldName !== '') {
sortInfo.sortValueFieldName = selectedValueFieldName;
}
if (pivotReferences) {
sortInfo.sortByPivotReferences = pivotReferences;
}
return sortInfo;
}
const syncSortInfo = () => {
let sortInfo = pivotTable.sort(selectedSortField);
let sortType = sortInfo && sortInfo.sortType;
if (sortType === undefined) {
sortType = GC.Spread.Pivot.SortType.asc;
}
let sortValueFieldName = sortInfo && sortInfo.sortValueFieldName;
if (sortValueFieldName === undefined) {
sortValueFieldName = '';
}
let pivotReferences = sortInfo && sortInfo.sortByPivotReferences;
setSelectedSortType(sortType + '')
setSelectedValueFieldName(sortValueFieldName);
setPivotReferences(pivotReferences);
}
const initPivotReferences = () => {
let cellRef = _rangeSelector.text();
if (!cellRef || !selectedValueFieldName) {
return;
}
let sheet = spread.getActiveSheet();
let range = GCsheets.CalcEngine.formulaToRange(sheet, _rangeSelector.text());
if (range) {
let row = range.row,
col = range.col;
if (sheet.pivotTables.findPivotTable(row, col)) {
let pivotInfo = pivotTable.getPivotInfo(row, col);
let fieldArea = pivotTable.getField(selectedSortField).pivotArea;
let 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) {
let 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]
};
});
}
}
}
}
}
const setPivotReferences = (pivotReferences) => {
let rangeStr = '',
resultRow,
resultCol;
if (pivotReferences) {
let refSourceNames = pivotReferences.map(ref => ref.fieldName);
let allFields = pivotTable.getFieldsByArea(GC.Spread.Pivot.PivotTableFieldType.rowField).concat(pivotTable.getFieldsByArea(GC.Spread.Pivot.PivotTableFieldType.columnField));
let pivotArea = {
references: allFields.map(function (field) {
let index = refSourceNames.indexOf(field.sourceName);
if (index !== -1) {
return { fieldName: field.fieldName, items: pivotReferences[index].items }
} else {
return { fieldName: field.fieldName }
}
})
}
let range = pivotTable.getPivotAreaRanges(pivotArea)[0];
resultRow = range.row;
resultCol = range.col;
rangeStr = GCsheets.CalcEngine.rangeToFormula(new GCsheets.Range(resultRow, resultCol, 1, 1));
}
_rangeSelector.text(rangeStr);
}
const 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;
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={
spread => setSpread(spread)
}></SpreadSheets>
</div>
<div className="options-container">
<div className="options-row">
<span>Sort Field</span>
<select value={selectedSortField}
onChange={
(event) => {
setSelectedSortField(event.target.value);
}
}>
{
sortFields.map((option) => (
<option value={option}>
{option} </option>
))
} </select>
</div>
<div className="options-row">
<span>Sort Type</span>
<label>
<input type="radio" value="0"
checked={
selectedSortType === "0"
}
onChange={
(event) => setSelectedSortType(event.target.value)
} />
ASC
</label>
<label>
<input type="radio" value="1"
checked={
selectedSortType === "1"
}
onChange={
(event) => setSelectedSortType(event.target.value)
} />
DESC
</label>
</div>
<div className="options-row">
<span>Value Field Name</span>
<select value={selectedValueFieldName}
onChange={
(event) => setSelectedValueFieldName(event.target.value)
}>
{
valueFieldNames.map((option) => (
<option value={option}>
{option} </option>
))
} </select>
</div>
<div className="options-row">
<span>Cell Ref</span>
<div id='formulaTextBox' />
</div>
<hr />
<div className="options-row">
<button onClick={setSortInfo}>Set Sort Info</button>
</div>
<div className="options-row">
<button onClick={clearSortInfo}>Clear Sort Info</button>
</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" style="height: 100%;"></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;
}

(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);

Summary

API

Interface

API

Sample Code

Feedback