The formatter is divided into the following three cases
normal cell formatter: Standard formatter syntax format.
formula format string: Standard formula syntax format
text template format string: Text string where formula is contained by "{{" and "}}".
In the past, you could not set values and formulas in one cell at the same time, but now you can set the formula to the formatter of Style.
This template is a string of text values and formula, use "{{" and "}}" to wrap the formula in the string, in the formula you still can reference the current cell.
We provide the '@' symbol, this symbol represents the current cell reference. you can use it directly in the formula or text value template
We provide an option called saveAsView for workbook JSON serialization, which helps you save the format string result to excel.
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 GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
export function AppFunc() {
let spread = null;
let autoGenerateColumns = true;
const initSpread = (currSpread) => {
spread = currSpread;
spread.suspendPaint();
let sheet = spread.getSheet(0);
let gcns = GC.Spread.Sheets;
let data = [
[, "FY 2019"],
[, "Sales"],
[, "Monthly", "Cumulative"],
["Apr", 188897, 188897],
["May", 208146, 397043],
["Jun", 226196, 623239],
["Jul", 277318, 900557],
["Aug", 263273, 1163830],
["Sep", 259845, 1423675],
["Oct", 241047, 1664722],
["Nov", 256306, 1921028],
["Dec", 195845, 2116873],
["Jan", 204934, 2321808],
["Feb", 257852, 2579660],
["Mar", 227779, 2807439]
];
sheet.setArray(3, 1, data);
sheet.setColumnWidth(2, 110);
sheet.setColumnWidth(3, 110);
sheet.setRowCount(20);
sheet.setColumnCount(9);
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.getRange(3, 1, 15, 3).setBorder(
new gcns.LineBorder("black", gcns.LineStyle.medium),
{ all: true });
sheet.addSpan(3, 2, 1, 2);
sheet.addSpan(4, 2, 1, 2);
sheet.getRange(3, 2, 3, 2).backColor('#CFE1F3').hAlign(gcns.HorizontalAlign.center);
sheet.getRange(6, 1, 12, 1).backColor('#CCC1DA');
let cMapSource = [
{ "Currency": "USD", "Value": 1, "Symbol": "$" },
{ "Currency": "CNY", "Value": 7.02, "Symbol": "¥" },
{ "Currency": "JPY", "Value": 108.8, "Symbol": "¥" },
{ "Currency": "EURO", "Value": 0.91, "Symbol": "€" },
];
sheet.tables.addFromDataSource('cT', 3, 5, cMapSource);
[5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); });
sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right);
sheet.getRange(1, 3, 1, 1).backColor("yellow").setBorder(
new gcns.LineBorder("blue", gcns.LineStyle.medium),
{ all: true });
let dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]');
sheet.setDataValidator(1, 3, dv1);
sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD");
sheet.getRange(6, 2, 12, 2)
.hAlign(gcns.HorizontalAlign.center)
.formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&" "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")');
spread.resumePaint();
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={initSpread}>
<Worksheet autoGenerateColumns={autoGenerateColumns}>
</Worksheet>
</SpreadSheets>
</div>
<Panel />
</div>;
}
function Panel() {
return (
<div class="options-container">
<div className="option-row" style={{ padding: '2px 10px' }}>
<p>
By changing the value of D2 to select the currency calculation strategy, you will notice that the table on the left has changed.
</p>
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.autoGenerateColumns = true;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}>
<Worksheet autoGenerateColumns={this.autoGenerateColumns}>
</Worksheet>
</SpreadSheets>
</div>
<Panel/>
</div>;
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
let sheet = spread.getSheet(0);
let gcns = GC.Spread.Sheets;
let data = [
[, "FY 2019"],
[, "Sales"],
[, "Monthly", "Cumulative"],
["Apr", 188897, 188897],
["May", 208146, 397043],
["Jun", 226196, 623239],
["Jul", 277318, 900557],
["Aug", 263273, 1163830],
["Sep", 259845, 1423675],
["Oct", 241047, 1664722],
["Nov", 256306, 1921028],
["Dec", 195845, 2116873],
["Jan", 204934, 2321808],
["Feb", 257852, 2579660],
["Mar", 227779, 2807439]
];
sheet.setArray(3, 1, data);
sheet.setColumnWidth(2, 110);
sheet.setColumnWidth(3, 110);
sheet.setRowCount(20);
sheet.setColumnCount(9);
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.getRange(3, 1, 15, 3).setBorder(
new gcns.LineBorder("black", gcns.LineStyle.medium),
{ all: true });
sheet.addSpan(3, 2, 1, 2);
sheet.addSpan(4, 2, 1, 2);
sheet.getRange(3, 2, 3, 2).backColor('#CFE1F3').hAlign(gcns.HorizontalAlign.center);
sheet.getRange(6, 1, 12, 1).backColor('#CCC1DA');
let cMapSource = [
{ "Currency": "USD", "Value": 1, "Symbol": "$" },
{ "Currency": "CNY", "Value": 7.02, "Symbol": "¥" },
{ "Currency": "JPY", "Value": 108.8, "Symbol": "¥" },
{ "Currency": "EURO", "Value": 0.91, "Symbol": "€" },
];
sheet.tables.addFromDataSource('cT', 3, 5, cMapSource);
[5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); });
sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right);
sheet.getRange(1, 3, 1, 1).backColor("yellow").setBorder(
new gcns.LineBorder("blue", gcns.LineStyle.medium),
{ all: true });
let dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]');
sheet.setDataValidator(1, 3, dv1);
sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD");
sheet.getRange(6, 2, 12, 2)
.hAlign(gcns.HorizontalAlign.center)
.formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&" "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")');
spread.resumePaint();
}
}
class Panel extends Component{
constructor(props){
super(props);
}
render(){
return(
<div class="options-container">
<div className="option-row" style={{padding: '2px 10px'}}>
<p>
By changing the value of D2 to select the currency calculation strategy, you will notice that the table on the left has changed.
</p>
</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"></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;
}
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);
