Security in Excel (XlsIO) Library (Excel Protected View)

24 Jul 202623 minutes to read

You can prevent unauthorized users from viewing, moving, editing, or deleting important data in a worksheet or workbook by protecting a worksheet or workbook, with or without a password.

To quickly encrypt and decrypt Excel documents with the .NET XlsIO library, check out this video:

NOTE

Encrypt and Decrypt can be performed by referring to .NET Standard assemblies on the 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.

  1. Password to Open - This password protects your workbook from unauthorized viewing or access.
  2. Password to Modify - This password allows specific users to modify the workbook data and save changes to the file.

Read-Only Recommended - When the Excel file is set as Read-Only Recommended, Microsoft Excel displays a message recommending that the workbook be opened as read-only. This can be set with or without requiring a password to open the file.

The following code snippets illustrate how to achieve these options.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputExcel.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Encrypt workbook with password
	workbook.PasswordToOpen = "syncfusion";                
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/EncryptedWorkbook.xlsx"));
	#endregion
}
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 to Save Excel file.

A complete working example to encrypt a workbook in C# is available 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.Xlsx;
	
	//Open encrypted Excel document with password
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/EncryptedWorkbook.xlsx"), ExcelParseOptions.Default, false, "syncfusion");
	IWorksheet worksheet = workbook.Worksheets[0];

	//Decrypt workbook
	workbook.PasswordToOpen = string.Empty;
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/DecryptedWorkbook.xlsx"));
	#endregion
}
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 available 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 a 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.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputWorkbook.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Protect workbook with password
	workbook.Protect(true, true, "syncfusion");
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/ProtectedWorkbook.xlsx"));
	#endregion
}
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 available 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.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputWorkbook.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//UnProtect workbook with password
	workbook.Unprotect("syncfusion");
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/UnProtectedWorkbook.xlsx"));
	#endregion
}
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 available on this GitHub page.

Protect Worksheet 

XlsIO supports protecting and unprotecting elements in worksheets by using the Protect method of the 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.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputData.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Protect worksheet with multiple options
	worksheet.Protect("Protect", ExcelSheetProtection.FormattingCells | ExcelSheetProtection.LockedCells | ExcelSheetProtection.UnLockedCells);
					
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/ProtectedSheet.xlsx"));
	#endregion
}
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 available on this GitHub page.

The following table lists the supported Excel sheet protection options and their behavior in XlsIO.

Option Description
ExcelSheetProtection.DeletingRows Allows users to delete entire rows when the worksheet is protected. The deletion is only possible if the rows contain only unlocked cells.
ExcelSheetProtection.DeletingColumns Allows users to delete entire columns in a protected worksheet. The operation is permitted only if the columns consist entirely of unlocked cells.
ExcelSheetProtection.InsertingRows Permits the insertion of new rows into areas with unlocked cells.
ExcelSheetProtection.InsertingColumns Permits the insertion of new columns into areas with unlocked cells.
ExcelSheetProtection.InsertingHyperlinks Allows the user to insert hyperlinks into unlocked cells.
ExcelSheetProtection.FormattingCells Enables formatting of unlocked cells (e.g., fonts, number formats) while the sheet is protected.
ExcelSheetProtection.FormattingColumns Allows formatting of entire columns even when the sheet is protected.
ExcelSheetProtection.FormattingRows Allows formatting of entire rows even when the sheet is protected.
ExcelSheetProtection.Objects Prevents editing or deletion of objects like charts, shapes, or images on the sheet.
ExcelSheetProtection.Scenarios Protects defined scenarios from modification or deletion.
ExcelSheetProtection.Sorting Enables sorting functionality in a protected worksheet, assuming all cells involved are unlocked.
ExcelSheetProtection.Filtering Allows filtering of data using AutoFilter drop-downs while the sheet is protected.
ExcelSheetProtection.UsingPivotTables Enables users to interact with PivotTables (e.g., refresh or rearrange) in a protected sheet.
ExcelSheetProtection.LockedCells Applies protection to all locked cells. Users cannot modify these cells unless unlocked or explicitly permitted.
ExcelSheetProtection.UnLockedCells Specifies that users are allowed to edit unlocked cells even when the worksheet is protected.
ExcelSheetProtection.Content Prevents editing of any content on the worksheet unless specific permissions are granted.
ExcelSheetProtection.All Applies all protection options available for the worksheet, including locking cells, disabling formatting, insertion, deletion, etc.

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;
  IWorkbook workbook = application.Workbooks.Open("ChartSheet.xlsx");
  IChart chart = workbook.Charts[0];

  //Protect chart sheet
  chart.Protect("syncfusion", ExcelSheetProtection.All);

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
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

Unprotect 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.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/ProtectedWorksheet.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//UnProtect worksheet with password
	worksheet.Unprotect("syncfusion");
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/UnProtectedSheet.xlsx"));
	#endregion
}
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 IWorksheet = 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 available 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;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IChart chart = workbook.Charts[0];

  //Unprotect chart sheet
  chart.Unprotect("syncfusion");

  //Saving the workbook 
  workbook.SaveAs("Output.xlsx");
}
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 Cells

XlsIO supports locking and unlocking cells through the Locked property of CellStyle. Use this property to allow editing of specific cells in a protected worksheet.

NOTE

By default, all cells are locked. Locking or unlocking cells has no effect in an unprotected worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;

	//Open Excel
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputData.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Unlock cell
	worksheet["A1"].CellStyle.Locked = false;
	
	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/LockedCells.xlsx"));
	#endregion
}
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 supported in .NET Standard 1.4 and later.

NOTE

By default, all cells in an Excel worksheet have the Locked property set to true. This property only takes effect when the worksheet is protected. To allow edits in specific cells, you must explicitly set the Locked property to false before applying protection. Once the sheet is protected, only the unlocked cells remain editable. However, XlsIO allows programmatic cell editing even when the worksheet is protected.

A complete working example to protect cells in C# is available on this GitHub page.