5 Ways to Protect Excel XLSX Data in .NET Applications

The importance of data security is on a constant rise. Developers in every industry are being tasked to implement greater security measures to protect sensitive and confidential data, all while making the data easily accessible to authorized end-users that need to perform essential business activities. Businesses everywhere rely on Microsoft Excel, or another spreadsheet software, as an essential tool in these activities, such as creating budgets, organizing employee information, or even summarizing customer and sales data. Excel allows users to analyze and sort small or large amounts of data quickly and easily. However once the sensitive data is exported from our business apps to an Excel file, it is hard to ensure the security and integrity of that data.

Spreadsheet components and libraries are a solution to securing and protecting your company's sensitive business data, both within your app and in user-exported Excel files. Developers can programmatically prevent unauthorized access, unwanted modifications, or even the complete deletion of Excel worksheets and workbooks by protecting the workbook or worksheet, locking cells, and applying a password.

During this blog, we discuss 5 different ways to protect Excel data using a .NET application. With the help of Spread.NET, the world’s #1 selling .NET Spreadsheet Components, we demonstrate how to programmatically protect Excel data (using C# and VB.NET), and how developers can give their end-users the power to protect Excel data during a .NET apps runtime using Excel-like UI dialogs.

5 Ways to Protect Excel Workbook Data in .NET Applications:

Programmatically Protect and Unprotect an Excel File (.XLSX)
Programmatically Protect and Unprotect a Specific Excel Worksheet
Implement Protect Sheet Options Using a Runtime Excel-like Dialog in a .NET App
- Access the Protect Sheet Dialog During Runtime from a Context Menu
- Programmatically Invoke the Protect Sheet Dialog During Runtime
Programmatically Protect Specific Cells, Ranges, and Objects on a Worksheet
How to Programmatically Password Protect an Excel Workbook
- Programmatically Set a Password to an Excel Workbook
- Programmatically Set a Password to an Excel Worksheet

Programmatically Protect and Unprotect an Excel File (.XLSX)

Restrict the editing of an entire Excel (XLSX) workbook with one line of Spread.NET’s API code. This line includes the Workbook Protect method which passes the WorkbookLocks enumeration as the parameter. The WorkbookLocks enumeration represents the protected data of the workbook. In the code below, we set for All so that all of the data, including workbook structure and windows, are protected.

C#

FpSpread fpSpread1 = new FpSpread(); // Initialize Spread.NET 
fpSpread1.AsWorkbook().Protect(WorkbookLocks.All); // Protect the Excel Workbook

VB.NET

FpSpread fpSpread1 = new FpSpread() 'Initialize Spread.NET
fpSpread1.AsWorkbook().Protect(WorkbookLocks.All) 'Protect the Excel Workbook

To programmatically unprotect an Excel workbook in your .NET application, invoke the Spread.NET Workbook’s Unprotect method like so:

C#

FpSpread fpSpread1 = new FpSpread(); // Initialize Spread.NET
fpSpread1.AsWorkbook().Unprotect(); // Unprotect the Excel Workbook

VB.NET

FpSpread fpSpread1 = new FpSpread() 'Initialize Spread.NET
fpSpread1.AsWorkbook().Unprotect() 'Unprotect the Excel Workbook

Programmatically Protect and Unprotect a Specific Excel Worksheet

Users may need to protect a specific worksheet in an Excel workbook. Using Spread.NET, .NET developers can programmatically apply protection to worksheets using the Worksheet’s Protect method, which passes the WorksheetLocks enumeration, which represents the protected data of the worksheet and can be used to specify certain behaviors when the worksheet is locked.

Learn more about using the WorksheetLocks enumeration to set custom Worksheet Protection Settings later in this blog.

C#

fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All);

VB.NET

fpSpread1.LegacyBehaviors = LegacyBehaviors.None
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All)

Users can programmatically unprotect the Excel worksheet using the Spread.NET Worksheet’s Unprotect method.

C#

fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.AsWorkbook().ActiveSheet.Unprotect();

VB.NET

fpSpread1.LegacyBehaviors = LegacyBehaviors.None
fpSpread1.AsWorkbook().ActiveSheet.Unprotect()

Implement Protect Worksheet Options Using a Runtime Excel-like Dialog in a .NET App

New with the v16 release, Spread.NET fully supports worksheet protection options being set in the user interface with a new Excel-like Protect Sheet Dialog. This allows users to implement the Protect method during runtime through the UI; users can also specify the worksheet protection options and set an optional password. The dialog can be accessed through the Spread.NET Tab Strip’s context menu, or invoked programmatically using the API.

Access the Protect Sheet Dialog During Runtime

End-users can access the Protect Sheet Dialog during runtime from the Spread.NET Tab Strip’s context menu options. The context menu must be set to be Editable like so:

C#

fpSpread1.TabStrip.Editable = true;

