Wednesday, 10 August 2011 02:48

.NET Protect Excel-Protect Excel Workbook and Worksheet

Written by  support iceblue
Rate this item
(0 votes)

One Excel workbook has several worksheets (default 3) so that users can choose to protect Excel workbook or specified worksheets in this workbook. Solutions in this guide focus on demonstrating how to protect Excel workbook and worksheet in C# and VB.NET with Spire.XLS for .NET.

Protect Workbook

Spire.XLS for .NET provides a Workbook.Protect(String) method of Workbook class to prevent other from opening Excel file without a correct password.

[C#]
using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
            book.LoadFromFile(@"E:\Work\Documents\VendorInfo.xlsx");
            //Protect Workbook
            book.Protect("abc-123");
            //Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}
          
[VB.NET]
Imports Spire.Xls

Namespace ProtectExcel
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Load Workbook
            Dim book As New Workbook()
            book.LoadFromFile("E:\Work\Documents\VendorInfo.xlsx")
            'Protect Workbook
            book.Protect("abc-123")
            'Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ProtectExcel.xlsx")
        End Sub
    End Class
End Namespace
          

Effect as screenshot below:

Encrypt Excel File

Protect Worksheet

Workbook class contains a WorksheetCollection property to enable users to access specified worksheet. After accessing this worksheet, users can invoke Protect method of XlsWorksheetBase class to protect.

There are two possibilities to protect worksheet.

With Password: Pass String password to Protect worksheet to protect worksheet with a password. After protecting, users cannot do any actions on this worksheet.

With Password and Options: Pass String password and SheetProtectionType options to protect worksheet with password and specified permissions.

The following table presents 18 sheet protection types.

None Represents none flags.
Object Protects shapes.
Scenarios Protects scenarios.
FormattingCells Allows users to format any cells on a protected worksheet.
FormattingColumns Allows users to format any columns on a protected worksheet.
FormattingRows Allows users to format any rows on a protected worksheet.
InsertingColumns Allows users to insert columns on a protected worksheet.
InsertingRows Allows users to insert rows on a protected worksheet.
InsertingHyperlinks Allows users to insert hyperlinks on a protected worksheet.
DeletingColumns Allows users to delete columns on a protected worksheet.
DeletingRows Allows users to delete rows on a protected worksheet.
LockedCells Protects locked cells.
Sorting Allows users to sort on a protected worksheet.
Filtering Allows users to set filters on a protected worksheet.
UsingPivotTable Allows users to use pivot table reports on a protected worksheet.
UnlockedCells Protects users interface, but not macros.
Contents Represents all flags.
All Represents default protection.

Code to protect worksheet with password:

[C#]
using Spire.Xls;

namespace ProtectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Workbook
            Workbook book = new Workbook();
            book.LoadFromFile(@"E:\Work\Documents\VendorInfo.xlsx");
            //Protect Worksheet with Password
            Worksheet sheet = book.Worksheets[0];
            sheet.Protect("def-345",SheetProtectionType.None);
            //Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ProtectExcel.xlsx");
        }
    }
}
          
[VB.NET]
Imports Spire.Xls

Namespace ProtectExcel
    Friend Class Program
        Shared Sub Main(ByVal args() As String)
            'Load Workbook
            Dim book As New Workbook()
            book.LoadFromFile("E:\Work\Documents\VendorInfo.xlsx")
            'Protect Worksheet with Password
            Dim sheet As Worksheet = book.Worksheets(0)
            sheet.Protect("def-345", SheetProtectionType.None)
            'Save and Launch
            book.SaveToFile("ProtectExcel.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ProtectExcel.xlsx")
        End Sub
    End Class
End Namespace
          

Effect as screenshot below:

Encrypt Excel File

Spire.XLS, specified designed for developers, enables to generate, write, edit and save Excel workbooks (Excel 97-2003, Excel 2007 and Excel 2010) in .NET, Silverlight and WPF with C# and VB.NET.

About .NET Excel Component

Additional Info

  • tutorial_title: Protect Excel Workbook and Worksheet
Last modified on Tuesday, 07 January 2014 09:50