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
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
export function AppFunc() {
const initSpread=(workbook)=> {
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]
},
];
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
workbook.suspendCalcService();
initStyles(workbook);
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();
}
const 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);
}
const 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);
}
}
}
}
const setColumnsWidth=(sheet, columnsWidth)=> {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
export class App extends Component {
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
initSpread(workbook) {
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]
},
];
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
workbook.suspendCalcService();
this.initStyles(workbook);
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);
this.setCells(sheet, sheetData.cells, 0, 0);
this.setColumnsWidth(sheet, sheetData.columnsWidth);
}
workbook.resumeCalcService();
workbook.resumePaint();
}
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);
}
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);
}
}
}
}
setColumnsWidth(sheet, columnsWidth) {
if (!columnsWidth) {
return;
}
for (var i = 0; i < columnsWidth.length; i++) {
sheet.setColumnWidth(i, columnsWidth[i]);
}
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
input {
padding: 4px 6px;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
