Sometimes you may wish to be able to hide formulas and not show them to external parties, Just like the performance evaluation sheet form above, Salary Increase Result and Year-end Bonus calculations are relatively private data, and we do not wish for others to see the specific calculation formulas. To do this, use the hidden, For example:
When the hidden attribute takes effect, the following functionalities will be affected.
When the hidden cell is in edit mode, the default data in the Input Editor will be empty.
When the hidden cell is copied to other cells, only the result will be copied, not the formula.
The sheet option 'showFormulas' cannot display the formula of cells that hidden have taken effect.
When the hidden cell is an active cell, the Formula Bar will not display any data.
The Formula Editor Panel cannot display the formula of hidden cells that have taken effect.
Note that if the worksheet is not protected, the 'hidden' property will not take effect.
If you want to thoroughly prevent others from knowing and tampering with your formulas, you can use 'hidden' and 'locked' together. This way, after the worksheet is protected, the cell will be both unable to view the formula and modify it. For example:
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
const alreadyProtect = 'The worksheet is already protected!';
const unprotectImg = 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAACXBIWXMAAAsTAAALEwEAmpwYAAAB0UlEQVR4nO2YzU7CQBDHNyae9A30qF6NNlFv7QzhqheukN1An4Mbnv14A70YwgERX0XiWRNBKl8eMDGuGWy0LopoS0tl/8kkZLMT/r+dmW1axrS0tAKTYRjziJgCgFNEvAKAR0Tsub9pLUV72DTKsqw9ALhGRDkqaA/tZdOifD4/h4j7PxlX4gURC5QbtX/2B/PeKERqnlrBPU1vi/QR8dCyrO1kMrlAYZrmDgAcAcCTWgkA2I3EPA2j2vMAcAMA69/lJBKJDQC4VWfCiGKw6UZRT36UeU/e5heVSLGw5V6LXhMHv8g9VuBPWNhCxJoCsDVuLs2EkltjYct9QL2bME1zcdxc2qsA9FjYUq/EsPN9SwOgrsD4yuVyy5zzIue8K4SQkwz+9h8lzvlqkOadSRsXwyBOJpNZ8g1AJx+2efEBcRYEQDdCgLZvgKjMCzc0gNAVELqFfGkmW8i2bXl5cS7bTkO2mnVZrZQHa7EBqFbKUj73PwWtxQag7TSGAGgtNgCtZn0I4OH+Lj4A1bi3kG3bA8NUiVgOsQgwmAYQugJSt9Cst1An7q+UpQgBir4B0un0mhCiGYF5J5vNrrAgRN9n6BNHSO3UoZMPzLyW1j/XK1zJDTLJ864hAAAAAElFTkSuQmCC';
const protectImg = 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAAACXBIWXMAAAsTAAALEwEAmpwYAAAB10lEQVR4nO2Yz0rDQBDGB0Ev+gZ6VK+iAfWWzJRe9dL3qfVQlWwR/7yBXqT0JepD2Nar0h4s2ERq3YArW4LGrdZq0qSx+8FACDvw/XZmNmQBtLS0IpNhGLNElEPESyK6QcQnInL9Z/kuJ9fAJMqyrF1EbBCRGBZyjVwLk6J8Pj9DRAc/GVfilYiKMjdp//AH88EoJmpetoK/m8EW6RHRqWVZW9lsdl6GaZrbiHiGiC9qJRBxJxHzchjVnkfEO0Rc+y4nk8msI+K9OhNGEoMtTxR154eZD+RtfFGJHMQt/1gMmjj5Re65An8BcYuIagrA5qi5ciaU3BrELf8D9W7CNM2FUXPlWgXAhbilHolx54eWBiBdgdElDmGJMyhzBo7HQIwzOAOHM6j0jmAlOvM2tMdt3FNBbGiLEiyGBpA7H7d576MaV1EAOAkCPIYGSMq854cG8HQFQLdQKE1nCx3Pie71vnBbDeE266JbLfTfpQagWy0Ix3E+RR8iLQBuqzEA4LZuUwTQrA8CNGtpb6G9lA1xtdCvRCqH2IswQAMwXQGhW2iqW4jb0En1LyVnUEkQoBwaoMdglTN4iN28De3nEixDFJL3M/KKI4524jZ05M5HZl5L65/rDc6nQtkkwmMVAAAAAElFTkSuQmCC';
let initSpread = function(spread) {
spread.suspendPaint();
spread.fromJSON(performanceEvaluationSheet);
initSheet1(spread);
bindEvent(spread);
spread.resumePaint();
}
let initSheet1 = function(spread) {
let sheet = spread.getActiveSheet();
sheet.getCell(13, 1).hidden(true);
sheet.getCell(13, 1).locked(false);
sheet.getCell(14, 1).hidden(true);
sheet.getCell(14, 1).locked(false);
sheet.getCell(14, 5).hidden(true);
sheet.getCell(14, 5).locked(false);
sheet.options.isProtected = true;
updateLabels(sheet, sheet.getCell(13, 1))
};
let updateLabels = function(sheet, activeCell) {
_getElementById("Locked").checked = activeCell.locked();
_getElementById("Hidden").checked = activeCell.hidden();
let formula = activeCell.formula();
if (formula) {
_getElementById("Formula").value = "=" + formula;
} else {
_getElementById("Formula").value = "no formula is set";
}
}
let bindEvent = function(spread) {
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, (e, info) => {
let sheetTmp = info.sheet;
let row = sheetTmp.getActiveRowIndex();
let col = sheetTmp.getActiveColumnIndex();
_getElementById("commentTip").innerHTML = "[ " + row + " : " + col + " ]";
let activeCell = sheetTmp.getCell(row, col);
updateLabels(sheetTmp, activeCell);
});
_getElementById("setProperty").addEventListener('click', function() {
let sheet = spread.getActiveSheet();
let row = sheet.getActiveRowIndex();
let col = sheet.getActiveColumnIndex();
let activeCell = sheet.getCell(row, col);
if (activeCell) {
activeCell.locked(_getElementById("Locked").checked);
activeCell.hidden(_getElementById("Hidden").checked);
}
})
_getElementById("ShowFormula").addEventListener("click", () => {
let sheet = spread.getActiveSheet();
let showFormulas = sheet.options.showFormulas;
sheet.options.showFormulas = !showFormulas;
})
const protectStatus = _getElementById('protectStatus');
let currentProtect = spread.getActiveSheet().options.isProtected
protectStatus.src = currentProtect ? protectImg : unprotectImg;
let protectBtn = _getElementById('protectBtn'), unprotectBtn = _getElementById('unprotectBtn');
protectBtn.disabled = currentProtect;
unprotectBtn.disabled = !currentProtect;
protectBtn.addEventListener('click', function() {
let sheet = spread.getActiveSheet();
if (sheet.options.isProtected) {
alert(alreadyProtect);
return;
}
sheet.protect();
protectStatus.src = protectImg;
protectBtn.disabled = true;
unprotectBtn.disabled = false;
});
unprotectBtn.addEventListener('click', function() {
let sheet = spread.getActiveSheet();
protectStatus.src = sheet.options.isProtected ? protectImg : unprotectImg;
sheet.unprotect();
protectStatus.src = unprotectImg;
protectBtn.disabled = false;
unprotectBtn.disabled = true;
})
}
function _getElementById(id) {
return document.getElementById(id);
}
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet />
</SpreadSheets>
</div>
<div class="options-container">
<div class="option-row">
<input type="button" value="Protect" id="protectBtn" style={{width:"100px"}} />
<input type="button" value="Unprotect" id="unprotectBtn" style={{width:"100px"}} />
<img id="protectStatus" style={{height:"25px"}} />
</div>
<div class="cellFormatSetting">
<div class="option-row">
<h4>Current cell is :
<span id="commentTip"></span>
</h4>
</div>
<div class="option-row">
<div class="option">
<label>Actual Formula</label>
<input id="Formula" disabled />
</div>
</div>
<div class="option-row">
<div class="checkbox">
<input id="Locked" type="checkbox" />
<label for="Locked">Locked</label>
</div>
<div class="checkbox">
<input id="Hidden" type="checkbox" />
<label for="Hidden">Hidden</label>
</div>
</div>
<div class="option-row">
<input type="button" id="setProperty" value="Apply" />
</div>
</div>
<div class="option-row">
<input id="ShowFormula" type="button" value="Show Formula" />
</div>
</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/performanceEvaluationSheet.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;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.sample-options{
z-index: 1000;
}
.option {
padding-bottom: 6px;
}
.checkbox {
padding-right: 12px;
display: inline-block;
}
label {
display: inline-block;
min-width: 100px;
}
input, select {
width: 100%;
padding: 4px 0;
margin-top: 4px;
box-sizing: border-box;
}
input[type=checkbox] {
width: auto;
padding: 0;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.cellFormatSetting {
margin: 20px auto;
padding: 10px;
border: 2px solid #C0C0C0;
}
#protectStatus {
margin-bottom: -5px;
}
#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);
