To make it easier to manipulate arrays in worksheets, SpreadJS has a collection of 11 array manipulation functions.
Combining Arrays
It can be challenging to combine data, especially when their sources are flexible in size. With VSTACK and HSTACK, you can easily combine dynamic arrays, stacking your data vertically or horizontally.
VSTACK - Stacks arrays vertically
HSTACK- Stacks arrays horizontally
Shaping Arrays
It can be challenging to change the “shape” of data, especially from arrays to lists and vice versa. If you find yourself with a two-dimensional array that you would like to convert to a simple list, use TOROW and TOCOL to convert a 2D array into a single row or column of data.
Using the WRAPROWS and WRAPCOLS functions, do the opposite: create a 2D array of a specified width or height by “wrapping” data to the next line (just like the text in this document) once your chosen width/height limit is reached.
TOROW - Returns the array as one row
TOCOL - Returns the array as one column
WRAPROWS - Wraps a row array into a 2D array
WRAPCOLS - Wraps a column array into a 2D array
Resizing Arrays
The TAKE and DROP functions enable you to reduce your arrays by specifying the number of rows to keep or remove from the start or end of your array.
Using CHOOSEROWS or CHOOSECOLS, you can pick specific rows or columns out of an array by their index.
EXPAND allows you to grow an array to the size of your choice—you just need to provide the new dimensions and a value to fill the extra space with.
TAKE - Returns rows or columns from array start or end
DROP - Drops rows or columns from array start or end
CHOOSEROWS - Returns the specified rows from an array
CHOOSECOLS - Returns the specified columns from an array
EXPAND - Expands an array to the specified dimensions
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initStyles(spread);
initSpread(spread);
};
var data = [
{
sheetName: 'VSTACK',
cells: [
[{v:"Data",s:"title"}],
["A","B","C",null,"AA","BB","CC"],
["D","E","F",null,"DD","EE","FF"],
[{v:"Formula",s:"title"}],
["=FORMULATEXT(A7)"],
[{v:"Result",s:"title"}],
["=VSTACK(A2:C3,E2:G3)"]
],
columnsWidth: [69]
},
{
sheetName: 'HSTACK',
cells: [
[{v:"Data",s:"title"}],
["A","B","C",null,"AA","BB","CC"],
["D","E","F",null,"DD","EE","FF"],
[{v:"Formula",s:"title"}],
["=FORMULATEXT(A7)"],
[{v:"Result",s:"title"}],
["=HSTACK(A2:C3,E2:G3)"]
],
columnsWidth: [69]
},
{
sheetName: 'TOROW',
cells: [
[{v:"Data",s:"title"}],
["Ben","Peter","Mary","Sam"],
["John","Hillary","Jenny","James"],
["Agnes","Harry","Felicity","Joe"],
[{v:"Formula",s:"title"}],
["=FORMULATEXT(A8)"],
[{v:"Result",s:"title"}],
["=TOROW(A2:D4)"]
],
columnsWidth: [69]
},
{
sheetName: 'TOCOL',
cells: [
[{v:"Data",s:"title"}],
["Ben","Peter","Mary","Sam"],
["John","Hillary","Jenny","James"],
["Agnes","Harry","Felicity","Joe"],
[{v:"Formula",s:"title"}],
["=FORMULATEXT(A8)"],
[{v:"Result",s:"title"}],
["=TOCOL(A2:D4)"]
],
columnsWidth: [69]
},
{
sheetName: 'WRAPROWS',
cells: [
[{v:"Data",s:"title"}],
["A","B","C","D","E","F","G"],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B5)"],
[{v:"Result",s:"title"}, "=WRAPROWS(A2:G2,3)"],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B10)"],
[{v:"Result",s:"title"}, '=WRAPROWS(A2:G2,3,"x")'],
],
columnsWidth: [69]
},
{
sheetName: 'WRAPCOLS',
cells: [
[{v:"Data",s:"title"}],
["A","B","C","D","E","F","G"],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B5)"],
[{v:"Result",s:"title"}, "=WRAPCOLS(A2:G2,3)"],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B10)"],
[{v:"Result",s:"title"}, '=WRAPCOLS(A2:G2,3,"x")'],
],
columnsWidth: [69]
},
{
sheetName: 'TAKE',
cells: [
[{v:"Data",s:"title"}],
[1,2,3],
[4,5,6],
[7,8,9],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B7)"],
[{v:"Result",s:"title"}, "=TAKE(A2:C4,2)"],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B11)"],
[{v:"Result",s:"title"}, '=TAKE(A2:C4,,2)'],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B16)"],
[{v:"Result",s:"title"}, '=TAKE(A2:C4,-2)'],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B20)"],
[{v:"Result",s:"title"}, '=TAKE(A2:C4,2,2)'],
],
columnsWidth: [69]
},
{
sheetName: 'DROP',
cells: [
[{v:"Data",s:"title"}],
[1,2,3],
[4,5,6],
[7,8,9],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B7)"],
[{v:"Result",s:"title"}, "=DROP(A2:C4,2)"],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B10)"],
[{v:"Result",s:"title"}, '=DROP(A2:C4,,2)'],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B15)"],
[{v:"Result",s:"title"}, '=DROP(A2:C4,-2)'],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B18)"],
[{v:"Result",s:"title"}, '=DROP(A2:C4,2,2)'],
],
columnsWidth: [69]
},
{
sheetName: 'CHOOSEROWS',
cells: [
[{v:"Data",s:"title"}],
[1,2],
[3,4],
[5,6],
[7,8],
[9,10],
[11,12],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B10)"],
[{v:"Result",s:"title"}, "=CHOOSEROWS(A2:B7,1,3,5,1)"],
[],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B16)"],
[{v:"Result",s:"title"}, '=CHOOSEROWS(A2:B7,-1,-2)'],
],
columnsWidth: [69]
},
{
sheetName: 'CHOOSECOLS',
cells: [
[{v:"Data",s:"title"}],
[1,2,3,4,5],
[6,7,8,9,10],
[11,12,13,14,15],
[16,17,18,19,20],
[21,22,23,24,25],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B9)"],
[{v:"Result",s:"title"}, "=CHOOSECOLS(A2:E6,1,3,5,1)"],
[],
[],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B16)"],
[{v:"Result",s:"title"}, '=CHOOSECOLS(A2:E6,-1,-2)'],
],
columnsWidth: [69]
},
{
sheetName: 'EXPAND',
cells: [
[{v:"Data",s:"title"}],
[1,2],
[3,4],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B6)"],
[{v:"Result",s:"title"}, "=EXPAND(A2:B3,3,3)"],
[],
[],
[],
[{v:"Formula",s:"title"}, "=FORMULATEXT(B11)"],
[{v:"Result",s:"title"}, '=EXPAND(A2:B3,3,3, "-")'],
],
columnsWidth: [69]
},
];
function initSpread(workbook) {
workbook.suspendPaint();
workbook.suspendCalcService();
workbook.setSheetCount(data.length);
for (var i = 0; i < data.length; i++) {
var sheetData = data[i];
var sheet = workbook.sheets[i];
sheet.name(sheetData.sheetName);
setCells(sheet, sheetData.cells, 0, 0);
setColumnsWidth(sheet, sheetData.columnsWidth);
}
workbook.resumeCalcService();
workbook.resumePaint();
}
function initStyles(workbook) {
var style = new GC.Spread.Sheets.Style();
style.name = 'title';
style.font = 'normal bold 16px Segoe UI';
style.foreColor = "#172b4d";
workbook.addNamedStyle(style);
}
function setCells(sheet, cells, rowIndex, colIndex) {
for(var i = 0; i < cells.length; i++) {
var row = cells[i];
var r = rowIndex + i;
for (var j = 0; j < row.length; j++) {
var cell = row[j];
var c = colIndex + j;
if (cell === null) {
continue;
}
if (typeof cell === "object") {
if (cell.v !== undefined) {
sheet.setValue(r, c, cell.v);
}
if (cell.s !== undefined) {
sheet.setStyle(r, c, cell.s)
}
} else if (cell[0] === '=') {
sheet.setFormula(r, c, cell);
} else {
sheet.setValue(r, c, cell);
}
}
}
}
function setColumnsWidth(sheet, columnsWidth) {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
function _getElementById(id) {
return document.getElementById(id);
}
<!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">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.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>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
