透视表排序 | 增强插件 | SpreadJS 在线示例

概括 Pivot Table可以通过4种方式对字段进行排序： Sort By Field Item Name Sort By Value Sort By Custom Field Item Value Sort By Custom Callback Sort By Custom Sort List SortType可以以任何方式设置。 API 接口 PivotTable APIs PivotTableManager APIs 示例

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);
window.spread = spread;
bindEvent(spread);
initFormulaTextBox(spread);
syncSortInfoToDOM();
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) {
const autoSortState = getAutoSort();
const firstKeyList = getFirstKeyList();
const fieldName= getSortFieldName();
pivotTable.autoSortState(fieldName, autoSortState);
spread.getActiveSheet().pivotTables.customList(firstKeyList);
pivotTable.sort(fieldName, sortInfo);
_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();
});
const sortByContainer = document.getElementById('sortTypeContainer');
sortByContainer.addEventListener('change', (e) => {
const t = e.target;
if (t.matches('input[type="radio"]')) {
displayCustomListOrSortByValue();
syncFirstKeyListDisabledState();
}
});
_getElementById('autoSort').addEventListener('change', (e)=>{
syncFirstKeyListDisabledState();
});
_getElementById("value-field-name").addEventListener('change', (e)=>{
syncFirstKeyListDisabledState();
});
}
function syncFirstKeyListDisabledState () {
const disabled = getAutoSort() || getSortType() === 2 || !!getValueFieldName();
const firstKeyList = _getElementById('firstKeyList');
firstKeyList.disabled = disabled;
if (disabled) {
firstKeyList.classList.add('disabled-style');
} else {
firstKeyList.classList.remove('disabled-style');
}
}
function displayCustomListOrSortByValue() {
if (getSortType() === 2) {
getCustomSortListContainer().style.display='block';
getSortByValueContainer().style.display='none';
} else {
getCustomSortListContainer().style.display='none';
getSortByValueContainer().style.display='block';
}
}
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 getAutoSort() {
return _getElementById('autoSort').checked;
}
function setAutoSort(isAutoSort) {
_getElementById('autoSort').checked = isAutoSort;
}
function getSortByValueContainer () {
return _getElementById('sortByValueContainer');
}
function getCustomSortListContainer () {
return _getElementById('customSortListContainer');
}
function getFirstKeyList () {
const value = _getElementById('firstKeyList').value;
if (!value) {
return value;
}
return value.split(',');
}
function setCustomList (list) {
let value = '';
if (Array.isArray(list)) {
value = list.join(',');
}
_getElementById('customSortList').value = value;
}
function getCustomList () {
const value = _getElementById('customSortList').value;
if (!value) {
return value;
}
return value.split(',');
}
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 (sortType === 2) {
sortInfo.customSortList = getCustomList();
} else {
if (!_isNullOrUndefined(sortValueFieldName) && sortValueFieldName !== '') {
sortInfo.sortValueFieldName = sortValueFieldName;
}
if (!_isNullOrUndefined(pivotReferences)) {
sortInfo.sortByPivotReferences = pivotReferences;
}
}
return sortInfo;
}
function syncSortInfoToDOM() {
const fieldName = getSortFieldName();
const autoSortState = pivotTable.autoSortState(fieldName)
setAutoSort(autoSortState);
var sortInfo = pivotTable.sort(fieldName);
var sortType = sortInfo && sortInfo.sortType;
if (_isNullOrUndefined(sortType)) {
sortType = GC.Spread.Pivot.SortType.asc;
}
setSortType(sortType);
displayCustomListOrSortByValue();
if (sortType === 2) {
setCustomList(sortInfo?.customSortList);
} else {
var sortValueFieldName = sortInfo && sortInfo.sortValueFieldName;
if (_isNullOrUndefined(sortValueFieldName)) {
sortValueFieldName = '';
}
setValueFieldName(sortValueFieldName);
var pivotReferences = sortInfo && sortInfo.sortByPivotReferences;
setPivotReferences(pivotReferences);
}
syncFirstKeyListDisabledState();
}
function _getElementById(id) {
return document.getElementById(id);
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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>第一排序字段排序顺序:</span>
</div>
<div class="options-row">
<input type="text" id="firstKeyList" class="text-input" placeholder="用逗号分隔不同的项目" />
</div>
<hr>
<div class="options-row">
<span>排序字段:</span>
<select id="sort-field">
<option value="Salesperson" checked>Salesperson</option>
<option value="Cars">Cars</option>
<option value="Quarters (Date)">Quarters (Date)</option>
</select>
</div>
<div class="options-row" id="autoSortContainer">
<label><input type="checkbox" id="autoSort" value="0" checked>自动排序</label>
</div>
<div class="options-row">
<span>排序类型:</span>
</div>
<div class="options-row" id="sortTypeContainer">
<label><input type="radio" name="sort-type" value="0" checked>升序</label>
<label><input type="radio" name="sort-type" value="1">降序</label>
<label><input type="radio" name="sort-type" value="2">自定义</label>
</div>
<div id="sortByValueContainer">
<div class="options-row">
<span>值字段名称:</span>
<select id="value-field-name">
<option value="" checked></option>
<option value="Quantity">数量</option>
</select>
</div>
<div class="options-row">
<span>单元格引用:</span>
<div id="formulaTextBox"></div>
</div>
</div>
<div id="customSortListContainer">
<div class="options-row">
<span>自定义排序列表:</span>
</div>
<div class="options-row">
<input type="text" id="customSortList" class="text-input" placeholder="用逗号分隔不同的项目" />
</div>
</div>
<hr>
<div class="options-row">
<button id="setSortInfo">设置排序信息</button>
</div>
<div class="options-row">
<button id="clearSortInfo">清除排序信息</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 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;
}
input.text-input {
width: 100%;
background-color: white; 
padding: 0.625rem 0.75rem;
border: 1px solid #d1d5db;
border-radius: 6px;
font-size: 1rem;
transition: border-color 0.2s, box-shadow 0.2s;
}
input.text-input:focus {
outline: none;
border-color: #3b82f6;
box-shadow: 0 0 0 3px rgba(59, 130, 246, 0.1);
}
.text-input.disabled-style {
background-color: #f3f4f6;
color: #6b7280;
border-color: #d1d5db;
cursor: not-allowed;
}
.text-input.disabled-style::placeholder {
color: #9ca3af;
}
.text-input.disabled-style:focus {
border-color: #d1d5db;
box-shadow: none;
}
#sortTypeContainer, #autoSortContainer {
margin-bottom: 15px;
}
#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;
}