PivotTable Filter&Sort

SpreadJS PivotTables support Label Filters, Value Filters, and Sorting. These functions can be used to filter or sort data. Different filters can be set to obtain the required data.

You can set different filters to get the data that you need. The user can set the filter as follows: You can use the Label Filter as follows: You can use the Value Filter as follows: SpreadJS PivotTable supports five conditions: conType: Used to describe the type of condition operator: Used to describe the operate type of filter; setting up an operator corresponds to the type of conType SpreadJS PivotTables support two filters, and users can set filters using the following two data types, for example: SpreadJS PivotTable supports two sort types, Users can set different sort types to get the data The PivotTable supports A to Z ordering and Z to A ordering
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); let pivotTable = addPivotTable(sheet1, tableName); bindEvent(pivotTable,spread); spread.resumePaint(); } 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); pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; pivotTable.group(groupInfo); pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); sheet.resumePaint(); pivotTable.autoFitColumn(); return pivotTable; } function _isNullOrUndefined (obj) { return obj === null || obj === undefined; } function bindEvent(pivotTable,spread) { document.getElementById("set-label-filter").addEventListener("click", function (e) { spread.suspendPaint(); let labelType, labelFilterValue1, labelFilterValue2; let labelNode = document.getElementById("labelFilter"); labelType = labelNode.selectedIndex; if (!_isNullOrUndefined(labelType)) { labelFilterValue1 = document.getElementById("label-val1").value; labelFilterValue2 = document.getElementById("label-val2").value; if (labelType < 13) { labelFilterValue2 = null; } pivotTable.labelFilter("Salesperson", { condition: { conType: GC.Pivot.PivotConditionType.caption, val: [labelFilterValue1, labelFilterValue2], operator: labelType} }); } else { pivotTable.labelFilter("Salesperson", null); } spread.resumePaint(); }); document.getElementById("clear-label-filter").addEventListener("click", function (e) { spread.suspendPaint(); pivotTable.labelFilter("Salesperson", null); document.getElementById("labelFilter").selectedIndex = 0; document.getElementById("label-val1").value = null; document.getElementById("label-val2").value = null; spread.resumePaint(); }); document.getElementById("clear-value-filter").addEventListener("click", function (e) { spread.suspendPaint(); pivotTable.valueFilter("Salesperson", null); document.getElementById("valueFilter").selectedIndex = 0; document.getElementById("value-val1").value = null; document.getElementById("value-val2").value = null; spread.resumePaint(); }); document.getElementById("set-value-filter").addEventListener("click", function (e) { spread.suspendPaint(); let valueType, valueFilterValue1, valueFilterValue2; let valueNode = document.getElementById("valueFilter"); valueType = valueNode.selectedIndex; if (!_isNullOrUndefined(valueType)) { valueFilterValue1 = document.getElementById("value-val1").value; valueFilterValue2 = document.getElementById("value-val2").value; if (valueType < 7) { valueFilterValue2 = null; } pivotTable.valueFilter("Salesperson", { condition: { conType: GC.Pivot.PivotConditionType.value, val: [valueFilterValue1, valueFilterValue2], operator: valueType }, conditionByName: "Quantity" }); } else { pivotTable.valueFilter("Salesperson", null); } spread.resumePaint(); }); document.getElementById("sort-type").addEventListener("click", function (e) { spread.suspendPaint(); if (e.target.classList.contains("sort-asc")) { pivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc }); } else { pivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.desc }); } spread.resumePaint(); }); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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="whole-field">The following operations all work on <b>Salesperson</b> Field:</div> <div>Label Filter:</div> <div class="pivottable-filter label-filter"> <select name="labelFilter" id="labelFilter" class="pivot-filter"> <option value="equalsTo" selected>equalsTo</option> <option value="notEqualsTo">notEqualsTo</option> <option value="beginsWith">beginsWith</option> <option value="doesNotBeginWith">doesNotBeginWith</option> <option value="endsWith">endsWith</option> <option value="doesNotEndWith">doesNotEndWith</option> <option value="contains">contains</option> <option value="doesNotContain">doesNotContain</option> <option value="greaterThan">greaterThan</option> <option value="greaterThanOrEqualsTo">greaterThanOrEqualsTo</option> <option value="lessThan">lessThan</option> <option value="lessThanOrEqualsTo">lessThanOrEqualsTo</option> <option value="between">between</option> <option value="notBetween">notBetween</option> </select> <input type="text" class="label-filter-input filter-input" id="label-val1" placeholder = "value1"> <input type="text" class="label-filter-input filter-input" id="label-val2" placeholder = "value2"> </div> <input type="button" class="set-label-filter set-filter" id="set-label-filter" value="Set Label Filter"> <br /> <input type="button" class="set-label-filter set-filter" id="clear-label-filter" value="Clear Label Filter"> <br /> <hr /> <div class="value-filter-label">Value Filter:</div> <div class="pivottable-filter value-filter"> <select name="valueFilter" id="valueFilter" class="pivot-filter"> <option value="equalsTo" selected>equalsTo</option> <option value="notEqualsTo">notEqualsTo</option> <option value="greaterThan">greaterThan</option> <option value="greaterThanOrEqualsTo">greaterThanOrEqualsTo</option> <option value="lessThan">lessThan</option> <option value="lessThanOrEqualsTo">lessThanOrEqualsTo</option> <option value="between">between</option> <option value="notBetween">notBetween</option> </select> <input type="text" class="value-filter-input filter-input" id="value-val1" placeholder = "value1"> <input type="text" class="value-filter-input filter-input" id="value-val2" placeholder = "value2"> </div> <input type="button" class="set-value-filter set-filter" id="set-value-filter" value="Set Value Filter"> <br /> <input type="button" class="set-value-filter set-filter" id="clear-value-filter" value="Clear Value Filter"> <br /> <hr /> <div class="sort-type" id="sort-type"> <input type="button" class="sort-asc sort" value="Sort A To Z"> <input type="button" class="sort-desc sort" value="Sort Z To A"> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .pivottable-filter{ height: 100px; } .pivot-filter{ width: 200px; height: 25px; display: block; margin-bottom: 10px; float: left; } .filter-input{ width: 200px; height: 20px; display: block; /* margin-left: 15px; */ margin-top: 10px; } .set-filter{ width: 200px; margin-top: 20px; /* float: right; */ } .value-filter-label{ margin-top: 25px; } .sort-type{ width: 200px; margin-top: 25px; display: block; } .sort{ width: 200px; margin-top: 10px; display: block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .whole-field{ margin-bottom: 10px; }