<p>You can use the <strong>filterButtonVisible</strong> method to get or set whether the filter buttons are displayed. For example:</p>
<pre><code class="hljs">var range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.<span class="hljs-constructor">Range(-1, 0, -1, 2)</span>;
var rowFilter = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.<span class="hljs-constructor">HideRowFilter(<span class="hljs-params">range</span>)</span>;
sheet.row<span class="hljs-constructor">Filter(<span class="hljs-params">rowFilter</span>)</span>;
rowFilter.filter<span class="hljs-constructor">ButtonVisible(<span class="hljs-params">false</span>)</span>;
</code></pre>
<p>If you want to know whether any row is filtered, use the <strong>isFiltered</strong> method. You can also use the <strong>isRowFilteredOut</strong> method to determine whether the specified row is filtered out, Use the <strong>isColumnFiltered</strong> method to determine whether the specified column is filtered. For example:</p>
<pre><code class="hljs js language-js"> sheet.setValue(<span class="hljs-number">0</span>,<span class="hljs-number">0</span>,<span class="hljs-number">1</span>);
sheet.setValue(<span class="hljs-number">1</span>,<span class="hljs-number">0</span>,<span class="hljs-number">2</span>);
sheet.setValue(<span class="hljs-number">2</span>,<span class="hljs-number">0</span>,<span class="hljs-number">3</span>);
<span class="hljs-keyword">var</span> range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Range(<span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>);
sheet.rowFilter(<span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.HideRowFilter(range));
<span class="hljs-keyword">var</span> compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo;
<span class="hljs-keyword">var</span> condition = <span class="hljs-keyword">new</span> GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {<span class="hljs-attr">compareType</span>: compareType, <span class="hljs-attr">expected</span>: <span class="hljs-string">'3'</span>});
<span class="hljs-keyword">var</span> rowfilter = sheet.rowFilter();
rowfilter.addFilterItem(<span class="hljs-number">0</span>, condition);
rowfilter.filter(<span class="hljs-number">0</span>);
rowfilter.isFiltered(); <span class="hljs-comment">// true</span>
rowfilter.isColumnFiltered(<span class="hljs-number">0</span>); <span class="hljs-comment">// true</span>
rowfilter.isRowFilteredOut(<span class="hljs-number">2</span>); <span class="hljs-comment">// false</span>
</code></pre>
<p>You can use <strong>getFilterItems</strong> to get the filters (Conditions Array) for the specified column. You can also use <strong>getFilteredItems</strong> to get all filtered conditions. For example:</p>
<pre><code class="hljs js language-js"> rowfilter.getFilterItems(<span class="hljs-number">0</span>);
rowfilter.getFilteredItems();
</code></pre>
<p>The filtered row can be sorted. Use <strong>sortColumn</strong> to sort the specified column in the specified order, and use <strong>getSortState</strong> to get the current sort state. For example:</p>
<pre><code class="hljs js language-js"> rowfilter.SortColumns(<span class="hljs-number">0</span>, <span class="hljs-literal">true</span>); <span class="hljs-comment">// sort as ascending.</span>
rowfilter.getSortState() === GC.Spread.Sheets.SortState.ascending; <span class="hljs-comment">// true</span>
</code></pre>
<p>If you don't want the filters, you can remove some filters or clear all filters. For example:</p>
<pre><code class="hljs js language-js"> rowfilter.removeFilterItems(<span class="hljs-number">0</span>);
rowfilter.unfilter(<span class="hljs-number">0</span>);
rowfilter.reset();
</code></pre>
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 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$/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;
}