How to set Excel document protection in C #-workbooks, worksheets, cells

Introduction

Excel is an indispensable tool for data statistics and processing, which is widely used in work and learning. For some important Excel files, you need to set up document protection for special people to view, edit, or prevent important data from being compromised, including setting access passwords, setting file read-only, and so on. This article describes how to use C # to set up protection for Excel workbooks and worksheets, with the following points in the example:

1. Encrypt an Excel workbook
2. Decrypt an Excel workbook
3. Encrypt an Excel worksheet
   3.1 Encrypt the entire worksheet
   3.2 Locking a range of cells
4. Decrypt a worksheet
5. Hide cell formulas

Tool Use

• Spire. XLS for. NET 8.0
  PS: after installation, add reference Spire.Xls.dll to the project and add the appropriate namespace, DLL files can be obtained in the Bin folder under the installation path.

Note: because we encounter a variety of requirements in the work, encryption of Excel tables can be implemented in different ways to implement encryption requirements, Spire.xls can provide 18 different types of protection, as shown in the following table:

code example 1. Encrypt an Excel workbook

"C #"

using Spire.Xls;namespace ProtectWorkbook_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个工作簿并加载一个工作簿实例            Workbook book = new Workbook();            book.LoadFromFile("test.xls");            //为工作簿设置访问密码            book.Protect("myworkbook");            //保存并打开文档            book.SaveToFile("ProtectedWorkbook.xlsx", ExcelVersion.Version2010);            System.Diagnostics.Process.Start("ProtectedWorkbook.xlsx");        }    }}

Run the project, generate the file (viewable in the project folder Bin>debug), click Open the workbook, as shown in, the password settings are complete. Enter a password to view the document.

2. Decrypt an Excel workbook

"C #"

using Spire.Xls;namespace UnProtestedWordbook_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个Workbook实例            Workbook workbook = new Workbook();            //输入密码并加载文档            workbook.OpenPassword = ("123");            workbook.LoadFromFile("test.xlsx", ExcelVersion.Version2013);            //取消保护            workbook.UnProtect();            //保存并打开文档            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);            System.Diagnostics.Process.Start("Output.xlsx");        }    }}

There is no password protection after running the program.

3. Encrypt an Excel worksheet 3.1 encrypt the entire worksheet (set to read-only)

"C #"

using Spire.Xls;namespace ProtectWorksheet_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个工作簿并加载一个工作簿实例            Workbook book = new Workbook();            book.LoadFromFile("test.xlsx");            //指定需要加密的工作表            Worksheet sheet = book.Worksheets[0];            //为工作表设置启用编辑的密码            sheet.Protect("myworksheet", SheetProtectionType.None);            //保存并打开文件            book.SaveToFile("ProtectedWorksheet.xlsx", ExcelVersion.Version2010);            System.Diagnostics.Process.Start("ProtectedWorksheet.xlsx");        }    }}

At this point, you need a password to edit your worksheet, and you can edit it when you enter it correctly.

3.2 Specifying an editable range of cells

"C #"

using Spire.Xls;namespace UnlockCell_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个工作簿并加载一个实例            Workbook book = new Workbook();            book.LoadFromFile("test.xlsx");            //获取工作簿中第一个工作表            Worksheet sheet = book.Worksheets[0];            //设置工作表保护密码，并指定不受密码保护（即允许用户编辑）的单元格区域                sheet.AddAllowEditRange("AAA", sheet.Range["B2:E6"], "");             sheet.Protect("AAA", SheetProtectionType.All);             //保存并打开文件            book.SaveToFile("UnlockCell.xlsx", ExcelVersion.Version2010);            System.Diagnostics.Process.Start("UnlockCell.xlsx");        }    }}

：

At this point, the cells in the specified range can be edited arbitrarily. You must enter a password when editing a cell outside the specified range.

4. Decrypt the worksheet

"C #"

using Spire.Xls;namespace ProtectWorksheet_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个工作簿并加载一个工作簿实例            Workbook book = new Workbook();            book.LoadFromFile("ProtectedWorksheet.xlsx");            //获取工作簿中第一个工作表            Worksheet sheet = book.Worksheets[0];            //设置撤销保护的密码             sheet.Unprotect("myworksheet");            //保存并打开文件            book.SaveToFile("UnProtectedWorksheet.xlsx", ExcelVersion.Version2010);             System.Diagnostics.Process.Start("UnProtectedWorksheet.xlsx");         }    }}

The worksheet no longer has password protection after running the program.

5. Hide Formulas

"C #"

using Spire.Xls;namespace ProtectWorksheet_XLS{    class Program    {        static void Main(string[] args)        {            //初始化一个工作簿并加载一个工作簿实例            Workbook book = new Workbook();            book.LoadFromFile("test.xlsx");            //获取工作簿中第一个工作表            Worksheet sheet = book.Worksheets[0];            //设置公式隐藏 ，并设置保护密码             sheet.AllocatedRange.IsFormulaHidden = true;            sheet.Protect("123");            //保存并打开文件            book.SaveToFile("HideFormular.xlsx", ExcelVersion.Version2010);            System.Diagnostics.Process.Start("HideFormular.xlsx");         }    }}

Effect comparison:
Before

After

When you select a cell, the cell formula is no longer displayed.

All of this is about setting up the Excel document protection, for different needs, you can choose the protection method, you can also try to find out the table of other types of protection methods.
Finish this article!
(If you need to reprint, please specify the source)

How to set Excel document protection in C #-workbooks, worksheets, cells