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:

1. Programmatically Protect and Unprotect an Excel File (.XLSX)
2. Programmatically Protect and Unprotect a Specific Excel Worksheet
3. 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
4. Programmatically Protect Specific Cells, Ranges, and Objects on a Worksheet
5. How to Programmatically Password Protect an Excel Workbook
   • Programmatically Set a Password to an Excel Workbook
   • Programmatically Set a Password to an Excel Worksheet

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

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()

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:

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

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 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.

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: