Search
The search function in Jspreadsheet makes it easy to filter rows based on a specified keyword. If needed, you can modify or cancel the search behavior by utilizing one of the available events described below.
Documentation
Methods
The following methods are related to the spreadsheet search.
Method |
Description |
search |
search(terms: String) : void Search for the data grid rows that contain the specified terms. |
resetSearch |
resetSearch() : void Reset the search terms and show all rows again. |
showSearch |
showSearch() : void Show the search input box. |
hideSearch |
hideSearch() : void Hide the search input box. |
updateSearch |
updateSearch() : void Refresh the results on the viewport. |
Events
JSS allows developers to customize the data grid search functionality through a set of events that can be utilized to modify or cancel the search behavior. These events enable more advanced control over the search feature, allowing for more complex and specific filtering based on various criteria.
Event |
Description |
onbeforesearch |
onbeforesearch(worksheet: Object, terms: String, results: Array, search: Object) Action to be executed before the search. It can be used to cancel or to intercept and customize the search process. |
onsearchstart |
onsearchstart(worksheet: Object, terms: String) It happens before all the search events. |
onsearchrow |
onsearchrow(worksheet: Object, rowNumber: number, terms: String) It helps to customize the searching process. |
onsearch |
onbeforesearch(worksheet: Object, terms: String, rowNumber: Array, search: Object) After the search process is completed. |
Initial Settings
The following properties can be used during the initialization of the online spreadsheet.
Property |
Description |
search: boolean |
Enable or disable search. |
Examples
Simple data grid with search and pagination
Basic example with search and pagination.
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<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='Search for APP' id="searchbtn"/>
<script>
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
let worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
columns: [
{ type:'text', width:80 },
{ type:'text', width:200 },
{ type:'text', width:100 },
{ type:'text', width:200 },
{ type:'text', width:100 },
],
}]
});
document.getElementById("searchbtn").onclick = () => worksheets[0].search('app');
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
export default function App() {
const spreadsheet = useRef();
const columns = [
{ type:'text', width:80 },
{ type:'text', width:200 },
{ type:'text', width:100 },
{ type:'text', width:200 },
{ type:'text', width:100 },
]
return (
<>
<Spreadsheet ref={spreadsheet} license={license}>
<Worksheet
columns={columns}
csv="/tests/demo.csv"
csvHeaders
search
pagination="10"
paginationOptions={[10,25,50,100]} />
</Spreadsheet>
<input type={'button'} value={'Search for APP'} onClick={() => spreadsheet.current[0].search('app')} />
</>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet
:columns="columns"
:csv="/tests/demo.csv"
csvHeaders
search
:pagination="10"
:paginationOptions="[10,25,50,100]"
/>
</Spreadsheet>
<input type='button' value='Search for APP' @click="search" />
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
search(cell) {
this.$refs.spreadsheet.current[0].search('app');
},
}
data() {
const columns = [
{ type:'text', width:80 },
{ type:'text', width:200 },
{ type:'text', width:100 },
{ type:'text', width:200 },
{ type:'text', width:100 },
],
return {
columns,
license,
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>
<input type='button' value='Search for APP' (click)="this.worksheets[0].search('app')" />`,
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
worksheets: jspreadsheet.worksheetInstance[];
ngAfterViewInit() {
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
columns: [
{ type:'text', width:80 },
{ type:'text', width:200 },
{ type:'text', width:100 },
{ type:'text', width:200 },
{ type:'text', width:100 },
],
});
}
}
Custom local data grid search
How to create a custom method to search for your rows.
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<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>
<script>
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
defaultColWidth: 150,
}],
oncreatecell: function(worksheet, td, col, row) {
if (worksheet.highlight && worksheet.highlight[row] && worksheet.highlight[row][col]) {
td.classList.add('jss_highlight');
}
},
onsearchstart: function(worksheet) {
if (worksheet.highlight) {
for (let j = 0; j < worksheet.highlight.length; j++) {
if (worksheet.highlight[j]) {
for (let i = 0; i < worksheet.highlight[j].length; i++) {
if (worksheet.records[j][i] && worksheet.records[j][i].element) {
worksheet.records[j][i].element.classList.remove('jss_highlight');
}
}
}
}
}
worksheet.highlight = [];
},
onsearchrow: function(worksheet, row, terms) {
terms = new RegExp(terms, 'gi');
let value = null;
let test = false;
for (let col = 0; col < worksheet.options.columns.length; col++) {
value = '' + worksheet.getValueFromCoords(col,row,true);
if (value.match(terms)) {
if (worksheet.records[row][col].element) {
worksheet.records[row][col].element.classList.add('jss_highlight');
}
if (! worksheet.highlight[row]) {
worksheet.highlight[row] = [];
}
worksheet.highlight[row][col] = true;
test = true;
}
}
return test;
}
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
export default function App() {
const spreadsheet = useRef();
const oncreatecell = (worksheet, td, col, row) => {
if (worksheet.highlight && worksheet.highlight[row] && worksheet.highlight[row][col]) {
td.classList.add('jss_highlight');
}
}
const onsearchstart = (worksheet) => {
if (worksheet.highlight) {
for (let j = 0; j < worksheet.highlight.length; j++) {
if (worksheet.highlight[j]) {
for (let i = 0; i < worksheet.highlight[j].length; i++) {
if (worksheet.records[j][i] && worksheet.records[j][i].element) {
worksheet.records[j][i].element.classList.remove('jss_highlight');
}
}
}
}
}
worksheet.highlight = [];
}
const onsearchrow = (worksheet,row,terms) => {
let terms = new RegExp(terms, 'gi');
let value = null;
let test = false;
for (let col = 0; col < worksheet.options.columns.length; col++) {
value = '' + worksheet.getValueFromCoords(col,row,true);
if (value.match(terms)) {
if (worksheet.records[row][col].element) {
worksheet.records[row][col].element.classList.add('jss_highlight');
}
if (! worksheet.highlight[row]) {
worksheet.highlight[row] = [];
}
worksheet.highlight[row][col] = true;
test = true;
}
}
return test;
}
return (
<>
<Spreadsheet ref={spreadsheet} license={license} oncreatecell={oncreatecell}
onsearchstart={onSearchStart} onsearchrow={onsearchrow}>
<Worksheet
csv="/tests/demo.csv"
csvHeaders
search
pagination="10"
paginationOptions={[10,25,50,100]}
defaultColWidth="150" />
</Spreadsheet>
<input type='button' value='Search for APP' onClick={() => spreadsheet.current[0].search('app')} />
</>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license" :oncreatecell="oncreatecell"
:onsearchstart="onsearchstart" :onsearchrow="onsearchrow">
<Worksheet defaultColWidth="150" :csv="/tests/demo.csv"
csvHeaders
search
:pagination="10"
:paginationOptions="[10,25,50,100]" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
search(cell) {
this.$refs.spreadsheet.current[0].search('app');
},
oncreatecell(worksheet, td, col, row) {
if (worksheet.highlight && worksheet.highlight[row] && worksheet.highlight[row][col]) {
td.classList.add('jss_highlight');
}
},
onsearchstart(worksheet) {
if (worksheet.highlight) {
for (let j = 0; j < worksheet.highlight.length; j++) {
if (worksheet.highlight[j]) {
for (let i = 0; i < worksheet.highlight[j].length; i++) {
if (worksheet.records[j][i] && worksheet.records[j][i].element) {
worksheet.records[j][i].element.classList.remove('jss_highlight');
}
}
}
}
}
worksheet.highlight = [];
},
onsearchrow(worksheet, row, terms) {
let terms = new RegExp(terms, 'gi');
let value = null;
let test = false;
for (let col = 0; col < worksheet.options.columns.length; col++) {
value = '' + worksheet.getValueFromCoords(col,row,true);
if (value.match(terms)) {
if (worksheet.records[row][col].element) {
worksheet.records[row][col].element.classList.add('jss_highlight');
}
if (! worksheet.highlight[row]) {
worksheet.highlight[row] = [];
}
worksheet.highlight[row][col] = true;
test = true;
}
}
return test;
}
},
data() {
return {
columns,
license,
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`,
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
worksheets: jspreadsheet.worksheetInstance[];
ngAfterViewInit() {
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
defaultColWidth: 150,
}],
oncreatecell: function(worksheet, td, col, row) {
if (worksheet.highlight && worksheet.highlight[row] && worksheet.highlight[row][col]) {
td.classList.add('jss_highlight');
}
},
onsearchstart: function(worksheet) {
if (worksheet.highlight) {
for (let j = 0; j < worksheet.highlight.length; j++) {
if (worksheet.highlight[j]) {
for (let i = 0; i < worksheet.highlight[j].length; i++) {
if (worksheet.records[j][i] && worksheet.records[j][i].element) {
worksheet.records[j][i].element.classList.remove('jss_highlight');
}
}
}
}
}
worksheet.highlight = [];
},
onsearchrow: function(worksheet, row, terms) {
let terms = new RegExp(terms, 'gi');
let value = null;
let test = false;
for (let col = 0; col < worksheet.options.columns.length; col++) {
value = '' + worksheet.getValueFromCoords(col,row,true);
if (value.match(terms)) {
if (worksheet.records[row][col].element) {
worksheet.records[row][col].element.classList.add('jss_highlight');
}
if (! worksheet.highlight[row]) {
worksheet.highlight[row] = [];
}
worksheet.highlight[row][col] = true;
test = true;
}
}
return test;
}
});
}
}
Custom search using the backend
The example below demonstrates how to modify the search behavior by fetching data from the backend. For the sake of the testing, the server will return always [2,20,200]
<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" />
<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>
<script>
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
defaultColWidth: 150,
}],
onbeforesearch: function(worksheet, str, currentResults) {
jSuites.loading.show();
jSuites.ajax({
url: '/v10/test?q=' + str,
dataType: 'json',
success: function(newResults) {
jSuites.loading.hide();
if (newResults && newResults.length) {
worksheet.results = newResults;
} else {
worksheet.results = null;
}
worksheet.updateSearch();
}
})
return false;
}
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
export default function App() {
const spreadsheet = useRef();
const onbeforesearch = (worksheet, str, currentResults) => {
jSuites.loading.show();
jSuites.ajax({
url: '/v10/test?q=' + str,
dataType: 'json',
success: function(newResults) {
jSuites.loading.hide();
if (newResults && newResults.length) {
worksheet.results = newResults;
} else {
worksheet.results = null;
}
worksheet.updateSearch();
}
})
return false;
}
return (
<>
<Spreadsheet ref={spreadsheet} license={license}>
<Worksheet csv="/tests/demo.csv"
csvHeaders
search
pagination="10"
paginationOptions={[10,25,50,100]}
defaultColWidth="150" />
</Spreadsheet>
<input type='button' value='Search for APP' onClick={() => spreadsheet.current[0].search('app')} />
</>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license" :onbeforesearch="onbeforesearch">
<Worksheet defaultColWidth="150" :csv="/tests/demo.csv"
csvHeaders
search
:pagination="10"
:paginationOptions="[10,25,50,100]" />
</Spreadsheet>
<input type='button' value='Search for APP' @click="search" />
</template>
<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import jSuites from "jsuites";
const license = 'ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
const onbeforesearch = (worksheet, str, currentResults) => {
jSuites.loading.show();
jSuites.ajax({
url: '/v10/test?q=' + str,
dataType: 'json',
success: function(newResults) {
jSuites.loading.hide();
if (newResults && newResults.length) {
worksheet.results = newResults;
} else {
worksheet.results = null;
}
worksheet.updateSearch();
}
})
return false;
}
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
search(cell) {
this.$refs.spreadsheet.current[0].search('app');
},
onbeforesearch,
}
data() {
return {
columns,
license,
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"
import jSuites from "jSuites";
jspreadsheet.setLicense('ZGExZDk5ZDJjODJiNTBmYmZiOWVkYjRmN2MyYmM1MDQzNjIzMjA5ODUzNWI3MzdlZDNiMWY2YTFlMDU4MTBmZWQ5NTBjN2FhYjVhNDYwNjM0MjZlOTUyZDExNTVhNGZiYzk5YmY2MDU1ZDdhZTFmMjMyYmRhNTZjYmYwZjYyZjgsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME1ERTBOek01TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>
<input type='button' value='Search for APP' (click)="this.worksheets[0].search('app')" />`,
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
worksheets: jspreadsheet.worksheetInstance[];
ngAfterViewInit() {
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
csv: '/tests/demo.csv',
csvHeaders: true,
search: true,
pagination: 10,
paginationOptions: [10,25,50,100],
defaultColWidth: 150,
}],
onbeforesearch: function(worksheet, str, currentResults) {
jSuites.loading.show();
jSuites.ajax({
url: '/v10/test?q=' + str,
dataType: 'json',
success: function(newResults) {
jSuites.loading.hide();
if (newResults && newResults.length) {
worksheet.results = newResults;
} else {
worksheet.results = null;
}
worksheet.updateSearch();
}
})
return false;
}
});
}
}
Related content
Data grid pagination