Sorting
JSS provides a great deal of flexibility and control when it comes to sorting, enabling the developer to:- Create a sorting handler method to customize the JavaScript sorting behavior.
- Use the onbeforesorting event to intercept, change, or cancel the sorting results for the user.
orderBy
method.Documentation
Methods
The developer can call the spreadsheet sorting programmatically using the following methods.Method | Description |
---|---|
orderBy |
Sort the data grid.
@param {number} columnNumber - Sort by column number @param {boolean} direction: false (asc), true (desc) orderBy(columnNumber: Number, direction: Boolean) : void |
Events
Theonbeforesort
can be used to intercept, change or cancel the order results.Event | Description |
---|---|
onbeforesort | onbeforesort(worksheet: Object, column: Number, direction: Number, newValue: Array) : Array |
onsort | onsort(worksheet: Object, column: Number, direction: Number, newValue: Array) : void |
Initial Settings
The following property helps to define the sorting behavior.Property | Description |
---|---|
sorting: function | Define a custom sorting handler.sorting(direction: Boolean) : function |
columnSorting: bool | Allow the user to sort the spreadsheet by columns. Default: true |
Native sorting handler
The following code is the default sorting method. You can customize the function to create different sorting results.JavaScript example
<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> /** * Default sorting method * @param {boolean} Direction * @param {number} Column number starting on zero. */ const sortingHandler = function(direction, column) { // Handler return function(a, b) { var valueA = a[1]; var valueB = b[1]; if (! direction) { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet var table = jspreadsheet(document.getElementById('spreadsheet'), { worksheet: [{ minDimensions: [10,10], }], sorting: sortingHandler, }); </script> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; /** * Default sorting method * @param {boolean} Direction * @param {number} Column number starting on zero. */ const sortingHandler = function(direction, column) { // Handler return function(a, b) { var valueA = a[1]; var valueB = b[1]; if (! direction) { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Render component return ( <Spreadsheet ref={spreadsheet} license={license} sorting={sortingHandler}> <Worksheet /> </Spreadsheet> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license" :sorting="sortingHandler"> <Worksheet :minDimensions="[10,10]" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; /** * Default sorting method * @param {boolean} Direction * @param {number} Column number starting on zero. */ const sortingHandler = function(direction, column) { // Handler return function(a, b) { var valueA = a[1]; var valueB = b[1]; if (! direction) { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } // Create the data grid component export default { components: { Spreadsheet, Worksheet, }, data() { return { sortingHandler, 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('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); /** * Default sorting method * @param {boolean} Direction * @param {number} Column number starting on zero. */ const sortingHandler = function(direction, column) { // Handler return function(a, b) { var valueA = a[1]; var valueB = b[1]; if (! direction) { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } @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, { worksheet: [{ minDimensions: [10,10], }], sorting: sortingHandler, }); } }
Examples
Basic sorting
The following example shows the behavior when sorting through different column types.Double click in any data grid
column header below
Change the spreadsheet column order programmatically
JavaScript example
<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" /> <div id="spreadsheet"></div> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet var table = jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ ['Mazda', 2001, 2000, '2006-01-01', '453.00', '2', '=E1*F1'], ['Peugeot', 2010, 5000, '2005-01-01', '23.00', '5', '=E2*F2'], ['Honda Fit', 2009, 3000, '2004-01-01', '214.00', '3', '=E3*F3'], ['Honda CRV', 2010, 6000, '2003-01-01', '56.11', '2', '=E4*F4'], ], columns: [ { type: 'text', width:300 }, { type: 'text', width:80 }, { type: 'text', width:100 }, { type: 'calendar', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, ], }] }); </script> <select id='columnNumber'> <option value='0'>Column 1</option> <option value='1'>Column 2</option> <option value='2'>Column 3</option> <option value='3'>Column 4</option> </select> <input type='button' value='Sort column' onclick="table[0].orderBy(document.getElementById('columnNumber').value)"> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); const select = useRef(); // Data const data = [ ['Mazda', 2001, 2000, '2006-01-01', '453.00', '2', '=E1*F1'], ['Peugeot', 2010, 5000, '2005-01-01', '23.00', '5', '=E2*F2'], ['Honda Fit', 2009, 3000, '2004-01-01', '214.00', '3', '=E3*F3'], ['Honda CRV', 2010, 6000, '2003-01-01', '56.11', '2', '=E4*F4'], ]; // Columns const columns = [ { type: 'text', width:300 }, { type: 'text', width:80 }, { type: 'text', width:100 }, { type: 'calendar', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, ]; // Render component return ( <> <Spreadsheet ref={spreadsheet} license={license}> <Worksheet data={data} columns={columns} /> </Spreadsheet> <select ref={select}> <option value='0'>Column 1</option> <option value='1'>Column 2</option> <option value='2'>Column 3</option> <option value='3'>Column 4</option> </select> <input type='button' value='Sort column' onClick={() => spreadsheet.current[0].orderBy(select.current.value)} /> </> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license"> <Worksheet :data="data" :columns="columns" /> </Spreadsheet> <select ref="select"> <option value='0'>Column 1</option> <option value='1'>Column 2</option> <option value='2'>Column 3</option> <option value='3'>Column 4</option> </select> <input type="button" value="Sort column" @click="sortColumn" /> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; // Create the data grid component export default { components: { Spreadsheet, Worksheet, }, methods: { sortColumn() { this.$refs.spreadsheet.current[0].orderBy(this.$refs.select.value.value); } }, data() { // Data const data = [ ['Mazda', 2001, 2000, '2006-01-01', '453.00', '2', '=E1*F1'], ['Peugeot', 2010, 5000, '2005-01-01', '23.00', '5', '=E2*F2'], ['Honda Fit', 2009, 3000, '2004-01-01', '214.00', '3', '=E3*F3'], ['Honda CRV', 2010, 6000, '2003-01-01', '56.11', '2', '=E4*F4'], ]; // Columns const columns = [ { type: 'text', width:300 }, { type: 'text', width:80 }, { type: 'text', width:100 }, { type: 'calendar', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, ]; return { data, 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('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); @Component({ selector: "app-root", template: ` <div #spreadsheet></div> <select id='columnNumber'> <option value='0'>Column 1</option> <option value='1'>Column 2</option> <option value='2'>Column 3</option> <option value='3'>Column 4</option> </select> <input type='button' value='Sort column' (click)="this.worksheets[0].orderBy(this.select.nativeElement.value)">` }) export class AppComponent { @ViewChild("spreadsheet") spreadsheet: ElementRef; @ViewChild("select") select: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ data: [ ['Mazda', 2001, 2000, '2006-01-01', '453.00', '2', '=E1*F1'], ['Peugeot', 2010, 5000, '2005-01-01', '23.00', '5', '=E2*F2'], ['Honda Fit', 2009, 3000, '2004-01-01', '214.00', '3', '=E3*F3'], ['Honda CRV', 2010, 6000, '2003-01-01', '56.11', '2', '=E4*F4'], ], columns: [ { type: 'text', width:300 }, { type: 'text', width:80 }, { type: 'text', width:100 }, { type: 'calendar', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, { type: 'number', width:100 }, ], }] }); } }
Custom sorting handler
The following example shows how to customize the spreadsheet sorting behavior using the sorting
property.
JavaScript example
<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" /> <div id="spreadsheet"></div> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet jspreadsheet(document.getElementById('custom-sorting'), { worksheets: [{ data: [ ['Spreadsheets', 1], ['Grids', 2], ['Tables', 3], ['Plugins', 4], ['', ''], ['', ''], ['', ''], ['', ''], ], columns: [ { type: 'text', width:200 }, { type: 'text', width:400 }, ], }], sorting: function(direction, column) { return function(a, b) { let valueA = a[1]; let valueB = b[1]; // Consider blank rows in the sorting if (! direction) { return (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } }); </script> </html>
React example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [ ['Spreadsheets', 1], ['Grids', 2], ['Tables', 3], ['Plugins', 4], ['', ''], ['', ''], ['', ''], ['', ''], ]; // Columns const columns = [ { type: 'text', width:200 }, { type: 'text', width:400 }, ]; // Sorting handler const sorting = (direction, column) => { return (a, b) => { let valueA = a[1]; let valueB = b[1]; // Consider blank rows in the sorting if (! direction) { return (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } // Render component return ( <Spreadsheet ref={spreadsheet} license={license} sorting={sorting}> <Worksheet data={data} columns={columns} /> </Spreadsheet> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license" :sorting="sorting"> <Worksheet :data="data" :columns="columns" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; // Create the data grid component export default { components: { Spreadsheet, Worksheet, }, methods: { // Sorting handler sorting(direction, column) { return (a, b) => { let valueA = a[1]; let valueB = b[1]; // Consider blank rows in the sorting if (! direction) { return (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } }, data() { // Data const data = [ ['Spreadsheets', 1], ['Grids', 2], ['Tables', 3], ['Plugins', 4], ['', ''], ['', ''], ['', ''], ['', ''], ]; // Columns const columns = [ { type: 'text', width:200 }, { type: 'text', width:400 }, ]; return { data, 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('MWE4YTAwN2NiZjcyY2E2ODI4NGY5YjYwMDQxM2Y3ZWJiODY0ODc4MTQzMjIyODlkZTIyNmRlZDZkMDQ0ZmE5NGY1NWZkYzhkOTkxMGMzYmZkYWRiMGZiZWQyMDU1NjMyM2EwYTU5NWRiOTkxNTczYmM2MGY1OTJmZjEyNDI4YjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RFNE1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); @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: [ ['Spreadsheets', 1], ['Grids', 2], ['Tables', 3], ['Plugins', 4], ['', ''], ['', ''], ['', ''], ['', ''], ], columns: [ { type: 'text', width:200 }, { type: 'text', width:400 }, ], }], sorting: function(direction, column) { return function(a, b) { let valueA = a[1]; let valueB = b[1]; // Consider blank rows in the sorting if (! direction) { return (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } }); } }