There are three steps that are done behind the scenes with format hints.
Fetch the formats
Fetch the format from the referenced cell
Fetch the percentage format from the percent value
Fetch the format from the special functions, e.g. NOW/TODAY/IRR/NPV
Fetch the format from the dynamic reference e.g. =INDIRECT(“C3“)
Calculate the format
Here is strategy that is followed in calculating the format:
A date format minus a date format will be an empty format
A date format plus a time format will be a datetime format
A currency format multiplied by a number format will be a currency format
A number format multiplied by a percentage format will be a number format
An empty format multiplied by a percentage format will be an empty format
The SUM/MAX/MIN/AVEDEV/FLOOR/ROUND/ROUNDUP/ROUNDDOWN/INT/TRUNC functions will use the format in the first cell of the first argument
Apply the format
When entering a formula in a cell, SpreadJS will apply the format hints to the cell if the cell doesn't have an explicitly set format.
You can use the spread.options.formulaFormatHint option to control whether to enable the format hints or not.
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, Column } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const initSpread = function (value) {
setSpread(value);
let workbook = value;
let sheet = value.getActiveSheet();
sheet.suspendPaint();
sheet.setColumnWidth(0, 40);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 150);
sheet.setValue(0, 0, 'Preset:');
sheet.setValue(1, 1, 'Init a scientific format');
sheet.setFormatter(1, 2, "0.00E+00");
sheet.setValue(1, 2, 1100);
sheet.setValue(0, 0, 'Preset:');
sheet.setValue(2, 1, 'Init a currency format');
sheet.setFormatter(2, 2, "$#,##0;[Red]($#,##0)");
sheet.setValue(2, 2, 1100);
sheet.setValue(3, 1, 'Init a date format');
sheet.setFormatter(3, 2, "M/d/yyyy");
sheet.setValue(3, 2, new Date(2023, 2, 23));
sheet.setValue(4, 1, 'Init a number format');
sheet.setFormatter(4, 2, "[red][DBNum1][$-411]0");
sheet.setValue(4, 2, 123);
sheet.setValue(6, 0, 'Format Hints:');
sheet.setValue(7, 1, 'Format hints from reference');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 7,
col: 2,
newValue: "=C3"
});
sheet.setValue(8, 1, 'Format hints from functions');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 8,
col: 2,
newValue: "=TIME(12,30,0)"
});
sheet.setValue(9, 1, 'Add operate of date format and time format');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 9,
col: 2,
newValue: "=C4+C9"
});
sheet.setValue(10, 1, 'Number format plus a number');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 10,
col: 2,
newValue: "=C5+31"
});
sheet.setValue(11, 1, 'The currency format in multiplication');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 11,
col: 2,
newValue: "=C3*95%"
});
sheet.setValue(12, 1, 'Format hints from INDIRECT result');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 12,
col: 2,
newValue: '=INDIRECT("C4")+1'
});
sheet.setValue(13, 1, 'Currency format multiplied by scientific format');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 13,
col: 2,
newValue: '=C2*C3'
});
sheet.setValue(14, 1, 'Percent format multiplied by scientific format');
workbook.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 14,
col: 2,
newValue: '=C2*10%'
});
sheet.resumePaint();
}
const formulaFormatHint = function ($event) {
const formulaFormatHint = $event.target.checked;
spread.options.formulaFormatHint = formulaFormatHint;
}
return (
<div class="sample-tutorial" >
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
formulaFormatHint={(e) => formulaFormatHint(e)}
></Panel>
</div>
);
}
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) {
return (
<div class="options-container">
<div className="option-row">
<p>The C8:C15 are the auto format (format hints) after input formulas.</p>
</div>
<div className="option-row">
<CheckBoxInput id="formulaFormatHint" checked={true}
onChange={props.formulaFormatHint}></CheckBoxInput>
<label htmlFor="formulaFormatHint">Enable format hints</label>
</div>
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component, useState = React.useState;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={ spread => this.initSpread(spread) }>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<Panel
formulaFormatHint={ (e) => this.formulaFormatHint(e) }
></Panel>
</div>
);
}
initSpread(spread) {
this.spread = spread;
let sheet = spread.getActiveSheet();
sheet.suspendPaint();
sheet.setColumnWidth(0, 40);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 150);
sheet.setValue(0, 0, 'Preset:');
sheet.setValue(1, 1, 'Init a scientific format');
sheet.setFormatter(1, 2, "0.00E+00");
sheet.setValue(1, 2, 1100);
sheet.setValue(0, 0, 'Preset:');
sheet.setValue(2, 1, 'Init a currency format');
sheet.setFormatter(2, 2, "$#,##0;[Red]($#,##0)");
sheet.setValue(2, 2, 1100);
sheet.setValue(3, 1, 'Init a date format');
sheet.setFormatter(3, 2, "M/d/yyyy");
sheet.setValue(3, 2, new Date(2023, 2, 23));
sheet.setValue(4, 1, 'Init a number format');
sheet.setFormatter(4, 2, "[red][DBNum1][$-411]0");
sheet.setValue(4, 2, 123);
sheet.setValue(6, 0, 'Format Hints:');
sheet.setValue(7, 1, 'Format hints from reference');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 7,
col: 2,
newValue: "=C3"
});
sheet.setValue(8, 1, 'Format hints from functions');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 8,
col: 2,
newValue: "=TIME(12,30,0)"
});
sheet.setValue(9, 1, 'Add operate of date format and time format');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 9,
col: 2,
newValue: "=C4+C9"
});
sheet.setValue(10, 1, 'Number format plus a number');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 10,
col: 2,
newValue: "=C5+31"
});
sheet.setValue(11, 1, 'The currency format in multiplication');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 11,
col: 2,
newValue: "=C3*95%"
});
sheet.setValue(12, 1, 'Format hints from INDIRECT result');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 12,
col: 2,
newValue: '=INDIRECT("C4")+1'
});
sheet.setValue(13, 1, 'Currency format multiplied by scientific format');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 13,
col: 2,
newValue: '=C2*C3'
});
sheet.setValue(14, 1, 'Percent format multiplied by scientific format');
spread.commandManager().execute({
cmd: "editCell", // Use the command to set formula will have the format hints
sheetName: "Sheet1",
row: 14,
col: 2,
newValue: '=C2*10%'
});
sheet.resumePaint();
}
formulaFormatHint($event) {
let spread = this.spread, formulaFormatHint = $event.target.checked;
spread.options.formulaFormatHint = formulaFormatHint;
}
}
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)
} }/>
);
}
class Panel extends Component {
constructor(props) {
super(props);
}
render() {
let props = this.props;
return (
<div class="options-container">
<div className="option-row">
<p>Cells C8:C15 are auto formatted (using format hints) after the formulas were entered.</p>
</div>
<div className="option-row">
<CheckBoxInput id="formulaFormatHint" checked={ true }
onChange={ props.formulaFormatHint }></CheckBoxInput>
<label htmlFor="formulaFormatHint">Enable format hints</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">
<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;
}
.option-row {
font-size: 14px;
margin-top: 10px;
}
label {
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
}
p{
padding:2px 10px;
background-color:#F4F8EB;
}
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);
