Custom spreadsheet search

Jspreadsheet Pro v7.7.1+ brings two new events onbeforesearch and onsearch. It is possible, using the event onbeforesearch to intercept the search, cancel or apply a different algorithm to customize the results to the user. In the following example, the event will cancel the native method, trigger an ajax request to query the results in the backend.

How to process a search in the backend

<html>
<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

<div id="spreadsheet"></div>

<script>
let table = jspreadsheet(document.getElementById('spreadsheet'), {
    csv: '/jspreadsheet/demo.csv',
    csvHeaders: true,
    search: true,
    pagination: 10,
    paginationOptions: [10,25,50,100],
    defaultColWidth: 100,
    license: 'YTk5MTFhY2U0YjBlZmI4N2ZkMTY3MjMwZTViYWU2OWJiN2MwY2U4ZDE4NWYzNWU2Y2YzZjgwY2RmNWE5MTVhOGMwZTdjZjQ3YzIwZGM2Yjg1MzI2ODcxMGJlZjY0Yjk3ZWUwYmQ2YWZjOTk3ZWRkYzc1MzkwNzJhYmI3MjZmMjksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNeE5UWTFPVFE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==',
    onbeforesearch: function(el, str, currentResults) {
        // Loading spin
        jSuites.loading.show();
        // Remote search processing
        jSuites.ajax({
            url: '/search',
            dataType: 'json',
            success: function(newResults) {
                // Set the rowIds that should be return to the user.
                // For this example the return will be [5,21]
                el.jexcel.results = newResults;
                // Execute the update
                el.jexcel.search.update();
                // Loading spin
                jSuites.loading.hide();
            }
        })

        // Cancel the native search
        return false;
    } 
});
</script>
</html>

Onbeforesearch signature

onbeforesearch(el: DOMElement, str: String, rowNumbers: Array, search: Object) => arrayWithResults: Array

// The return should be an array with rowNumbers.
// In the following example the spreasheed will show three records.
// It is possible to cancel the search by return === false

return [10,99,1355];

Argument	description
el	The jspreadsheet DOM container.
str	The string used by the user
rowNumbers	The current results, which can be intercepted and overwrite
search	The object with the search handler methods

Related events

Events	Description
onbeforesearch	`onbeforesearch(DOMElement el, String str, Array rowNumbers, Object search)` Action to be executed before the search. It can be used to cancel or to intercept and customize the searching process.
onsearch	`onbeforesearch(DOMElement el, String str, Array rowNumbers, Object search)` After the search process is completed.
onchangepage	`onchangepage(DOMElement el, Number pageNumber, Number oldPage, Number quantityPerPage)` Action to be executed before the page changes. It can cancel the action by returning false.
onpage	`onpage(DOMElement el, Number quantityPerPage, Number oldPage, Number pageNumber)` After the page has changed.

Related methods

Method	Description
page	`table.page(Number pageNumber)` Go to the page number.
whichPage	`table.whichPage()` Return the current page.
quantiyOfPages	`table.quantiyOfPages()` Get the quantity of pages.

Edit this page on GitHub