Search results

Protection in React Spreadsheet component

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

Protect Sheet

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 is false.

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 are false.

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

User Interface

In the active Spreadsheet, the sheet protection can be done by 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 code example shows Protect Sheet functionality in the Spreadsheet control.

Source
Preview
app.tsx
datasource.tsx
index.html
app.jsx
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

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, select the Unprotect Sheet item in the context menu.
  • Use the unprotectSheet() method programmatically.

Unlock the particular cells in the protected sheet

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.

Source
Preview
app.tsx
datasource.tsx
index.html
app.jsx
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'));

See Also