The SORT function returns a sorted array of the elements in an array. The returned array is the same shape as the provided array argument. For example:
Argument
Required
Description
array
Y
The range, or array to sort
[sort_index]
N
A number indicating the row or column to sort by
[sort_order]
N
A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order
[by_col]
N
A logical value indicating the desired sort direction; false to sort by row (default), true to sort by column
Note:
The SORT function is only valid when allowDynamicArray is true.
The SORT function will return an array, which will spill if it's the final result of a formula.
Where sortindex is not provided, row1/col1 will be presumed. Where order is not provided, ascending order will be presumed. By default sort by row, and will only sort by column where bycol is true. When by_col is false or missing will sort by row.
The SORT function is provided to sort data in an array. If you want to sort data in the grid, it's better to use the SORTBY function, as it is more flexible. SORTBY will respect column additions/deletions, because it references a range, where SORT references a column index number.
An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
function initSpread (spread) {
var demoData = [
["Region","Sales Rep","Product","Units"],
["East","Tom","Apple",6380],
["West","Fred","Grape", 5619],
["North ","Amy","Pear", 4565],
["South","Sal","Banana", 5323],
["East","Fritz","Apple", 4394],
["West","Sravan","Grape", 7195],
["North ","Xi","Pear", 5231],
["South","Hector","Banana", 2427],
["East","Tom","Banana", 4213],
["West","Fred","Pear", 3239],
["North ","Amy","Grape", 6420],
["South","Sal","Apple", 1310],
["East","Fritz","Banana", 6274],
["West","Sravan","Pear", 4894],
["North ","Xi","Grape", 7580],
["South","Hector","Apple", 9814]
];
spread.options.allowDynamicArray = true;
var sheet = spread.getActiveSheet();
spread.suspendPaint();
spread.suspendCalcService();
sheet.name('SORT');
sheet.defaults.colWidth = 80;
sheet.setColumnWidth(0, 20);
sheet.setColumnWidth(1, 20);
sheet.setText(0, 1, 'Use the SORT function to sort a range or array in ascending or descending order. Syntax: SORT(array,[sort_index],[sort_order],[by_col])');
var row = 2;
var col = 2;
applyTableStyleForRange(sheet, row, col, 11, 1);
applyTableStyleForRange(sheet, row, col + 2, 11, 1);
sheet.setText(row - 1, col + 2, 'Sort data in descending order: SORT(C4:C13,1,-1)');
sheet.setArray(row, col, [['Units'], [622], [961], [691], [445], [378], [483], [650], [783], [142], [404]]);
sheet.setText(row, col + 2, 'Units');
sheet.setFormula(row + 1, col + 2 , 'SORT(C4:C13,1,-1)');
row = 15;
var col2 = col + 5;
sheet.setText(row - 1, col2, 'Sort range C17:F32 by Units: SORT(C17:F32,4,1,FALSE)');
var rowCount = 17;
applyTableStyleForRange(sheet, row, col, rowCount, 4);
applyTableStyleForRange(sheet, row, col2, rowCount, 4);
sheet.setArray(row, col, demoData);
sheet.setArray(row, col2, [["Region","Sales Rep","Product","Units"]]);
var formatter = '#,##0';
sheet.getRange(row + 1, col + 3, rowCount - 1, 1).formatter(formatter);
sheet.getRange(row + 1, col2 + 3, rowCount - 1, 1).formatter(formatter);
sheet.setFormula(row + 1, col2, 'SORT(C17:F32,4,1,FALSE)');
spread.resumeCalcService();
spread.resumePaint();
}
function applyTableStyleForRange (sheet, row, col, rowCount, colCount, options) {
var tableName = "tmpTable";
var TableThemes = GC.Spread.Sheets.Tables.TableThemes;
// use table to help set style then remove like convert table to range in Excel
sheet.tables.add(tableName, row, col, rowCount, colCount, TableThemes.medium7, options);
sheet.tables.remove(tableName, 2 /* keep style */);
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
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;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
