Filters
Jspreadsheet offers Excel-like filtering capabilities for columns, cell ranges, and tables. This section covers the configuration options, methods, events, and properties for implementing powerful data grid filtering.
Documentation
Version 12 introduces updated method signatures that support table-based filter management, a new feature for enhanced filtering capabilities.
Methods
| Method | Description |
|---|---|
setFilter | Applies filters programmatically.setFilter(col: Number, row: Number | null, values: String[]) : void |
getFilter | Returns the currently applied filters for a specific column or all columns.getFilter(col?: Number, row?: Number) : Array | Set |
openFilter | Opens the filter input.openFilter(column: Number, row?: Number) : void |
closeFilter | Closes the filter input.closeFilter(update?: boolean) : void |
resetFilters | Resets the filters for one or all columns.resetFilters(column?: Number, row?: Number) : void |
showFilter | Enables the filter icon for one or all columns.showFilter(columnOrCellRange?: Number|String) : void |
hideFilter | Disables the column or range filters.hideFilter() : void |
Related Events
Use the available events to intercept, cancel, or modify filter operations and results.
| Events | Description |
|---|---|
onbeforefilter | This method is executed before the filter is applied. It can return an array of valid row numbers or false to cancel the event. If no return or undefined is returned, the event will proceed without any modifications.onbeforefilter(worksheet: Object, terms: Object, rowNumbers: Number[]) : Boolean | Number[] | void |
onfilter | Called after the filter has been applied.onfilter(worksheet: Object, terms: String[], rowNumbers: Number[]) |
onopenfilter | Allows customization of filter editor options upon opening.onopenfilter(worksheet: Object, column: Number, options: Object[]) : Object[] | Promise | void |
Initial Settings
| Property | Description |
|---|---|
filters: boolean | string | Start the spreadsheet with the filters enabled. Default: false |
Translations
Filter control text can be translated using the dictionary system, as shown 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', 'No matches': 'Pas de correspondance', 'Ok': 'Ok', 'Cancel': 'Annuler', }); Examples
Column Filters Methods
Initialize column filters during setup, then apply or reset them programmatically as needed. This example applies a filter for 'Honda' to the second column on the first worksheet.
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <p><input type='button' value='Apply' id="btn1" /> <input type='button' value='Reset' id="btn2" /></p> <script> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); 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', // Start the data grid with the following filters applied for this column filters: ['Civic','Picanto'], }, { type: 'dropdown', title:'Make', 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', options: { format:'DD/MM/YYYY' } }, { type: 'checkbox', title: 'Stock', }, { type: 'number', title:'Price', mask: '$ #.##0,00' }, { type: 'text', title:'Commission', mask: '0,00' }, { title: 'Color', type: 'color', render:'square', align: 'left' }, ] }] }); document.getElementById("btn1").onclick = apply document.getElementById("btn2").onclick = reset </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); 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', // Start the data grid with the following filters applied for this column filters: ['Civic','Picanto'], }, { type: 'dropdown', title:'Make', 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', options: { format:'DD/MM/YYYY' } }, { type: 'checkbox', title: 'Stock', }, { type: 'number', title:'Price', mask: '$ #.##0,00' }, { type: 'text', title:'Commission', mask: '0,00' }, { title: 'Color', type: 'color', render:'square', align: 'left' }, ] // Render component return ( <> <Spreadsheet ref={spreadsheet}> <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()}/> </> ); } <template> <Spreadsheet ref="spreadsheet"> <Worksheet :data="data" :columns="columns" :filters="true" /> </Spreadsheet> <input type='button' value='Apply' @click="setFilter(1, ['Honda'])"> <input type='button' value='Reset' @click="resetFilters()"> </template> <script> import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); 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', // Start the data grid with the following filters applied for this column filters: ['Civic','Picanto'], }, { type: 'dropdown', title:'Make', 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', options: { format:'DD/MM/YYYY' } }, { type: 'checkbox', title: 'Stock', }, { type: 'number', title:'Price', mask: '$ #.##0,00' }, { type: 'text', title:'Commission', mask: '0,00' }, { title: 'Color', type: 'color', render:'square', align: 'left' }, ]; return { columns, data }; } } </script> import { Component, ViewChild, ElementRef, AfterViewInit } from '@angular/core'; import jspreadsheet from 'jspreadsheet'; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); @Component({ selector: 'app-root', template: ` <div #spreadsheet></div> <input type='button' value='Apply' (click)="applyFilter()"> <input type='button' value='Reset' (click)="resetFilters()"> `, }) export class AppComponent implements AfterViewInit { @ViewChild('spreadsheet') spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Methods to handle button clicks applyFilter() { if (this.worksheets && this.worksheets[0]) { this.worksheets[0].setFilter(1, ['Honda']); } } resetFilters() { if (this.worksheets && this.worksheets[0]) { this.worksheets[0].resetFilters(); } } // Create a new data grid ngAfterViewInit() { // 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', // Start the data grid with the following filters applied for this column filters: ['Civic', 'Picanto'], }, { type: 'dropdown', title: 'Make', 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', options: { format: 'DD/MM/YYYY' }, }, { type: 'checkbox', title: 'Stock', }, { type: 'number', title: 'Price', mask: '$ #.##0,00', }, { type: 'text', title: 'Commission', mask: '0,00' }, { title: 'Color', type: 'color', render: 'square', align: 'left' }, ], }, ], }); } } Cell Range filter
You can enable filtering for a specific cell range, as shown in the example below.
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <script> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ [ '', '' ], [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Picanto', 'Kia' ], [ 'Optima', 'Kia' ], ], filters: 'A1:B4', minDimensions: [6,6], }] }); </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [ [ '', '' ], [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Picanto', 'Kia' ], [ 'Optima', 'Kia' ], ] // Render component return ( <Spreadsheet ref={spreadsheet}> <Worksheet data={data} filters={"A1:B4"} /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet"> <Worksheet :data="data" filters="A1:B4" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); export default { components: { Spreadsheet, Worksheet, }, data() { // Data const data = [ [ '', '' ], [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Picanto', 'Kia' ], [ 'Optima', 'Kia' ], ] return { data }; } } </script> import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); @Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent implements AfterViewInit { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngAfterViewInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ data: [ [ '', '' ], [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Civic', 'Honda' ], [ 'Picanto', 'Kia' ], [ 'Optima', 'Kia' ], ], filters: 'A1:B4', minDimensions: [6,6], }] }); } } Custom Filter Behavior
Use the onbeforefilter event to develop custom filtering logic. For instance, this example cancels the filtering process if "Canada" is among the selected options.
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <script> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { 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> import React, { useRef } from "react"; import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); 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} onbeforefilter={onbeforefilter}> <Worksheet data={data} filters={true} defaultColWidth="140px" /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet" :onbeforefilter="onbeforefilter"> <Worksheet :data="data" :filters="true" defaultColWidth="140px" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); 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 }; } } </script> import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); @Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent implements AfterViewInit { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngAfterViewInit() { // 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; } }); } } Customizing Filter Options
This example uses the onopenfilter event to remove duplicate case-sensitive values from filter options, demonstrating Jspreadsheet's flexibility in customizing filters.
<html> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v6/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <script> // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'CIVIC', 'Honda' ], ], filters: true, columns: [ { type: 'text', width: '300px' }, { type: 'text' }, ] }], onopenfilter: function(worksheet, column, filters) { let unique = new Map; let newFilters = []; filters.forEach(function(item) { if (typeof(item.k) !== 'undefined') { let key = item.k.toLowerCase(); if (! unique.get(key)) { unique.set(key, true); newFilters.push(item); } } else { newFilters.push(item); } }); return newFilters; } }); </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [ [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'CIVIC', 'Honda' ], ] // Columns const columns = [ { type: 'text', width: '300px' }, { type: 'text' }, ] // Event const onOpenFilter = function(worksheet, column, filters) { let unique = new Map; let newFilters = []; filters.forEach(function(item) { if (typeof(item.k) !== 'undefined') { let key = item.k.toLowerCase(); if (! unique.get(key)) { unique.set(key, true); newFilters.push(item); } } else { newFilters.push(item); } }); return newFilters; } // Render component return ( <Spreadsheet ref={spreadsheet} onopenfilter={onOpenFilter}> <Worksheet data={data} columns={columns} filters={true} /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet" :onopenfilter="onOpenFilter"> <Worksheet :data="data" :columns="columns" :filters="true" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); export default { components: { Spreadsheet, Worksheet, }, methods: { // Event onOpenFilter(worksheet, column, filters) { let unique = new Map; let newFilters = []; filters.forEach(function(item) { if (typeof(item.k) !== 'undefined') { let key = item.k.toLowerCase(); if (! unique.get(key)) { unique.set(key, true); newFilters.push(item); } } else { newFilters.push(item); } }); return newFilters; } }, data() { // Data const data = [ [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'CIVIC', 'Honda' ], ] const columns = [ { type: 'text', width: '300px' }, { type: 'text' }, ] return { data, columns, }; } } </script> import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('MGViY2EzZTRhMTkyZDMwZmUxMTI4NzFlYTYxMGYyMmQzNDNjZGFkMDQxYmI1NjQxODVmYTcwNDRkNDE4ZGM4YWUzMmNiY2ZkY2Y5OWI0OTY2OTljODFkNTRhZDNhODliMzg1ZjA3NzlhNGM4YTRhMjVlY2FmMTIxN2RmYTVlNTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UWTJOVEExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); @Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent implements AfterViewInit { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngAfterViewInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ data: [ [ 'Jazz', 'Honda' ], [ 'Civic', 'Honda' ], [ 'CIVIC', 'Honda' ], ], columns: [ { type: 'text', width: '300px' }, { type: 'text' }, ], filters: true, }], onopenfilter: function(worksheet, column, filters) { let unique = new Map; let newFilters = []; filters.forEach(function(item) { if (typeof(item.k) !== 'undefined') { let key = item.k.toLowerCase(); if (! unique.get(key)) { unique.set(key, true); newFilters.push(item); } } else { newFilters.push(item); } }); return newFilters; } }); } } Load Remote Options
The onopenfilter event accepts a promise as a return value, allowing you to populate filter options from a backend API call.
let options = { worksheets: [{ // Data pass as a reference minDimensions: [10,10], filters: true, }], onopenfilter: function(worksheet, columns, items) { return fetch('/getmyitems') .then(response => response.json()) .then(data => { // Assuming data is an array of items to be added data.forEach(item => { items.push(item); }); // Return the updated items return items; }); } }; Changes from Version 11
- resetFilters: Signature changed (removed
destroy). UsehideFiltersto remove filters.- hideFilters: Signature changed; now destroys the entire filter instance.
- Filters cannot be activated for specific column headers; they can only be applied at the header level or by defining a cell range.
- Improved support for range-based filters.
- Toggle filters are now included in the history, allowing undo/redo operations.
- openFilter: Can open a filter from a table using a second argument.
- getFilters: Signature changed.
See Also
- Data Grid Search - Search and highlight data in spreadsheets
- Data Sorting - Sort columns and manage sort states
- Pagination - Paginate large filtered datasets