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.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 2
});
initSpread(spread);
};
function initSpread(spread) {
initWorkbook(spread);
initEvents(spread);
}
function initWorkbook(spread) {
var 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();
}
function initEvents(spread) {
document.getElementById('formulaFormatHint').onchange = function (e) {
var formulaFormatHint = e.target.checked;
spread.options.formulaFormatHint = formulaFormatHint;
};
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p>Cells C8:C15 are auto formatted (using format hints) after the formulas were entered.</p>
<div class="sp-demo-childBlock">
<div id="settingsDiv">
<div class="option-row">
<input type="checkbox" id="formulaFormatHint" checked="checked"/>
<label for="formulaFormatHint">Enable format hints</label>
</div>
</div>
</div>
</div>
</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;
}
