自定义筛选

在设置行筛选之后,你也可以对其进行自定义。 这对于那些过滤工作方式的标准会变更的应用程序很友好,因为你只需要改变过滤的条件。

你可以调用 filterButtonVisible 方法来获取或者设置筛选按钮的显示情况. 例如: 如果你想知道是否有一些行被过滤,你可以调用 isFiltered 方法。你也可以调用 isRowFilteredOut 方法来决定是否过滤掉制定的行,调用 isColumnFiltered 方法来决定是否过滤掉指定的列。例如: 你可以调用 getFilterItems 方法来获取指定列上的筛选(条件数组)。你也可以调用 getFilteredItems 方法来获取所有的过滤条件。例如: 被过滤的行也可以被排序. 调用 sortColumn 方法来对指定的列以指定的顺序来进行排序,调用 getSortState 来获取当前的排序状态。例如: 如果你不想使用筛选,你可以删除一些筛选或者清除所有筛选。例如:
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); spread.suspendPaint(); initSpread(spread); spread.resumePaint(); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.setArray(1, 1, [ ["Number"],[1],[2],[3],[4],[5],[6],[7],[8] ]) sheet.setColumnWidth(2, 120); sheet.setArray(1, 2, [ ["Date"],['01/01/2017'],['02/01/2017'],['03/01/2017'],['04/01/2017'],['05/01/2017'],['06/01/2017'],['07/01/2017'],['08/01/2017'] ]) sheet.setArray(1, 3, [ ["String"],["Abby"],["Aimee"],["Alisa"],["Angelia"],["Anne"],["Bobe"],["Jack"],["Grace"] ]) var backColorArray = ['yellow','red','green','blue','orange','purple','pink','grey']; for(var i=0;i<backColorArray.length;i++){ sheet.getCell(2+i,4).backColor(backColorArray[i]); } _getElementById("Condition1").addEventListener('change',function () { var condition = _getElementById("Condition1").value; var type = _getElementById("optEnumType1"); setEnumType(condition, type); }); _getElementById("Condition2").addEventListener('change',function () { var condition = _getElementById("Condition2").value; var type = _getElementById("optEnumType2"); setEnumType(condition, type); }); function setEnumType(condition, type) { var data=[]; switch (condition) { case "0": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "1": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "2": data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain']; _appendChild(type,data); break; case "3": data = ['BackgroundColor','ForegroundColor']; _appendChild(type,data); break; case "4": data = ['Empty','NonEmpty','Error','NonError','Formula']; _appendChild(type,data); break; case "5": data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo']; _appendChild(type,data); break; case "6": data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear']; _appendChild(type,data); break; case "7": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "8": data = ['Top','Bottom']; _appendChild(type,data); break; default: data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; } } function getConditionBase(condition, type, value) { var sheet = spread.getActiveSheet(); var condtionbase; var formula; if ((value != null) && (value[0] == "=")) { formula = value; value = null; } else { formula = null; if (!isNaN(value)) { value = parseFloat(value); } } switch (condition) { case "0": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; case "1": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, { compareType: type, expected: value, formula: formula }); break; case "2": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, { compareType: type, expected: value, formula: formula }); break; case "3": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, { compareType: type, expected: value }); break; case "4": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, { customValueType: type, formula: formula }); break; case "5": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, { compareType: type, expected: value, formula: formula }); break; case "6": if (type < 10) { condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, { expected: type, }); } else if (type == 10) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromDay(value); } else if (type == 11) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromMonth(value); } else if (type == 12) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromQuarter(value); } else if (type == 13) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromWeek(value); } else { condtionbase = spreadNS.ConditionalFormatting.Condition.fromYear(value); } break; case "7": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, { compareType: type, expected: value, formula: formula }); break; case "8": var ranges = sheet.getSelections().slice(0); condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, { type: type, expected: value, ranges: ranges }); break; default: condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; } return condtionbase; } function getConditions() { var radio1 = _getElementById("rdoAND").checked; var radio2 = _getElementById("rdoOR").checked; var condition1 = _getElementById("Condition1").value; var condition2 = _getElementById("Condition2").value; var type1 = parseInt(_getElementById("optEnumType1").value); var type2 = parseInt(_getElementById("optEnumType2").value); var value1 = _getElementById("txtFormulas1").value; var value2 = _getElementById("txtFormulas2").value; var con1 = getConditionBase(condition1, type1, value1); var con2 = getConditionBase(condition2, type2, value2); var conditions; if (value2 != null || value2 != "" || value2 != undefined) { if (radio1) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.and, item1: con1, item2: con2 }); } else if (radio2) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.or, item1: con1, item2: con2 }); } else { conditions = con1; } } else { conditions = con1; } return conditions; } _getElementById("btnHideRowFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var sels = sheet.getSelections(); if (sels.length == 0) return; var sel = sels[0]; // set filter var _drf = new spreadNS.Filter.HideRowFilter(sel); sheet.rowFilter(_drf); var nc = getConditions(); nc.ignoreBlank(_getElementById('chkIgnoreBlank').checked); _drf.addFilterItem(sheet.getActiveColumnIndex(), nc); // filter _drf.filter((sel.col >= 0) ? sel.col : 0); sheet.invalidateLayout(); sheet.repaint(); }); _getElementById("btnClearFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); sheet.rowFilter(null); sheet.invalidateLayout(); sheet.repaint(); }); } function _getElementById(id){ return document.getElementById(id); } function _appendChild(type,data){ type.innerHTML=''; for(var i=0;i<data.length;i++){ var option = document.createElement('option'); var value = document.createAttribute('value'); value.nodeValue = i; option.setAttributeNode(value); option.innerHTML = data[i]; type.appendChild(option); } }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <p> Use these options to create a custom filter in Spread. Highlight some names in column D and click the “Set Filter” to create a filter that allows the user to select which rows to show based on those names.</p> <div class="option-row"> <label>Conditions:</label> <select id="Condition1"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>CompareType:</label> <select id="optEnumType1"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>Compare value or Formula:</label> <input id="txtFormulas1" type="text" /> </div> <div class="option-row"> <label></label> <input type="radio" value="And" name="relation" id="rdoAND" /> <label for="rdoAND" >And</label> <input type="radio" value="Or" name="relation" id="rdoOR" /> <label for="rdoOR">OR</label> </div> <div class="option-row"> <label>Conditions:</label> <select id="Condition2"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>CompareType:</label> <select id="optEnumType2"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>Compare value or Formula:</label> <input id="txtFormulas2" type="text" /> </div> <div class="option-row"> <label></label> <input type="checkbox" id="chkIgnoreBlank" /> <label for="chkIgnoreBlank">Ignore Blank</label> </div> <div class="option-row"> <input type="button" value="Set Filter" id="btnHideRowFilter" /> <input type="button" value="Clear Filter" id="btnClearFilter" /> </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; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } p{ padding:2px 10px; background-color:lavender; } input, select { width: 100%; padding: 4px 6px; box-sizing: border-box; } label { display:block; margin-bottom: 6px; } input[type="checkbox"], input[type="radio"] { display: inline-block; width: auto; } input[type="checkbox"]+label, input[type="radio"]+label { display: inline-block; } input[type="button"] { display: block; margin: 0 0 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }