Use the addRows, addColumns, deleteRows, deleteColumns, setRowCount and setColumnCount methods to change the number of rows or columns in each sheet area.
When calling the setRowCount and setColumnCount methods, you can choose which used range type to protect.
Each of the following lines could be used to change the row count of the sheet's viewport area.
If a row or column's resizeable property is false, it cannot be resized by user operations, but you can change its height or width directly by using code.
Use the setRowVisible and setColumnVisible methods to specify whether a row or column is displayed.
In the sheet's viewport area, when a row or column is automatically sized, its height or width will be determined by the content's length. Use the following code to set a row or column to auto fit:
SpreadJS also provides many methods to get useful information about a sheet's row or column, as illustrated in these lines of code.
SpreadJS provides the resizeZeroIndicator method to control whether to display double or single gridlines in the row or column header when the row height or column width is 0. The parameter is a ResizeZeroIndicator enumeration value.
default: single gridline (same as normal)
enhanced: double gridline (default)
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 useState = React.useState, spreadNS = GC.Spread.Sheets,
SheetArea = spreadNS.SheetArea;
export function AppFunc() {
const [rowIndex, setRowIndex] = useState(0);
const [columnIndex, setColumnIndex] = useState(0);
const [spread, setSpread] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setRowCount(2, SheetArea.colHeader);
sheet.setRowCount(10, SheetArea.viewport);
sheet.setColumnCount(2, SheetArea.rowHeader);
sheet.setColumnCount(6, SheetArea.viewport);
sheet.setRowHeight(4, 0);
sheet.setColumnWidth(2, 0);
spread.options.resizeZeroIndicator = spreadNS.ResizeZeroIndicator.enhanced;
for (let rowIndex = 1; rowIndex <= 9; rowIndex++) {
sheet.setText(rowIndex, 0, "Row");
sheet.setValue(rowIndex, 1, rowIndex);
}
for (let columnIndex = 1; columnIndex <= 5; columnIndex++) {
sheet.setText(0, columnIndex, "Column");
sheet.setValue(1, columnIndex, columnIndex);
}
sheet.resumePaint();
}
/*
* Add a row in viewport area.
*/
const addRow = () => {
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.addRows(sheet.getRowCount(SheetArea.viewport), 1);
}
}
/*
* Delete a row in viewport area.
*/
const addColumn = () => {
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.addColumns(sheet.getColumnCount(SheetArea.viewport), 1);
}
}
/*
* Add a column in viewport area.
*/
const deleteRow = () => {
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.deleteRows(sheet.getRowCount(SheetArea.viewport) - 1, 1);
}
}
/*
* Delete a column in viewport area.
*/
const deleteColumn = () => {
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.deleteColumns(sheet.getColumnCount(SheetArea.viewport) - 1, 1);
}
}
const _setRowIndex = ($event) => {
setRowIndex($event.target.value);
}
const _setColumnIndex = ($event) => {
setColumnIndex($event.target.value);
}
/*
* Show or hide the specified row.
*/
const checkRowVisible = ($event) => {
let sheet = spread.getActiveSheet();
if (!isNaN(rowIndex)) {
sheet.setRowVisible(+rowIndex, $event.target.checked);
}
}
/*
* Auto fit or not fit the specified row.
*/
const checkRowAutoFit = ($event) => {
let sheet = spread.getActiveSheet();
if (!isNaN(rowIndex)) {
let checked = $event.target.checked;
if (checked) {
sheet.autoFitRow(+rowIndex);
}
}
}
/*
* Show or hide the specified column.
*/
const checkColumnVisible = ($event) => {
let sheet = spread.getActiveSheet();
if (!isNaN(columnIndex)) {
sheet.setColumnVisible(+columnIndex, $event.target.checked);
}
}
/*
* Auto fit or not fit the specified column.
*/
const checkColumnAutoFit = ($event) => {
let sheet = spread.getActiveSheet();
if (!isNaN(columnIndex)) {
let checked = $event.target.checked;
if (checked) {
sheet.autoFitColumn(+columnIndex);
}
}
}
const setResizeZeroIndicator = ($event) => {
spread.options.resizeZeroIndicator = +$event.target.value;
spread.repaint();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
addRow={() => addRow()}
deleteRow={() => deleteRow()}
addColumn={() => addColumn()}
deleteColumn={() => deleteColumn()}
setRowIndex={(e) => _setRowIndex(e)}
checkRowVisible={(e) => checkRowVisible(e)}
checkRowAutoFit={(e) => checkRowAutoFit(e)}
setColumnIndex={(e) => _setColumnIndex(e)}
checkColumnVisible={(e) => checkColumnVisible(e)}
checkColumnAutoFit={(e) => checkColumnAutoFit(e)}
setResizeZeroIndicator={(e) => setResizeZeroIndicator(e)}
></Panel>
</div>
);
}
function TextInput(props) {
const [value, setValue] = useState(props.value);
return (
<input type="text" value={value} onChange={(e) => {
setValue(e.target.value);
props.onChange(e)
}} />
);
}
function SelectInput(props) {
const [value, setValue] = useState(props.value);
function renderSelectOptions(options) {
let optionElems = [];
for (let i = 0; i < options.length; i++) {
optionElems.push(<option value={options[i].value}>{options[i].text}</option>);
}
return optionElems;
}
return (
<select
id={props.id}
className={props.className}
value={value}
name={props.name}
onChange={(e) => {
setValue(e.target.value);
props.onChange(e);
}}
>
{renderSelectOptions(props.options)}
</select>
);
}
function CheckBoxInput(props) {
const [checked, setChecked] = useState(props.checked);
return (
<input type="checkbox" id={props.id} checked={checked} onChange={(e) => {
setChecked(e.target.checked);
props.onChange(e)
}} />
);
}
function Panel(props) {
let selectOptions = [
{
value: 0,
text: "Default"
}, {
value: 1,
text: "Enhanced"
}
];
return (
<div className="options-container">
<div class="options-row">
<label>The following buttons add/remove rows/columns at the end of the sheet.</label>
</div>
<div className="options-row">
<input type="button" value="Add Row" onClick={props.addRow} />
<input type="button" value="Delete Row" onClick={props.deleteRow} />
</div>
<div className="options-row">
<input type="button" value="Add Column" onClick={props.addColumn} />
<input type="button" value="Delete Column" onClick={props.deleteColumn} />
</div>
<hr />
<div className="options-row">
<label htmlFor="rowIndex">Row Index:</label>
<TextInput id="rowIndex" value="" onChange={props.setRowIndex}></TextInput>
<label htmlFor="rowIndex" style={{ display: "block" }}>The index is zero based.</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkRowVisible" checked={true} onChange={props.checkRowVisible}></CheckBoxInput>
<label htmlFor="checkRowVisible">Row Visible</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkRowAutoFit" checked={false} onChange={props.checkRowAutoFit}></CheckBoxInput>
<label htmlFor="checkRowAutoFit">Row AutoFit</label>
</div>
<hr />
<div className="options-row">
<label htmlFor="columnIndex">Column Index:</label>
<TextInput id="columnIndex" value="" onChange={props.setColumnIndex}></TextInput>
<label htmlFor="columnIndex" style={{ display: "block" }}>The index is zero based.</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkColumnVisible" checked={true}
onChange={props.checkColumnVisible}></CheckBoxInput>
<label htmlFor="checkColumnVisible">Column Visible</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkColumnAutoFit" checked={false}
onChange={props.checkColumnAutoFit}></CheckBoxInput>
<label htmlFor="checkColumnAutoFit">Column AutoFit</label>
</div>
<hr />
<div className="options-row">
<label htmlFor="resizeZeroIndicator">ResizeZeroIndicator:</label>
<SelectInput id="resizeZeroIndicator" value={1} onChange={props.setResizeZeroIndicator}
options={selectOptions}></SelectInput>
</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, useState = React.useState, spreadNS = GC.Spread.Sheets,
SheetArea = spreadNS.SheetArea;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
initSpread(spread) {
this.spread = spread;
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setRowCount(2, SheetArea.colHeader);
sheet.setRowCount(10, SheetArea.viewport);
sheet.setColumnCount(2, SheetArea.rowHeader);
sheet.setColumnCount(6, SheetArea.viewport);
sheet.setRowHeight(4, 0);
sheet.setColumnWidth(2, 0);
spread.options.resizeZeroIndicator = spreadNS.ResizeZeroIndicator.enhanced;
for (let rowIndex = 1; rowIndex <= 9; rowIndex++) {
sheet.setText(rowIndex, 0, "Row");
sheet.setValue(rowIndex, 1, rowIndex);
}
for (let columnIndex = 1; columnIndex <= 5; columnIndex++) {
sheet.setText(0, columnIndex, "Column");
sheet.setValue(1, columnIndex, columnIndex);
}
sheet.resumePaint();
}
/*
* Add a row in viewport area.
*/
addRow() {
let spread = this.spread;
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.addRows(sheet.getRowCount(SheetArea.viewport), 1);
}
}
/*
* Delete a row in viewport area.
*/
addColumn() {
let spread = this.spread;
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.addColumns(sheet.getColumnCount(SheetArea.viewport), 1);
}
}
/*
* Add a column in viewport area.
*/
deleteRow() {
let spread = this.spread;
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.deleteRows(sheet.getRowCount(SheetArea.viewport) - 1, 1);
}
}
/*
* Delete a column in viewport area.
*/
deleteColumn() {
let spread = this.spread;
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.deleteColumns(sheet.getColumnCount(SheetArea.viewport) - 1, 1);
}
}
setRowIndex($event) {
this.rowIndex = $event.target.value;
}
setColumnIndex($event) {
this.columnIndex = $event.target.value;
}
/*
* Show or hide the specified row.
*/
checkRowVisible($event) {
let spread = this.spread;
let sheet = spread.getActiveSheet();
let rowIndex = parseInt(this.rowIndex);
if (!isNaN(rowIndex)) {
sheet.setRowVisible(rowIndex, $event.target.checked);
}
}
/*
* Auto fit or not fit the specified row.
*/
checkRowAutoFit($event) {
let spread = this.spread;
let sheet = spread.getActiveSheet();
let rowIndex = parseInt(this.rowIndex);
if (!isNaN(rowIndex)) {
let checked = $event.target.checked;
if (checked) {
sheet.autoFitRow(rowIndex);
}
}
}
/*
* Show or hide the specified column.
*/
checkColumnVisible($event) {
let spread = this.spread;
let sheet = spread.getActiveSheet();
let columnIndex = parseInt(this.columnIndex);
if (!isNaN(columnIndex)) {
sheet.setColumnVisible(columnIndex, $event.target.checked);
}
}
/*
* Auto fit or not fit the specified column.
*/
checkColumnAutoFit($event) {
let spread = this.spread;
let sheet = spread.getActiveSheet();
let columnIndex = parseInt(this.columnIndex);
if (!isNaN(columnIndex)) {
let checked = $event.target.checked;
if (checked) {
sheet.autoFitColumn(columnIndex);
}
}
}
setResizeZeroIndicator($event) {
let spread = this.spread;
spread.options.resizeZeroIndicator = +$event.target.value;
spread.repaint();
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
addRow={() => this.addRow()}
deleteRow={() => this.deleteRow()}
addColumn={() => this.addColumn()}
deleteColumn={() => this.deleteColumn()}
setRowIndex={(e) => this.setRowIndex(e)}
checkRowVisible={(e) => this.checkRowVisible(e)}
checkRowAutoFit={(e) => this.checkRowAutoFit(e)}
setColumnIndex={(e) => this.setColumnIndex(e)}
checkColumnVisible={(e) => this.checkColumnVisible(e)}
checkColumnAutoFit={(e) => this.checkColumnAutoFit(e)}
setResizeZeroIndicator={(e) => this.setResizeZeroIndicator(e)}
></Panel>
</div>
);
}
}
function TextInput(props) {
const [value, setValue] = useState(props.value);
return (
<input type="text" value={value} onChange={(e) => {
setValue(e.target.value);
props.onChange(e)
}} />
);
}
function SelectInput(props) {
const [value, setValue] = useState(props.value);
function renderSelectOptions(options) {
let optionElems = [];
for (let i = 0; i < options.length; i++) {
optionElems.push(<option value={options[i].value}>{options[i].text}</option>);
}
return optionElems;
}
return (
<select
id={props.id}
className={props.className}
value={value}
name={props.name}
onChange={(e) => {
setValue(e.target.value);
props.onChange(e);
}}
>
{renderSelectOptions(props.options)}
</select>
);
}
function CheckBoxInput(props) {
const [checked, setChecked] = useState(props.checked);
return (
<input type="checkbox" id={props.id} checked={checked} onChange={(e) => {
setChecked(e.target.checked);
props.onChange(e)
}} />
);
}
function Panel(props) {
let selectOptions = [
{
value: 0,
text: "Default"
}, {
value: 1,
text: "Enhanced"
}
];
return (
<div className="options-container">
<div class="options-row">
<label>The following buttons add/remove rows/columns at the end of the sheet.</label>
</div>
<div className="options-row">
<input type="button" value="Add Row" onClick={props.addRow} />
<input type="button" value="Delete Row" onClick={props.deleteRow} />
</div>
<div className="options-row">
<input type="button" value="Add Column" onClick={props.addColumn} />
<input type="button" value="Delete Column" onClick={props.deleteColumn} />
</div>
<hr />
<div className="options-row">
<label htmlFor="rowIndex">Row Index:</label>
<TextInput id="rowIndex" value="" onChange={props.setRowIndex}></TextInput>
<label htmlFor="rowIndex" style={{ display: "block" }}>The index is zero based.</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkRowVisible" checked={true} onChange={props.checkRowVisible}></CheckBoxInput>
<label htmlFor="checkRowVisible">Row Visible</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkRowAutoFit" checked={false} onChange={props.checkRowAutoFit}></CheckBoxInput>
<label htmlFor="checkRowAutoFit">Row AutoFit</label>
</div>
<hr />
<div className="options-row">
<label htmlFor="columnIndex">Column Index:</label>
<TextInput id="columnIndex" value="" onChange={props.setColumnIndex}></TextInput>
<label htmlFor="columnIndex" style={{ display: "block" }}>The index is zero based.</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkColumnVisible" checked={true}
onChange={props.checkColumnVisible}></CheckBoxInput>
<label htmlFor="checkColumnVisible">Column Visible</label>
</div>
<div className="options-row">
<CheckBoxInput id="checkColumnAutoFit" checked={false}
onChange={props.checkColumnAutoFit}></CheckBoxInput>
<label htmlFor="checkColumnAutoFit">Column AutoFit</label>
</div>
<hr />
<div className="options-row">
<label htmlFor="resizeZeroIndicator">ResizeZeroIndicator:</label>
<SelectInput id="resizeZeroIndicator" value={1} onChange={props.setResizeZeroIndicator}
options={selectOptions}></SelectInput>
</div>
</div>
);
}
<!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: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
input {
display: inline-block;
}
input[type="text"] {
width: 200px;
}
input[type=button] {
width: 110px;
display: inline-block;
}
label {
margin-bottom: 6px;
}
(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);
