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.
MethodDescription
orderByorderBy(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.
EventDescription
onbeforesortonbeforesort(worksheet: Object, column: Number, direction: Number, newValue: Array) : Array
onsortonsort(worksheet: Object, column: Number, direction: Number, newValue: Array) : void


Initial Settings

The following property helps to define the sorting behavior.
PropertyDescription
sorting: functionDefine a custom sorting handler.
sorting(bool Direction) : method
columnSorting: boolAllow the user to sort the spreadsheet by columns. Default: true
orderBy: integerDeprecated: 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.
 */
var sorting = 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;
        }
    }
}

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('NDliMmYwNDI1OTc0ZDZmYTA1YjZiMWFiYjQ2YzQxODAwMmQwMjIyMmMzOWQ4YmU1NzQzOGRiNDNmYjZlNGExYjM0Mjg5ZmQ1OWJmMzdkMzZkYjAwZmZmMTc0NjM2OWQ5OGZmNTliZjJkYmY1MTdhZDgwYWUzNTE3YzliZGQ4MjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3hNRGt4TVRFNU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
var table = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheet: [{
        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




Source code

<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>
var 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('NDliMmYwNDI1OTc0ZDZmYTA1YjZiMWFiYjQ2YzQxODAwMmQwMjIyMmMzOWQ4YmU1NzQzOGRiNDNmYjZlNGExYjM0Mjg5ZmQ1OWJmMzdkMzZkYjAwZmZmMTc0NjM2OWQ5OGZmNTliZjJkYmY1MTdhZDgwYWUzNTE3YzliZGQ4MjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3hNRGt4TVRFNU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
var 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 },
        ],
    }]
});
</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>


Custom sorting handler

The following example shows how to customize the spreadsheet sorting behavior using the sorting property.



Source code

<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('NDliMmYwNDI1OTc0ZDZmYTA1YjZiMWFiYjQ2YzQxODAwMmQwMjIyMmMzOWQ4YmU1NzQzOGRiNDNmYjZlNGExYjM0Mjg5ZmQ1OWJmMzdkMzZkYjAwZmZmMTc0NjM2OWQ5OGZmNTliZjJkYmY1MTdhZDgwYWUzNTE3YzliZGQ4MjMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3hNRGt4TVRFNU15d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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) {
            var valueA = a[1];
            var 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>