Security in Excel (XlsIO) Library (Excel Protected View)
26 Aug 202422 minutes to read
You can protect an anonymous user from viewing, moving, editing or deleting important data from a worksheet or workbook by protecting a worksheet or workbook, with or without a password.
NOTE
Encrypt and Decrypt can be performed by referring .NET Standard assemblies in UWP platform.
Protect Workbook
To keep others from making structural changes to your documents such as moving, deleting and adding sheets, you can protect the workbook in the following ways.
Encryption with password
There are two different passwords to encrypt a document.
- Password To Open - This password helps to protect your workbook from unauthorized viewing or accessing.
- Password to Modify - This password helps to allow give specific users permission to modify the workbook data and save changes to the file.
Read-Only Recommended – If the excel file is set as Read-only recommended, then Microsoft Excel displays a message recommending that you open the workbook as read-only when users open the excel file. This can be set with or without requiring a password to open the file.
The Following code snippets illustrate how to achieve the above options.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
//Encrypt the workbook with password
workbook.PasswordToOpen = "password";
//Set the password to modify the workbook
workbook.SetWriteProtectionPassword("modify_password");
//Set the workbook as read-only
workbook.ReadOnlyRecommended = true;
//Saving the workbook as stream
FileStream stream = new FileStream("Encrypt.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
//Encrypt the workbook with password
workbook.PasswordToOpen = "password";
//Set the password to modify the workbook
workbook.SetWriteProtectionPassword("modify_password");
//Set the workbook as read-only
workbook.ReadOnlyRecommended = true;
workbook.SaveAs("Encrypt.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Encrypt the workbook with password
workbook.PasswordToOpen = "password"
'Set the password to modify the workbook
workbook.SetWriteProtectionPassword("modify_password")
'Set the workbook as read-only
workbook.ReadOnlyRecommended = True
workbook.SaveAs("Encrypt.xlsx")
End Using
Now, the encrypted workbook can be saved. Refer Save Excel file.
A complete working example to encrypt a workbook in C# is present on this GitHub page.
Opening an encrypted workbook
You can open an existing encrypted workbook (decrypting) from either the file system or the stream using the following overloads.
//Creates a new instance for ExcelEngine
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(workbookStream, ExcelParseOptions.Default, false, "password");
//Creates a new instance for ExcelEngine
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileName, ExcelParseOptions.Default, false, "password");
'Creates a new instance for ExcelEngine
Dim excelEngine As New ExcelEngine()
'Loads or open an existing workbook through Open method of IWorkbooks
Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open(fileName, ExcelParseOptions.Default, False, "password")
Removing encryption
The following code illustrates how to remove a protection for an encrypted document.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default, true, "password");
//Removing a protection
workbook.PasswordToOpen = string.Empty;
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = excelEngine.Excel.Workbooks.Open("Sample.xlsx", ExcelParseOptions.Default, true, "password");
//Removing a protection
workbook.PasswordToOpen = string.Empty;
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = excelEngine.Excel.Workbooks.Open("Sample.xlsx", ExcelParseOptions.Default, True, "password")
'Removing a protection
workbook.PasswordToOpen = String.Empty
workbook.SaveAs("Output.xlsx")
End Using
A complete working example to decrypt a workbook in C# is present on this GitHub page.
NOTE
By default, Microsoft Excel uses AES-128 encryption for versions Excel 2007 and above, and MD5 encryption for versions Excel97 to 2003 when providing the password for Excel documents. So, XlsIO uses the same encryptions for password protection based on the Excel versions.
Protect workbook elements
XlsIO provides options to protect and unprotect workbook elements with password. The following code example illustrates how to protect a workbook with a password.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("ProtectWorkbook.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
bool isProtectWindow = true;
bool isProtectContent = true;
//Protect Workbook
workbook.Protect(isProtectWindow, isProtectContent, "password");
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
bool isProtectWindow = true;
bool isProtectContent = true;
//Protect Workbook
workbook.Protect(isProtectWindow, isProtectContent, "password");
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("ProtectWorkbook.xlsx")
Dim isProtectWindow As Boolean = True
Dim isProtectContent As Boolean = True
'protect workbook
workbook.Protect(isProtectWindow, isProtectContent, "password")
workbook.SaveAs("Output.xlsx")
End Using
A complete working example to protect a workbook in C# is present on this GitHub page.
Unprotect Workbook elements
You can unprotect or remove protection for a workbook as shown below.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("ProtectedWorkbook.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
//Unprotect (unlock) Workbook using Password
workbook.Unprotect("password");
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("ProtectedWorkbook.xlsx");
//Unprotect (unlock) Workbook using Password
workbook.Unprotect("password");
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("ProtectedWorkbook.xlsx")
'Unprotect (unlock) Workbook using Password
workbook.Unprotect("password")
workbook.SaveAs("Output.xlsx")
End Using
A complete working example to unprotect a workbook in C# is present on this GitHub page.
Protect Worksheet
XlsIO provides support for protecting and unprotecting elements in worksheets by using the Protect method of IWorksheet. The following code example illustrates how to protect a worksheet with a password.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Protecting the Worksheet by using a Password
sheet.Protect("syncfusion", ExcelSheetProtection.All);
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Protecting the Worksheet by using a Password
sheet.Protect("syncfusion", ExcelSheetProtection.All);
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Protecting the Worksheet by using a Password
sheet.Protect("syncfusion", ExcelSheetProtection.All)
workbook.SaveAs("Output.xlsx")
End Using
A complete working example to protect a worksheet in C# is present on this GitHub page.
NOTE
By using the ExcelSheetProtection enumerator, you can set protection to the workbook elements/operations.
Chart Sheet Protection
Essential XlsIO can also provide support to protect or unprotect a chart sheet.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("ChartSheet.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IChart chart = workbook.Charts[0];
//Protect chart sheet
chart.Protect("syncfusion", ExcelSheetProtection.All);
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("sample.xlsx");
IChart chart = workbook.Charts[0];
//Protect chart sheet
chart.Protect("syncfusion", ExcelSheetProtection.All);
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim chart As IChart = workbook.Charts(0)
'Protect chart sheet
chart.Protect("syncfusion", ExcelSheetProtection.All)
workbook.SaveAs("Output.xlsx")
End Using
Un-Protect Worksheet
You can also unprotect the worksheet by using the Unprotect method of XlsIO. The following code example illustrates how to remove worksheet protection.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Unprotecting (unlocking) the Worksheet using the Password
worksheet.Unprotect("syncfusion");
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Unprotecting (unlocking) the Worksheet using the Password
sheet.Unprotect("syncfusion");
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("sample.xlsx")
Dim sheet As IWorkbook = workbook.Worksheets(0)
'Unprotecting (unlocking) the Worksheet using the Password
sheet.Unprotect("syncfusion")
workbook.SaveAs("Output.xlsx")
End Using
A complete working example to unprotect a worksheet in C# is present on this GitHub page.
Removing protection of a Chart Sheet
You can remove the protection of a chart sheet as shown below.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IChart chart = workbook.Charts[0];
//Unprotect chart sheet
chart.Unprotect("syncfusion");
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IChart chart = workbook.Charts[0];
//Unprotect chart sheet
chart.Unprotect("syncfusion");
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim chart As IChart = workbook.Charts(0)
'Unprotect chart sheet
chart.Unprotect("syncfusion")
workbook.SaveAs("Output.xlsx")
End Using
Protect Cell
XlsIO supports locking and unlocking cells by using the cell’s Locked property of CellStyle. This can be manipulated to make certain cells editable in a protected worksheet.
NOTE
By default, cells are locked. Lock or Unlock cell in an unprotected worksheet has no effect.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Unlocking a cell to edit in worksheet protection mode
worksheet.Range["A1"].CellStyle.Locked = false;
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
//Unlocking a cell to edit in worksheet protection mode
worksheet.Range["A1"].CellStyle.Locked = false;
workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Unlocking a cell to edit in worksheet protection mode
worksheet.Range("A1").CellStyle.Locked = False
workbook.SaveAs("Output.xlsx")
End Using
NOTE
Security features are now supported in .NET Standard 1.4 onwards.
A complete working example to protect a cell in C# is present on this GitHub page.