Sorting Data

SpreadJS can sort data in either ascending or descending order. This can be accessed within the spreadsheet interface via table headers or context menus on cell range selections. JavaScript code can also be used for sorting by using the sheet's sortRange function. In some cases, traditional ascending and descending sorting is not enough, which is why sorting on the workbook can be customized with compare functions as described in the code below.

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. 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.
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss')); 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)); spread.resumePaint(); }; function initSheet0(sheet) { var style = sheet.getStyle(4, 7); style.cellButtons= [{ useButtonStyle: true, caption: "Sort with 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 with 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); sheet.setRowHeight(5,35); style = sheet.getStyle(6, 7); style.cellButtons= [{ useButtonStyle: true, caption: "Sort with 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); sheet.setRowHeight(6,35); } function 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; } }); } function initSheet2(sheet) { sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) { info.groupSort = GC.Spread.Sheets.GroupSort.full; }); } function initSheet3(sheet) { sheet.outlineColumn.options({ columnIndex: 0, showImage: false, showIndicator: true, showCheckBox: true, maxLevel: 10 }); } function 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), }, ], {ignoreHidden: true}); }, }, { useButtonStyle: true, caption: "ignoreHidden = false", command: function() { sheet.sortRange(2, 0, 15, 1, true, [ { index: 0, ascending: sheet.getValue(1, 3), }, ], {ignoreHidden: false}); }, }, { useButtonStyle: true, caption: "groupSort = group", command: function() { sheet.sortRange(2, 0, 15, 1, true, [ { index: 0, ascending: sheet.getValue(1, 3), }, ], {groupSort: GC.Spread.Sheets.GroupSort.group}); }, }]; sheet.setStyle(1, 4, style); }
<!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/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/sorting.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }