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
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div class="options-container" style="float:right; width: 380px;">
<div>
<div v-for="(option, index) in optionsRef" :key="index" class="options-row">
<span v-if="option.type === 'text'">{{ option.text }}</span>
<span v-if="option.type === 'select'">{{ option.text }}</span>
<select v-if="option.type === 'select'" :id="option.id" v-model="option.selected">
<option v-for="item in option.items" :key="item" :value="item">{{ item }}</option>
</select>
<input v-else-if="option.type === 'input'" :id="option.id" type="text" v-model="option.value">
<div v-if="option.type === 'radio-group'">
<span>{{ option.text }}</span>
<label v-for="(item, i) in option.items" :key="i">
<input type="radio" :name="option.id" :id="option.id + i" :value="item.value" v-model="option.selected">
{{ item.label }}
</label>
</div>
<button v-else-if="option.type === 'button'" :id="option.id" @click="option.click">{{ option.text }}</button>
<div v-else-if="option.type === 'split-line'" style="border-bottom: 1px solid #ccc; margin: 10px 0;"></div>
<span v-if="option.type === 'div'">{{ option.text }}</span>
<div v-if="option.type === 'div'" :id="option.id"></div>
</div>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-vue";
import { ref, shallowRef } from "vue";
import "@mescius/spread-sheets-pivot-addon";
let spreadRef = shallowRef(null);
let pivotTableRef = shallowRef(null);
let _rangeSelectorRef = ref(null);
let optionsRef = ref([
{ type: "select", text: "Sort Field", id: "sort-field", items: ["Salesperson", "Cars", "Date"], selected: "Salesperson" },
{
type: "radio-group",
text: "Sort Type",
items: [
{ value: "0", label: "Ascending" },
{ value: "1", label: "Descending" },
],
selected: "0",
},
{ type: "select", text: "Value Field Name", id: "value-field-name", items: ["", "Quantity"], selected: "" },
{ type: "div", text: "Cell Ref", id: "formulaTextBox" },
{ type: "split-line" },
{ type: "button", text: "Set Sort Info", id: "set-sort-info", click: handleSetSortInfoClick },
{ type: "button", text: "Clear Sort Info", id: "clear-sort-info", click: handleClearSortInfoClick },
]);
function initSpread (spread) {
spreadRef.value = spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getSource(sheet2, pivotSales);
let pivotTable = addPivotTable(sheet1, tableName);
pivotTableRef.value = pivotTable;
initFormulaTextBox(spread);
spread.focus();
spread.resumePaint();
_getElementById("sort-field").addEventListener("change", () => {syncSortInfo()});
}
function initFormulaTextBox(spread) {
let host = _getElementById("formulaTextBox");
_rangeSelectorRef.value = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(host, {
rangeSelectMode: true,
absoluteReference: true,
needSheetName: false
}, spread);
_rangeSelectorRef.value.workbook(spread);
}
function handleSetSortInfoClick() {
let spread = spreadRef.value;
let pivotTable = pivotTableRef.value;
let _rangeSelector = _rangeSelectorRef.value;
spread.suspendPaint();
let sortInfo = generateSortInfo();
if (sortInfo) {
pivotTable.sort(optionsRef.value[0].selected, sortInfo);
syncSortInfo();
_rangeSelector.endSelectMode();
spread.focus();
}
spread.resumePaint();
}
function handleClearSortInfoClick() {
let spread = spreadRef.value;
let pivotTable = pivotTableRef.value;
let _rangeSelector = _rangeSelectorRef.value;
pivotTable.sort(optionsRef.value[0].selected, undefined);
syncSortInfo();
_rangeSelector.endSelectMode();
spread.focus();
}
function generateSortInfo() {
let pivotReferences = initPivotReferences();
let sortInfo = {
sortType: +optionsRef.value[1].selected
};
let selectedValueFieldName = optionsRef.value[2].selected;
if (selectedValueFieldName !== '') {
sortInfo.sortValueFieldName = selectedValueFieldName;
}
if (pivotReferences) {
sortInfo.sortByPivotReferences = pivotReferences;
}
return sortInfo;
}
function syncSortInfo() {
let pivotTable = pivotTableRef.value;
let sortInfo = pivotTable.sort(optionsRef.value[0].selected);
let sortType = sortInfo && sortInfo.sortType;
if (sortType === undefined) {
sortType = GC.Spread.Pivot.SortType.asc;
}
optionsRef.value[1].selected = sortType + '';
let sortValueFieldName = sortInfo && sortInfo.sortValueFieldName;
if (sortValueFieldName === undefined) {
sortValueFieldName = '';
}
optionsRef.value[2].selected = sortValueFieldName;
let pivotReferences = sortInfo && sortInfo.sortByPivotReferences;
setPivotReferences(pivotReferences);
}
function initPivotReferences() {
let _rangeSelector = _rangeSelectorRef.value;
let pivotTable = pivotTableRef.value;
let cellRef = _rangeSelector.text();
if (!cellRef || !optionsRef.value[2].selected) {
return;
}
let spread = spreadRef.value;
let sheet = spread.getActiveSheet();
let range = GC.Spread.Sheets.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(optionsRef.value[0].selected).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]
};
});
}
}
}
}
}
function setPivotReferences(pivotReferences) {
let pivotTable = pivotTableRef.value;
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 = GC.Spread.Sheets.CalcEngine.rangeToFormula(new GC.Spread.Sheets.Range(resultRow, resultCol, 1, 1));
}
_rangeSelectorRef.value.text(rangeStr);
}
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 _getElementById(id) {
return document.getElementById(id);
}
</script>
<style scoped>
.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 div {
width: 100%;
display: flex;
}
.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;
}
#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);
