Data Grid Column Sorting
Jspreadsheet CE enables developers to create custom sorting handlers to override the default data grid sorting behaviour. Rows can be sorted through the context menu, double-clicking column headers, or programmatically using the orderBy method.
Documentation
Methods
The following method can be invoked to execute sorting programmatically:
| 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
| Event | Description |
|---|---|
| onsort | onsort(worksheet: Object, column: Number, direction: Number, newValue: Array) : void |
Initial Settings
You can define the sorting behaviour of your spreadsheet using the following properties:
| Property | Description |
|---|---|
| sorting: function | Defines a custom sorting handler. Use this to implement your specific sorting logic.sorting(direction: Boolean) : function |
| columnSorting: boolean | Enables or disables column-based sorting for the spreadsheet.Default: true |
Examples
Basic Sorting
The example below demonstrates sorting behaviour across various column types.
Double-click on any data grid column header below to observe sorting functionality.
<html> <script src="https://bossanova.uk/jspreadsheet/v5/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://bossanova.uk/jspreadsheet/v5/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> <p><select id='btn2'> <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="btn1" /></p> <script> // Create the spreadsheet let 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'], ] }] }); document.getElementById("btn1").onclick = (e) => table[0].orderBy(e.target.previousElementSibling.value); </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet-ce/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet-ce/dist/jspreadsheet.css"; 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'], ]; // Render component return ( <> <Spreadsheet ref={spreadsheet}> <Worksheet data={data} /> </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)} /> </> ); } <template> <Spreadsheet ref="spreadsheet"> <Worksheet :data="data" /> </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 { ref } from 'vue'; import { Spreadsheet, Worksheet } from "@jspreadsheet-ce/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet-ce/dist/jspreadsheet.css"; export default { components: { Spreadsheet, Worksheet, }, setup() { const spreadsheet = ref(null); const select = ref(null); 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'], ]; const sortColumn = () => { const columnIndex = select.value.value; spreadsheet.value.current[0].orderBy(columnIndex); }; return { spreadsheet, select, data, sortColumn }; } } </script> import { Component, ViewChild, ElementRef } from "@angular/core"; import jspreadsheet from "jspreadsheet-ce"; import "jspreadsheet-ce/dist/jspreadsheet.css" import "jsuites/dist/jsuites.css" @Component({ standalone: true, 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 ngAfterViewInit() { // 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'], ] }] }); } } Custom Sorting Handler
The example below demonstrates how to customize spreadsheet sorting behaviour using the sorting property.
<html> <script src="https://bossanova.uk/jspreadsheet/v5/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://bossanova.uk/jspreadsheet/v5/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> // 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> import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet-ce/react"; import "jsuites/dist/jsuites.css"; import "jspreadsheet-ce/dist/jspreadsheet.css"; 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} sorting={sorting}> <Worksheet data={data} columns={columns} /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet" :sorting="sorting"> <Worksheet :data="data" :columns="columns" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet-ce/vue"; import "jsuites/dist/jsuites.css"; import "jspreadsheet-ce/dist/jspreadsheet.css"; // 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, }; } } </script> import { Component, ViewChild, ElementRef } from "@angular/core"; import jspreadsheet from "jspreadsheet-ce"; import "jspreadsheet-ce/dist/jspreadsheet.css" import "jsuites/dist/jsuites.css" @Component({ standalone: true, selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngAfterViewInit() { // 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; } } } }); } }