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.
<ejs-spreadsheet id="spreadsheet" dataBound="dataBound">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet isProtected="true" name="Budget">
<e-spreadsheet-protect-settings selectCells="true"></e-spreadsheet-protect-settings>
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.budgetData"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
<e-spreadsheet-sheet name="Salary">
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.salaryData"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function dataBound() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.cellFormat({ fontWeight: 'bold'}, 'A11:D11');
}
</script>
public IActionResult Index()
{
List<object> data1 = new List<object>()
{
new { ExpenseType= "Housing", ProjectedCost= "7000", ActualCost= "7500", Difference= "-500"},
new { ExpenseType= "Transportation", ProjectedCost= "500", ActualCost= "500", Difference= "0"},
new { ExpenseType= "Insurance", ProjectedCost= "1000", ActualCost= "1000", Difference= "0"},
new { ExpenseType= "Food", ProjectedCost= "2000", ActualCost= "1800", Difference= "200"},
new { ExpenseType= "Pets", ProjectedCost= "300", ActualCost= "200", Difference= "100"},
new { ExpenseType= "Personel Care", ProjectedCost= "500", ActualCost= "500", Difference= "0"},
new { ExpenseType= "Loan", ProjectedCost= "1000", ActualCost= "1000", Difference= "0"},
new { ExpenseType= "Tax", ProjectedCost= "200", ActualCost= "200", Difference= "0"},
new { ExpenseType= "Savings", ProjectedCost= "1000", ActualCost= "900", Difference= "100"},
new { ExpenseType= "Total", ProjectedCost= "13500", ActualCost= "13600", Difference= "-100"},
};
List<object> data2 = new List<object>()
{
new { Earnings= "Basic", CreditAmount= "20000", Deductions= "Provident Fund", DebitAmount= "2400"},
new { Earnings= "HRA", CreditAmount= "8000", Deductions= "ESI", DebitAmount= "0"},
new { Earnings= "Special Allowance", CreditAmount= "25000", Deductions= "Professional Tax", DebitAmount= "200"},
new { Earnings= "Incentives", CreditAmount= "2000", Deductions= "TDS", DebitAmount= "2750"},
new { Earnings= "Bonus", CreditAmount= "1500", Deductions= "Other Deduction", DebitAmount= "0"},
new { Earnings= "Total Earnings", CreditAmount= "56500", Deductions= "Total Deductions", DebitAmount= "5350"},
};
ViewBag.budgetData = data1;
ViewBag.salaryData = data2;
return View();
}
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.
<button id="customBtn" class="e-btn"> Unlock cells</button>
<ejs-spreadsheet id="spreadsheet" dataBound="dataBound">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet isProtected="true" name="Budget">
<e-spreadsheet-protect-settings selectCells="true"></e-spreadsheet-protect-settings>
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.budgetData"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
<e-spreadsheet-sheet name="Salary">
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.salaryData"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-columns>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
<e-spreadsheet-column width="100"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<ejs-dialog id="defaultDialog" header="Spreadsheet" target="#spreadsheet" content="'A1:F3' range of cells has been unlocked." showCloseIcon="true" isModal="true" visible="false" width="500px" buttons="ViewBag.DefaultButtons"></ejs-dialog>
<script>
document.getElementById("customBtn").addEventListener('click', showAlert);
function dataBound() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:D1');
this.cellFormat({ fontWeight: 'bold' }, 'A11:D11');
}
function lockCells() {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
var dialogObj = ej.base.getComponent(document.getElementById('defaultDialog'), 'dialog');
spreadsheetObj.lockCells('A1:F3', false);
dialogObj.hide();
}
function showAlert() {
var dialogObj = ej.base.getComponent(document.getElementById('defaultDialog'), 'dialog');
dialogObj.show();
}
</script>
public IActionResult Index()
{
List<object> data1 = new List<object>()
{
new { ExpenseType= "Housing", ProjectedCost= "7000", ActualCost= "7500", Difference= "-500"},
new { ExpenseType= "Transportation", ProjectedCost= "500", ActualCost= "500", Difference= "0"},
new { ExpenseType= "Insurance", ProjectedCost= "1000", ActualCost= "1000", Difference= "0"},
new { ExpenseType= "Food", ProjectedCost= "2000", ActualCost= "1800", Difference= "200"},
new { ExpenseType= "Pets", ProjectedCost= "300", ActualCost= "200", Difference= "100"},
new { ExpenseType= "Personel Care", ProjectedCost= "500", ActualCost= "500", Difference= "0"},
new { ExpenseType= "Loan", ProjectedCost= "1000", ActualCost= "1000", Difference= "0"},
new { ExpenseType= "Tax", ProjectedCost= "200", ActualCost= "200", Difference= "0"},
new { ExpenseType= "Savings", ProjectedCost= "1000", ActualCost= "900", Difference= "100"},
new { ExpenseType= "Total", ProjectedCost= "13500", ActualCost= "13600", Difference= "-100"},
};
List<object> data2 = new List<object>()
{
new { Earnings= "Basic", CreditAmount= "20000", Deductions= "Provident Fund", DebitAmount= "2400"},
new { Earnings= "HRA", CreditAmount= "8000", Deductions= "ESI", DebitAmount= "0"},
new { Earnings= "Special Allowance", CreditAmount= "25000", Deductions= "Professional Tax", DebitAmount= "200"},
new { Earnings= "Incentives", CreditAmount= "2000", Deductions= "TDS", DebitAmount= "2750"},
new { Earnings= "Bonus", CreditAmount= "1500", Deductions= "Other Deduction", DebitAmount= "0"},
new { Earnings= "Total Earnings", CreditAmount= "56500", Deductions= "Total Deductions", DebitAmount= "5350"},
};
List<DialogDialogButton> buttons = new List<DialogDialogButton>() { };
buttons.Add(new DialogDialogButton() { Click = "lockCells", ButtonModel = new DefaultButtonModel() { content = "OK", isPrimary = true } });
ViewBag.DefaultButtons = buttons;
ViewBag.budgetData = data1;
ViewBag.salaryData = data2;
return View();
}
public class DefaultButtonModel
{
public string content { get; set; }
public bool isPrimary { get; set; }
}