概述
本 Demo 展示了如何在绩效评估表单中使用 hidden 属性隐藏公式，保护薪资增长和年终奖金等敏感计算逻辑。Demo 提供了交互式界面，可以实时设置单元格的 hidden 和 locked 属性，并切换工作表保护状态。
实现思路
使用 fromJSON 加载预设的绩效评估表单数据
初始化时为特定单元格（薪资增长和年终奖金计算列）设置 hidden 和 locked 属性
启用工作表保护（isProtected = true）使 hidden 属性生效
绑定事件监听器，实现单元格属性设置、保护状态切换和公式显示功能
代码解析
设置单元格的隐藏和锁定属性
这段代码通过 getCell(row, col) 方法获取指定单元格，然后设置 hidden(true) 隐藏公式，locked(false) 允许编辑单元格值。重要：hidden 属性必须在工作表受保护时才生效。
动态设置单元格属性
这段代码实现了动态设置任意单元格的 hidden 和 locked 属性。通过 SelectionChanged 事件获取当前活动单元格，然后在点击"应用"按钮时更新属性。
工作表保护控制
使用 sheet.protect() 和 sheet.unprotect() 方法控制工作表保护状态，配合 hidden 和 locked 属性实现灵活的公式保护策略。
运行效果
打开 Demo 后，薪资增长和年终奖金列的公式已被隐藏，选择这些单元格时公式栏为空
点击"取消保护"按钮后，隐藏的公式会重新显示在公式栏中
点击任意单元格后，可以勾选"隐藏"和"锁定"复选框，然后点击"应用"按钮设置属性
点击"显示公式"按钮可以切换整个工作表的公式显示状态（但不会显示已隐藏的单元格公式）
当工作表受保护且单元格设置了 hidden 属性时，复制单元格只会复制值，不会复制公式
API 参考
getCell 方法
row：行索引
col：列索引
sheetArea：可选，工作表区域，默认为 viewport
返回值：CellRange 对象
hidden 方法
value：布尔值，true 表示隐藏公式
仅在工作表受保护时生效
返回值：无参数时返回当前值，有参数时返回单元格对象
locked 方法
value：布尔值，true 表示锁定单元格
仅在工作表受保护时生效
返回值：无参数时返回当前值，有参数时返回单元格对象
protect 和 unprotect 方法
protect()：启用工作表保护
unprotect()：取消工作表保护
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 = "未设置公式";
}
}
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.getActiveSheet().getActiveRowIndex();
var col = sheet.getActiveSheet().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" />
<meta name="spreadjs culture" content="zh-cn" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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="保护" id="protectBtn" style="width:100px;" />
<input type="button" value="取消保护" id="unprotectBtn" style="width:100px;" />
<img id="protectStatus" style="height: 25px" />
</div>
<div class="cellFormatSetting">
<div class="option-row">
<h4>当前单元格是 :
<span id="commentTip"></span>
</h4>
</div>
<div class="option-row">
<div class="option">
<label>实际公式</label>
<input id="Formula" disabled />
</div>
</div>
<div class="option-row">
<div class="checkbox">
<input id="Locked" type="checkbox" checked />
<label for="Locked">锁定</label>
</div>
<div class="checkbox">
<input id="Hidden" type="checkbox" checked />
<label for="Hidden">隐藏</label>
</div>
</div>
<div class="option-row">
<input type="button" id="setProperty" value="应用" />
</div>
</div>
<div class="option-row">
<input id="ShowFormula" type="button" value="显示公式" />
</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;
}
