排序数据

您可以对电子表格中的数据进行排序，并指定要排序的列或行索引以及排序条件。您还可以指定多个排序键（首先按指定的列或行排序，然后按另一列或行排序，依此类推）。

按升序或降序对范围进行排序

使用 sortRange 方法对数据进行排序。在 sortRange 方法中使用 sortInfo 对象来指定排序键和升序或降序。

codesort

示例代码

选择一个按钮以对第一列进行排序。

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(){
     //每次单击按钮时，按升序对Column1进行排序。
    activeSheet.sortRange(-1, 0, -1, 1, true, [{index:0, ascending:true}]);
   });
 $("#button2").click(function(){
    //每次单击按钮时降序对Column1进行排序。
     activeSheet.sortRange(-1, 0, -1, 1, true, [
       {index:0, ascending:false}
     ]);
 });
//向页面添加按钮控件
<input type="button" id="button1" value="button1"/>
<input type="button" id="button2" value="button2"/>

对多个范围进行排序

您可以通过在sortInfo对象中指定多个排序键来对多个范围进行排序。

sort-multiple-keys

本示例使用多个排序键对行进行排序。

$(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()
   {
  
      // 创建一个SortInfo对象，其中1st Key：Column1 / 2nd Key：Column2。
      var sortInfo =
       [
            {index:0, ascending:true},
            {index:1, ascending:true}
       ];
       
      // 执行基于排序条件针对所有行的排序。
      spread.getActiveSheet().sortRange(0, 0, 6, 2, true, sortInfo);
   });
});

按单元格背景颜色排序

您可以使用sortInfo.backColor选项按背景颜色对单元格进行排序。

使用sortInfo.order选项按所选范围顶部或底部的位置对单元格进行分组。

sort-backcolor

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

按字体颜色排序

您可以使用sortInfo.foreColor选项按字体颜色对单元格进行排序。

使用sortInfo.order选项按所选范围顶部或底部的位置对单元格进行分组。

sort-forecolor

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

保留上次排序状态

可以在对数据进行排序之前保留数据的默认状态或上次应用的状态，

无论何时应用不同的排序顺序，该状态都不会更改。

可以在需要时使用getSortState方法获取当前工作表的最后排序状态，并且可以使用sortRange方法对数据范围重新排序。

以下代码示例实现工作表的ValueChanged事件，该事件调用getSortRange方法来恢复排序状态，然后在排序区域中检测到任何更改时使用sortRange方法。

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

type=warning
注意：
此功能支持 SSJSON、SJS 和 XLSX 的导出/导入，但不支持 ExcelIO
使用worksheet.getsortState()进行过滤不会返回排序状态

排序时忽略隐藏数据

您还可以通过使用API中的 ignoreHidden 选项在排序时跳过隐藏数据。在以下情况下，单元格或范围被认为是隐藏的：

行的高度或列的宽度为0
行或列被隐藏
行或列被筛选掉
某些行或列被分组且未展开

以下示例包含隐藏的行（单元格A5：A6），并显示如何使用不同的 ignoreHidden 值对数据进行排序：

原始数据	ignoreHidden 为 true	ignoreHidden 为 false

示例代码

此示例代码显示了如何通过跳过隐藏的行对数据进行排序。

// 获取活跃表单
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);
// 隐藏行
activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

// 排序范围，即“ignoreHidden”设置为true的“Age”列
activeSheet.sortRange(1, 1, 10, 1, true, [{ index: 1, ascending: true }], { ignoreHidden: true });

SpreadJS还允许您使用 groupSort 枚举对分组的数据进行排序。分组和未扩展的数据被视为隐藏的行或列。要了解有关对分组数据进行排序的更多信息，请参阅对分组数据进行排序。

下表说明了 groupSort 和 ignoreHidden 一起使用时的数据行为：

	ignoreHidden=true	ignoreHidden=false	ignoreHidden undefined
groupSort (组, 子级别或全级别)	组排序工作和隐藏值不会被忽略
groupSort (flat)	忽略隐藏的值	不忽略隐藏的值	忽略隐藏的值
groupSort undefined	忽略隐藏的值	不忽略隐藏的值	如果排序范围包含组，则应用组排序如果排序范围不包含组，则忽略隐藏值

ignoreHidden=true

ignoreHidden=false

ignoreHidden undefined

groupSort (组, 子级别或全级别)

组排序工作和隐藏值不会被忽略

groupSort (flat)

忽略隐藏的值

不忽略隐藏的值

忽略隐藏的值

groupSort undefined

忽略隐藏的值

不忽略隐藏的值

如果排序范围包含组，则应用组排序

如果排序范围不包含组，则忽略隐藏值

此示例代码显示了如何一起使用 ignoreHidden 和 groupSort 。

// 设置数据
 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();

 // 设置行筛选
 activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(3, 0, 13, 1)));

 //隐藏行
 activeSheet.setRowHeight(4, 0.0, GC.Spread.Sheets.SheetArea.viewport);
 activeSheet.setRowHeight(5, 0.0, GC.Spread.Sheets.SheetArea.viewport);

 //1) 如果要使用筛选对话框对具有增强组功能和ignoreHidden的数据进行排序，则应使用RangeSorting事件
 spread.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
     // 将GroupSort设置为full
     info.groupSort = GC.Spread.Sheets.GroupSort.full;
     // 将ignoreHidden设置为true
     info.ignoreHidden = true;
 });

 //2) 如果要使用api对具有增强的组功能和ignoreHidden的数据进行排序，则应使用此代码
 // activeSheet.sortRange(3, 0, 13, 1, true, [{ index: 0, ascending: true }], { ignoreHidden: true, groupSort: GC.Spread.Sheets.GroupSort.full });