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