Protect Sheet in EJ2 JavaScript Spreadsheet control

27 Jul 202624 minutes to read

Sheet protection helps prevent users from modifying the data in the spreadsheet.

Protect Sheet

Protect Sheet feature helps you prevent unauthorized users from accidentally changing, editing, moving, or deleting data in a spreadsheet. You can also protect the sheet with a password.

You can use the isProtected property to enable or disable the sheet protection functionality.

The default value for isProtected property is false.

When a sheet is protected, selecting, formatting, inserting, and deleting operations are disabled by default. To enable some of the above said functionalities the protectSettings options are used in a protected spreadsheet.

The available protectSettings options in the spreadsheet are:

Options Description
Select Cells Used to perform cell selection.
Format Cells Used to perform cell formatting.
Format Rows Used to perform row formatting.
Format Columns Used to perform column formatting.
Insert Link Used to perform hyperlink insertions.

The default value of all protectSettings options are false.

By default, the Protect Sheet module is injected internally into the Spreadsheet to perform the sheet protection function.

User Interface:

In the active Spreadsheet, sheet protection can be performed in any of the following ways:

  • Select the Protect Sheet item in the Ribbon toolbar under the Data tab, and then select your desired options.
  • Right-click the sheet tab, select the Protect Sheet item in the context menu, and then select your desired options.
  • Use the protectSheet() method programmatically.

The following example shows Protect Sheet functionality with password in the Spreadsheet control.

// Initialize the Spreadsheet component.
var columns = [{ width: 100 }, { width: 100 },{ width: 100},
    { width: 100 }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData }], columns: columns,isProtected: true, protectSettings: {selectCells: true} },
                {name: 'Salary', ranges: [{ dataSource: salaryData }], columns: columns}],
    dataBound: function () {
                spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
                spreadsheet.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
            }
});

// Render initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 SpreadSheet</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript UI Controls">
    <meta name="author" content="Syncfusion">
    <link rel="shortcut icon" href="resources/favicon.ico">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    <div id="dialog"></div>
    
    <div id="container">
        <div id="spreadsheet"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Limitations of Protect Sheet

  • Password encryption is not supported

Unprotect Sheet

Unprotect sheet is used to enable all the functionalities that are already disabled in a protected spreadsheet.

User Interface:

In the active Spreadsheet, the sheet Unprotection can be done by any of the following ways:

  • Select the Unprotect Sheet item in the Ribbon toolbar under the Data tab.

  • Right-click the sheet tab, and select the Unprotect Sheet item in the context menu.

  • Use the unprotectSheet() method programmatically.

Unlock the particular cells in the protected sheet

In a protected spreadsheet, to make a particular cell or range of cells editable, use the lockCells() method with the range parameter and the isLocked property set to false.

// Initialize the Spreadsheet component.
var columns = [{ width: 100 }, { width: 100 }, { width: 100 },
{ width: 100 }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData }], columns: columns, isProtected: true, protectSettings: { selectCells: true } },
    { name: 'Salary', ranges: [{ dataSource: salaryData }], columns: columns }],
    dataBound: function () {
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
        spreadsheet.cellFormat({ fontWeight: 'bold' }, 'A11:D11');
    }
});

spreadsheet.appendTo('#spreadsheet');

var dialogObj = new ej.popups.Dialog({
    header: 'Spreadsheet',
    target: document.getElementById('spreadsheet'),
    content: '"A1:F3" range of cells has been unlocked.',
    showCloseIcon: true,
    isModal: true,
    visible: false,
    width: '500px',
    buttons: [{
        click: lockCells,
        buttonModel: { content: 'Ok', isPrimary: true }
    }]
});
dialogObj.appendTo('#dialog');

var button = new ej.buttons.Button({ content: 'Unlock cells' });
button.appendTo('#button');
document.getElementById('button').onclick = function () {
    dialogObj.show();
};
function lockCells() {
    spreadsheet.lockCells('A1:F3', false);
    dialogObj.hide();
}
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 SpreadSheet</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript UI Controls">
    <meta name="author" content="Syncfusion">
    <link rel="shortcut icon" href="resources/favicon.ico">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    <div id="dialog"></div>
    
    <div id="container">
        <div id="button"></div>
        <div id="spreadsheet"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Make cells read-only without protecting worksheet

Previously, you could make cells read-only by protecting the entire sheet using the protectSheet method or through the UI option. Meanwhile, to make a specific range of cells editable within a protected sheet, you needed to use the lockCells method, passing the range parameter and setting the isLocked property to false.

Now, you can make an entire row, an entire column, or a specific range of cells read-only using the setRangeReadOnly method without protecting the entire sheet. This method accepts three parameters, as detailed in the following table:

Parameter Description  
readOnly Specifies whether an entire row, an entire column, or a specific range of cells should be set as read-only (true) or editable (false). .
range Specifies the particular range of cells to be set as read-only.  
sheetIndex Specifies the index of the sheet.  

You can make an entire row, an entire column, or a specific range of cells read-only by passing the range as shown in the code snippet below:

