Sreadsheet Column Sorting
The most recent JavaScript spreadsheet plugin (v8+) gives the developer more control over the sorting feature with:
- A global sorting handler to customize the JavaScript sorting behavior.
- The onbeforesorting event to intercept, change or cancel the results for the user.
To sort the spreadsheet, the user can use the context menu or double-click the column header.
Documentation
Methods
The developer can call the spreadsheet sorting programmatically using the following methods.
Method | Description |
---|---|
orderBy | orderBy(columnNumber: integer, direction: bool) : void @param {number} columnNumber - Sort by column number @param {boolean} direction: false (ASC), true (DESC) |
Events
The onbeforesort
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(bool Direction) : method |
columnSorting: bool | Allow the user to sort the spreadsheet by columns. Default: true |
~~orderBy: integer~~ | ~~Deprecated: The initial sorting by a column number.~~ Please call the orderBy method inside the onload event to define any initial sorting. |
Native sorting handler
The following code is the default sorting method. You can customize the function to create different sorting results.
/**
* Default sorting method
* @param {boolean} Direction
* @param {number} Column number starting on zero.
*/
let sorting = function(direction, column) {
// Handler
return function(a, b) {
let valueA = a[1];
let 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;
}
}
}
Custom sorting handler
The following example shows how to customize the spreadsheet sorting behavior.
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/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>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTY1ZjU4ZDU2NzUwYjNlMGUwMWUzMzU0MzE1YjUxMThjOWEyMDJkMDQ4MDRiODQ2NmYwNTBhMDFjYzkwYzY4ZTE4ZWU2YTZhODY4NmZhZjhlMzE0YTQ4MGRlODhjZTI1YjVkZjQyMzI4YmY4OWQ2NGRmMGVjZDQ0MzM3ZjE5N2YsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXhPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create the spreadsheet
let table = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
minDimensions: [10,10],
}],
sorting: customSortingHandler,
});
</script>
</html>
Examples
Basic sorting
The following example shows the behavior when sorting through different column types.
Double click in any spreadsheet column header below
Change the spreadsheet column order programmatically
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<div id="spreadsheet"></div>
<script>
let 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'],
];
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTY1ZjU4ZDU2NzUwYjNlMGUwMWUzMzU0MzE1YjUxMThjOWEyMDJkMDQ4MDRiODQ2NmYwNTBhMDFjYzkwYzY4ZTE4ZWU2YTZhODY4NmZhZjhlMzE0YTQ4MGRlODhjZTI1YjVkZjQyMzI4YmY4OWQ2NGRmMGVjZDQ0MzM3ZjE5N2YsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXhPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create the spreadsheet
let table = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data:data,
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 },
],
}]
});
document.getElementById("orderby").onclick = () => table[0].orderBy(document.getElementById('columnNumber').value);
</script>
<br/>
<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' id="orderby"/>
</html>
Custom sorting handler
The following example shows how to customize the spreadsheet sorting behavior using the sorting
property.
<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/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('OTY1ZjU4ZDU2NzUwYjNlMGUwMWUzMzU0MzE1YjUxMThjOWEyMDJkMDQ4MDRiODQ2NmYwNTBhMDFjYzkwYzY4ZTE4ZWU2YTZhODY4NmZhZjhlMzE0YTQ4MGRlODhjZTI1YjVkZjQyMzI4YmY4OWQ2NGRmMGVjZDQ0MzM3ZjE5N2YsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNek5UUXhPVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
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>