SpreadJS 15
Features / Sort / Sort Data
In This Topic
    Sort Data
    In This Topic

    You can sort data in a spreadsheet and specify the 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).

    Sort Range in Ascending or Descending Order

    The sortRange method is used to sort data. The sortInfo object in the sortRange method specifies sort keys and ascending or descending order.

    The following GIF illustrates a column sorting in ascending and descending order.

    Column sorting

    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"/>

    Sort Multiple Ranges

    You can sort multiple ranges by specifying multiple sort keys in the sortInfo object.

     

    JavaScript
    Copy Code 
     $(document).ready(function ()
{
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {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:false}
        ];
        // Execute sorting which targets all rows based on the sorting conditions.
        spread.getActiveSheet().sortRange(0, 0, 6, 2, true, sortInfo);
    });
});

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

    Sort by Cell Background Color

    You can sort the cells by background color using the sortInfo.backColor option. The cells are grouped by position at the top or bottom of the selected range using the sortInfo.order option.

    The following GIF illustrates the green-colored cells being sorted at the bottom.

    JavaScript
    Copy Code 
    sheet.setArray(0, 0, [
    ["Order No.","Order Date","Product Type","Qty","Customer ID","Delivery Status"],
    ["1234", "11-Jul", "Electronic", "1", "861861", "Delivered"],
    ["1235", "12-Jul", "Clothing",   "4", "530317", "Past Due"],
    ["1236", "13-Jul", "Cleaning",   "2", "753904", "Past Due"],
    ["1237", "14-Jul", "Food Item",  "6", "623424", "Delivered"],
    ["1238", "15-Jul", "Electronic", "3", "214403", "Past Due"],
    ["1239", "16-Jul", "Clothing",   "7", "105146", "Due in 4 Days"],
    ["1240", "17-Jul", "Cleaning",   "3", "860876", "Past Due"],
    ["1241", "18-Jul", "Food Item",  "8", "126990", "Delivered"],
    ["1242", "19-Jul", "Electronic", "2", "505788", "Delivered"],
    ["1243", "20-Jul", "Clothing",   "5", "298332", "Due in 4 Days"]
]);
            
spread.getSheet(0).getCell(1, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(2, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(3, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(4, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(5, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(6, 5).backColor("#FFF2CC");            
spread.getSheet(0).getCell(7, 5).backColor("#FCE4D6");            
spread.getSheet(0).getCell(8, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(9, 5).backColor("#E2EFDA");            
spread.getSheet(0).getCell(10, 5).backColor("#FFF2CC");

var ColorList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
ColorList.items([
    { text: "Green", value: "green" },
    { text: "Red", value: "red" }, 
    { text: "Yellow", value: "yellow" }
]);
spread.getSheet(0).getCell(12, 2).cellType(ColorList);

var OrderList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
OrderList.items([
    { text: "Top", value: "top" },
    { text: "Bottom", value: "bottom" }
]);
spread.getSheet(0).getCell(12, 3).cellType(OrderList);
  
sortBackColor(spread.getSheet(0));
    
function sortBackColor (sheet){
    const CELL_COLOR_MAPPING = {
        green: "#E2EFDA",
        red: "#FCE4D6",
        yellow: "#FFF2CC",
    }
    sheet.setColumnWidth(3,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(12,2);
        var order = sheet.getValue(12,3);
        value = value ? value : "green";
        order = order ? order : "top";
        var color = CELL_COLOR_MAPPING[value];
        sheet.sortRange(1,0,10,6,true,[{
            index:5,
            backColor:color,
            order:order,
        }])
    }
    }];
    sheet.setStyle(13,3,style);
}

    Using SpreadJS Designer

    You can sort by background color by choosing the "Cell Color" option in "SortOn" dropdown. The Sort dialog can be accessed from Home > Editing > Sort & Filter > "Custom Sort" option.

    Sort by Font Color

    You can sort the cells by font color using the sortInfo.foreColor option. The cells are grouped by position at the top or bottom of the selected range using the sortInfo.order option.

    The following GIF illustrates red-colored cells being sorted at the top.

    JavaScript
    Copy Code 
    spread.getSheet(1).setArray(0, 0, [
    ["Order No.","Order Date","Product Type","Qty","Customer ID","Delivery Status"],
    ["1234", "11-Jul", "Electronic", "1", "861861", "Delivered"],
    ["1235", "12-Jul", "Clothing",   "4", "530317", "Past Due"],
    ["1236", "13-Jul", "Cleaning",   "2", "753904", "Past Due"],
    ["1237", "14-Jul", "Food Item",  "6", "623424", "Delivered"],
    ["1238", "15-Jul", "Electronic", "3", "214403", "Past Due"],
    ["1239", "16-Jul", "Clothing",   "7", "105146", "Due in 4 Days"],
    ["1240", "17-Jul", "Cleaning",   "3", "860876", "Past Due"],
    ["1241", "18-Jul", "Food Item",  "8", "126990", "Delivered"],
    ["1242", "19-Jul", "Electronic", "2", "505788", "Delivered"],
    ["1243", "20-Jul", "Clothing",   "5", "298332", "Due in 4 Days"]
]);

spread.getSheet(1).name("Sort by font color");
spread.getSheet(1).getCell(1, 5).foreColor("#548235");            
spread.getSheet(1).getCell(2, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(3, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(4, 5).foreColor("#548235");            
spread.getSheet(1).getCell(5, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(6, 5).foreColor("#BF8F00");            
spread.getSheet(1).getCell(7, 5).foreColor("#C65911");            
spread.getSheet(1).getCell(8, 5).foreColor("#548235");            
spread.getSheet(1).getCell(9, 5).foreColor("#548235");            
spread.getSheet(1).getCell(10, 5).foreColor("#BF8F00");

var FontColorList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
FontColorList.items([
    { text: "Green", value: "green" },
    { text: "Red", value: "red" }, 
    { text: "Yellow", value: "yellow" }
]);
spread.getSheet(1).getCell(12, 2).cellType(FontColorList);

var FontColorOrder = new GC.Spread.Sheets.CellTypes.RadioButtonList();
FontColorOrder.items([
    { text: "Top", value: "top" },
    { text: "Bottom", value: "bottom" }
]);
spread.getSheet(1).getCell(12, 3).cellType(FontColorOrder);
  
sortFontColor(spread.getSheet(1));

function sortFontColor (sheet){
    const FONT_COLOR_MAPPING = {
        green:"#548235",
        red:"#C65911",
        yellow: "#BF8F00"
    }
    sheet.setColumnWidth(3,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(12,2);
                var order = sheet.getValue(12,3);
                value = value ? value : "green";
                order = order ? order : "top";
                var color = FONT_COLOR_MAPPING[value];
                sheet.sortRange(1,0,10,6,true,[{
                    index:5,
                    fontColor:color,
                    order:order
                }])
            }
        }];
    sheet.setStyle(13,3,style);
}

    Using SpreadJS Designer

    You can sort by font color by choosing the "Font Color" option in "SortOn" dropdown. The Sort dialog can be accessed from Home > Editing > Sort & Filter > "Custom Sort" option.

    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 © 2022 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page