Use the Filter or Table object's filterButtonVisible method to get or set whether the filter buttons are displayed. This allows you to control what you want to allow users to filter without completely getting rid of filters, such as when you have user identification in your application, and only certain users can filter specific columns or rows.
The filterButtonVisible method's get and set data depends on the following arguments:
No arguments: Get whether filter buttons are displayed. True if one is visible; otherwise, false.
1 argument: If it's a number, then use it as a column index that returns whether the corresponding column's filter button is displayed. If it's a boolean, set all filter buttons to the specified value.
2 arguments: The first argument is the column index and the second one is the value. Sets whether the corresponding column's filter button is displayed.
The filter dialog container contains sortByValue, sortByColor, filterByColor, filterByValue and listFilterArea. The filterDialogVisibleInfo method is used to get or set whether these options are displayed.
Enhanced functionality: Adding Current Selection To Filter
Let's say you've filtered a selection once, and part of the selection has been filtered out. If you check "add current selection to filter" during your second filter, your second filter will be displayed at the same time as your first filter, instead of clearing the first filter to show only the second filter.
The filter dialog supports resizing by clicking and dragging the indicator in the lower right corner.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="option-group">
<input type="button" value="Show All" title="Show all filter buttons of the table" @click="setAllFilterButtonsVisible($event, true)" />
<input type="button" value="Hide All" title="Hide all filter buttons of the table" @click="setAllFilterButtonsVisible($event, false)" />
</div>
<div class="option-group">
<h4>Show filter buttons:</h4>
<div id="tableColumnsContainer"></div>
<h4>Filter dialog visible info:</h4>
<div>
<div><label><input type="checkbox" checked @change="setMenuItem($event, 'sortByValue')" />ShowSortByValue</label></div>
<div><label><input type="checkbox" checked @change="setMenuItem($event, 'sortByColor')" />ShowSortByColor</label></div>
<div><label><input type="checkbox" checked @change="setMenuItem($event, 'filterByColor')" />ShowFilterByColor</label></div>
<div><label><input type="checkbox" checked @change="setMenuItem($event, 'filterByValue')" />ShowFilterByValue</label></div>
<div><label><input type="checkbox" checked @change="setMenuItem($event, 'listFilterArea')" />ShowListFilterArea</label></div>
</div>
</div>
</div>
</div>
</template>
<script>
import Vue from 'vue';
import '@mescius/spread-sheets-vue'
import GC from '@mescius/spread-sheets';
import './styles.css';
import { data } from './data';
let { salesData, outlineColumnData } = data;
const spreadNS = GC.Spread.Sheets;
let App = Vue.extend({
name: "app",
methods:{
initSpread: function (spread) {
this.spread = spread;
var sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.options.allowCellOverflow = true;
sheet.name("FilterDialog");
sheet.setArray(1, 1, salesData);
var filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(2, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
this.filter = filter;
this.prepareFilterItems(sheet, salesData[0]);
sheet.defaults.rowHeight = 28;
sheet.setColumnWidth(1, 110);
sheet.setColumnWidth(2, 80);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 80);
sheet.setColumnWidth(5, 80);
sheet.setColumnWidth(6, 80);
sheet.getRange(2, 2, 10, 1).formatter("yyyy/mm/dd");
var ComparisonOperators = spreadNS.ConditionalFormatting.ComparisonOperators;
var equalsTo = ComparisonOperators.equalsTo;
var range = sheet.getRange(1, 1, 11, 6);
range.setBorder(new spreadNS.LineBorder("gray", spreadNS.LineStyle.thin), {all: true});
var ranges = [new spreadNS.Range(2, 3, 10, 1)];
var style1 = new spreadNS.Style();
style1.foreColor = "Accent 2";
var rule1 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, equalsTo, "West", "");
sheet.conditionalFormats.addRule(rule1);
var style2 = new spreadNS.Style();
style2.foreColor = "Accent 3";
var rule2 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, equalsTo, "East", "");
sheet.conditionalFormats.addRule(rule2);
var style3 = new spreadNS.Style();
style3.foreColor = "Accent 6";
var rule3 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, equalsTo, "North", "");
sheet.conditionalFormats.addRule(rule3);
var style4 = new spreadNS.Style();
style4.foreColor = "Accent 1";
var rule4 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style4, equalsTo, "South", "");
sheet.conditionalFormats.addRule(rule4);
ranges = [new spreadNS.Range(2, 2, 10, 1)];
style1 = new spreadNS.Style();
style1.backColor = "rgb(241, 135, 102)";
rule1 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style1, ComparisonOperators.lessThan, "1990/01/01", "");
sheet.conditionalFormats.addRule(rule1);
style2 = new spreadNS.Style();
style2.backColor = "lightGreen";
rule2 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style2, ComparisonOperators.between, "1990/01/01", "2000/01/01");
sheet.conditionalFormats.addRule(rule2);
style3 = new spreadNS.Style();
style3.backColor = "deepSkyBlue";
rule3 = new spreadNS.ConditionalFormatting.NormalConditionRule(1, ranges, style3, ComparisonOperators.greaterThan, "2000/01/01", "");
sheet.conditionalFormats.addRule(rule3);
sheet.resumePaint();
var sheet2 = spread.sheets[1];
this.initOutlineColumnFilter(sheet2);
sheet2.name("OutlineColumnFilter");
},
setAllFilterButtonsVisible (e, visible) {
var filter = this.filter;
if (filter) {
filter.filterButtonVisible(visible);
this.checkBoxes.forEach(function(item) {
item.checked = visible;
});
}
},
setMenuItem (e, key) {
var options = {};
options[key] = e.target.checked;
this.filter.filterDialogVisibleInfo(options);
},
prepareFilterItems(sheet, headers) {
var items = [];
var filter = sheet.rowFilter(),
range = filter.range,
startColumn = range.col;
for (var c = 0, length = headers.length; c < length; c++) {
var name = headers[c];
items.push('<div><label><input type="checkbox" checked data-index="' + (startColumn + c) + '">'+ name + '</label></div>');
}
this.tableColumnsContainer = document.getElementById("tableColumnsContainer");
this.tableColumnsContainer.innerHTML = items.join("");
var nodeList = this.tableColumnsContainer.querySelectorAll("input[type='checkbox']");
this.checkBoxes = [];
var checkBoxes = this.checkBoxes;
for (var i = 0, count = nodeList.length; i < count; i++) {
var element = nodeList[i];
checkBoxes.push(element);
element.addEventListener('change', function () {
var index = +this.dataset.index; // +this.getAttribute("data-index");
if (filter) {
filter.filterButtonVisible(index, this.checked);
}
});
}
},
initOutlineColumnFilter(sheet) {
sheet.setColumnWidth(2, 120);
sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(-1, 0, -1, 1)));
sheet.suspendPaint();
sheet.setColumnWidth(0, 200);
sheet.setRowCount(12);
sheet.outlineColumn.options({
columnIndex: 0,
showIndicator: true,
});
var sourceData = outlineColumnData;
sheet.setDataSource(sourceData);
sheet.bindColumn(0, "name");
sheet.setColumnCount(3);
sheet.setColumnWidth(0, 300);
for (var r = 0; r < sourceData.length; r++) {
var level = sourceData[r].level;
sheet.getCell(r, 0).textIndent(level);
}
sheet.showRowOutline(false);
sheet.outlineColumn.refresh();
sheet.resumePaint();
}
}
});
new Vue({ render: h => h(App) }).$mount('#app');
</script>
<!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/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/en/lib/vue/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
export const data = {
salesData: [
["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"],
["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26],
["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99],
["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141],
["Erich", new Date("1994/05/23"), "West", 410, 0.12, 49.2],
["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120],
["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135],
["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110],
["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2],
["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76],
["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35]
],
outlineColumnData: [
{name: 'Preface', chapter: '1', page: 1, level: 0},
{name: 'Java SE5 and SE6', chapter: '1.1', page: 2, level: 1},
{name: 'Java SE6', chapter: '1.1.1', page: 2, level: 2},
{name: 'The 4th edition', chapter: '1.2', page: 2, level: 1},
{name: 'Changes', chapter: '1.2.1', page: 3, level: 2},
{name: 'Note on the cover design', chapter: '1.3', page: 4, level: 1},
{name: 'Acknowledgements', chapter: '1.4', page: 4, level: 1},
{name: 'Introduction', chapter: '2', page: 9, level: 0},
{name: 'Prerequisites', chapter: '2.1', page: 9, level: 1},
{name: 'Learning Java', chapter: '2.2', page: 10, level: 1},
{name: 'Goals', chapter: '2.3', page: 10, level: 1},
{name: 'Teaching from this book', chapter: '2.4', page: 11, level: 1},
{name: 'JDK HTML documentation', chapter: '2.5', page: 11, level: 1},
{name: 'Exercises', chapter: '2.6', page: 12, level: 1},
{name: 'Foundations for Java', chapter: '2.7', page: 12, level: 1},
{name: 'Source code', chapter: '2.8', page: 12, level: 1},
{name: 'Coding standards', chapter: '2.8.1', page: 14, level: 2},
{name: 'Errors', chapter: '2.9', page: 14, level: 1},
{name: 'Introduction to Objects', chapter: '3', page: 15, level: 0},
{name: 'The progress of abstraction', chapter: '3.1', page: 15, level: 1},
{name: 'An object has an interface', chapter: '3.2', page: 17, level: 1},
{name: 'An object provides services', chapter: '3.3', page: 18, level: 1},
{name: 'The hidden implementation', chapter: '3.4', page: 19, level: 1},
{name: 'Reusing the implementation', chapter: '3.5', page: 20, level: 1},
{name: 'Inheritance', chapter: '3.6', page: 21, level: 1},
{name: 'Is-a vs. is-like-a relationships', chapter: '3.6.1', page: 24, level: 2},
{name: 'Interchangeable objects with polymorphism', chapter: '3.7', page: 25, level: 1},
{name: 'The singly rooted hierarchy', chapter: '3.8', page: 28, level: 1},
{name: 'Containers', chapter: '3.9', page: 28, level: 1},
{name: 'Parameterized types (Generics)', chapter: '3.10', page: 29, level: 1},
{name: 'Object creation & lifetime', chapter: '3.11', page: 30, level: 1},
{name: 'Exception handling: dealing with errors', chapter: '3.12', page: 31, level: 1},
{name: 'Concurrent programming', chapter: '3.13', page: 32, level: 1},
{name: 'Java and the Internet', chapter: '3.14', page: 33, level: 1},
{name: 'What is the Web?', chapter: '3.14.1', page: 33, level: 2},
{name: 'Client-side programming', chapter: '3.14.2', page: 34, level: 2},
{name: 'Server-side programming', chapter: '3.14.3', page: 38, level: 2}
]
};
.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-group {
margin-bottom: 6px;
}
.option-group input[type="checkbox"] {
margin-right: 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
meta: {
'*.css': { loader: 'css' },
'*.vue': { loader: 'vue-loader' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'jszip': 'npm:jszip/dist/jszip.js',
'css': 'npm:systemjs-plugin-css/css.js',
'vue': 'npm:vue/dist/vue.min.js',
'vue-loader': 'npm:systemjs-vue-browser/index.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'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'js'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
}
}
});
})(this);
