테이블 시트는 ITableSheetOptions의 allowSorts 옵션을 통해 여러 열 정렬을 지원합니다.
allowSorts가 활성화되면 사용자가 여러 열을 동시에 기준으로 정렬할 수 있으므로 더 유연하게 데이터를 구성할 수 있습니다.
API
tableSheet.sort(sortInfos?)로 활성 정렬 정의를 가져오거나 설정하고, tableSheet.removeSort()로 모든 활성 정렬을 지웁니다.
sortInfos를 전달하면 이전 정렬 정의를 지우고 새 정의를 한 번에 적용합니다. 인수 없이 tableSheet.sort()를 호출하면 활성 정렬 정의의 복제본을 반환하므로, 반환된 배열을 변경한 뒤 tableSheet.sort(sortInfos)에 다시 전달해야 테이블 시트가 업데이트됩니다.
각 정렬 정의는 ITableSheetSortInfo를 사용합니다.
field는 정렬할 테이블 시트 필드 이름입니다.
ascending은 정렬 방향을 제어하며 기본값은 true입니다.
compare는 등록된 함수 이름이나 직접 함수 참조로 사용자 지정 비교 로직을 제공합니다. 직렬화가 필요하면 등록된 함수 이름을 권장합니다. 직접 함수 참조는 런타임에서 동작하지만 직렬화 후에는 손실됩니다.
여러 정렬 정의를 함께 적용할 수 있습니다.
사용자 지정 정렬에서는 함수를 등록해 이름으로 참조하거나 함수를 직접 전달합니다.
생성된 열에서는 데이터 뷰에서 열 정보를 읽고 열 정보의 value를 필드 이름으로 사용합니다.
명령
명령 관리자를 통해 테이블 시트 정렬 명령을 사용할 수 있으며 각 명령은 실행 취소와 다시 실행을 지원합니다.
TableSheetSortColumn은 한 열을 정렬하고 다른 활성 정렬을 대체합니다. 옵션은 sheetName, col, ascending 및 선택적 compare입니다.
TableSheetAddSortColumn은 현재 정렬을 대체하지 않고 다중 정렬 상태에 열을 추가합니다.
TableSheetRemoveSortColumn은 한 열의 정렬을 제거하며 sheetName과 col 옵션을 사용합니다.
이벤트
필터 대화 상자나 관련 테이블 시트 정렬 명령으로 정렬을 적용하거나 지울 때 다음 이벤트가 발생합니다.
TableSheetSorting: 열을 정렬하기 전에 발생하며 args.cancel = true로 취소할 수 있습니다. 페이로드는 sheetName, col, ascending, compare, cancel입니다.
TableSheetSorted: 열 정렬 후 발생합니다. 페이로드는 sheetName, col, ascending입니다.
TableSheetSortClearing: 열 정렬을 지우기 전에 발생하며 취소할 수 있습니다. 페이로드는 sheetName, col, cancel입니다.
TableSheetSortCleared: 열 정렬을 지운 후 발생합니다. 페이로드는 sheetName, 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\/learn-spreadjs\//)[0] + 'server/api';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ko-kr" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/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$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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')">옵션</button>
<button class="options-tab-link" onclick="openOptionsTab(event, 'sortapi-tab')">정렬 API</button>
</div>
<div class="options-tab-panels">
<div id="options-tab" class="tab-content active">
<fieldset>
<legend>다중 정렬 옵션</legend>
<input type="checkbox" id="allowSorts" checked/>
<label for="allowSorts">정렬 허용</label>
<br>
</fieldset>
<fieldset>
<legend>열 정렬 옵션</legend>
<label for="columnSelect">열: </label>
<select id="columnSelect">
<option value="id">ID</option>
<option value="state">주</option>
<option value="dept">부서 번호</option>
<option value="title">직책</option>
<option value="firstName">이름</option>
<option value="lastName">성</option>
<option value="birth">생일</option>
<option value="salary">급여</option>
</select>
<div style="margin-top: 6px;">
<input type="checkbox" id="columnAllowSort" checked/>
<label for="columnAllowSort">정렬 표시</label>
</div>
</fieldset>
<fieldset class="data-source-controls">
<legend>데이터 원본</legend>
<label for="dataRows">행 수: </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="데이터 원본 설정"/>
</fieldset>
<fieldset style="height: 155px;">
<legend>성능</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>정렬 정보</legend>
<div class="sort-actions-row">
<input type="button" id="addSortEntry" value="+ 정렬 정보 추가"/>
</div>
<div id="sortEntries"></div>
<div class="sort-actions-row">
<input type="button" id="applySort" value="정렬 적용"/>
<input type="button" id="clearSort" value="모든 정렬 지우기"/>
</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;
}
