Sometimes you don't want the value of the cells on this sheet to be edited. If so, you can use the isProtected option to set and get whether the sheet is protected.
If you want to protect the worksheet with a password, you can use protect, unprotect and hasPassword API.
When a sheet is protected, you can use protectionOptions to limit user actions by providing an object with the following properties:
allowSelectLockedCells: Boolean that represents whether the user can select locked cells.
allowSelectUnlockedCells: Boolean that represents whether the user can select unlocked cells.
allowSort: Boolean that represents whether the user can sort ranges.
allowFilter: Boolean that represents whether the user can filter ranges.
allowEditObjects: Boolean that represents whether the user can edit floating objects.
allowResizeRows: Boolean that represents whether the user can resize rows.
allowResizeColumns: Boolean that represents whether the user can resize columns.
allowDragInsertRows: Boolean that represents whether the user can drag insert rows.
allowDragInsertColumns: Boolean that represents whether the user can drag insert columns.
allowInsertRows: Boolean that represents whether the user can insert rows.
allowInsertColumns: Boolean that represents whether the user can insert columns.
allowDeleteRows: Boolean that represents whether the user can delete rows.
allowDeleteColumns: Boolean that represents whether the user can delete columns.
allowOutlineRows: Boolean that represents whether the user can expand or collapse row outline.
allowOutlineColumns: Boolean that represents whether the user can expand or collapse column outline.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), {
sheetCount: 1
});
initSpread(spread);
};
const passwordWrongTip = 'Password is not correct!';
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) {
var sheet = spread.getActiveSheet();
spread.suspendPaint();
var salesData = [
['Salesperson', 'Region'],
['Joe', 'North'],
['Robert', 'South'],
['Michelle', 'East'],
['Erich', 'West'],
['Dafna', 'North'],
['Rob', 'South'],
['Joe', 'North'],
['Robert', 'South'],
['Michelle', 'East'],
['Erich', 'West'],
['Dafna', 'North'],
['Rob', 'South'],
['Joe', 'North'],
['Robert', 'South'],
['Michelle', 'East']
];
sheet.setArray(0, 0, salesData);
sheet.setColumnWidth(0, 120);
sheet.setColumnWidth(1, 120);
//unlocked cells
var style = new GC.Spread.Sheets.Style();
style.locked = false;
style.backColor = '#C3C3C3';
sheet.setStyle(-1, 8, style);
sheet.setStyle(-1, 9, style);
sheet.setStyle(15, -1, style);
sheet.setStyle(16, -1, style);
sheet.setStyle(8, 2, style);
//locked cells
var style2 = new GC.Spread.Sheets.Style();
style2.locked = true;
style2.backColor = '#F4F8EB';
sheet.setStyle(13, -1, style2);
sheet.setStyle(18, 8, style2);
sheet.setStyle(0, 0, style2)
sheet.setStyle(0, 1, style2);
var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(1, 0, 100, 2));
sheet.rowFilter(filter);
sheet.comments.add(5, 4, 'locked comment');
sheet.comments.add(22, 4, 'unlocked comment');
sheet.comments.get(5, 4).locked(true).displayMode(1);
sheet.comments.get(22, 4).locked(false).displayMode(1).lockText(false);
var _commandManager = spread.commandManager();
_commandManager.execute({
cmd: "outlineColumn",
sheetName: sheet.name(),
index: 11,
count: 3
});
_commandManager.execute({
cmd: "outlineRow",
sheetName: sheet.name(),
index: 18,
count: 3
});
spread.resumePaint();
var option = {
allowSelectLockedCells:true,
allowSelectUnlockedCells:true,
allowFilter: true,
allowSort: false,
allowResizeRows: true,
allowResizeColumns: false,
allowEditObjects: false,
allowDragInsertRows: false,
allowDragInsertColumns: false,
allowInsertRows: false,
allowInsertColumns: false,
allowDeleteRows: false,
allowDeleteColumns: false,
allowOutlineColumns: false,
allowOutlineRows: false
};
sheet.options.protectionOptions = option;
sheet.options.isProtected = true;
option = sheet.options.protectionOptions;
_getElementById('chkSelectLockedCells').checked = option.allowSelectLockedCells;
_getElementById('chkSelectUnlockedCells').checked = option.allowSelectUnlockedCells;
_getElementById('chkAllowSort').checked = option.allowSort;
_getElementById('chkAllowFilter').checked = option.allowFilter;
_getElementById('chkAllowResizeRows').checked = option.allowResizeRows;
_getElementById('chkAllowResizeColumns').checked = option.allowResizeColumns;
_getElementById('chkAllowEditObjects').checked = option.allowEditObjects;
_getElementById('chkAllowDragInsertRows').checked = option.allowDragInsertRows;
_getElementById('chkAllowDragInsertColumns').checked = option.allowDragInsertColumns;
_getElementById('chkAllowInsertRows').checked = option.allowInsertRows;
_getElementById('chkAllowInsertColumns').checked = option.allowInsertColumns;
_getElementById('chkAllowDeleteRows').checked = option.allowDeleteRows;
_getElementById('chkAllowDeleteColumns').checked = option.allowDeleteColumns;
_getElementById('chkallowOutlineColumns').checked = option.allowOutlineColumns;
_getElementById('chkallowOutlineRows').checked = option.allowOutlineRows;
const protectStatus = _getElementById('protectStatus');
const passwordInput = _getElementById('protectPassword')
protectStatus.src = sheet.options.isProtected ? protectImg : unprotectImg;
let protectBtn = _getElementById('protectBtn'), unprotectBtn = _getElementById('unprotectBtn');
protectBtn.disabled = true;
protectBtn.addEventListener('click', function () {
sheet = spread.getActiveSheet();
if (sheet.options.isProtected) {
alert(alreadyProtect);
return ;
}
const password = passwordInput.value;
passwordInput.value = '';
sheet.protect(password);
protectStatus.src = protectImg;
protectBtn.disabled = true;
unprotectBtn.disabled = false;
});
unprotectBtn.addEventListener('click', function () {
const password = passwordInput.value;
sheet = spread.getActiveSheet();
if (sheet.hasPassword()) {
let success = sheet.unprotect(password);
if (!success) {
alert(passwordWrongTip);
return ;
}
} else {
sheet.unprotect();
}
passwordInput.value = '';
protectStatus.src = unprotectImg;
protectBtn.disabled = false;
unprotectBtn.disabled = true;
})
_getElementById('setProtectionOptions').addEventListener('click', function() {
var option = {
allowSelectLockedCells: _getElementById('chkSelectLockedCells').checked,
allowSelectUnlockedCells: _getElementById('chkSelectUnlockedCells').checked,
allowSort: _getElementById('chkAllowSort').checked,
allowFilter: _getElementById('chkAllowFilter').checked,
allowResizeRows: _getElementById('chkAllowResizeRows').checked,
allowResizeColumns: _getElementById('chkAllowResizeColumns').checked,
allowEditObjects: _getElementById('chkAllowEditObjects').checked,
allowDragInsertRows: _getElementById('chkAllowDragInsertRows').checked,
allowDragInsertColumns: _getElementById('chkAllowDragInsertColumns').checked,
allowInsertRows: _getElementById('chkAllowInsertRows').checked,
allowInsertColumns: _getElementById('chkAllowInsertColumns').checked,
allowDeleteRows: _getElementById('chkAllowDeleteRows').checked,
allowDeleteColumns: _getElementById('chkAllowDeleteColumns').checked,
allowOutlineColumns: _getElementById('chkallowOutlineColumns').checked,
allowOutlineRows: _getElementById('chkallowOutlineRows').checked
};
var sheet = spread.getActiveSheet();
sheet.options.protectionOptions = option;
});
}
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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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">
<br/>
<div class="option-row" style="height: 25px">
<img id = "protectStatus" style="height: 25px"/>
</div>
<div class="option-row">
<label for="protectPassword">Password (optional)</label>
<input id="protectPassword" type="password"/>
<input type="button" value="Protect" id="protectBtn" style="width:100px;"/>
<input type="button" value="Unprotect" id="unprotectBtn" style="width:100px;"/>
</div>
<br />
<div class="option-row">
<label >Use following protection options to limit what the user can do in regards to editing the sheet.</label>
</div>
<div>
<div class="option-row">
<input id="chkSelectLockedCells" type="checkbox" checked="checked" />
<label for="chkSelectLockedCells" class="sizedLabel">Select locked cells</label>
</div>
<div class="option-row">
<input id="chkSelectUnlockedCells" type="checkbox" checked="checked" />
<label for="chkSelectUnlockedCells" class="sizedLabel">Select unlocked cells</label>
</div>
<div class="option-row">
<input id="chkAllowSort" type="checkbox" />
<label for="chkAllowSort" class="sizedLabel">Sort</label>
</div>
<div class="option-row">
<input id="chkAllowFilter" type="checkbox" />
<label for="chkAllowFilter" class="sizedLabel">Filter</label>
</div>
<div class="option-row">
<input id="chkAllowResizeRows" type="checkbox" />
<label for="chkAllowResizeRows" class="sizedLabel">Resize rows</label>
</div>
<div class="option-row">
<input id="chkAllowResizeColumns" type="checkbox" />
<label for="chkAllowResizeColumns" class="sizedLabel">Resize columns</label>
</div>
<div class="option-row">
<input id="chkAllowEditObjects" type="checkbox" />
<label for="chkAllowEditObjects" class="sizedLabel">Edit objects</label>
</div>
<div class="option-row">
<input id="chkAllowDragInsertRows" type="checkbox" />
<label for="chkAllowDragInsertRows" class="sizedLabel">Drag insert rows</label>
</div>
<div class="option-row">
<input id="chkAllowDragInsertColumns" type="checkbox" />
<label for="chkAllowDragInsertColumns" class="sizedLabel">Drag insert columns</label>
</div>
<div class="option-row">
<input id="chkAllowInsertRows" type="checkbox" />
<label for="chkAllowInsertRows" class="sizedLabel">Insert rows</label>
</div>
<div class="option-row">
<input id="chkAllowInsertColumns" type="checkbox" />
<label for="chkAllowInsertColumns" class="sizedLabel">Insert columns</label>
</div>
<div class="option-row">
<input id="chkAllowDeleteRows" type="checkbox" />
<label for="chkAllowDeleteRows" class="sizedLabel">Delete rows</label>
</div>
<div class="option-row">
<input id="chkAllowDeleteColumns" type="checkbox" />
<label for="chkAllowDeleteColumns" class="sizedLabel">Delete columns</label>
</div>
<div class="option-row">
<input id="chkallowOutlineRows" type="checkbox" />
<label for="chkallowOutlineRows" class="sizedLabel">Operate row outline</label>
</div>
<div class="option-row">
<input id="chkallowOutlineColumns" type="checkbox" />
<label for="chkallowOutlineColumns" class="sizedLabel">Operate column outline</label>
</div>
<div class="option-row">
<input type="button" value="Set" id="setProtectionOptions" style="width:100px;"/>
</div>
<div class="option-row">
<label>Select the protection options and then click Set</label>
</div>
</div>
</div>
</div>
</body>
</html>
.sizedLabel {
display: inline-block;
width: 180px;
}
.colorLabel {
background-color: #F4F8EB;
}
.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;
padding: 5px;
}
label {
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}