Sorting Data

SpreadJS can sort data in either ascending or descending order. This can be accessed within the spreadsheet interface via table headers or context menus on cell range selections. JavaScript code can also be used for sorting by using the sheet's sortRange function. In some cases, traditional ascending and descending sorting is not enough, which is why sorting on the workbook can be customized with compare functions as described in the code below.

<p>Use the <strong>sortRange</strong> method to sort a range with given order, as shown in the following code.</p> <pre><code class="hljs js language-js"><span class="hljs-comment">// sort the range with column 5 descending and column 6 ascending.</span> sheet.sortRange(<span class="hljs-number">1</span>, <span class="hljs-number">5</span>, <span class="hljs-number">6</span>, <span class="hljs-number">2</span>, <span class="hljs-literal">true</span>, [ { <span class="hljs-attr">index</span>: <span class="hljs-number">5</span>, <span class="hljs-attr">ascending</span>: <span class="hljs-literal">false</span>}, { <span class="hljs-attr">index</span>: <span class="hljs-number">6</span>, <span class="hljs-attr">ascending</span>: <span class="hljs-literal">true</span>}, ]); </code></pre> <p>Users can also use context menu -&gt; sort or filter to sort the data.</p> <p>The priority of data type is: boolean &gt; string &gt; number, eg: TRUE &gt; '4' &gt; 8.</p> <p><strong>Customize sorting</strong></p> <p>Users can use the callback function to define the customized compare method to sort.</p> <p>The following code shows how to use localCompare to sort.</p> <pre><code class="hljs js language-js"><span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">pinyinCompare</span> (<span class="hljs-params">obj1, obj2</span>) </span>{ <span class="hljs-keyword">return</span> obj1.toString().localeCompare(obj2.toString(), <span class="hljs-string">'zh'</span>); } sheet.sortRange(<span class="hljs-number">1</span>, <span class="hljs-number">0</span>, <span class="hljs-number">8</span>, <span class="hljs-number">2</span>, <span class="hljs-literal">true</span>, [ {<span class="hljs-attr">index</span>:<span class="hljs-number">0</span>, <span class="hljs-attr">ascending</span>:<span class="hljs-literal">true</span>, <span class="hljs-attr">compareFunction</span>: pinyinCompare}, <span class="hljs-comment">//Use the pinyin of column 0 to sort;</span> ]); </code></pre> <p>Users can define the callback function that is used when the <strong>RangeSorting</strong> event is raised.</p> <pre><code class="hljs js language-js"><span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">compareSize</span>(<span class="hljs-params">value1, value2</span>) </span>{ <span class="hljs-comment">//...</span> <span class="hljs-comment">//return -1;</span> <span class="hljs-comment">//return 1;</span> <span class="hljs-keyword">return</span> <span class="hljs-number">0</span>; } sheet.bind(GC.Spread.Sheets.Events.RangeSorting, <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">e, info</span>) </span>{ <span class="hljs-comment">//if(info.range is suited) {</span> info.compareFunction = compareSize; <span class="hljs-comment">// set the customize function.</span> <span class="hljs-comment">//}</span> }); </code></pre>
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 }); spread.suspendPaint(); spread.fromJSON(sjsData); initSheet0(spread.getSheet(0)); initSheet1(spread.getSheet(1)); spread.resumePaint(); }; function initSheet0(sheet) { var style = sheet.getStyle(4, 7); style.cellButtons= [{ useButtonStyle: true, caption: "Sort by last name", width: 222, command: function() { sheet.sortRange(4, 0, 27, 5, true, [ { index: 1, ascending: true, compareFunction: function (value1, value2) { var str1 = value1.split(" ")[1], str2 = value2.split(" ")[1]; return str1.localeCompare(str2); } }, ]) }, }]; sheet.setStyle(4, 7, style); var grade = ["Freshmen", "Sophomore", "Junior", "Senior"]; var clothesSize = ["XX-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "XX-Large"]; 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: "Sort by Grade", 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); style = sheet.getStyle(6, 7); style.cellButtons= [{ useButtonStyle: true, caption: "Sort by T-Shirt Size", 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); } 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; } }); }
<!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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.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> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }