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.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import { data } from './data';
let { salesData, outlineColumnData } = data;
const spreadNS = GC.Spread.Sheets;
function _getElementById(id) {
return document.getElementById(id);
}
export function AppFunc() {
let spread = null;
let filter = null;
let tableColumnsContainer = null;
let checkBoxes = null;
const initSpread = (currSpread) => {
spread = currSpread;
var sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.options.allowCellOverflow = true;
sheet.name("FilterDialog");
sheet.setArray(1, 1, salesData);
filter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(2, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
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];
initOutlineColumnFilter(sheet2);
sheet2.name("OutlineColumnFilter");
}
const setAllFilterButtonsVisible = (e, visible) => {
if (filter) {
filter.filterButtonVisible(visible);
checkBoxes.forEach(function (item) {
item.checked = visible;
});
}
}
const setMenuItem = (e, key) => {
var options = {};
options[key] = e.target.checked;
filter.filterDialogVisibleInfo(options);
}
const prepareFilterItems = (sheet, headers) => {
var items = [];
filter = sheet.rowFilter();
var 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>');
}
tableColumnsContainer = _getElementById("tableColumnsContainer");
tableColumnsContainer.innerHTML = items.join("");
var nodeList = tableColumnsContainer.querySelectorAll("input[type='checkbox']");
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);
}
});
}
}
const 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();
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
setAllFilterButtonsVisible={(e, visible) => { setAllFilterButtonsVisible(e, visible) }}
setMenuItem={(e, key) => { setMenuItem(e, key) }}
/>
</div>;
}
function Panel(props) {
return (
<div class="options-container">
<div class="option-group">
<input type="button" value="Show All" title="Show all filter buttons of the table" onClick={(e) => { props.setAllFilterButtonsVisible(e, true) }} />
<input type="button" value="Hide All" title="Hide all filter buttons of the table" onClick={(e) => { props.setAllFilterButtonsVisible(e, 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" defaultChecked={true} onClick={(e) => { props.setMenuItem(e, 'sortByValue') }} />ShowSortByValue</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e) => { props.setMenuItem(e, 'sortByColor') }} />ShowSortByColor</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e) => { props.setMenuItem(e, 'filterByColor') }} />ShowFilterByColor</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e) => { props.setMenuItem(e, 'filterByValue') }} />ShowFilterByValue</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e) => { props.setMenuItem(e, 'listFilterArea') }} />ShowListFilterArea</label></div>
</div>
</div>
</div>
)
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import { data } from './data';
let { salesData, outlineColumnData } = data;
const Component = React.Component;
const spreadNS = GC.Spread.Sheets;
function _getElementById(id) {
return document.getElementById(id);
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.filter = null;
this.tableColumnsContainer = null;
this.checkBoxes = null;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
setAllFilterButtonsVisible={(e, visible)=>{this.setAllFilterButtonsVisible(e, visible)}}
setMenuItem={(e, key)=>{this.setMenuItem(e, key)}}
/>
</div>;
}
initSpread(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 = _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();
}
}
class Panel extends Component{
constructor(props){
super(props);
}
render(){
return(
<div class="options-container">
<div class="option-group">
<input type="button" value="Show All" title="Show all filter buttons of the table" onClick={(e)=>{this.props.setAllFilterButtonsVisible(e, true)}} />
<input type="button" value="Hide All" title="Hide all filter buttons of the table" onClick={(e)=>{this.props.setAllFilterButtonsVisible(e, 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" defaultChecked={true} onClick={(e)=>{this.props.setMenuItem(e, 'sortByValue')}} />ShowSortByValue</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e)=>{this.props.setMenuItem(e, 'sortByColor')}} />ShowSortByColor</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e)=>{this.props.setMenuItem(e, 'filterByColor')}} />ShowFilterByColor</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e)=>{this.props.setMenuItem(e, 'filterByValue')}} />ShowFilterByValue</label></div>
<div><label><input type="checkbox" defaultChecked={true} onClick={(e)=>{this.props.setMenuItem(e, 'listFilterArea')}} />ShowListFilterArea</label></div>
</div>
</div>
</div>
)
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></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-group {
margin-bottom: 6px;
}
.option-group input[type="checkbox"] {
margin-right: 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
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}
]
};
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
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-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.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: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
