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	The initial sorting by a column number. `Default: false`

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;
        }
    }
}

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

	A	B	C	D	E	F	G
1	Mazda	2001	2000	2006-01-01	453	2	906
2	Peugeot	2010	5000	2005-01-01	23	5	115
3	Honda Fit	2009	3000	2004-01-01	214	3	642
4	Honda CRV	2010	6000	2003-01-01	56.11	2	112.22

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/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'],
];

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>

<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.

	A	B
1	Spreadsheets	1
2	Grids	2
3	Tables	3
4	Plugins	4
5
6
7
8

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

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

<script>
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>

Edit this page on GitHub