// To set read-only for single cell.
spreadsheet.setRangeReadOnly(true, 'A2', 0)
// To set read-only for range of cells.
spreadsheet.setRangeReadOnly(true, 'A2:B5', 0)
// To set read-only for entire row.
spreadsheet.setRangeReadOnly(true, '3:3', 0)
// To set read-only for entire column.
spreadsheet.setRangeReadOnly(true, 'A:A', 0)

You can make the cells read-only in the cell data binding by setting the isReadOnly property to true for the respective rows, columns, and cells. Please refer to the code snippet below to see how to set cells to read-only in the cell data binding:

   sheets: [
       {
           rows: [
               //To set read-only for entire row.
               {index: 3, isReadOnly: true},
               {
                   index: 4,
                   cells: [
                       //To set read-only for the cell.
                       { index: 4, isReadOnly: true }
                   ]
               }],
           columns: [
               //To set read-only for entire column.
               { isReadOnly: true }
           ]
       }]

The following example demonstrates how to make rows, columns, and cells read-only without protecting the sheet:

var columns = [{ width: 100 }, { width: 100 }, { width: 100, isReadOnly: true },
{ width: 100 }, { width: 100 }, { width: 100 }];

var rows = [{ index: 3, isReadOnly: true }, { index: 4, cells: [{ index: 5, isReadOnly: true }] }, {}]

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData }], rows: rows, columns: columns }]
});

spreadsheet.appendTo('#spreadsheet');

// To make row 2 readonly.
document.getElementById('button1').onclick = function () {
    spreadsheet.setRangeReadOnly(true, '2:2', spreadsheet.activeSheetIndex);
}

// To make Column A readonly.
document.getElementById('button2').onclick = function () {
    spreadsheet.setRangeReadOnly(true, 'A:A', spreadsheet.activeSheetIndex);
}

// To make E5 cell readonly.
document.getElementById('button3').onclick = function () {
    spreadsheet.setRangeReadOnly(true, 'E5:E5', spreadsheet.activeSheetIndex);
}

// To remove readonly.
document.getElementById('button4').onclick = function () {
    spreadsheet.setRangeReadOnly(false, '2:2', spreadsheet.activeSheetIndex);
    spreadsheet.setRangeReadOnly(false, 'A:A', spreadsheet.activeSheetIndex);
    spreadsheet.setRangeReadOnly(false, 'E5:E5', spreadsheet.activeSheetIndex);
}
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 SpreadSheet</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript UI Controls">
    <meta name="author" content="Syncfusion">
    <link rel="shortcut icon" href="resources/favicon.ico">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    <div id="container">
        <button class="e-btn custom-btn" id="button1">Make row 2 Read Only</button>
        <button class="e-btn custom-btn" id="button2">Make Column A Read Only</button>
        <button class="e-btn custom-btn" id="button3">Make E5 cell Read Only</button>
        <button class="e-btn custom-btn" id="button4">Remove Read Only</button>
        <div id="spreadsheet"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Protect Workbook

Protect workbook feature helps you protect the workbook so that users cannot insert, delete, rename, hide the sheets in the spreadsheet.

  • You can use the password property to protect the workbook with a password.
  • You can use the isProtected property to protect or unprotect the workbook without a password.

The default value of the isProtected property is false.

User Interface:

In the active Spreadsheet, you can protect the workbook by selecting the Data tab in the Ribbon toolbar and choosing the Protect Workbook item. Then, enter the password, confirm it, and click OK.

The following example shows Protect Workbook by using the isProtected property in the Spreadsheet control.

// Initialize the Spreadsheet component.
var columns = [{ width: 100 }, { width: 100 },{ width: 100},
    { width: 100 }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    isProtected: true,
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData }], columns: columns }],
    dataBound: function () {
                spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
                spreadsheet.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
            }
});

spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 SpreadSheet</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript UI Controls">
    <meta name="author" content="Syncfusion">
    <link rel="shortcut icon" href="resources/favicon.ico">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    
    <div id="container">
        <div id="spreadsheet"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

The following example shows Protect Workbook by using the password property in the Spreadsheet control. To unprotect the workbook, click the unprotect workbook button in the data tab and provide the password as syncfusion® in the dialog box.

// Initialize the Spreadsheet component.
var columns = [{ width: 100 }, { width: 100 },{ width: 100},
    { width: 100 }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
    password: 'syncfusion',
    sheets: [{ name: 'Budget', ranges: [{ dataSource: budgetData }], columns: columns }],
    dataBound: function () {
                spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
                spreadsheet.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
            }
});

spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 SpreadSheet</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript UI Controls">
    <meta name="author" content="Syncfusion">
    <link rel="shortcut icon" href="resources/favicon.ico">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
    <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.css" rel="stylesheet">
    <link href="styles.css" rel="stylesheet">
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
    <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/33.1.44/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
    <!--Element which is going to render-->
    
    <div id="container">
        <div id="spreadsheet"></div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Unprotect Workbook

Unprotect Workbook is used to enable the insert, delete, rename, move, copy, hide or unhide sheets feature in the spreadsheet.

User Interface:

In the active Spreadsheet, the workbook can be unprotected in any of the following ways:

  • Select the Unprotect Workbook item in the Ribbon toolbar under the Data tab, and provide the valid password in the dialog box.

See Also