VB.NET

fpSpread1.TabStrip.Editable = True

With the Tab Strip set to be Editable, users can access the Protect Sheet Dialog by right-clicking on the worksheets tab, and selecting Protect Sheet from the worksheet tab areas context menu, as seen here:

Access the a Excel-like Protect Sheet Dialog During Runtime

Programmatically Invoke the Protect Sheet Dialog During Runtime

Developers can invoke the Protect Sheet Dialog programmatically during runtime using the ProtectSheetDialog class and the showDialog method (Inherited from System.Windows.Forms.Form).

Check out the Spread.NET WinForms Demo Explorer’s Protection Dialog sample which shows the built-in worksheet protection dialog with protection options and an optional password.

Below is a code snippet from the above-mentioned Protection Dialog sample. The code logic in OnProtectSheetMenuItemClick shows how to show the ProtectSheetDialog for the instance of FpSpread using ShowDialog (this dialog should show a modal).

C#

private void OnProtectSheetMenuItemClick(object sender, System.EventArgs e)
{
  if (_fpSpread.ActiveSheet == null)
        return;
  if (!_isEmptyPasswordProtected)
    {
        ProtectSheetDialog protectSheetDialog = new ProtectSheetDialog(_fpSpread);
        protectSheetDialog.ShowDialog(this);
    }
  else
    {
        _fpSpread.AsWorkbook().ActiveSheet.Unprotect(string.Empty);
    }
}

VB.NET

Private Sub OnProtectSheetMenuItemClick(ByVal sender As Object, ByVal e As System.EventArgs)
    If _fpSpread.ActiveSheet Is Nothing Then Return

    If Not _isEmptyPasswordProtected Then
        Dim protectSheetDialog As ProtectSheetDialog = New ProtectSheetDialog(_fpSpread)
        protectSheetDialog.ShowDialog(Me)
    Else
        _fpSpread.AsWorkbook().ActiveSheet.Unprotect(String.Empty)
    End If
End Sub

Programmatically Protect Specific Cells, Ranges, and Objects on a Worksheet

When protecting an Excel (XLSX) worksheet, all of the cells are locked by default, and the CRUD operations are restricted on rows, columns, or any other elements. There are scenarios where a user only requires to protect a specific element or range of cells, instead of the entire worksheet, or the user could want to only restrict or allow a specific behavior when protecting the sheet. Using Spread.NET, developers can easily set cells, or ranges of cells, to be locked or unlocked, and apply custom worksheet protection options to restrict modification functionalities to the workbook or other objects.

Programmatically Lock and Unlock Cells in an Excel Worksheet

Cells in a worksheet have a Locked property to get or set if the cell is marked as locked from editing. To programmatically lock only specific cells or ranges, we must first unlock all cells in the worksheet by setting their Locked property to false. We must do this because, by default, all of the cells are locked. Then, with all of the cells unlocked, we can set the Locked property to true for only the cell or range of cells we require to be un-editable.

In this code, we are unlocking all of the worksheet's cells by setting a column object’s Locked property to false, then using a cell object’s Locked property to lock the cell range B2:D5.

C#

FpSpread fpSpread1 = new FpSpread(); // Initialize Spread.NET 
fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.TabStrip.Editable = true;
// Set style for locked cells
fpSpread1.ActiveSheet.LockBackColor = System.Drawing.Color.LightCyan;
fpSpread1.ActiveSheet.LockForeColor = System.Drawing.Color.Green;
// Using the Column Object unlock all the cells
FarPoint.Win.Spread.Column columnobj; // Column Object
int count = fpSpread1.ActiveSheet.Columns.Count - 1; // Get the column count
columnobj = fpSpread1.ActiveSheet.Columns[0, count]; // Get all of the columns in the worksheet
columnobj.Locked = false; // Unlock the cells
// Use the Cells Object to lock a specific cells or cell ranges
FarPoint.Win.Spread.Cell cellobj; // Cell Object
cellobj = fpSpread1.ActiveSheet.Cells["B2:D5"]; // Get a cell range 
cellobj.Value = "Locked Cells"; // Display Text
cellobj.Locked = true; // Lock the cell range
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All); // Protect the worksheet

VB.NET

Dim fpSpread1 As FpSpread = New FpSpread() 'Initialize Spread.NET
fpSpread1.LegacyBehaviors = LegacyBehaviors.None
fpSpread1.TabStrip.Editable = True
'Set style for locked cells
fpSpread1.ActiveSheet.LockBackColor = System.Drawing.Color.LightCyan
fpSpread1.ActiveSheet.LockForeColor = System.Drawing.Color.Green
'Using the Column Object Unlock all the cells
Dim columnobj As FarPoint.Win.Spread.Column 'Column Object
Dim count As Integer = fpSpread1.ActiveSheet.Columns.Count - 1 'Get the column count
columnobj = fpSpread1.ActiveSheet.Columns(0, count) 'Get all of the columns in the worksheet
columnobj.Locked = False 'Unlock the cells
'Use the Cells Object to lock a specific cells or cell ranges
Dim cellobj As FarPoint.Win.Spread.Cell 'Cell Object
cellobj = fpSpread1.ActiveSheet.Cells("B2:D5") 'Get a cell range
cellobj.Value = "Locked Cells" 'Lock the cell range
cellobj.Locked = True 'Lock the cell range
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All) 'Protect the worksheet

