Protection

Spread allows you to limit user operations in a workbook. By locking/unlocking cells or making menus visible/invisible you can decide what users can edit or not.

Sometimes you don't want the values of cells in a 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/@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="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; }