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.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective, protectSheet, ProtectSettings } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective} from '@syncfusion/ej2-react-spreadsheet';
import { budgetData, salaryData } from './datasource';
export default class App extends React.Component<{}, {}> {
public spreadsheet: SpreadsheetComponent;
public protectSettings = {selectCells: true};
public dataBound(): void{
this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheet.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
};
render() {
return ( <div> <SpreadsheetComponent
ref={(ssObj) => { this.spreadsheet = ssObj }} dataBound={this.dataBound.bind(this)} >
<SheetsDirective>
<SheetDirective name={"Budget"} isProtected={true} protectSettings={this.protectSettings}>
<RangesDirective>
<RangeDirective dataSource={budgetData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
<SheetDirective name={"Salary"}>
<RangesDirective>
<RangeDirective dataSource={salaryData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));
export let budgetData: Object[] = [
{
"Expense Type": "Housing",
"Projected Cost": 7000,
"Actual Cost": 7500,
"Difference": -500,
},
{
"Expense Type": "Transportation",
"Projected Cost": 500,
"Actual Cost": 500,
"Difference": 0,
},
{
"Expense Type": "Insurance",
"Projected Cost": 1000,
"Actual Cost": 1000,
"Difference": 0,
},
{
"Expense Type": "Food",
"Projected Cost": 2000,
"Actual Cost": 1800,
"Difference": 200,
},
{
"Expense Type": "Pets",
"Projected Cost": 300,
"Actual Cost": 200,
"Difference": 100,
},
{
"Expense Type": "Personel Care",
"Projected Cost": 500,
"Actual Cost": 500,
"Difference": 0,
},{
"Expense Type": "Loan",
"Projected Cost": 1000,
"Actual Cost": 1000,
"Difference": 0,
},{
"Expense Type": "Tax",
"Projected Cost": 200,
"Actual Cost": 200,
"Difference": 0,
},{
"Expense Type": "Savings",
"Projected Cost": 1000,
"Actual Cost": 900,
"Difference": 100,
},
{
"Expense Type": "Total",
"Projected Cost": 13500,
"Actual Cost": 13600,
"Difference": -100,
}
]
export let salaryData: Object[] = [
{
"Earnings": "Basic",
"Credit Amount": 20000,
"Deductions": "Provident Fund",
"Debit Amount": 2400,
},
{
"Earnings": "HRA",
"Credit Amount": 8000,
"Deductions": "ESI",
"Debit Amount": 0,
},
{
"Earnings": "Special Allowance",
"Credit Amount": 25000,
"Deductions": "Professional Tax",
"Debit Amount": 200,
},
{
"Earnings": "Incentives",
"Credit Amount": 2000,
"Deductions": "TDS",
"Debit Amount": 2750,
},
{
"Earnings": "Bonus",
"Credit Amount": 1500,
"Deductions": "Other Deduction",
"Debit Amount": 0,
},
{
"Earnings": "Total Earnings",
"Credit Amount": 56500,
"Deductions": "Total Deductions",
"Debit Amount": 5350,
}
]
<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Spreadsheet</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/material.css" rel="stylesheet" type="text/css"/>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
body {
overflow: hidden;
}
.customClass.e-cell {
background-color: red;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { budgetData, salaryData } from './datasource';
export default class App extends React.Component {
constructor() {
super(...arguments);
this.protectSettings = { selectCells: true };
}
dataBound() {
this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheet.cellFormat({ fontWeight: 'bold' }, 'A11:D11');
}
;
render() {
return (<div> <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} dataBound={this.dataBound.bind(this)}>
<SheetsDirective>
<SheetDirective name={"Budget"} isProtected={true} protectSettings={this.protectSettings}>
<RangesDirective>
<RangeDirective dataSource={budgetData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
<SheetDirective name={"Salary"}>
<RangesDirective>
<RangeDirective dataSource={salaryData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent> </div>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));
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.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective, RowsDirective, RowDirective, CellDirective, CellsDirective, protectSheet, ProtectSettings } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective} from '@syncfusion/ej2-react-spreadsheet';
import { budgetData, salaryData } from './datasource';
import { DialogComponent, ButtonPropsModel } from '@syncfusion/ej2-react-popups';
export default class App extends React.Component<{}, {}> {
public spreadsheet: SpreadsheetComponent;
public dialogObj: DialogComponent;
public protectSettings = {selectCells: true};
public btn: ButtonPropsModel[] =[ {click: this.lockCells.bind(this),
buttonModel: { content: 'Ok', isPrimary: true }}];
public dataBound(): void{
this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheet.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
};
public lockCells(): void {
this.spreadsheet.lockCells('A1:F3', false);
this.dialogObj.hide();
}
public showDlg(): void {
this.dialogObj.show();
}
render() {
return ( <div> <button className='e-btn' onClick={ this.showDlg.bind(this) }>Unlock cells</button>
<SpreadsheetComponent
ref={(ssObj) => { this.spreadsheet = ssObj }} dataBound={this.dataBound.bind(this)} >
<SheetsDirective>
<SheetDirective name={"Budget"} isProtected={true} protectSettings={this.protectSettings}>
<RangesDirective>
<RangeDirective dataSource={budgetData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
<SheetDirective name={"Salary"}>
<RangesDirective>
<RangeDirective dataSource={salaryData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
<DialogComponent ref={(dlgObj) => { this.dialogObj = dlgObj }} header={'Spreadsheet'} target={document.getElementById('spreadsheet')} content={'"A1:F3" range of cells has been unlocked.'}
showCloseIcon={true} isModal={true} visible={false} width={'500px'} buttons={this.btn}>
</DialogComponent> </div>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));
export let budgetData: Object[] = [
{
"Expense Type": "Housing",
"Projected Cost": 7000,
"Actual Cost": 7500,
"Difference": -500,
},
{
"Expense Type": "Transportation",
"Projected Cost": 500,
"Actual Cost": 500,
"Difference": 0,
},
{
"Expense Type": "Insurance",
"Projected Cost": 1000,
"Actual Cost": 1000,
"Difference": 0,
},
{
"Expense Type": "Food",
"Projected Cost": 2000,
"Actual Cost": 1800,
"Difference": 200,
},
{
"Expense Type": "Pets",
"Projected Cost": 300,
"Actual Cost": 200,
"Difference": 100,
},
{
"Expense Type": "Personel Care",
"Projected Cost": 500,
"Actual Cost": 500,
"Difference": 0,
},{
"Expense Type": "Loan",
"Projected Cost": 1000,
"Actual Cost": 1000,
"Difference": 0,
},{
"Expense Type": "Tax",
"Projected Cost": 200,
"Actual Cost": 200,
"Difference": 0,
},{
"Expense Type": "Savings",
"Projected Cost": 1000,
"Actual Cost": 900,
"Difference": 100,
},
{
"Expense Type": "Total",
"Projected Cost": 13500,
"Actual Cost": 13600,
"Difference": -100,
}
]
export let salaryData: Object[] = [
{
"Earnings": "Basic",
"Credit Amount": 20000,
"Deductions": "Provident Fund",
"Debit Amount": 2400,
},
{
"Earnings": "HRA",
"Credit Amount": 8000,
"Deductions": "ESI",
"Debit Amount": 0,
},
{
"Earnings": "Special Allowance",
"Credit Amount": 25000,
"Deductions": "Professional Tax",
"Debit Amount": 200,
},
{
"Earnings": "Incentives",
"Credit Amount": 2000,
"Deductions": "TDS",
"Debit Amount": 2750,
},
{
"Earnings": "Bonus",
"Credit Amount": 1500,
"Deductions": "Other Deduction",
"Debit Amount": 0,
},
{
"Earnings": "Total Earnings",
"Credit Amount": 56500,
"Deductions": "Total Deductions",
"Debit Amount": 5350,
}
]
<!DOCTYPE html>
<html lang="en">
<head>
<title>Syncfusion React Spreadsheet</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Essential JS 2 for React Components" />
<meta name="author" content="Syncfusion" />
<link href="https://cdn.syncfusion.com/ej2/material.css" rel="stylesheet" type="text/css"/>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<style>
#loader {
color: #008cff;
height: 40px;
left: 45%;
position: absolute;
top: 45%;
width: 30%;
}
body {
overflow: hidden;
}
.customClass.e-cell {
background-color: red;
}
</style>
</head>
<body>
<div id='root'>
<div id='loader'>Loading....</div>
</div>
</body>
</html>
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { SpreadsheetComponent, SheetsDirective, SheetDirective, RangesDirective } from '@syncfusion/ej2-react-spreadsheet';
import { RangeDirective, ColumnsDirective, ColumnDirective } from '@syncfusion/ej2-react-spreadsheet';
import { budgetData, salaryData } from './datasource';
import { DialogComponent } from '@syncfusion/ej2-react-popups';
export default class App extends React.Component {
constructor() {
super(...arguments);
this.protectSettings = { selectCells: true };
this.btn = [{ click: this.lockCells.bind(this),
buttonModel: { content: 'Ok', isPrimary: true } }];
}
dataBound() {
this.spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheet.cellFormat({ fontWeight: 'bold' }, 'A11:D11');
}
;
lockCells() {
this.spreadsheet.lockCells('A1:F3', false);
this.dialogObj.hide();
}
showDlg() {
this.dialogObj.show();
}
render() {
return (<div> <button className='e-btn' onClick={this.showDlg.bind(this)}>Unlock cells</button>
<SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj; }} dataBound={this.dataBound.bind(this)}>
<SheetsDirective>
<SheetDirective name={"Budget"} isProtected={true} protectSettings={this.protectSettings}>
<RangesDirective>
<RangeDirective dataSource={budgetData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
<SheetDirective name={"Salary"}>
<RangesDirective>
<RangeDirective dataSource={salaryData}></RangeDirective>
</RangesDirective>
<ColumnsDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
<ColumnDirective width={100}></ColumnDirective>
</ColumnsDirective>
</SheetDirective>
</SheetsDirective>
</SpreadsheetComponent>
<DialogComponent ref={(dlgObj) => { this.dialogObj = dlgObj; }} header={'Spreadsheet'} target={document.getElementById('spreadsheet')} content={'"A1:F3" range of cells has been unlocked.'} showCloseIcon={true} isModal={true} visible={false} width={'500px'} buttons={this.btn}>
</DialogComponent> </div>);
}
}
ReactDOM.render(<App />, document.getElementById('root'));