Use the sortRange method to sort a range with given order, as shown in the following code.
Users can also use context menu -> sort or filter to sort the data.
The priority of data type is: boolean > string > number, eg: TRUE > '4' > 8.
Customize sorting
User can use the callback function to define the customized compare method to sort.
The following code shows use localCompare to sort.
Sort By Cell Color
User can sort cell by cell color according special sort info. This feature supports the solid fill, pattern fill, gradient fill.
The following code shows use backColor to sort by cell color.
Sort by Font Color
User can sort cell by font color according the special sort info. This feature only supports solid fill.
User can define the callback function to used when the RangeSorting events raised.
Group sort
You can set the whether keep the data grouped by using the groupSort options. The GroupSort provides the following types:
flat: Sort ignore the group.
group: Move the group with the sort, but don't sort inner the group
child: Only sort inner the group.
full: Move the group with the sort, and sort inner the group
The following code shows use groupSort option.
User can define the groupSort option to used when the RangeSorting events raised.
Sort ignore hidden
You can set the whether ignore the hidden values when sorting.
When ignoreHidden set to true, spread will skip and don't move the hidden value.
When ignoreHidden set to false, spread will compare and move the hidden value.
When groupSort set to group/child/full, SpreadJS will move the hidden value and move the row/column visibility to keep value hidden.
The following code shows use ignoreHidden option.
User can define the ignoreHidden option to used when the RangeSorting events raised.
By default, if the sort ranges contains group, SpreadJS will sort the data with option group sort. Otherwise, it will use flat sort and ignore hidden.
Keep last sort state
The Last sort Action setting will be recorded by worksheet, you can get the last sort state by using getSortState function.
The following code shows use getSortState function.
And now you can call the sortRange without passing any arguments to trigger reorder, the reorder action are base on the last sort state.
User can use sortRange with bind function to implement automatic sorting.The following code shows a simple code example.
The above code will be called automatically when a value in the sheet changed, so a reorder will be triggered whenever you make a value change to the sheet.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const CELL_COLOR_MAPPING = {
red: "#FF0000",
green: "#00B050",
blue: "#00B0F0",
gradient: {
degree: 90,
stops: [
{
color: "#ffffff",
position: 0,
},
{
color: "#5B9BD5",
position: 1,
}
]
},
pattern: {
patternColor: "",
type: 14,
backgroundColor: ""
}
}
const FONT_COLOR_MAPPING = {
red: "#FF0000",
blue: "#00B0F0",
purple: "#7030A0",
green: "#92D050",
null: ""
}
function _getElementById(id) {
return document.getElementById(id);
}
const useState = React.useState;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
spread.suspendPaint();
spread.fromJSON(sjsData);
initSheet0(spread.getSheet(0));
initSheet1(spread.getSheet(1));
initSheet2(spread.getSheet(2));
initSheet3(spread.getSheet(3));
initSheet4(spread.getSheet(4));
initSheet5(spread.getSheet(5));
initSheet6(spread.getSheet(6));
initSheet7(spread.getSheet(7));
initSortStatePanel(spread);
spread.resumePaint();
}
const initSheet0 = (sheet) => {
var style = sheet.getStyle(4, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by last name",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 1,
ascending: true,
compareFunction: function (value1, value2) {
var str1 = value1.split(" ")[1], str2 = value2.split(" ")[1];
return str1.localeCompare(str2);
}
},
])
},
}];
sheet.setStyle(4, 7, style);
var grade = ["Freshmen", "Sophomore", "Junior", "Senior"];
var clothesSize = ["XX-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "XX-Large"];
function compareList(obj1, obj2, list) {
var index1 = list.indexOf(obj1), index2 = list.indexOf(obj2);
if (index1 > index2) {
return 1;
} else if (index1 < index2) {
return -1;
} else {
return 0;
}
}
style = sheet.getStyle(5, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by Grade",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 2,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, grade);
}
},
])
},
}];
sheet.setStyle(5, 7, style);
style = sheet.getStyle(6, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by T-Shirt Size",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 3,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, clothesSize);
}
},
])
},
}];
sheet.setStyle(6, 7, style);
}
const initSheet1 = (sheet) => {
function sortDomain(value1, value2) {
var str1 = value1.substr(value1.lastIndexOf(".") + 1), str2 = value2.substr(value2.lastIndexOf(".") + 1);
return str1.localeCompare(str2);
}
function sortIP(ip1, ip2) {
var value1 = ip1.split("."), value2 = ip2.split(".");
for (var i = 0; i < 4; i++) {
var num1 = parseInt(value1[i]), num2 = parseInt(value2[i]);
if (num1 > num2) {
return 1;
} else if (num1 < num2) {
return -1;
}
}
return 0;
}
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
if (info.col === 0) {
info.compareFunction = sortDomain;
} else if (info.col === 1) {
info.compareFunction = sortIP;
}
});
}
const initSheet2 = (sheet) => {
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
info.groupSort = GC.Spread.Sheets.GroupSort.full;
});
}
const initSheet3 = (sheet) => {
sheet.outlineColumn.options({
columnIndex: 0,
showImage: false,
showIndicator: true,
showCheckBox: true,
maxLevel: 10
});
}
const initSheet4 = (sheet) => {
var style = sheet.getStyle(1, 4);
style.cellButtons = [
{
useButtonStyle: true,
caption: "ignoreHidden = true",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: true });
},
}, {
useButtonStyle: true,
caption: "ignoreHidden = false",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: false });
},
}, {
useButtonStyle: true,
caption: "groupSort = group",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { groupSort: GC.Spread.Sheets.GroupSort.group });
},
}];
sheet.setStyle(1, 4, style);
}
const initSheet5 = (sheet) => {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [
{
caption: "Sort By Cell Color",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = CELL_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
backColor: color,
order: order,
}])
}
}
];
sheet.setStyle(16, 4, style);
}
const initSheet6 = (sheet) => {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [
{
caption: "Sort By Font Color",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = FONT_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
fontColor: color,
order: order
}])
}
}
];
sheet.setStyle(16, 4, style);
}
const initSheet7 = (sheet) => {
function inSortStateRange(sortState, row, col) {
if (row >= sortState.row && row < sortState.row + sortState.rowCount && col >= sortState.col && col < sortState.col + sortState.colCount) {
return true;
}
return false;
}
sheet.sortRange(2, 2, 10, 1, true, [{ index: 2, ascending: false, compareFunction: undefined }]);
sheet.setSelection(2, 2, 10, 1);
sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
let sortState = sheet.getSortState();
if (inSortStateRange(sortState, info.row, info.col)) {
sheet.sortRange();
}
});
}
const initSortStatePanel = (spread) => {
_getElementById('get_SortState_Btn').addEventListener('click', function () {
let sheet = spread.getActiveSheet();
let sortState = sheet.getSortState();
if (!sortState) {
return;
}
let { row, col, rowCount, colCount, byRow, sortConditions } = sortState;
if (sortState) {
let sortStateStr = '';
sortStateStr += "row: " + row + ",\n";
sortStateStr += "col: " + col + ",\n";
sortStateStr += "rowCount: " + rowCount + ",\n";
sortStateStr += "colCount: " + colCount + ",\n";
sortStateStr += "byRow: " + byRow + ",\n";
sortStateStr += "sortCondition: " + JSON.stringify(sortConditions); +"}\n";
document.getElementById("showEventArgs").value = sortStateStr;
}
});
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets groupAppearanceSs">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<div class="options-container">
<div id="settingsDiv">
<br />
<label>This text box shows sortState information about the last sort action.</label>
<br />
<textarea id="showEventArgs" cols={85} rows={8} style={{ maxWidth: '98%' }}></textarea>
<div class="option-row">
<input type="button" id="get_SortState_Btn" value="Get Sort State" />
</div>
</div>
</div>
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
const CELL_COLOR_MAPPING = {
red: "#FF0000",
green: "#00B050",
blue: "#00B0F0",
gradient: {
degree: 90,
stops: [
{
color: "#ffffff",
position: 0,
},
{
color: "#5B9BD5",
position: 1,
}
]
},
pattern: {
patternColor: "",
type: 14,
backgroundColor: ""
}
}
const FONT_COLOR_MAPPING = {
red: "#FF0000",
blue: "#00B0F0",
purple: "#7030A0",
green: "#92D050",
null: ""
}
function _getElementById(id) {
return document.getElementById(id);
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets groupAppearanceSs">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<div class="options-container">
<div id="settingsDiv">
<br />
<label>This text box shows sortState information about the last sort action.</label>
<br />
<textarea id="showEventArgs" cols={85} rows={8} style={{ maxWidth: '98%' }}></textarea>
<div class="option-row">
<input type="button" id="get_SortState_Btn" value="Get Sort State" />
</div>
</div>
</div>
</div>
);
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
spread.fromJSON(sjsData);
this.initSheet0(spread.getSheet(0));
this.initSheet1(spread.getSheet(1));
this.initSheet2(spread.getSheet(2));
this.initSheet3(spread.getSheet(3));
this.initSheet4(spread.getSheet(4));
this.initSheet5(spread.getSheet(5));
this.initSheet6(spread.getSheet(6));
this.initSheet7(spread.getSheet(7));
this.initSortStatePanel(spread);
spread.resumePaint();
}
initSheet0(sheet) {
var style = sheet.getStyle(4, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by last name",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 1,
ascending: true,
compareFunction: function (value1, value2) {
var str1 = value1.split(" ")[1], str2 = value2.split(" ")[1];
return str1.localeCompare(str2);
}
},
])
},
}];
sheet.setStyle(4, 7, style);
var grade = ["Freshmen", "Sophomore", "Junior", "Senior"];
var clothesSize = ["XX-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "XX-Large"];
function compareList(obj1, obj2, list) {
var index1 = list.indexOf(obj1), index2 = list.indexOf(obj2);
if (index1 > index2) {
return 1;
} else if (index1 < index2) {
return -1;
} else {
return 0;
}
}
style = sheet.getStyle(5, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by Grade",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 2,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, grade);
}
},
])
},
}];
sheet.setStyle(5, 7, style);
style = sheet.getStyle(6, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "Sort by T-Shirt Size",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 3,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, clothesSize);
}
},
])
},
}];
sheet.setStyle(6, 7, style);
}
initSheet1(sheet) {
function sortDomain(value1, value2) {
var str1 = value1.substr(value1.lastIndexOf(".") + 1), str2 = value2.substr(value2.lastIndexOf(".") + 1);
return str1.localeCompare(str2);
}
function sortIP(ip1, ip2) {
var value1 = ip1.split("."), value2 = ip2.split(".");
for (var i = 0; i < 4; i++) {
var num1 = parseInt(value1[i]), num2 = parseInt(value2[i]);
if (num1 > num2) {
return 1;
} else if (num1 < num2) {
return -1;
}
}
return 0;
}
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
if (info.col === 0) {
info.compareFunction = sortDomain;
} else if (info.col === 1) {
info.compareFunction = sortIP;
}
});
}
initSheet2(sheet) {
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
info.groupSort = GC.Spread.Sheets.GroupSort.full;
});
}
initSheet3(sheet) {
sheet.outlineColumn.options({
columnIndex: 0,
showImage: false,
showIndicator: true,
showCheckBox: true,
maxLevel: 10
});
}
initSheet4(sheet) {
var style = sheet.getStyle(1, 4);
style.cellButtons = [
{
useButtonStyle: true,
caption: "ignoreHidden = true",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: true });
},
}, {
useButtonStyle: true,
caption: "ignoreHidden = false",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: false });
},
}, {
useButtonStyle: true,
caption: "groupSort = group",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { groupSort: GC.Spread.Sheets.GroupSort.group });
},
}];
sheet.setStyle(1, 4, style);
}
initSheet5(sheet) {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [
{
caption: "Sort By Cell Color",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = CELL_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
backColor: color,
order: order,
}])
}
}
];
sheet.setStyle(16, 4, style);
}
initSheet6(sheet) {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [
{
caption: "Sort By Font Color",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = FONT_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
fontColor: color,
order: order
}])
}
}
];
sheet.setStyle(16, 4, style);
}
initSheet7(sheet) {
function inSortStateRange(sortState, row, col) {
if (row >= sortState.row && row < sortState.row + sortState.rowCount && col >= sortState.col && col < sortState.col + sortState.colCount) {
return true;
}
return false;
}
sheet.sortRange(2, 2, 10, 1, true, [{ index: 2, ascending: false, compareFunction: undefined }]);
sheet.setSelection(2, 2, 10, 1);
sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
let sortState = sheet.getSortState();
if (inSortStateRange(sortState, info.row, info.col)) {
sheet.sortRange();
}
});
}
initSortStatePanel(spread) {
_getElementById('get_SortState_Btn').addEventListener('click', function () {
let sheet = spread.getActiveSheet();
let sortState = sheet.getSortState();
if (!sortState) {
return;
}
let { row, col, rowCount, colCount, byRow, sortConditions } = sortState;
if (sortState) {
let sortStateStr = '';
sortStateStr += "row: " + row + ",\n";
sortStateStr += "col: " + col + ",\n";
sortStateStr += "rowCount: " + rowCount + ",\n";
sortStateStr += "colCount: " + colCount + ",\n";
sortStateStr += "byRow: " + byRow + ",\n";
sortStateStr += "sortCondition: " + JSON.stringify(sortConditions); +"}\n";
document.getElementById("showEventArgs").value = sortStateStr;
}
});
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/sorting.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
input {
padding: 4px 6px;
}
label {
display: block;
margin-bottom: 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
