Protect Worksheet and Lock Cells

SpreadJS allows you to protect a worksheet that locks all cells and prevents other users from changing, moving, or deleting the data. However, you can still copy the data from locked cells.

The isProtected option can be set to true to protect a worksheet and the locked method can be used to lock or unlock the cells.

Unlock Cells in a Protected Worksheet

You can allow the user to edit specific cells in a protected worksheet by setting the locked method as False.

sheet.getCell(1,1, GC.Spread.Sheets.SheetArea.viewport).locked(false);
sheet.setValue(1,1,"unLocked");
sheet.getRange(-1,3, -1, 1).locked(false);
sheet.getRange(5, -1, 1, -1).locked(false);
sheet.options.isProtected = true;

Lock a Range of Cells in a Protected Worksheet

You can choose to lock a specific range of cells in a protected worksheet by following the steps below:

1. Unlock all the cells by creating a custom style and setting the locked method as False.

2. Separately lock the cell range you want to be read-only.

// Configure Workbook and Worksheet
var spread = new GC.Spread.Sheets.Workbook("ss");
var sheet = spread.getActiveSheet();
// Unlock all the cells in the worksheet via the styles
let style = new GC.Spread.Sheets.Style();
style.locked = false;
// Set style as default style for all the cells in the sheet
sheet.setDefaultStyle(style);
// Separately lock the cell range you want to be read-only
new GC.Spread.Sheets.CellRange(sheet, 0, 0, 13, 4).locked(true);
// Set sheet to be protected
sheet.options.isProtected = true;

The options.protectionOptions property can be used to specify the areas which can be changed. These areas can include resizing, dragging, inserting, or deleting rows or columns, and so on.

When the isProtected option is set to True, the following properties will take effect:

When the isProtected option is set to false, the above protection options will not take any effect.

Enable Protection Options in a Locked Worksheet

You can protect the worksheet and enable various protection options that are available in SpreadJS.

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();
sheet.options.isProtected = true;
sheet.options.protectionOptions.allowDeleteRows = true;
sheet.options.protectionOptions.allowDeleteColumns = true;
sheet.options.protectionOptions.allowInsertRows = true;
sheet.options.protectionOptions.allowInsertColumns = true;
sheet.options.protectionOptions.allowDragInsertRows = true;
sheet.options.protectionOptions.allowDragInsertColumns = true;
sheet.options.protectionOptions.allowOutlineColumns = true;

Note: The protection options: allowInsertRows, allowInsertColumns, allowDeleteRows, and allowDeleteColumns are available only when you are performing an operation on the user interface. In other words, the value will only restrict the insertion and deletion of rows and columns commands in the context menu.

Set Alert Message for Protected Groups

By default, you cannot expand or collapse the outline row or column groups in a protected worksheet. An invalidOperation event is triggered which you can also bind to display an alert message as shown below:

// Set alert message
spread.bind(GC.Spread.Sheets.Events.InvalidOperation, (e, args) => {
    if(args.invalidType === GC.Spread.Sheets.InvalidOperationType.groupProtected){
    args.message="Expand or Collapse operation is not allowed for a protected worksheet.";
    alert(args.message)
    }
}); 

Protect Sheet with Password

You can choose to set the password while protecting the worksheet using the protect method. However, on invoking the protect method, the isProtected option will be set to true automatically.

//Protect the worksheet 
var password = 'AddedPassword'; 
activeSheet.protect(password); 

You can further unprotect the worksheet by providing the password as a parameter to the unprotect method.

//unprotect the worksheet with a password
if(activeSheet.hasPassword()) {
    activeSheet.unprotect(password);
} else {
    activeSheet.unprotect();
}

Hide Formula in a Protected Worksheet

You can control the visibility of formula cells in a protected sheet by using either the hidden property of the Style class or the hidden method of the CellRange class. By default, the hidden attribute is set to false, indicating that the formulas are not hidden. Additionally, it supports export and import of SSJSON, SJS, and XLSX.

The hidden attribute is useful in the cases like generating KPI data or year-end bonus based on employee self-assessment and supervisor evaluation, wherein the employer wants to hide the relevant formulas and protect the evaluation system.

The following code sample shows how to a hide cell using the hidden property in GC.Spread.Sheets.Style type.

// Sets whether the cell formula is visible when the sheet is protected.
activeSheet.options.isProtected = true;

// Create a style with hidden property true
var style = new GC.Spread.Sheets.Style();
style.hidden = true;
activeSheet.setStyle(1, 1, style, GC.Spread.Sheets.SheetArea.viewport);

// Apply style to a cell containing formula
activeSheet.setFormula(1, 1, "=SUM(1,2)");

Alternatively, this code sample uses the hidden method in GC.Spread.Sheets.CellRange type.

// Sets whether the cell formula is visible when the sheet is protected.
activeSheet.options.isProtected = true;
activeSheet.setFormula(1, 3, "=SUM(10,20)");
activeSheet.getRange(1, 3, GC.Spread.Sheets.SheetArea.viewport).hidden(true);

The following SpreadJS functionalities are affected by hidden attributes:

• The Formula bar and Formula Editor panel do not display any formula when the hidden property is set on cells.

• The default data in the Input Editor is empty when the hidden cell is in the edit mode.

• Hidden cells do not copy formulas, only results, but cuts always contain formulas.

  However, you can prevent this by setting the locked property to true.

• The showFormulas property does not display the formulas of hidden cells.

• FORMULATEXT() method cannot get the cells’ formula if the hidden property has taken effect.

Note that the hidden and locked properties mutually influence each other’s functionality as explained in the given table:

Limitations

• TableSheet, GanttSheet, and ReportSheet do not support hidden attributes.

• Hidden attributes do not affect the formulas accessed through the APIs.

Using Designer

To access the Protect Sheet dialog, navigate to the Protect Sheet option by right-clicking on the sheet name displayed on the Tab strip.

Steps to protect and unprotect the worksheet using Protect Sheet Dialog

1. Enter the password to protect the worksheet and check/ uncheck multiple options from the available list of options in the Protect Sheet dialog box.

   
   

   
   

   
   

2. When you enter the password to protect the worksheet, another dialog confirms the password.

   
   

   
   

Once a password is successfully set, you can access the protected worksheet, but cannot make any changes to the sheet. To unprotect the worksheet, enter the set password using the Unprotect Sheet dialog.