你可以调用 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"],[new Date('01/01/2017')],[new Date('02/01/2017')],[new Date('03/01/2017')],[new Date('04/01/2017')],[new Date('05/01/2017')],[new Date('06/01/2017')],[new Date('07/01/2017')],[new Date('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 conditionType = _getElementById("Condition1").value;
var element = _getElementById("optEnumType1");
updateEnumType(conditionType, element);
});
_getElementById("Condition2").addEventListener('change',function () {
var condition = _getElementById("Condition2").value;
var element = _getElementById("optEnumType2");
updateEnumType(condition, element);
});
function updateEnumType(conditionType, element) {
var data=[];
switch (conditionType) {
case "2":
data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain'];
updateEnumTypeList(element, data);
break;
case "3":
data = ['BackgroundColor','ForegroundColor'];
updateEnumTypeList(element, data);
break;
case "4":
data = ['Empty','NonEmpty','Error','NonError','Formula'];
updateEnumTypeList(element, data);
break;
case "5":
data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo'];
updateEnumTypeList(element, data);
break;
case "6":
data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear'];
updateEnumTypeList(element, data);
break;
case "8":
data = ['Top','Bottom'];
updateEnumTypeList(element, data);
break;
default: // case "0", "1", "7" same items
data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo'];
updateEnumTypeList(element, data);
break;
}
}
function getCondition(conditionType, compareType, value) {
var sheet = spread.getActiveSheet();
var condition;
var formula;
if ((value != null) && (value[0] == "=")) {
formula = value;
value = null;
}
else {
formula = null;
if (!isNaN(value)) {
value = parseFloat(value);
}
}
switch (conditionType) {
case "1":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "2":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "3":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, {
compareType: compareType,
expected: value
});
break;
case "4":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, {
customValueType: compareType,
formula: formula
});
break;
case "5":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "6":
if (compareType < 10) {
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, {
expected: compareType,
});
}
else if (compareType == 10) {
condition = spreadNS.ConditionalFormatting.Condition.fromDay(value);
} else if (compareType == 11) {
condition = spreadNS.ConditionalFormatting.Condition.fromMonth(value);
} else if (compareType == 12) {
condition = spreadNS.ConditionalFormatting.Condition.fromQuarter(value);
} else if (compareType == 13) {
condition = spreadNS.ConditionalFormatting.Condition.fromWeek(value);
} else {
condition = spreadNS.ConditionalFormatting.Condition.fromYear(value);
}
break;
case "7":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "8":
var ranges = sheet.getSelections().slice(0);
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, {
type: compareType,
expected: value,
ranges: ranges
});
break;
default: // case "0" same
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
}
return condition;
}
function getConditions() {
var radio1 = _getElementById("rdoAND").checked;
var radio2 = _getElementById("rdoOR").checked;
var conditionType1 = _getElementById("Condition1").value;
var conditionType2 = _getElementById("Condition2").value;
var compareType1 = parseInt(_getElementById("optEnumType1").value);
var compareType2 = parseInt(_getElementById("optEnumType2").value);
var value1 = _getElementById("txtFormulas1").value;
var value2 = _getElementById("txtFormulas2").value;
var con1 = getCondition(conditionType1, compareType1, value1);
var con2 = getCondition(conditionType2, compareType2, 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 selections = sheet.getSelections();
if (selections.length == 0) return;
var selection = selections[0];
// set filter
var hideRowFilter = new spreadNS.Filter.HideRowFilter(selection);
sheet.rowFilter(hideRowFilter);
var conditions = getConditions();
conditions.ignoreBlank(_getElementById('chkIgnoreBlank').checked);
hideRowFilter.addFilterItem(sheet.getActiveColumnIndex(), conditions);
// filter
hideRowFilter.filter((selection.col >= 0) ? selection.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 updateEnumTypeList(element, data){
element.innerHTML='';
for(var i=0;i<data.length;i++){
var option = document.createElement('option');
var attribute = document.createAttribute('value');
attribute.nodeValue = i;
option.setAttributeNode(attribute);
option.innerHTML = data[i];
element.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-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-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:#F4F8EB;
}
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;
}
