概述
本 Demo 展示了 SpreadJS 的多种数据排序功能，通过 8 个工作表分别演示了自定义比较函数排序、组排序、忽略隐藏值排序、按颜色排序以及保持排序状态等功能。
实现思路
加载预定义的表格数据（使用 fromJSON 加载）
为每个工作表设置不同的排序场景和交互按钮
使用 sortRange 方法实现各种排序逻辑
通过 RangeSorting 事件和 compareFunction 实现自定义排序规则
使用 getSortState 方法保存和恢复排序状态
代码解析
自定义比较函数排序
Demo 通过 compareFunction 参数实现自定义排序规则。例如按年级排序时，使用预定义的顺序进行比较：
这段代码为年级列设置自定义排序，比较函数返回两个值在预定义列表中的位置差。
通过事件设置自定义排序
Demo 还展示了通过 RangeSorting 事件设置排序规则的方式：
这种方式适用于用户通过 UI（右键菜单或筛选框）触发排序时，动态设置比较规则。
按单元格背景色排序
通过 backColor 参数指定目标颜色，order 参数指定将该颜色的单元格移动到顶部还是底部。
保持排序状态并自动重新排序
getSortState 方法获取最后一次排序的状态信息，之后调用 sortRange() 不传参数时，会基于该状态重新排序。
运行效果
点击第一个工作表中的按钮，可以按姓名最后一个字、年级或 T 恤尺码进行自定义排序
在第二个工作表通过右键菜单或筛选框排序时，会自动按域名后缀或 IP 地址规则排序
第三个工作表演示组排序效果
第四个工作表可以测试忽略隐藏值的不同排序行为
第五个工作表支持按单元格背景色排序
第六个工作表支持按字体颜色排序
第七个工作表展示大纲列功能
第八个工作表在单元格值改变时自动重新排序，点击"获取排序状态"按钮可查看当前排序信息
API 参考
sortRange 方法
row、column：起始行列索引
rowCount、columnCount：行列数量
byRows：是否按行排序
sortInfo：排序条件数组，包含 index（列索引）、ascending（是否升序）、compareFunction（自定义比较函数）、backColor（背景色）、fontColor（字体颜色）、order（位置）等
sortOption：排序选项，包含 groupSort（组排序模式）和 ignoreHidden（是否忽略隐藏值）
getSortState 方法
返回最后一次排序的状态信息，包含 row、col、rowCount、colCount、byRow、sortConditions 等属性。
RangeSorting 事件
当用户通过 UI 触发排序时触发，可通过事件参数设置 compareFunction、groupSort、ignoreHidden 等选项。
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
spread.suspendPaint();
spread.fromJSON(sjsData);
initSheet0(spread.getSheet(0));
initSheet1(spread.getSheet(1));
initSheet2(spread.getSheet(2));
initSheet3(spread.getSheet(3));
initSheet4(spread.getSheet(4));
initSheet5(spread.getSheet(5));
initSheet6(spread.getSheet(6));
initSheet7(spread.getSheet(7));
initSortStatePanel(spread);
spread.resumePaint();
};
const CELL_COLOR_MAPPING = {
red: "#FF0000",
green: "#00B050",
blue: "#00B0F0",
gradient: {
degree: 90,
stops: [
{
color: "#ffffff",
position: 0,
},
{
color: "#5B9BD5",
position: 1,
}
]
},
pattern: {
patternColor: "",
type: 14,
backgroundColor: ""
}
}
const FONT_COLOR_MAPPING = {
red: "#FF0000",
blue: "#00B0F0",
purple: "#7030A0",
green: "#92D050",
null: ""
}
function initSheet0(sheet) {
var style = sheet.getStyle(4, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "按姓名最后一个字排序",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 1,
ascending: true,
compareFunction: function (value1, value2) {
var str1 = value1[2], str2 = value2[2];
return str1.localeCompare(str2);
}
},
])
},
}];
sheet.setStyle(4, 7, style);
var grade = ["一年级", "二年级", "三年级", "四年级"];
var clothesSize = ["XXS", "XS", "S", "M", "L", "XL", "XXL"];
function compareList(obj1, obj2, list) {
var index1 = list.indexOf(obj1), index2 = list.indexOf(obj2);
if (index1 > index2) {
return 1;
} else if (index1 < index2) {
return -1;
} else {
return 0;
}
}
style = sheet.getStyle(5, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "按年级排序",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 2,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, grade);
}
},
])
},
}];
sheet.setStyle(5, 7, style);
sheet.setRowHeight(5, 35);
style = sheet.getStyle(6, 7);
style.cellButtons = [{
useButtonStyle: true,
caption: "按T恤尺码排序",
width: 222,
command: function () {
sheet.sortRange(4, 0, 27, 5, true, [
{
index: 3,
ascending: true,
compareFunction: function (value1, value2) {
return compareList(value1, value2, clothesSize);
}
},
])
},
}];
sheet.setStyle(6, 7, style);
sheet.setRowHeight(6, 35);
}
function initSheet1(sheet) {
function sortDomain(value1, value2) {
var str1 = value1.substr(value1.lastIndexOf(".") + 1), str2 = value2.substr(value2.lastIndexOf(".") + 1);
return str1.localeCompare(str2);
}
function sortIP(ip1, ip2) {
var value1 = ip1.split("."), value2 = ip2.split(".");
for (var i = 0; i < 4; i++) {
var num1 = parseInt(value1[i]), num2 = parseInt(value2[i]);
if (num1 > num2) {
return 1;
} else if (num1 < num2) {
return -1;
}
}
return 0;
}
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
if (info.col === 0) {
info.compareFunction = sortDomain;
} else if (info.col === 1) {
info.compareFunction = sortIP;
}
});
}
function initSheet2(sheet) {
sheet.bind(GC.Spread.Sheets.Events.RangeSorting, function (e, info) {
info.groupSort = GC.Spread.Sheets.GroupSort.full;
});
}
function initSheet3(sheet) {
sheet.outlineColumn.options({
columnIndex: 0,
showImage: false,
showIndicator: true,
showCheckBox: true,
maxLevel: 10
});
}
function initSheet4(sheet) {
var style = sheet.getStyle(1, 4);
style.cellButtons = [
{
useButtonStyle: true,
caption: "ignoreHidden = true",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: true });
},
}, {
useButtonStyle: true,
caption: "ignoreHidden = false",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { ignoreHidden: false });
},
}, {
useButtonStyle: true,
caption: "groupSort = group",
command: function () {
sheet.sortRange(2, 0, 15, 1, true, [
{
index: 0,
ascending: sheet.getValue(1, 3) === '1',
},
], { groupSort: GC.Spread.Sheets.GroupSort.group });
},
}];
sheet.setStyle(1, 4, style);
}
function initSheet5(sheet) {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [{
caption: "按单元格颜色排序",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = CELL_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
backColor: color,
order: order,
}])
}
}];
sheet.setStyle(16, 4, style);
}
function initSheet6(sheet) {
sheet.setColumnWidth(4, 120);
var style = new GC.Spread.Sheets.Style();
style.cellButtons = [{
caption: "按字体颜色排序",
useButtonStyle: true,
width: 120,
command: function (sheet) {
var value = sheet.getValue(15, 3);
var order = sheet.getValue(15, 4);
value = value ? value : "red";
order = order ? order : "top";
var color = FONT_COLOR_MAPPING[value];
sheet.sortRange(3, 2, 10, 1, true, [{
index: 2,
fontColor: color,
order: order
}])
}
}];
sheet.setStyle(16, 4, style);
}
function initSheet7(sheet) {
sheet.sortRange(2, 2, 10, 1, true, [{ index: 2, ascending: false, compareFunction: undefined }]);
sheet.setSelection(2, 2, 10, 1);
sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
let sortState = sheet.getSortState();
if (inSortStateRange(sortState, info.row, info.col)) {
sheet.sortRange();
}
});
}
function initSortStatePanel(spread) {
_getElementById('get_SortState_Btn').addEventListener('click', function () {
let sheet = spread.getActiveSheet();
let sortState = sheet.getSortState();
if (!sortState) {
return;
}
let { row, col, rowCount, colCount, byRow, sortConditions } = sortState;
if (sortState) {
let sortStateStr = '';
sortStateStr += "row: " + row + ",\n";
sortStateStr += "col: " + col + ",\n";
sortStateStr += "rowCount: " + rowCount + ",\n";
sortStateStr += "colCount: " + colCount + ",\n";
sortStateStr += "byRow: " + byRow + ",\n";
sortStateStr += "sortCondition: " + JSON.stringify(sortConditions); +"}\n";
document.getElementById("showEventArgs").value = sortStateStr;
}
});
}
function inSortStateRange(sortState, row, col) {
if (row >= sortState.row && row < sortState.row + sortState.rowCount && col >= sortState.col && col < sortState.col + sortState.colCount) {
return true;
}
return false;
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/sorting.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div id="settingsDiv">
<br/>
<label>此文本框显示最后一次排序操作的排序状态信息。</label>
<br/>
<textarea id="showEventArgs" cols="85" rows="8" style="max-width: 98%"></textarea>
<div class="option-row">
<input type="button" id="get_SortState_Btn" value="获取排序状态"/>
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