Set Custom Worksheet Protection Settings

Set custom Excel (XLSX) Worksheet Protection options in your .NET app in two different ways. Programmatically using the Spread.NET API, or during runtime using the Spread.NET built-in, Excel-like, Protect Sheet dialog.

Programmatically Set Custom Worksheet Protection Settings

Developers can programmatically set custom worksheet protection settings when invoking the Protect method. The Protect method passes the WorksheetLocks Enumeration, which represents the protected data of the worksheet – and allows developers to customize interactions and behaviors when the worksheet is protected. Below we are setting the WorksheetLocks SelectLockCells member to specify not to allow the user to select the locked cells.

C#

fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.SelectLockedCells);

VB.NET

fpSpread1.LegacyBehaviors = LegacyBehaviors.None
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.SelectLockedCells)

In this GIF, all of the cells in the workbook are unlocked except for 2 (B2:C2), and the worksheet is protected. Notice how the user is unable to select the locked cells:

Programmatically Set Custom Worksheet Protection Settings - unable to select Locked cells in a Protected Workbook

Apply Custom Worksheet Protection Settings During Runtime

End-users can apply custom worksheet protection options during runtime using Spread.NET’s built-in Protect Sheet Dialog. Observe in the below image, the dialog lists the behaviors for the user to allow or not allow when the worksheet is protected. See this blog section for more details on using the Protect Sheet Dialog.

SPNET's Built-In Protect Sheet Dialog - Available During Runtime

How to Programmatically Password Protect an Excel File

Programmatically Set a Password to an Excel Workbook

Add a layer of encryption and security to your Excel file by programmatically setting a password when protecting the workbook. Excel supports 2 different passwords being set at the workbook level, one to restrict opening the workbook and another to restrict workbook modification.

Workbook Password to Restrict Opening

Setting a password to restrict opening a workbook ensures that only authorized users can access the data within the Excel file. Developers can programmatically apply a password to restrict opening or open a password-protected Excel workbook in their .NET app using Spread.NET’s SaveExcel and OpenExcel methods.

C#

fpSpread1.OpenExcel("D:\\tmp\\SalesInfo.xlsx", "password"); // Open password protected Excel file
fpSpread1.SaveExcel("D:\\tmp\\SalesInfo.xlsx", ExcelSaveFlags.UseOOXMLFormat, "password"); // Programmatically save a password protected Excel file

VB.NET

fpSpread1.OpenExcel("D:\tmp\SalesInfo.xlsx", "password") 'Open password protected Excel file 
fpSpread1.SaveExcel("D:\tmp\SalesInfo.xlsx", ExcelSaveFlags.UseOOXMLFormat, "password") 'Programmatically save a password protected Excel file

When opening a password-secured document with Excel, the user is prompted to enter the password. If the password is not provided the workbook will not open.

Workbook Password to Restrict Modification

There are scenarios that require the developer to only protect the structure of the workbook, for example, restricting users from adding, removing, or hiding/unhiding worksheets. Spread.NET supports protecting the structure of the workbook by setting a modification password using the workbook's Protect method.

C#

fpSpread1.AsWorkbook().Protect(WorkbookLocks.All, "password");

VB.NET

fpSpread1.AsWorkbook().Protect(WorksheetLocks.All, "password")

When opening the modification-password-protected workbook, the user will be informed that the workbook is protected and cannot be changed.

Programmatically Set a Password to a Worksheet in an Excel File

Developers can restrict users from editing certain worksheets within an Excel workbook by setting a password to the specific worksheets. The worksheet’s Protect and Unprotect methods provide a password parameter to set when protecting/unprotecting a specific worksheet.

C#

fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All, "password");

VB.NET

fpSpread1.LegacyBehaviors = LegacyBehaviors.None
fpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.All, "password")

With the password applied to a worksheet, users can view the data but not edit the content. If the workbook is opened using Excel and an edit is attempted, the user is notified that the sheet is protected and to enter the password to make changes:

Excel prompt informing the user that worksheet is protected.

GrapeCity Spreadsheet Components

This article only scratches the surface of the full capabilities of the GrapeCity Spread.NET spreadsheet component. Review the documentation to see some of the many available features, and download the demo explorer to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about Spread.NET and the new features added with the v16 release, check out our release blog.