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:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<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" @change="onConditionChanged($event, 1)">
<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" @change="onConditionChanged($event, 2)">
<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" @click="applyFilter()" />
<input type="button" value="Clear Filter" id="btnClearFilter" @click="clearFilter()" />
</div>
</div>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
let spreadRef;
const spreadNS = GC.Spread.Sheets;
function getElementById(id) {
return document.getElementById(id);
}
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 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.toString();
option.setAttributeNode(attribute);
option.innerHTML = data[i];
element.appendChild(option);
}
}
const initSpread = (spread) => {
spreadRef = spread;
spread.suspendPaint();
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]);
}
spread.resumePaint();
};
const onConditionChanged = ($event, type) => {
var conditionType = getElementById("Condition" + type).value;
var element = getElementById("optEnumType" + type);
updateEnumType(conditionType, element);
};
const getCondition = (conditionType, compareType, value) => {
var sheet = spreadRef.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;
};
const 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;
};
const applyFilter = () => {
var sheet = spreadRef.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();
};
const clearFilter = () => {
var sheet = spreadRef.getActiveSheet();
sheet.rowFilter(null);
sheet.invalidateLayout();
sheet.repaint();
};
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);