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 { Component, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { enableRipple } from '@syncfusion/ej2-base';
import { dataSource1, dataSource2 } from './datasource';
enableRipple(true);
@Component({
selector: 'app-container',
template: `<ejs-spreadsheet #spreadsheet (created)="created()">
<e-sheets>
<e-sheet name="Budget" [isProtected]="true" [protectSettings]="{ selectCells: true }">
<e-ranges>
<e-range [dataSource]="budgetData"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
</e-columns>
</e-sheet>
<e-sheet name="Salary">
<e-ranges>
<e-range [dataSource]="salaryData"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>`
})
export class AppComponent {
@ViewChild('spreadsheet')
spreadsheetObj: SpreadsheetComponent;
budgetData: object[] = dataSource1;
salaryData: object[] = dataSource2;
created() {
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheetObj.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Salary!A1:D1');
}
}
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { AppComponent } from './app.component';
/**
* Module
*/
@NgModule({
imports: [
BrowserModule,
SpreadsheetAllModule
],
declarations: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
/**
* Protect sheet data source
*/
export let dataSource1: 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 dataSource2: 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,
}
];
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';
enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);
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 { Component, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';
import { Dialog } from '@syncfusion/ej2-popups';
import { enableRipple } from '@syncfusion/ej2-base';
import { dataSource1, dataSource2 } from './datasource';
enableRipple(true);
@Component({
selector: 'app-container',
template: `<button class="e-btn" style="margin: 5px 0;" (click)="btnClick()">
Unlock cells</button>
<div id="dialog"></div>
<ejs-spreadsheet #spreadsheet id="spreadsheet" (created)="created()">
<e-sheets>
<e-sheet name="Budget" [isProtected]="true" [protectSettings]="{ selectCells: true }">
<e-ranges>
<e-range [dataSource]="budgetData"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
</e-columns>
</e-sheet>
<e-sheet name="Salary">
<e-ranges>
<e-range [dataSource]="salaryData"></e-range>
</e-ranges>
<e-columns>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
<e-column [width]=100></e-column>
</e-columns>
</e-sheet>
</e-sheets>
</ejs-spreadsheet>`
})
export class AppComponent {
@ViewChild('spreadsheet')
spreadsheetObj: SpreadsheetComponent;
dialogObj: Dialog;
budgetData: object[] = dataSource1;
salaryData: object[] = dataSource2;
created() {
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.spreadsheetObj.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Salary!A1:D1');
// Creating dialog component,
this.dialogObj = new 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: this.lockCells.bind(this), buttonModel: { content: 'Ok', isPrimary: true }
}];
});
this.dialogObj.appendTo('#dialog');
}
btnClick (): void {
this.dialogObj.show();
}
lockCells(): void {
this.spreadsheetObj.lockCells('A1:F3', false);
this.dialogObj.hide();
}
}
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { AppComponent } from './app.component';
/**
* Module
*/
@NgModule({
imports: [
BrowserModule,
SpreadsheetAllModule
],
declarations: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
/**
* Protect sheet data source
*/
export let dataSource1: 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 dataSource2: 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,
}
];
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';
enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);