透视表排序

SpreadJS 数据透视表支持数据排序。这个函数可以用来排序和组织数据。

概括 Pivot Table可以通过4种方式对字段进行排序: Sort By Field Item Name Sort By Value Sort By Custom Field Item Value Sort By Custom Callback SortType可以以任何方式设置。 API 接口 API 示例
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("季度 (date)", "季度 (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="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>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; }