Handle Excel Security Features with ExpertXls Library for .NET |
The library can protect a workbook in three ways:
Worksheet protection: the locked cells of a protected worksheet cannot be changed in Excel. All the cells are locked by default; unlock the cells the user should edit. A formula can be hidden from the formula bar.
Workbook protection: the structure (adding, deleting, renaming and moving worksheets) and the windows of the workbook are protected.
A password to open and a password to modify the workbook. These passwords are supported for the Excel 97-2003 (.xls) format.
The worksheet protection is set with the WorksheetSecurity property of the worksheet; the workbook protection and the passwords with the DocumentSecurity property of the workbook.
// the input cells can be edited, the formula of the total is hidden worksheet["C8:C11"].Style.Protection.Locked = false; worksheet["D12"].Formula = "=SUM(D8:D11)"; worksheet["D12"].Style.Protection.HiddenFormula = true; // protect the worksheet with a password; the flags are what the user can still do worksheet.WorksheetSecurity.ProtectWorksheet("password", ExcelProtectionAllowFlags.AllowSelectLockedCells | ExcelProtectionAllowFlags.AllowSelectUnlockedCells | ExcelProtectionAllowFlags.AllowFormatColumns);
// protect the structure of the workbook, not its windows, with a password workbook.DocumentSecurity.ProtectWorkbook(false, true, "password"); // Excel 97-2003 workbooks: a password to open and a password to modify the file if (workbook.Format == ExcelWorkbookFormat.Xls_2003) { workbook.DocumentSecurity.OpenWorkbookPassword = "open"; workbook.DocumentSecurity.WriteWorkbookPassword = "write"; } workbook.Save("Protected.xls"); workbook.Close(); // a workbook with a password to open is opened with the password ExcelWorkbook protectedWorkbook = new ExcelWorkbook("Protected.xls", "open");