TableSheet supports multiple column sorting through the allowSorts option on ITableSheetOptions.
When allowSorts is enabled, users can sort by multiple columns simultaneously, providing more flexible data organization.
APIs
Use tableSheet.sort(sortInfos?) to get or set the active sort definitions, and use tableSheet.removeSort() to clear all active sorts.
When sortInfos is passed, TableSheet clears the previous sort definitions and applies the supplied definitions in one operation. Calling tableSheet.sort() without arguments returns a cloned list of the active sort definitions, so changing the returned array does not update the TableSheet until it is passed back to tableSheet.sort(sortInfos).
Each sort definition uses ITableSheetSortInfo:
field is the TableSheet field name to sort.
ascending controls the direction and defaults to true.
compare provides custom comparison logic by registered function name or by direct function reference. A registered function name is recommended when the sort definition needs to be serialized. A direct function reference works at runtime but is lost after serialization.
Multiple sort definitions can be applied together:
For custom sorting, register a custom function and reference it by name, or pass a function directly:
For generated columns, read the column information from the data view and use the column info value as the field name:
Commands
Use the TableSheet sort commands through the command manager. Each command supports undo and redo.
TableSheetSortColumn sorts one column and replaces the other active sorts.
Options: sheetName, col, ascending, and optional compare.
TableSheetAddSortColumn adds a column to the current multiple-sort state instead of replacing the current sorts.
Options: sheetName, col, ascending, and optional compare.
TableSheetRemoveSortColumn removes the sort from one column.
Options: sheetName and col.
Events
TableSheet raises sort events when sorting or clearing sorting from the filter dialog or related TableSheet sort commands.
TableSheetSorting occurs before a column is sorted. It is cancellable by setting args.cancel = true.
Payload fields: sheetName, col, ascending, compare, and cancel.
TableSheetSorted occurs after a column has been sorted.
Payload fields: sheetName, col, and ascending.
TableSheetSortClearing occurs before sorting is cleared from a column. It is cancellable by setting args.cancel = true.
Payload fields: sheetName, col, and cancel.
TableSheetSortCleared occurs after sorting has been cleared from a column.
Payload fields: sheetName and col.
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
var tableName = "Employee";
var baseApiUrl = getBaseApiUrl();
var apiUrl = baseApiUrl + "/" + tableName;
var sheet, spread;
var columnAllowSort = {
id: true, firstName: true, lastName: true, birth: true,
state: true, dept: true, title: true, salary: true
};
var columnIndexMap = {
id: 0, state: 1, dept: 2, title: 3,
firstName: 4, lastName: 5, birth: 6, salary: 7
};
var titleCompareFunctionName = "SortTitleLocaleCompareHandler";
function SortTitleLocaleCompareHandler() {
GC.Spread.CalcEngine.Functions.Function.apply(this, [titleCompareFunctionName, 1, 1]);
}
SortTitleLocaleCompareHandler.prototype = new GC.Spread.CalcEngine.Functions.Function();
SortTitleLocaleCompareHandler.prototype.evaluate = function (item1, item2, ascending) {
var title1 = getTitleValue(item1);
var title2 = getTitleValue(item2);
var title1IsBlank = isBlankTitle(title1);
var title2IsBlank = isBlankTitle(title2);
if (title1IsBlank && title2IsBlank) {
return 0;
}
if (title1IsBlank) {
return 1;
}
if (title2IsBlank) {
return -1;
}
var result = String(title1).localeCompare(String(title2));
return ascending === false ? -result : result;
};
function getTitleValue(item) {
if (item && typeof item === "object" && Object.prototype.hasOwnProperty.call(item, "title")) {
return item.title;
}
return item;
}
function isBlankTitle(value) {
return value === "" || value === null || value === undefined;
}
function isTitleColumn(tableSheet, col) {
var dataView = tableSheet && tableSheet.getDataView && tableSheet.getDataView();
var column = dataView && dataView.getColumn && dataView.getColumn(col);
return column && column.value === "title";
}
function applyInitialSort(tableSheet) {
if (!tableSheet || !tableSheet.sort) {
return;
}
tableSheet.sort([
{ field: "state", ascending: false },
{ field: "dept", ascending: true },
{ field: "title", ascending: true, compare: titleCompareFunctionName }
]);
}
window.onload = function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread();
bindEvents();
};
function initSpread() {
spread = GC.Spread.Sheets.findControl(document.getElementById("ss"));
spread.suspendPaint();
//1. init a sheet
spread.clearSheets();
spread.clearSheetTabs();
sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
sheet.options.allowSorts = getElementById("allowSorts").checked;
var data = generateData(+getElementById("dataRows").value);
var timeBeforeCreate = new Date();
var dataManager = spread.dataManager();
var employeeTable = dataManager.addTable("employeeTable", {
data: data.employees
});
var departmentTable = dataManager.addTable("departmentTable", {
data: data.departments
});
dataManager.addRelationship(employeeTable, "dept", "department", departmentTable, "dept_no", "employees");
dataManager.addRelationship(departmentTable, "leader_id", "manager", employeeTable, "id", "a");
spread.resumePaint();
var numericStyle = new GC.Spread.Sheets.Style();
numericStyle.formatter = "$ #,##0.00";
var formatStringStyle = new GC.Spread.Sheets.Style();
formatStringStyle.formatter = 'yyyy-mm-dd';
var cols = [
{ value: 'id', caption: 'ID', allowSort: columnAllowSort.id },
{ value: 'state', caption: 'State', width: 100, allowSort: columnAllowSort.state },
{ value: 'dept', caption: 'Department No', width: 130, allowSort: columnAllowSort.dept },
{ value: 'title', caption: 'Title', width: 120, allowSort: columnAllowSort.title },
{ value: 'firstName', caption: 'First Name', width: 100, allowSort: columnAllowSort.firstName },
{ value: 'lastName', caption: 'Last Name', width: 100, allowSort: columnAllowSort.lastName },
{ value: 'birth', caption: 'Birthday', width: 100, style: formatStringStyle, allowSort: columnAllowSort.birth },
{ value: 'salary', caption: 'Salary', style: numericStyle, width: 100, allowSort: columnAllowSort.salary },
];
var employeeView = employeeTable.addView("employeeView", cols, undefined);
employeeView.fetch().then(function (args) {
sheet.suspendPaint();
sheet.setDataView(employeeView);
sheet.resumePaint();
var timeGap = new Date() - timeBeforeCreate;
getElementById('showEventArgs').value = "Fetch data and paint - " + timeGap + " ms";
var sortApiStart = new Date();
applyInitialSort(sheet);
getElementById('showEventArgs').value = getElementById('showEventArgs').value + "\r\nSort - " + (new Date() - sortApiStart) + " ms";
});
}
function randomFromList(list) {
return list[~~(Math.random() * list.length)];
}
function generateData(itemCount) {
var data = {employees:[], departments: departments};
var states = ["Texas", "New York", "Florida", "Washington", "Ohio"];
var department_id = ["D001", "D002", "D003", "D004", "D005", "D006", "D007", "D008", "D009"];
var title = ["Senior Engineer", "Staff", "Engineer", "Senior Staff", "Assistant Engineer", "Technique Leader", "Manager"];
for (var i = 0; i < itemCount; i++) {
var date = new Date(parseInt(Math.random() * 12052666) * 24 * 3600); //The timestamp
date.setHours(0,0,0,0);
var item = {
id: i + 1,
firstName: randomFromList(firstNames),
lastName: randomFromList(lastNames),
birth: date,
state: randomFromList(states),
dept: i < 9 ? department_id[i] : randomFromList(department_id),
title: i < 9 ? "Manager" : randomFromList(title),
salary: 3000 + parseInt(Math.random() * 100) * 500,
};
data.employees.push(item);
}
return data;
}
function bindEvents() {
var showButton = document.getElementById('setDataSource');
showButton.addEventListener('click', function () {
initSpread();
});
var allowSortsCheckbox = document.getElementById('allowSorts');
allowSortsCheckbox.addEventListener('change', function () {
if (sheet) {
sheet.options.allowSorts = allowSortsCheckbox.checked;
}
});
var columnSelect = document.getElementById('columnSelect');
var columnAllowSortCheckbox = document.getElementById('columnAllowSort');
columnSelect.addEventListener('change', function () {
columnAllowSortCheckbox.checked = columnAllowSort[columnSelect.value];
});
columnAllowSortCheckbox.addEventListener('change', function () {
var colName = columnSelect.value;
columnAllowSort[colName] = columnAllowSortCheckbox.checked;
var colIndex = columnIndexMap[colName];
spread.commandManager().execute({
cmd: "ModifyColumn",
sheetName: "TableSheet1",
col: colIndex,
column: { value: colName, allowSort: columnAllowSort[colName] }
});
});
var sortStart;
spread.addCustomFunction(new SortTitleLocaleCompareHandler());
spread.bind(GC.Spread.Sheets.Events.TableSheetSorting, function(e, args) {
sortStart = new Date();
if (isTitleColumn(args.sheet || sheet, args.col)) {
args.compare = titleCompareFunctionName;
}
});
spread.bind(GC.Spread.Sheets.Events.TableSheetSorted, function(e, args) {
getElementById('showEventArgs').value = getElementById('showEventArgs').value + "\r\nSort - " + (new Date() - sortStart) + " ms";
if (document.getElementById('sortapi-tab').style.display === 'block') {
handleReadSort();
}
});
var sortClearStart;
spread.bind(GC.Spread.Sheets.Events.TableSheetSortClearing, function(e, args) {
sortClearStart = new Date();
});
spread.bind(GC.Spread.Sheets.Events.TableSheetSortCleared, function(e, args) {
var tableSheet = args.sheet || sheet;
var sortInfos = tableSheet && tableSheet.sort ? tableSheet.sort() : [];
var label = sortInfos.length > 1 ? "Clear & Sort" : "Clear Sort";
getElementById('showEventArgs').value = getElementById('showEventArgs').value + "\r\n" + label + " - " + (new Date() - sortClearStart) + " ms";
if (document.getElementById('sortapi-tab').style.display === 'block') {
handleReadSort();
}
});
// Sort API tab events
getElementById('addSortEntry').addEventListener('click', function () {
addSortEntryRow(getFirstUnusedSortField(), true);
});
getElementById('applySort').addEventListener('click', handleApplySort);
getElementById('clearSort').addEventListener('click', handleClearSort);
// Initialize first tab
document.querySelector('.options-tab-link').click();
}
function getElementById (domId) {
return document.getElementById(domId);
}
// Tab switching
function openOptionsTab(event, tabId) {
var i, tabcontent, tablinks;
tabcontent = document.getElementsByClassName("tab-content");
for (i = 0; i < tabcontent.length; i++) {
tabcontent[i].style.display = "none";
}
tablinks = document.getElementsByClassName("options-tab-link");
for (i = 0; i < tablinks.length; i++) {
tablinks[i].className = tablinks[i].className.replace(" active", "");
}
document.getElementById(tabId).style.display = "block";
event.currentTarget.className += " active";
if (tabId === 'sortapi-tab') {
handleReadSort();
}
}
var sortFieldCaptions = {
id: 'ID', state: 'State', dept: 'Department No', title: 'Title',
firstName: 'First Name', lastName: 'Last Name', birth: 'Birthday', salary: 'Salary'
};
var sortFields = ['id', 'state', 'dept', 'title', 'firstName', 'lastName', 'birth', 'salary'];
function getUsedSortFields(excludeRow) {
var rows = getElementById('sortEntries').getElementsByClassName('sort-entry-row');
var usedFields = {};
for (var i = 0; i < rows.length; i++) {
if (excludeRow && rows[i] === excludeRow) {
continue;
}
var fieldSelect = rows[i].querySelector('.sort-field');
if (fieldSelect && fieldSelect.value) {
usedFields[fieldSelect.value] = true;
}
}
return usedFields;
}
function getFirstUnusedSortField() {
var usedFields = getUsedSortFields();
for (var i = 0; i < sortFields.length; i++) {
if (!usedFields[sortFields[i]]) {
return sortFields[i];
}
}
return null;
}
function getAvailableSortFields(row) {
var fieldSelect = row.querySelector('.sort-field');
var currentField = fieldSelect ? fieldSelect.value : null;
var usedFields = getUsedSortFields(row);
var availableFields = [];
for (var i = 0; i < sortFields.length; i++) {
if (sortFields[i] === currentField || !usedFields[sortFields[i]]) {
availableFields.push(sortFields[i]);
}
}
return availableFields;
}
function updateAddSortEntryButton() {
getElementById('addSortEntry').disabled = !getFirstUnusedSortField();
}
function updateSortFieldOptions() {
var rows = getElementById('sortEntries').getElementsByClassName('sort-entry-row');
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
var fieldSelect = row.querySelector('.sort-field');
var currentField = fieldSelect.value;
var availableFields = getAvailableSortFields(row);
fieldSelect.innerHTML = '';
for (var j = 0; j < availableFields.length; j++) {
var opt = document.createElement('option');
opt.value = availableFields[j];
opt.textContent = sortFieldCaptions[availableFields[j]] || availableFields[j];
if (availableFields[j] === currentField) {
opt.selected = true;
}
fieldSelect.appendChild(opt);
}
}
updateAddSortEntryButton();
}
function getUniqueSortInfos(sortInfos) {
var usedFields = {};
var uniqueSortInfos = [];
for (var i = 0; i < sortInfos.length; i++) {
var field = sortInfos[i].field;
if (field && !usedFields[field]) {
usedFields[field] = true;
uniqueSortInfos.push(sortInfos[i]);
}
}
return uniqueSortInfos;
}
// Sort entry management
function addSortEntryRow(field, ascending) {
field = field || getFirstUnusedSortField();
if (!field) {
updateAddSortEntryButton();
return;
}
var container = getElementById('sortEntries');
var row = document.createElement('div');
row.className = 'sort-entry-row';
var fieldSelect = document.createElement('select');
fieldSelect.className = 'sort-field';
var usedFields = getUsedSortFields();
for (var i = 0; i < sortFields.length; i++) {
if (sortFields[i] !== field && usedFields[sortFields[i]]) {
continue;
}
var opt = document.createElement('option');
opt.value = sortFields[i];
opt.textContent = sortFieldCaptions[sortFields[i]] || sortFields[i];
if (sortFields[i] === field) opt.selected = true;
fieldSelect.appendChild(opt);
}
fieldSelect.onchange = updateSortFieldOptions;
var orderSelect = document.createElement('select');
orderSelect.className = 'sort-order';
var ascOpt = document.createElement('option');
ascOpt.value = 'true';
ascOpt.textContent = 'Ascending';
var descOpt = document.createElement('option');
descOpt.value = 'false';
descOpt.textContent = 'Descending';
orderSelect.appendChild(ascOpt);
orderSelect.appendChild(descOpt);
orderSelect.value = String(ascending);
var removeBtn = document.createElement('button');
removeBtn.className = 'sort-entry-remove';
removeBtn.type = 'button';
removeBtn.textContent = 'X';
removeBtn.title = 'Remove Sort Info';
removeBtn.onclick = function () {
row.remove();
updateSortFieldOptions();
};
row.appendChild(fieldSelect);
row.appendChild(orderSelect);
row.appendChild(removeBtn);
container.appendChild(row);
updateSortFieldOptions();
}
function clearSortEntries() {
getElementById('sortEntries').innerHTML = '';
updateSortFieldOptions();
}
function getSortEntriesFromUI() {
var rows = getElementById('sortEntries').getElementsByClassName('sort-entry-row');
var sortInfos = [];
for (var i = 0; i < rows.length; i++) {
var row = rows[i];
sortInfos.push({
field: row.querySelector('.sort-field').value,
ascending: row.querySelector('.sort-order').value === 'true'
});
}
return sortInfos;
}
function populateSortEntries(sortInfos) {
clearSortEntries();
var uniqueSortInfos = getUniqueSortInfos(sortInfos);
for (var i = 0; i < uniqueSortInfos.length; i++) {
var info = uniqueSortInfos[i];
addSortEntryRow(info.field, info.ascending !== false);
}
updateSortFieldOptions();
}
// Sort API handlers
function handleApplySort() {
var sortInfos = getSortEntriesFromUI();
if (sortInfos.length === 0) return;
if (sheet && sheet.sort) {
sheet.sort(sortInfos);
}
}
function handleReadSort() {
if (sheet && sheet.sort) {
var sortInfos = sheet.sort();
populateSortEntries(sortInfos);
}
}
function handleClearSort() {
if (sheet && sheet.removeSort) {
sheet.removeSort();
}
clearSortEntries();
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/departments.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 id="options-container" class="options-container">
<div class="options-tabs">
<button class="options-tab-link active" onclick="openOptionsTab(event, 'options-tab')">Options</button>
<button class="options-tab-link" onclick="openOptionsTab(event, 'sortapi-tab')">Sort API</button>
</div>
<div class="options-tab-panels">
<div id="options-tab" class="tab-content active">
<fieldset>
<legend>Multiple Sorting Options</legend>
<input type="checkbox" id="allowSorts" checked/>
<label for="allowSorts">Allow Sorts</label>
<br>
</fieldset>
<fieldset>
<legend>Column Sorting Options</legend>
<label for="columnSelect">Column: </label>
<select id="columnSelect">
<option value="id">ID</option>
<option value="state">State</option>
<option value="dept">Department No</option>
<option value="title">Title</option>
<option value="firstName">First Name</option>
<option value="lastName">Last Name</option>
<option value="birth">Birthday</option>
<option value="salary">Salary</option>
</select>
<div style="margin-top: 6px;">
<input type="checkbox" id="columnAllowSort" checked/>
<label for="columnAllowSort">Sort Visible</label>
</div>
</fieldset>
<fieldset class="data-source-controls">
<legend>Data Source</legend>
<label for="dataRows">Row Count: </label>
<select id="dataRows">
<option value="1000" selected="selected">1000</option>
<option value="3000">3000</option>
<option value="10000">10000</option>
<option value="30000">30000</option>
<option value="100000">100000</option>
<option value="300000">300000</option>
<option value="1000000">1000000</option>
</select>
<input type="button" id="setDataSource" value="Set DataSource"/>
</fieldset>
<fieldset style="height: 155px;">
<legend>Performance</legend>
<textarea id="showEventArgs" style="width: 234px;height: 135px;" cols="64" rows="15"></textarea>
</fieldset>
</div>
<div id="sortapi-tab" class="tab-content">
<fieldset>
<legend>Sort Infos</legend>
<div class="sort-actions-row">
<input type="button" id="addSortEntry" value="+ Add Sort Info"/>
</div>
<div id="sortEntries"></div>
<div class="sort-actions-row">
<input type="button" id="applySort" value="Apply Sorts"/>
<input type="button" id="clearSort" value="Clear All Sorts"/>
</div>
</fieldset>
</div>
</div>
</div>
</div>
</html>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
fieldset {
padding: 6px;
margin: 0;
margin-top: 10px;
}
.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;
}
fieldset span,
fieldset input,
fieldset select {
display: inline-block;
text-align: left;
}
fieldset span {
width: 50px;
}
fieldset input[type=text] {
width: calc(100% - 58px);
}
fieldset input[type=button] {
width: 100%;
text-align: center;
}
.data-source-controls label,
.data-source-controls select,
.data-source-controls input[type=button] {
display: block;
width: 100%;
box-sizing: border-box;
}
.data-source-controls select,
.data-source-controls input[type=button] {
margin-top: 4px;
}
fieldset select {
width: calc(100% - 50px);
}
.field-line {
margin-top: 4px;
}
/* Tab styles */
.options-tabs {
display: flex;
border-bottom: 1px solid #ccc;
margin-bottom: 8px;
}
.options-tab-link {
padding: 6px 12px;
background-color: #f1f1f1;
border: none;
cursor: pointer;
font-size: 13px;
flex: 1;
text-align: center;
transition: background-color 0.3s;
}
.options-tab-link:hover {
background-color: #ddd;
}
.options-tab-link.active {
background-color: #ccc;
font-weight: bold;
}
.tab-content {
display: none;
}
.tab-content.active {
display: block;
}
/* Sort entry rows */
#sortEntries {
max-height: 200px;
overflow-y: auto;
margin-bottom: 4px;
}
.sort-actions-row {
display: flex;
justify-content: flex-end;
gap: 6px;
margin-bottom: 4px;
}
.sort-actions-row input[type=button] {
width: auto;
padding: 2px 10px;
font-size: 13px;
}
.sort-entry-row {
display: flex;
align-items: center;
gap: 3px;
margin-bottom: 4px;
}
.sort-entry-row select {
font-size: 12px;
padding: 2px;
width: auto;
}
.sort-entry-row .sort-field {
flex: 1;
min-width: 0;
}
.sort-entry-row .sort-order {
width: 100px;
}
.sort-entry-remove {
width: 20px;
height: 20px;
padding: 0;
font-size: 12px;
line-height: 20px;
text-align: center;
border: none;
background: transparent;
cursor: pointer;
color: #999;
}
