You can use the filterButtonVisible method to get or set whether the filter buttons are displayed. For example:
If you want to know whether any row is filtered, use the isFiltered method. You can also use the isRowFilteredOut method to determine whether the specified row is filtered out. For example:
You can use getFilterItems to get the filters (Conditions Array) for the specified column. You can also use getFilteredItems to get all filtered conditions. For example:
The filtered row can be sorted. Use sortColumn to sort the specified column in the specified order, and use getSortState to get the current sort state. For example:
If you don't want the filters, you can remove some filters or reset all filters. For example:
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 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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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:#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;
}
