SpreadJS 15
Features / Worksheet / Protect Worksheet and Lock Cells
In This Topic
    Protect Worksheet and Lock Cells
    In This Topic

    SpreadJS allows you to protect a worksheet which locks all cells and prevent 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 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.

    JavaScript
    Copy Code 
    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.
    JavaScript
    Copy Code 
    // 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 set to True:

    Property Description
    allowDragInsertRows Allows you to perform the drag operation while inserting rows.
    allowDragInsertColumns Allows you to perform the drag operation while inserting columns.
    allowInsertRows Allows you to insert rows.
    allowInsertColumns Allows you to insert columns.
    allowDeleteRows Allows you to delete rows.
    allowDeleteColumns Allows you to delete columns.
    allowSelectLockedCells Allows you to select locked cells.
    allowSelectUnlockedCells Allows you to select unlocked cells.
    allowSort Allows you to sort ranges.
    allowFilter Allows you to filter ranges.
    allowEditObjects Allows you to edit floating objects.
    allowResizeRows Allows you to resize rows.
    allowResizeColumns Allows you to resize columns.
    allowOutlineRows Allows you to expand or collapse the row groups.
    allowOutlineColumns Allows you to expand or collapse the column groups.

    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.

    JavaScript
    Copy Code 
    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 insertion and deletion of rows and columns command 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:

    JavaScript
    Copy Code 
    // 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)
    }
});

     

     

    Copyright © 2022 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page