SpreadJS and Sorting

SpreadJS supports sorting data programmatically. Filters and tables have UI sorting options. Sorting data makes it easier to find information in a column or row. You can sort data in the widget and specify a column or row index to sort on as well as the sort criteria. You can also specify multiple sort keys (sort by a specified column or row first, then another column or row, and so on). Use the sortRange method to sort data. Use the sortInfo object in the sortRange method to specify sort keys and ascending or descending order and by row or column. The row filter has a sort option that allows the user to sort the filtered data or you can use the sortColumn method. Tables also have options that allow the user to sort data as shown in the following image. SpreadJSsort Table Sorting The RangeSorting or RangeSorted event occurs when automatically sorting (with a filter or a table). This example sorts the first column in ascending or descending order. Select a button to sort. SpreadJSsort1 Column Sorting 

<!DOCTYPE html><html>  
<head>  
<title>SpreadJS Sorting</title>  
<link type="text/css" href="./css/gcspread.sheets.8.40.20151.0.css" rel="stylesheet" />  
<script src="http://code.jquery.com/jquery-2.0.2.js" type="text/javascript"></script>  
<script type="text/javascript" src="./scripts/gcspread.sheets.all.8.40.20151.0.min.js"></script>  
<script type="text/javascript">  
$(document).ready(function () {  
var spread = new GcSpread.Sheets.Spread($("#ss").get(0),{sheetCount:3});  
var activeSheet = spread.getActiveSheet();  
activeSheet.setValue(0, 0, 10);  
activeSheet.setValue(1, 0, 100);  
activeSheet.setValue(2, 0, 50);  
activeSheet.setValue(3, 0, 40);  
activeSheet.setValue(4, 0, 80);  
activeSheet.setValue(5, 0, 1);  
activeSheet.setValue(6, 0, 65);  
activeSheet.setValue(7, 0, 20);  
activeSheet.setValue(8, 0, 30);  
activeSheet.setValue(9, 0, 35);  

$("#button1").click(function(){  
//Sort Column 1 in ascending order on button click.  
$("#ss").data("spread").getActiveSheet().sortRange(-1, 0, -1, 1, true, [  
{index:0, ascending:true}  
]);  
});  
$("#button2").click(function(){  
//Sort Column 1 in descending order on button click.  
$("#ss").data("spread").getActiveSheet().sortRange(-1, 0, -1, 1, true, [  
{index:0, ascending:false}  
]);  
});  
});</script>  
</head>  
<body><div id="ss" style="width: 500px; height: 300px; border: 1px solid gray">  
</div>  
<input type="button" id="button1" value="button1"/>  
<input type="button" id="button2" value="button2"/>  
</body>  
</html>

You can specify multiple sort conditions when sorting. This example sorts rows using multiple sort keys.


$(document).ready(function ()   
{  
var spread = new GcSpread.Sheets.Spread($("#ss").get(0),{sheetCount:3});  
var activeSheet = spread.getActiveSheet();  
activeSheet.setRowCount(6);  
activeSheet.setValue(0, 0, 10);  
activeSheet.setValue(1, 0, 100);  
activeSheet.setValue(2, 0, 100);  
activeSheet.setValue(3, 0, 10);  
activeSheet.setValue(4, 0, 5);  
activeSheet.setValue(5, 0, 10);  
activeSheet.setValue(0, 1, 10);  
activeSheet.setValue(1, 1, 40);  
activeSheet.setValue(2, 1, 10);  
activeSheet.setValue(3, 1, 20);  
activeSheet.setValue(4, 1, 10);  
activeSheet.setValue(5, 1, 40);  

$("#button1").click(function(){  
//Create a SortInfo object where 1st Key:Column1/2nd Key:Column2.  
var sortInfo = [  
{index:0, ascending:true},  
{index:1, ascending:true}];  
///Execute sorting which targets all rows based on the created sorting conditions.  
$("#ss").data("spread").getActiveSheet().sortRange(0, -1, 6, -1, true, sortInfo);  
});

This example uses the RangeSorted event.


activeSheet.setValue(0, 0, 10);  
activeSheet.setValue(1, 0, 100);  
activeSheet.setValue(2, 0, 50);  
activeSheet.setValue(3, 0, 40);  
activeSheet.setValue(4, 0, 80);  
activeSheet.setValue(5, 0, 1);  
activeSheet.setValue(6, 0, 65);  
activeSheet.setValue(7, 0, 20);  
activeSheet.setValue(8, 0, 30);  
activeSheet.setValue(9, 0, 35);  
var cellrange =new GcSpread.Sheets.Range(0, 0, 5, 1);  
var hideRowFilter =new GcSpread.Sheets.HideRowFilter(cellrange);  
activeSheet.rowFilter(hideRowFilter);  

$("#ss").data("spread").getActiveSheet().bind(GcSpread.Sheets.Events.RangeSorted, function (e, info) {  
alert("Col (" + info.col + ", " + info.ascending +")");  
});

This example uses the sortColumn method. 

//Create a custom condition.function CustomFilter(){};  
CustomFilter.prototype = {  
conditionType: "CustomFilter",  
evaluate: function (evaluator, row, col) {  
var value = evaluator.getValue(row, col);if(value !== null && value >= 10 && value <= 50){  
//Return True only when the following conditions are satisfied.  
// (1)Values are entered.  
// (2)Values are not lower than 10.  
// (3)Values are not greater than 50.  
return true;  
}else{  
return false;  
}  
}  
};  
activeSheet.setValue(0, 0, 10);  
activeSheet.setValue(1, 0, 100);  
activeSheet.setValue(2, 0, 50);  
activeSheet.setValue(3, 0, 40);  
activeSheet.setValue(4, 0, 80);  
activeSheet.setValue(5, 0, 1);  
activeSheet.setValue(6, 0, 65);  
activeSheet.setValue(7, 0, 20);  
activeSheet.setValue(8, 0, 30);  
activeSheet.setValue(9, 0, 35);//Set a row Filter.  
var rowFilter = new GcSpread.Sheets.HideRowFilter(new GcSpread.Sheets.Range(0, 0, 7, 1));  
activeSheet.rowFilter(rowFilter);  
rowFilter.addFilterItem(0, new CustomFilter());  
rowFilter.filter(0);  
rowFilter.sortColumn(0, true);

MESCIUS inc.

comments powered by Disqus