Use code similar to the following to set a formula for a cell or a range of cells.
You can set a range of cells with formula relative reference auto offset.
Use code similar to the following to set array formula for a range.
You can check if there is a formula in the specified cell by using the following code:
If a formula is lengthy or used frequently, you can add a custom name for it to make using it more convenient.
A custom name can be removed from the custom name collection like this:
You can remove all custom names by using the following method:
You can use the sheet property ShowFormulas to show the formula content instead of the values. With ShowFormulas, you can copy the formulas string to another applications, or print the formulas.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<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;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(0, 0, [
["Product", "Item Price", "Quantity", "Sales"],
['Kraft Real Mayo', 5.71, 1],
['Smartfood Popcorn', 2.5, 4],
['Teddy Grahams Crackers', 35, 5],
['Parmesan Cheese', 14.89, 9],
['Planter Deluxe Whole Cashew', 8.52, 3],
['Total']
]);
sheet.setColumnWidth(0, 190);
sheet.setColumnWidth(1, 80);
sheet.setColumnWidth(2, 80);
sheet.getRange(1, 3, 5, 1).formula("B2*C2", true);
sheet.addCustomName('customName1', '=$B$2:$B$6', 0, 0);
sheet.addCustomName('customName2', '=$C$2:$C$6', 0, 0);
sheet.setFormula(6, 1, "=SUM(customName1)");
sheet.setFormula(6, 2, "=SUM(customName2)");
sheet.getRange(6, 0, 1, 4).foreColor('red');
sheet.setFormula(6, 3, "B7*C7");
let style = sheet.getStyle(4, 7) || new GC.Spread.Sheets.Style();
style.cellButtons = [{
useButtonStyle: true,
caption: "Show Formulas",
width: 120,
command: function () {
sheet.options.showFormulas = !sheet.options.showFormulas;
if (sheet.options.showFormulas) {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.ok;
} else {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.none;
}
sheet.setStyle(4, 7, style);
},
}];
sheet.setStyle(4, 7, style);
sheet.setColumnWidth(7, 122);
sheet.resumePaint();
}
const setFormula = (formulaInfo) => {
let sheet = spread.getActiveSheet();
if (formulaInfo.formula) {
let formula = formulaInfo.formula;
let rowIndex = parseInt(formulaInfo.rowIndex);
let columnIndex = parseInt(formulaInfo.columnIndex);
if (!isNaN(rowIndex) && !isNaN(columnIndex)) {
let rowCount = Math.max(parseInt(formulaInfo.rowCount));
let columnCount = Math.max(parseInt(formulaInfo.columnCount));
if (!isNaN(rowCount) && !isNaN(columnCount)) {
sheet.setArrayFormula(rowIndex, columnIndex, rowCount, columnCount, formula);
} else {
sheet.setFormula(rowIndex, columnIndex, formula);
}
}
}
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
setFormula={(formulaInfo) => setFormula(formulaInfo)}
></Panel>
</div>
);
}
function TextInput(props) {
const [value, setValue] = useState(props.value);
return (
<input type="text" id={props.id} value={value} onChange={(e) => {
setValue(e.target.value);
props.onChange(e)
}} />
);
}
function Panel(props) {
const [formula, setFormula] = useState("=SUM(B2,B6)");
const [rowIndex, setRowIndex] = useState("");
const [columnIndex, setColumnIndex] = useState("");
const [rowCount, setRowCount] = useState("");
const [columnCount, setColumnCount] = useState("");
return (
<div class="options-container">
<div className="options-row" style={{ fontSize: "16px", padding: "1px 0px" }}>
Setting a formula via code
</div>
<div className="options-row" style={{ padding: "2px 10px" }}>
Specify the row and column index for the formula cell. Set the row count and column count to specify
the number of rows and columns to add the formula to. Add the formula to the Formula box and then
click SetFormula to apply the formula.
</div>
<div className="options-row">
<label htmlFor="rowIndex">RowIndex</label>
<TextInput id="rowIndex" value={rowIndex} onChange={(e) => {
setRowIndex(e.target.value);
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="columnIndex">ColumnIndex</label>
<TextInput id="columnIndex" value={columnIndex} onChange={(e) => {
setColumnIndex(e.target.value);
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="rowCount">RowCount</label>
<TextInput id="rowCount" value={rowCount} onChange={(e) => {
setRowCount(e.target.value);
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="columnCount">ColumnCount</label>
<TextInput id="columnCount" value={columnCount} onChange={(e) => {
setColumnCount(e.target.value);
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="formula">Formula:</label>
<TextInput id="formula" value={formula} onChange={(e) => {
setFormula(e.target.value);
}}></TextInput>
<input type="button" value="SetFormula" onClick={() => {
let formulaInfo = {
rowIndex: rowIndex,
columnIndex: columnIndex,
rowCount: rowCount,
columnCount: columnCount,
formula: formula,
};
props.setFormula(formulaInfo)
}} />
</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;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.dataSource = dataSource;
this.autoGenerateColumns = false;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
setFormula={(formulaInfo) => this.setFormula(formulaInfo)}
></Panel>
</div>
);
}
initSpread(spread) {
this.spread = spread;
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(0, 0, [
["Product", "Item Price", "Quantity", "Sales"],
['Kraft Real Mayo', 5.71, 1],
['Smartfood Popcorn', 2.5, 4],
['Teddy Grahams Crackers', 35, 5],
['Parmesan Cheese', 14.89, 9],
['Planter Deluxe Whole Cashew', 8.52, 3],
['Total']
]);
sheet.setColumnWidth(0, 190);
sheet.setColumnWidth(1, 80);
sheet.setColumnWidth(2, 80);
sheet.getRange(1, 3, 5, 1).formula("B2*C2", true);
sheet.addCustomName('customName1', '=$B$2:$B$6', 0, 0);
sheet.addCustomName('customName2', '=$C$2:$C$6', 0, 0);
sheet.setFormula(6, 1, "=SUM(customName1)");
sheet.setFormula(6, 2, "=SUM(customName2)");
sheet.getRange(6, 0, 1, 4).foreColor('red');
sheet.setFormula(6, 3, "B7*C7");
let style = sheet.getStyle(4, 7) || new GC.Spread.Sheets.Style();
style.cellButtons = [{
useButtonStyle: true,
caption: "Show Formulas",
width: 120,
command: function () {
sheet.options.showFormulas = !sheet.options.showFormulas;
if (sheet.options.showFormulas) {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.ok;
} else {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.none;
}
sheet.setStyle(4, 7, style);
},
}];
sheet.setStyle(4, 7, style);
sheet.setColumnWidth(7, 122);
sheet.resumePaint();
}
setFormula(formulaInfo) {
let spread = this.spread;
let sheet = spread.getActiveSheet();
if (formulaInfo.formula) {
let formula = formulaInfo.formula;
let rowIndex = parseInt(formulaInfo.rowIndex);
let columnIndex = parseInt(formulaInfo.columnIndex);
if (!isNaN(rowIndex) && !isNaN(columnIndex)) {
let rowCount = Math.max(parseInt(formulaInfo.rowCount));
let columnCount = Math.max(parseInt(formulaInfo.columnCount));
if (!isNaN(rowCount) && !isNaN(columnCount)) {
sheet.setArrayFormula(rowIndex, columnIndex, rowCount, columnCount, formula);
} else {
sheet.setFormula(rowIndex, columnIndex, formula);
}
}
}
}
}
function TextInput(props) {
const [value, setValue] = useState(props.value);
return (
<input type="text" id={props.id} value={value} onChange={(e) => {
setValue(e.target.value);
props.onChange(e)
}} />
);
}
class Panel extends Component {
constructor(props) {
super(props);
this.formula = "=SUM(B2,B6)";
}
render() {
return (
<div class="options-container">
<div className="options-row" style={{ fontSize: "16px", padding: "1px 0px" }}>
Setting a formula via code
</div>
<div className="options-row" style={{ padding: "2px 10px" }}>
Specify the row and column index for the formula cell. Set the row count and column count to specify
the number of rows and columns to add the formula to. Add the formula to the Formula box and then
click SetFormula to apply the formula.
</div>
<div className="options-row">
<label htmlFor="rowIndex">RowIndex</label>
<TextInput id="rowIndex" value={""} onChange={(e) => {
this.rowIndex = e.target.value
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="columnIndex">ColumnIndex</label>
<TextInput id="columnIndex" value={""} onChange={(e) => {
this.columnIndex = e.target.value
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="rowCount">RowCount</label>
<TextInput id="rowCount" value={""} onChange={(e) => {
this.rowCount = e.target.value
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="columnCount">ColumnCount</label>
<TextInput id="columnCount" value={""} onChange={(e) => {
this.columnCount = e.target.value
}}></TextInput>
</div>
<div className="options-row">
<label htmlFor="formula">Formula:</label>
<TextInput id="formula" value={this.formula} onChange={(e) => {
this.formula = e.target.value
}}></TextInput>
<input type="button" value="SetFormula" onClick={() => {
let formulaInfo = {
rowIndex: this.rowIndex,
columnIndex: this.columnIndex,
rowCount: this.rowCount,
columnCount: this.columnCount,
formula: this.formula,
};
this.props.setFormula(formulaInfo)
}} />
</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;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
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/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.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);
