Sort Data

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.

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 the page

Sort Multiple Ranges

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

$(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

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.

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 the "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.

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 the "SortOn" dropdown. The Sort dialog can be accessed from Home > Editing > Sort & Filter > "Custom Sort" option.

Keep the Last Sort State

You can preserve the default or last-applied state of data before you sort it, which does not change whenever a different sort order is applied. You can use the getSortState method to get the current worksheet’s last sort state whenever required and for reordering the range of data, the sortRange method can be used.

Refer to the following image that depicts a preserved sort state.

lastsortstate

The following code sample implements the sheet’s ValueChanged event which calls the getSortRange method to restore the sorted state and then uses the sortRange method whenever any change is detected in the sorting area.

// To call automatic sorting every time any value is changed.
sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
     let sortState = sheet.getSortState();
     if (inSortStateRange(sortState, info.row, info.col)) {
         sheet.sortRange();
     }
});

Note:
This feature supports export/import for SSJSON, SJS, and XLSX, but ExcelIO is not supported.
Filtering with worksheet.getsortState() does not return the sort state.

Ignore Hidden Data while Sorting

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

A row's height or column's width is 0
A row or column is hidden
A row or column is filtered out
Some rows or columns are grouped and unexpanded

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

The following code sample shows how to sort data by skipping hidden rows.

// 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 to 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 the sorting range contains a group, then group sort is applied. If the sorting range does not contain a group, then hidden values are ignored

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 the sorting range contains a group, then group sort is applied.

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

The following code sample shows how to use ignoreHidden and groupSort together.

// 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 });