Spreadsheet filters
This section provides in-depth information on customizing the data grid filters, including the various associated methods, events, and properties.
Operators
Documentation
Methods
Method
Description
setFilter(number, array)
Apply filters programmatically.setFilter(columnNumber: Number, values: String[])
getFilter(mixed)
Currently applied filters to a column or to all columns.getFilter(columnNumber?: Number)
openFilter(number)
Open the filter input.openFilter(columnNumber: Number)
closeFilter()
Close the filter input.closeFilter()
resetFilters()
Reset all filters.resetFilters()
showFilter(number)
Enable the filter icon for one or all columns.showFilter(columnNumber?: Number)
hideFilter(number)
Disable the filter icon for one or all columns.hideFilter(columnNumber?: Number)
resetFilters(mixed)
Reset the filters for one or all columns..resetFilters(columnNumber?: Number)
Related events
You can intercept, cancel, or modify the filter results using the
onbeforefilter event.
Events
Description
onbeforefilter
This method runs before the filter is applied. It returns an array of valid row numbers or false to return all rows.onbeforefilter(worksheet: Object, terms: Object, rowNumbers: Number[]) => Boolean | Number[] | void
onfilter
After the filter has been applied to the rows.onfilter(worksheet: Object, terms: String[], rowNumbers: Number[])
onopenfilter
Customize the items available when the filter editor is open.onopenfilter(worksheet: Object, column: number options: Object[]) => options | undefined
Initial Settings
Property
Description
filters: boolean
Start the spreadsheet with the filters enabled. Default: false
Enable the filter for individual columns
The filter property is available in the columns object for creating a new spreadsheet. Setting
filter: true enables the filter for the specified column.
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: data,
filters: false,
columns: [
{ type: 'text', title: 'Car', filter: true },
{ type: 'text' },
]
}]
});
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Columns
const columns = [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet columns={columns} filters={false} />
</Spreadsheet>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet :columns="columns" filters />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Data grid column definitions
const columns = [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
return {
columns,
license,
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: data,
filters: false,
columns: [
{ type: 'text', title: 'Car', filter: true },
{ type: 'text' },
]
}]
});
}
}
Translations
You can translate the filter controls by utilizing the command provided below.
// Translating the spreadsheet filter controls to French
jspreadsheet.setDictionary({
'Contains': 'Contient',
'Does not contain': 'Ne contient pas',
'Begins with': 'Commence par',
'Ends with': 'Se termine par',
'Equal': 'Égal à',
'Not equal': 'Pas égal à',
'Greater than': 'Plus grand que',
'Lower than': 'Moins que',
'Search': 'Recherche',
});
Examples
Interacting with the filters programmatically
Enable the filters on the initialization and apply or reset filters programmatically.
Apply ['Honda'] programmatically to the first worksheet, second column
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<div id="spreadsheet"></div>
<input type='button' value='Apply' onclick="apply()">
<input type='button' value='Reset' onclick="reset()">
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
const apply = function() {
worksheets[0].setFilter(1, ['Honda']);
}
const reset = function() {
worksheets[0].resetFilters();
}
const worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
['Jazz', 'Honda', '2019-02-12', true, '2000,00', '=E1*0.1', '#777700'],
['Civic', 'Honda', '2018-07-11', false, '4000,01', '=E2*0.1', '#007777'],
['Civic', 'Honda', '2018-07-12', true, '3200,01', '=E3*0.1', '#117717'],
['Picanto', 'Kia', '2018-07-12', false, '4000,00', '=E4*0.1', '#ffb74d'],
['Optima', 'Kia', '2020-01-12', false, '3000,00', '=E5*0.1', '#4db6ac'],
],
filters: true,
columns: [
{
type:'text',
title:'Car',
width:120
},
{
type: 'dropdown',
title:'Make',
width:180,
source:[
"Alfa Romeo",
"Audi",
"Bmw",
"Chevrolet",
"Chrystler",
"Dodge",
"Ferrari",
"Fiat",
"Ford",
"Honda",
"Hyundai",
"Jaguar",
"Jeep",
"Kia",
"Mazda",
"Mercedez-Benz",
"Mitsubish",
"Nissan",
"Peugeot",
"Porsche",
"Subaru",
"Suzuki",
"Toyota",
"Volkswagen"
]
},
{
type: 'calendar',
title:'Available',
width:120,
options:{ format:'DD/MM/YYYY' }
},
{
type: 'checkbox',
title:'Stock',
width:80
},
{
type: 'number',
title:'Price',
mask:'$ #.##0,00',
width:100,
decimal:','
},
{
type: 'text',
width:110,
title:'Commission',
truncate: 3,
},
{
title: 'Color',
type: 'color',
width:100,
render:'square',
},
]
}]
});
</script>
</html>
React code example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const data = [
['Jazz', 'Honda', '2019-02-12', true, '2000,00', '=E1*0.1', '#777700'],
['Civic', 'Honda', '2018-07-11', false, '4000,01', '=E2*0.1', '#007777'],
['Civic', 'Honda', '2018-07-12', true, '3200,01', '=E3*0.1', '#117717'],
['Picanto', 'Kia', '2018-07-12', false, '4000,00', '=E4*0.1', '#ffb74d'],
['Optima', 'Kia', '2020-01-12', false, '3000,00', '=E5*0.1', '#4db6ac'],
]
// Columns
const columns = [
{
type:'text',
title:'Car',
width:120
},
{
type: 'dropdown',
title:'Make',
width:180,
source:[
"Alfa Romeo",
"Audi",
"Bmw",
"Chevrolet",
"Chrystler",
"Dodge",
"Ferrari",
"Fiat",
"Ford",
"Honda",
"Hyundai",
"Jaguar",
"Jeep",
"Kia",
"Mazda",
"Mercedez-Benz",
"Mitsubish",
"Nissan",
"Peugeot",
"Porsche",
"Subaru",
"Suzuki",
"Toyota",
"Volkswagen"
]
},
{
type: 'calendar',
title:'Available',
width:120,
options:{ format:'DD/MM/YYYY' }
},
{
type: 'checkbox',
title:'Stock',
width:80
},
{
type: 'number',
title:'Price',
mask:'$ #.##0,00',
width:100,
decimal:','
},
{
type: 'text',
width:110,
title:'Commission',
truncate: 3,
},
{
title: 'Color',
type: 'color',
width:100,
render:'square',
},
]
// Render component
return (
<>
<Spreadsheet ref={spreadsheet} license={license}>
<Worksheet data={data} columns={columns} filters />
</Spreadsheet>
<input type='button' value='Apply' onClick={() => spreadsheet.current[0].setFilter(1, ['Honda'])}>
<input type='button' value='Reset' onClick={() => spreadsheet.current[0].resetFilters()}>
</>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet :data="data" :columns="columns"/>
</Spreadsheet>
<input type='button' value='Apply' @click="setFilter(1, ['Honda'])}">
<input type='button' value='Reset' @click="resetFilters()">
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
setFilter(column, filter) {
this.$refs.spreadsheet.current[0].setFilter(column, filter);
},
resetFilters() {
this.$refs.spreadsheet.current[0].resetFilters();
},
},
data() {
// Data
const data = [
['Jazz', 'Honda', '2019-02-12', true, '2000,00', '=E1*0.1', '#777700'],
['Civic', 'Honda', '2018-07-11', false, '4000,01', '=E2*0.1', '#007777'],
['Civic', 'Honda', '2018-07-12', true, '3200,01', '=E3*0.1', '#117717'],
['Picanto', 'Kia', '2018-07-12', false, '4000,00', '=E4*0.1', '#ffb74d'],
['Optima', 'Kia', '2020-01-12', false, '3000,00', '=E5*0.1', '#4db6ac'],
];
// Columns
const columns = [
{
type:'text',
title:'Car',
width:120
},
{
type: 'dropdown',
title:'Make',
width:180,
source:[
"Alfa Romeo",
"Audi",
"Bmw",
"Chevrolet",
"Chrystler",
"Dodge",
"Ferrari",
"Fiat",
"Ford",
"Honda",
"Hyundai",
"Jaguar",
"Jeep",
"Kia",
"Mazda",
"Mercedez-Benz",
"Mitsubish",
"Nissan",
"Peugeot",
"Porsche",
"Subaru",
"Suzuki",
"Toyota",
"Volkswagen"
]
},
{
type: 'calendar',
title:'Available',
width:120,
options:{ format:'DD/MM/YYYY' }
},
{
type: 'checkbox',
title:'Stock',
width:80
},
{
type: 'number',
title:'Price',
mask:'$ #.##0,00',
width:100,
decimal:','
},
{
type: 'text',
width:110,
title:'Commission',
truncate: 3,
},
{
title: 'Color',
type: 'color',
width:100,
render:'square',
},
];
return {
columns,
data,
license,
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `
<div #spreadsheet></div>
<input type='button' value='Apply' @click="this.worksheets[0].setFilter(1, ['Honda'])">
<input type='button' value='Reset' @click="this.worksheets[0].resetFilters()">
`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
['Jazz', 'Honda', '2019-02-12', true, '2000,00', '=E1*0.1', '#777700'],
['Civic', 'Honda', '2018-07-11', false, '4000,01', '=E2*0.1', '#007777'],
['Civic', 'Honda', '2018-07-12', true, '3200,01', '=E3*0.1', '#117717'],
['Picanto', 'Kia', '2018-07-12', false, '4000,00', '=E4*0.1', '#ffb74d'],
['Optima', 'Kia', '2020-01-12', false, '3000,00', '=E5*0.1', '#4db6ac'],
],
filters: true,
columns: [
{
type:'text',
title:'Car',
width:120
},
{
type: 'dropdown',
title:'Make',
width:180,
source:[
"Alfa Romeo",
"Audi",
"Bmw",
"Chevrolet",
"Chrystler",
"Dodge",
"Ferrari",
"Fiat",
"Ford",
"Honda",
"Hyundai",
"Jaguar",
"Jeep",
"Kia",
"Mazda",
"Mercedez-Benz",
"Mitsubish",
"Nissan",
"Peugeot",
"Porsche",
"Subaru",
"Suzuki",
"Toyota",
"Volkswagen"
]
},
{
type: 'calendar',
title:'Available',
width:120,
options:{ format:'DD/MM/YYYY' }
},
{
type: 'checkbox',
title:'Stock',
width:80
},
{
type: 'number',
title:'Price',
mask:'$ #.##0,00',
width:100,
decimal:','
},
{
type: 'text',
width:110,
title:'Commission',
truncate: 3,
},
{
title: 'Color',
type: 'color',
width:100,
render:'square',
},
]
}]
});
}
}
Enable filters for individual columns
It is possible to enable the filters at the column level. This means you can use the filters for one specific column only.
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<div id="spreadsheet-2"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet-2'), {
worksheets: [{
data: [
[ 'Jazz', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Picanto', 'Kia' ],
[ 'Optima', 'Kia' ],
],
filters: false,
columns: [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
}]
});
</script>
</html>
React code example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const data = [
[ 'Jazz', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Picanto', 'Kia' ],
[ 'Optima', 'Kia' ],
]
// Columns
const columns = [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
// Render component
return (
<Spreadsheet ref={spreadsheet} license={license}>
<Worksheet data={data} columns={columns} filters={false} />
</Spreadsheet>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet :data="data" :columns="columns"/>
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Data
const data = [
[ 'Jazz', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Picanto', 'Kia' ],
[ 'Optima', 'Kia' ],
]
// Columns
const columns = [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
return {
columns,
data,
license,
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[ 'Jazz', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Civic', 'Honda' ],
[ 'Picanto', 'Kia' ],
[ 'Optima', 'Kia' ],
],
filters: false,
columns: [
{ type: 'text', filter: true, width: '300px' },
{ type: 'text' },
]
}]
});
}
}
Customize the spreadsheet filter behavior
Custom filters can be created using the onbeforefilter event.
In this example, if "Canada" is present in the terms in the first column filter, always display all rows.
JavaScript example
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<div id="spreadsheet-3"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet-3'), {
worksheets: [{
data: [
['United States', 'Wholemeal', 'Yes', '2019-02-12'],
['Canada', 'Breakfast Cereals', 'Yes', '2019-03-01'],
['Canada', 'Grains', 'No', '2018-11-10'],
['Brazil', 'Pasta', 'Yes', '2019-01-12'],
],
defaultColWidth: '140px',
filters: true,
}],
onbeforefilter: function(worksheet, terms, results) {
// Show all rows if Canada is one of the options
if (terms[0] && terms[0].indexOf('Canada') >= 0) {
return false;
}
return results;
}
});
</script>
</html>
React code example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Data
const data = [
['United States', 'Wholemeal', 'Yes', '2019-02-12'],
['Canada', 'Breakfast Cereals', 'Yes', '2019-03-01'],
['Canada', 'Grains', 'No', '2018-11-10'],
['Brazil', 'Pasta', 'Yes', '2019-01-12'],
]
// Event
const onbeforefilter = (worksheet, terms, results) => {
// Show all rows if Canada is one of the options
if (terms[0] && terms[0].indexOf('Canada') >= 0) {
return false;
}
return results;
}
// Render component
return (
<Spreadsheet ref={spreadsheet} license={license} onbeforefilter={onbeforefilter}>
<Worksheet data={data} filters defaultColWidth="140px" />
</Spreadsheet>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license" :onbeforefilter="onbeforefilter">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
// Event
onbeforefilter(worksheet, terms, results) {
// Show all rows if Canada is one of the options
if (terms[0] && terms[0].indexOf('Canada') >= 0) {
return false;
}
return results;
}
},
data() {
// Data
const data = [
['United States', 'Wholemeal', 'Yes', '2019-02-12'],
['Canada', 'Breakfast Cereals', 'Yes', '2019-03-01'],
['Canada', 'Grains', 'No', '2018-11-10'],
['Brazil', 'Pasta', 'Yes', '2019-01-12'],
]
return {
data,
license,
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTJjNGU0MGFiODIzM2U0ZGNiNGY1MzJmZTU0NzUxZjMxZWVmMTFiZDFkMjQ3MjdkMmNmMTAyNmE2OGRiZTkzZjNkNjhjNDc3ZjMwYmFiMWJmOTg1OGFiNjY1YTg4NjNjYzU2MDE0YWFkYzdiZjQ1NDNlMzVkMDQxNDJiMmIxMGQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOakl6T1RjeE55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
['United States', 'Wholemeal', 'Yes', '2019-02-12'],
['Canada', 'Breakfast Cereals', 'Yes', '2019-03-01'],
['Canada', 'Grains', 'No', '2018-11-10'],
['Brazil', 'Pasta', 'Yes', '2019-01-12'],
],
defaultColWidth: '140px',
filters: true,
}],
onbeforefilter: function(worksheet, terms, results) {
// Show all rows if Canada is one of the options
if (terms[0] && terms[0].indexOf('Canada') >= 0) {
return false;
}
return results;
}
});
}
}
