Imagine you have hard worked to compile your data in an Excel workbook and saved it to your computer. Your kids are using the same computer for their educational purpose. They accidentally open the file you have compiled after so much hard work and somehow make changes to your data. All your hard work is now about to get wasted as data consistency may have arisen due to all these changes.
To protect your data, Excel lets you protect your worksheets by password-protecting your workbook. This ensures that changes can not be made to your file without entering the password that you have specified. As a .NET application developer, you may be interested in providing the functionality of protecting Excel workbooks from within your .NET applications. You can achieve this using NPOI API in your application using C# or VB.NET as shown in this article.
Protect Worksheet using Microsoft Excel
Before we can jump into writing a .NET application for protecting Excel Worksheet, lets have a look at how the same can be achieved using Microsoft Excel.
Steps to Protect Excel Worksheets with Microsoft Excel
If you want to protect Excel Worksheets using Microsoft Excel, you can use the following steps.
- Select File > Info.
- Select the Protect Workbook box and choose Encrypt with Password.
- Enter a password in the Password box, and then select OK.
- Confirm the password in the Reenter Password box, and then select OK.
Words of Caution
- You will not be able to retrieve forgotten passwords, so be sure that your password is especially memorable.
- It’s not always secure to distribute password-protected files that contain sensitive information such as credit card numbers.
Protect Excel Worksheets using NPOI in C#
Now that we have seen how to protect a worksheet using Microsoft Excel, let us now have a look at how we can do the same in our .NET application. We’ll be using the open-source API NPOI for .NET for this purpose and will write the code in C#.
But before that, we need to install NPOI in our .NET project to get started. You can learn about this in our detailed instructions guide for Installing NPOI for .NET.
Steps to Protect Excel Worksheets in C#
Now that our development environment is ready, we can create a simple console-based application and use the following code.
//Create workbook IWorkbook wb = new XSSFWorkbook(); //Create a new sheet ISheet ws = wb.CreateSheet("Sheet 1"); //Protect the sheet ws.ProtectSheet("fileformat.com"); //Save the file FileStream file = File.Create("ProtectedExcelWorkbook.xlsx"); wb.Write(file, false); file.Close();
In this article, we showed how to protect Excel worksheets using NPOI API in C#. You can further explore the API functionality by studying the API documentation. If you would like to know more about working with Excel workbooks using NPOI, stay tuned for more examples in this section.