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:
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';
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.suspendPaint();
spread.fromJSON(performanceEvaluationSheet);
initSheet1(spread);
bindEvent(spread);
spread.resumePaint();
};
function initSheet1(spread) {
var 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))
};
function updateLabels(sheet, activeCell) {
_getElementById("Locked").checked = activeCell.locked();
_getElementById("Hidden").checked = activeCell.hidden();
var formula = activeCell.formula();
if (formula) {
_getElementById("Formula").value = "=" + formula;
} else {
_getElementById("Formula").value = "no formula is set";
}
}
function bindEvent(spread) {
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function(e, info) {
var sheetTmp = info.sheet;
var row = sheetTmp.getActiveRowIndex();
var col = sheetTmp.getActiveColumnIndex();
_getElementById("commentTip").innerHTML = "[ " + row + " : " + col + " ]";
activeCell = sheetTmp.getCell(row, col);
updateLabels(sheetTmp, activeCell);
});
_getElementById("setProperty").addEventListener('click', function() {
var sheet = spread.getActiveSheet();
var row = sheet.getActiveRowIndex();
var col = sheet.getActiveColumnIndex();
activeCell = sheet.getCell(row, col);
if (activeCell) {
activeCell.locked(_getElementById("Locked").checked);
activeCell.hidden(_getElementById("Hidden").checked);
}
})
_getElementById("ShowFormula").addEventListener("click", () => {
var 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() {
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() {
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);
}
<!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="$DEMOROOT$/spread/source/data/performanceEvaluationSheet.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">
<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" checked />
<label for="Locked">Locked</label>
</div>
<div class="checkbox">
<input id="Hidden" type="checkbox" checked />
<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>
</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;
}
