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:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<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>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import { ref } from "vue";
import "@mescius/spread-sheets-vue";
const spreadRef = ref(null);
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';
function initSpread(spread) {
spread.suspendPaint();
spread.fromJSON(performanceEvaluationSheet);
initSheet1(spread);
bindEvent(spread);
spread.resumePaint();
}
function initSheet1(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))
};
function updateLabels(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";
}
}
function bindEvent(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);
}
</script>
<style scoped>
.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%;
}</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/performanceEvaluationSheet.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
