Protection

This samples shows how you can limit the user operation on workbook.

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.
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 style = new GC.Spread.Sheets.Style(); style.locked = false; style.backColor = 'lightGreen'; var salesData = [ [ 'SalesPers', 'Region' ], [ 'Joe', 'North' ], [ 'Robert', 'South' ], [ 'Michelle', 'East' ], [ 'Erich', 'West' ], [ 'Dafna', 'North' ], [ 'Rob', 'South' ], [ 'SalesPers', 'Region' ], [ 'Joe', 'North' ], [ 'Robert', 'South' ], [ 'Michelle', 'East' ], [ 'Erich', 'West' ], [ 'Dafna', 'North' ], [ 'Rob', 'South' ], [ 'SalesPers', 'Region' ], [ 'Joe', 'North' ], [ 'Robert', 'South' ], [ 'Michelle', 'East' ], [ 'Erich', 'West' ], [ 'Dafna', 'North' ], [ 'Rob', 'South' ] ]; sheet.setArray(0, 0, salesData); sheet.setStyle(1, 1, style); sheet.setStyle(1, 2, style); sheet.setStyle(5, 1, style); sheet.setStyle(5, 2, style); sheet.setStyle(8, -1, style); sheet.setStyle(9, -1, style); sheet.setStyle(12, -1, style); sheet.setStyle(13, -1, style); sheet.setStyle(-1, 8, style); sheet.setStyle(-1, 9, style); sheet.setStyle(-1, 12, style); sheet.setStyle(-1, 13, style); var style2 = new GC.Spread.Sheets.Style(); style2.locked = true; style2.backColor = 'gray'; sheet.setStyle(13, 1, style2); sheet.setStyle(1, 13, style2); sheet.tables.add('table1', 2, 3, 4, 4); var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0, 0, 100, 2)); sheet.rowFilter(filter); sheet.comments.add(17, 0, 'locked'); sheet.comments.add(17, 4, 'unlocked'); sheet.comments.get(17, 0).locked(true).displayMode(1); sheet.comments.get(17, 4).locked(false).displayMode(1).lockText(false); 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 }; 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('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 }; 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 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: lavender; } .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; }