For example, you can get an instance of a cell by specifying its row index and column index.
You also can get a series of cells in a range of the sheet area. There are two way to get range:
If you want to get a cell's position, width, or height, use the getCellRect method. This method returns the rectangle of the cell.
The spread provides a referenceStyle property. It sets the ReferenceStyle enumeration, which provides two settings:
a1
r1c1
The property sets the style of cell formulas and its default value is ReferenceStyle.a1. The appearance is the same as Excel. Use the following code to change the setting:
The sheet has an allowCellOverflow property; its default value is false. When this property's value has been set to true, the cell's data will overflow into adjacent empty cells. Use the following code to change the property's value:
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 { useState } from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
import './styles.css';
import { Panel } from './panel';
export function AppFunc() {
const [spread, test] = useState(null);
const [referenceStyle, setReferenceStyle] = useState('a1');
const [allowCellOverFlow, setAllowCellOverFlow] = useState(true);
let referenceStyleChange = function (e) {
let value = e.target.value;
let sheet = spread.getSheet(0);
let referenceStyleValue = value;
let referenceStyleResult = GC.Spread.Sheets.ReferenceStyle[value];
spread.options.referenceStyle = referenceStyleResult;
spread.getActiveSheet().recalcAll();
sheet.getCell(6, 0).value(referenceStyleValue.toLocaleLowerCase() === 'a1' ? 'A1' : 'R1C1');
setReferenceStyle(value);
}
let allowCellOverFlowChange = function (e) {
let value = e.target.checked;
let sheet = spread.getActiveSheet();
if (sheet) {
sheet.options.allowCellOverflow = value;
}
setAllowCellOverFlow(value);
}
let initSpread = function (spread) {
test(spread);
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.defaults.colWidth = 120;
sheet.setColumnWidth(1, 200);
sheet.setValue(0, 0, 12);
sheet.setValue(1, 0, 13);
sheet.setValue(2, 0, 14);
sheet.setValue(3, 0, 15);
sheet.setValue(5, 0, 'ReferenceStyle');
sheet.setValue(6, 0, 'A1');
sheet.setValue(5, 1, 'Formula:');
sheet.setFormula(6, 1, '=FORMULATEXT(C7)');
sheet.setValue(5, 2, 'Result:');
sheet.setFormula(6, 2, 'SUM(A4:A8)');
sheet.setValue(9, 0, 'textAlignment');
sheet.setValue(10, 0, 'right');
sheet.setValue(11, 0, 'center');
sheet.setValue(12, 0, 'left');
sheet.setValue(13, 0, 'centerContinues');
sheet.getCell(10, 2).value('The cell overflow to right cell').hAlign(3);
sheet.getCell(11, 2).value('The cell overflow to left and right cell').hAlign(1);
sheet.getCell(12, 2).value('The cell overflow to left cell').hAlign(2);
sheet.getCell(13, 2).value('The cell overflow to left and right cell').hAlign(4);
sheet.resumePaint();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel referenceStyle={referenceStyle}
allowCellOverFlow={allowCellOverFlow}
referenceStyleChange={referenceStyleChange}
allowCellOverFlowChange={allowCellOverFlowChange}
/>
</div>
)
}
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
import './styles.css';
import { Panel } from './panel';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {
referenceStyle: 'a1',
allowCellOverFlow: true
};
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel referenceStyle={this.state.referenceStyle}
allowCellOverFlow={this.state.allowCellOverFlow}
referenceStyleChange={(e) => { this.referenceStyleChange(e) }}
allowCellOverFlowChange={(e) => { this.allowCellOverFlowChange(e) }}
/>
</div>
)
}
referenceStyleChange(e) {
let value = e.target.value;
this.setState(() => ({
referenceStyle: value
}), () => {
let sheet = this.spread.getSheet(0);
let referenceStyleValue = this.state.referenceStyle;
let referenceStyle = GC.Spread.Sheets.ReferenceStyle[referenceStyleValue];
this.spread.options.referenceStyle = referenceStyle;
this.spread.getActiveSheet().recalcAll();
sheet.getCell(6, 0).value(referenceStyleValue.toLocaleLowerCase() === 'a1' ? 'A1' : 'R1C1');
});
}
allowCellOverFlowChange(e) {
let value = e.target.checked;
this.setState(() => ({
allowCellOverFlow: value
}), () => {
let sheet = this.spread.getActiveSheet();
if (sheet) {
sheet.options.allowCellOverflow = this.state.allowCellOverFlow;
}
});
}
initSpread(spread) {
this.spread = spread;
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.defaults.colWidth = 120;
sheet.setColumnWidth(1, 200);
sheet.setValue(0, 0, 12);
sheet.setValue(1, 0, 13);
sheet.setValue(2, 0, 14);
sheet.setValue(3, 0, 15);
sheet.setValue(5, 0, 'ReferenceStyle');
sheet.setValue(6, 0, 'A1');
sheet.setValue(5, 1, 'Formula:');
sheet.setFormula(6, 1, '=FORMULATEXT(C7)');
sheet.setValue(5, 2, 'Result:');
sheet.setFormula(6, 2, 'SUM(A4:A8)');
sheet.setValue(9, 0, 'textAlignment');
sheet.setValue(10, 0, 'right');
sheet.setValue(11, 0, 'center');
sheet.setValue(12, 0, 'left');
sheet.setValue(13, 0, 'centerContinues');
sheet.getCell(10, 2).value('The cell overflow to right cell').hAlign(3);
sheet.getCell(11, 2).value('The cell overflow to left and right cell').hAlign(1);
sheet.getCell(12, 2).value('The cell overflow to left cell').hAlign(2);
sheet.getCell(13, 2).value('The cell overflow to left and right cell').hAlign(4);
sheet.resumePaint();
}
}
import * as React from 'react';
import './styles.css';
export const Panel = (props) => {
const { referenceStyle, allowCellOverFlow, referenceStyleChange, allowCellOverFlowChange } = props;
return (
<div class="options-container">
<div class="position">
<p>The reference style is defined as 'A1' and 'R1C1', the rows and the columns on the worksheet are numbered.</p>
<p>If you change the reference style, the formula's reference will change too.</p>
<label for="referenceStyle">ReferenceStyle:</label>
<select id="referenceStyle" style={{ margin: '0 20px 0 6px' }} value={referenceStyle} onChange={(e) => { referenceStyleChange(e) }}>
<option value="a1">A1</option>
<option value="r1c1">R1C1</option>
</select>
</div>
<div style={{ paddingTop: '16px' }}>
<input type="checkbox" id="chkAllowCellOverFlow" checked={allowCellOverFlow} onChange={(e) => { allowCellOverFlowChange(e) }} />
<label for="chkAllowCellOverFlow">AllowCellOverFlow</label>
</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">
<!-- 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" style="height: 100%;"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
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;
}
label {
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(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);
