Protection

Spread allows you to limit the user operation on workbook. By locking/unlocking cells or making menus visible/ not visible you can decide what users can edit or not.

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. 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); }; 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('chkProtectSheet').checked = sheet.options.isProtected; _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; _getElementById('chkProtectSheet').addEventListener('click', function() { var sheet = spread.getActiveSheet(); var value = this.checked; sheet.options.isProtected = value; }); _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"> <input id="chkProtectSheet" type="checkbox" /> <label for="chkProtectSheet">Protect Sheet</label> </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; }