Sheet protection helps you to prevent the users from modifying the data in the spreadsheet.
Protect sheet feature helps you to prevent the unknown users from accidentally changing, editing, moving, or deleting data in a spreadsheet.
You can use the isProtected
property to enable or disable the Protecting functionality.
- The default value for
isProtected
property isfalse
.
By default in protected sheet, selecting, formatting, inserting, deleting functionalities are disabled. To enable some of the above said functionalities
the protectSettings
options are used in a protected spreadsheet.
The available protectSettings
options in spreadsheet are,
Options | Uses |
---|---|
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 for all
protectSettings
options arefalse
.
By default, the Protect Sheet
module is injected internally into the Spreadsheet to perform sheet protection function.
In the active Spreadsheet, the sheet protection can be done by any of the following ways:
protectSheet()
method programmatically.The following code example shows Protect Sheet
functionality in the Spreadsheet control.
// Initialize the Spreadsheet component.
document.getElementById('button').style.display="none";
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="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.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="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<button id="button" style="margin-bottom: 10px;">Unlock Cells</button>
<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>
Unprotect sheet is used to enable all the functionalities that are already disabled in a protected spreadsheet.
In the active Spreadsheet, the sheet Unprotection can be done by any of the following ways:
Unprotect Sheet
item in the Ribbon toolbar under the Data Tab.Unprotect Sheet
item in the context menu.unprotectSheet()
method programmatically.In protected spreadsheet, to make some particular cell or range of cells are editable, you can use lockCells()
method, with the parameter range
and isLocked
property as false.
// Initialize the Spreadsheet component.
document.getElementById('button').style.display="block";
var columns = [{ width: 100 }, { width: 100 },{ width: 100},
{ width: 100 }];
let 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,
isModel: 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 = () => {
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="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/ej2-spreadsheet/styles/material.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="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<button id="button" style="margin-bottom: 10px;">Unlock Cells</button>
<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>