SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage Data / Sort Data
In This Topic
    Sort Data
    In This Topic

    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.

    Using Code

    Select one of the buttons to sort the first column.

    JavaScript
    Copy Code 
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{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 Column1 by ascending at every button click.
    activeSheet.sortRange(-1, 0, -1, 1, true, [{index:0, ascending:true}]);
   });
 $("#button2").click(function(){
    //Sort Column1 by descending at every button click.
     activeSheet.sortRange(-1, 0, -1, 1, true, [
       {index:0, ascending:false}
     ]);
 });
//Add button controls to page
<input type="button" id="button1" value="button1"/>
<input type="button" id="button2" value="button2"/>

    Using Code

    Select the button to sort using the sort criteria specified by the sortInfo object.

    JavaScript
    Copy Code 
    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 is column 1 and the 2nd key is column 2.
        var sortInfo = [
            {index:0, ascending:true},
            {index:1, ascending:true}];
        ///Execute sorting which targets all rows based on the created sorting conditions.
        activeSheet.sortRange(0, -1, 6, -1, true, sortInfo);
    });

//Add button control to page
<input type="button" id="button1" value="button1"/>

    Ignore Hidden Data while Sorting

    You can also skip hidden data while sorting by using ignoreHidden option in API. A cell or a range is considered to be hidden when:

    The below example contains hidden rows (cells A5:A6) and displays how the data is sorted with different values of ignoreHidden:

    Original Data ignoreHidden is true ignoreHidden is false

    Using Code

    This example code shows how to sort data by skipping hidden rows.

    JavaScript
    Copy Code 
    // get the activesheet
var activeSheet = spread.getSheet(0);
// Set data
activeSheet.setValue(0, 0, "Name");
activeSheet.setValue(0, 1, "Age");
activeSheet.setValue(1, 0, "Rick");
activeSheet.setValue(1, 1, 52);
activeSheet.setValue(2, 0, "Tim");
activeSheet.setValue(2, 1, 50);
activeSheet.setValue(3, 0, "Jerry");
activeSheet.setValue(3, 1, 46);
activeSheet.setValue(4, 0, "Jack");
activeSheet.setValue(4, 1, 98);
activeSheet.setValue(5, 0, "Sandy");
activeSheet.setValue(5, 1, 99);
activeSheet.setValue(6, 0, "Smith");
activeSheet.setValue(6, 1, 42);
activeSheet.setValue(7, 0, "Greg");
activeSheet.setValue(7, 1, 41);
activeSheet.setValue(8, 0, "Sia");
activeSheet.setValue(8, 1, 36);
activeSheet.setValue(9, 0, "Jennet");
activeSheet.setValue(9, 1, 38);
activeSheet.setValue(10, 0, "Rachel");
activeSheet.setValue(10, 1, 37);
// Hide Row
activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

// Sort range i.e. "Age" column with ignoreHidden set to true
activeSheet.sortRange(1, 1, 10, 1, true, [{ index: 1, ascending: true }], { ignoreHidden: true });

    SpreadJS also allows you to sort grouped data by using groupSort enumeration. The grouped and unexpanded data is considered as hidden rows or columns. To know more about sorting grouped data, refer Sort with Grouped Data.

    The behavior of data when groupSort and ignoreHidden are used together, is explained in the below table:

    ignoreHidden=true ignoreHidden=false ignoreHidden undefined
    groupSort (group, child or full) Group sort works and hidden values are not ignored
    groupSort (flat) Ignores hidden values Does not ignore hidden values Ignores hidden values
    groupSort undefined Ignores hidden values Does not ignore hidden values

    If sorting range contains a group, then group sort is applied

    If sorting range does not contain a group, then hidden values are ignored

    This example code shows how to use ignoreHidden and groupSort together.

    JavaScript
    Copy Code 
    // set data
 activeSheet.setArray(3, 0, [
     [6221], [5125], ['Samsung'], [4348], [3432], ['LG'], [1928], [2290], ['Oppo'], [8939], [7006], ['Apple']
 ]);
 activeSheet.rowOutlines.group(3, 2);
 activeSheet.rowOutlines.group(6, 2);
 activeSheet.rowOutlines.group(9, 2);
 activeSheet.rowOutlines.group(12, 2);
 spread.resumePaint();

 // set rowFilter
 activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(3, 0, 13, 1)));

 // hide rows
 activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
 activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

 //1) If you want to use filter dialog to sort the data with enhanced group feature and ignoreHidden, you should use RangeSorting event
 spread.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
     // set GroupSort to full
     info.groupSort = GC.Spread.Sheets.GroupSort.full;
     // set ignoreHidden to true
     info.ignoreHidden = true;
 });

 //2) If you want to use api to sort the data with enhanced group feature and ignoreHidden, you should use this code
 // activeSheet.sortRange(3, 0, 13, 1, true, [{ index: 0, ascending: true }], { ignoreHidden: true, groupSort: GC.Spread.Sheets.GroupSort.full });

     

     

     

    Copyright © 2020 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page