Security

Security (9)

Wednesday, 29 November 2017 00:38

Hide Formulas When Protecting a Worksheet in C#

Written by support iceblue

If your worksheet has some important formulas that you don’t want others to view, you may want to hide these formulas. This article demonstrates how to hide formulas when protecting a worksheet using Spire.XLS and C#.

The XlsRange.IsFormulaHidden property is used to determine if the formula will be hidden when the worksheet is protected. You can hide the formulas in a specific cell range by setting the XlsRange.IsFormulaHidden property to true, but note that the formulas can be hidden only if the worksheet is protected, they won’t be hidden if the workbook is protected while the worksheet is not.

//Initialize an object of Workbook class 
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Hide the formulas in the used range
sheet.AllocatedRange.IsFormulaHidden = true;

//Protect the worksheet with password
sheet.Protect("123");

//Save the file
workbook.SaveToFile("HideFormulas.xlsx", ExcelVersion.Version2013);

Screenshot:

Hide Formulas When Protecting a Worksheet in C#

Spire.XLS supports to protect the whole excel workbook and specified worksheet with password. When we deal with the protected worksheet, sometimes we need to allow users to edit some specified ranges of the excel worksheet. This article will focus on demonstrating how to use the AddAllowEditRange method offered by Spire.XLS to set the specified range on a protected worksheet to be editable by users.

Step 1: Initialize an instance of Workbook and load the document from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Get the first worksheet from the workbook.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Define the specified ranges of Excel to allow users to edit while sheet is protected.

sheet.AddAllowEditRange("AAA", sheet.Range["C2:D8"], "");

Step 4: Protect the worksheet.

sheet.Protect("AAA", SheetProtectionType.All);

Step 5: Save the document to file.

workbook.SaveToFile("AllowEditRange.xlsx", ExcelVersion.Version2010);

Effective screenshot of Allow users to edit ranges:

Allow users to edit ranges for the protected Excel worksheet in C#

Full codes:

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
 
Worksheet sheet = workbook.Worksheets[0];

sheet.AddAllowEditRange("AAA", sheet.Range["C2:D8"], "");
sheet.Protect("AAA", SheetProtectionType.All);

workbook.SaveToFile("AllowEditRange.xlsx", ExcelVersion.Version2010);

Using Spire.XLS, the password of an encrypted workbook can be removed or modified in case you know the open password. This article presents how to load a password protected Excel workbook, remove the protection or reset the password and then save the changes to the original file.

Step 1: Initialize an instance of Workbook class.

Workbook wb = new Workbook();

Step 2: Specify the open password and then load the encrypted Excel file.

wb.OpenPassword = "oldpassword";
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Encrypted.xlsx");

Step 3: Remove the password protection with UnProtect() method or reset the password by Protect() method.

//unprotect workbook
wb.UnProtect();

//reset password
wb.Protect("newpassword");

Step 4: Save the changes to file.

wb.SaveToFile(@"C:\Users\Administrator\Desktop\Encrypted.xlsx, ExcelVersion.Version2010");

Full Code:

[C#]
Workbook wb = new Workbook();
wb.OpenPassword = "oldpassword";
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Encrypted.xlsx");

////unprotect workbook
//wb.UnProtect();

//reset password
wb.Protect("newpassword");
wb.SaveToFile(@"C:\Users\Administrator\Desktop\Encrypted.xlsx", ExcelVersion.Version2010);   
[VB.NET]
Dim wb As New Workbook()
wb.OpenPassword = "oldpassword"
wb.LoadFromFile("C:\Users\Administrator\Desktop\Encrypted.xlsx")

'''/unprotect workbook
'wb.UnProtect();

'reset password
wb.Protect("newpassword")
wb.SaveToFile("C:\Users\Administrator\Desktop\Encrypted.xlsx", ExcelVersion.Version2010)

Spire.XLS provides a class named Workbook that represents an Excel workbook. This class contains a method named IsPasswordProtected(string fileName) which returns a boolean value. If the value is true, means the workbook is encrypted with password, otherwise it's not.

This is an Excel workbook protected with password:

Detect if an Excel Workbook is Password Protected in C#, VB.NET

Now refer below code to detect if the Excel workbook is password protected:

[C#]
using System;
using Spire.Xls;

namespace Detect_if_workbook_is_password_protected
{
    class Program
    {
        static void Main(string[] args)
        {
            string fileName = "E:\\Program Files\\Sample.xlsx";
            //Detect if the Excel workbook is password protected.
            bool value = Workbook.IsPasswordProtected(fileName);
            Console.WriteLine(value);
            Console.ReadKey();
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace Detect_if_workbook_is_password_protected
	Class Program
		Private Shared Sub Main(args As String())
			Dim fileName As String = "E:\Program Files\Sample.xlsx"
			'Detect if the Excel workbook is password protected.
			Dim value As Boolean = Workbook.IsPasswordProtected(fileName)
			Console.WriteLine(value)
			Console.ReadKey()
		End Sub
	End Class
End Namespace

After running the project, we get the Output that shows the workbook is password protected:

Detect if an Excel Workbook is Password Protected in C#, VB.NET

Wednesday, 28 November 2012 02:16

Lock Excel Cell or Cells in C#, VB.NET

Written by support iceblue

Excel cells lock is a mainly used to protect a certain cell or part of cells in Excel worksheet. As long as the Excel cell or cells are locked, any edition on the locked cells are forbidden such as insert and modify data, change data property in the locked cells etc. This section will introduce a solution to lock Excel cell or cells via a .NET Excel component in C#, VB.NET.

Spire.XLS for .NET, with rich features in editing, reading and manipulating Excel files, enables you to quickly lock Excel cell or cells in C#, VB.NET. In the solution, you only need to set the Worksheet class property: Worksheet.Range.Style.Locked to be true. For example, you can lock A1 in the first worksheet by workbook.Worksheets[0].Range["A1"].Style.Locked = true; Now ,please see the target Excel file as below picture:

Lock Excel Cells

Here you can Download Spire.XLS for .NET and add Spire.XLS dll in your system. Then, perform lock Excel cells task by below code.

[C#]
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
            workbook.CreateEmptySheets(1);
            Spire.Xls.Worksheet sheet = workbook.Worksheets[0];

            sheet.Range["A1"].Text = "Locked";
            sheet.Range["B1"].Text = "Unlocked";

            sheet.Range["A1"].Style.Locked = true;
            sheet.Range["B1"].Style.Locked = false;

            sheet.Protect("123", SheetProtectionType.All);

            workbook.SaveToFile(@"Sample_Lock.xlsx", ExcelVersion.Version2013);
[VB.NET]
Dim workbook As New Spire.Xls.Workbook()
workbook.CreateEmptySheets(1)
Dim sheet As Spire.Xls.Worksheet = workbook.Worksheets(0)

sheet.Range("A1").Text = "Locked"
sheet.Range("B1").Text = "Unlocked"

sheet.Range("A1").Style.Locked = true
sheet.Range("B1").Style.Locked = false

sheet.Protect("123", SheetProtectionType.All)

workbook.SaveToFile("Sample_Lock.xlsx", ExcelVersion.Version2013)
Thursday, 11 August 2011 03:06

Decrypt Excel Worksheet in C#, VB.NET

Written by support iceblue

Why Decrypt Excel Worksheet?

To protect our Excel file data information, we can encrypt Excel worksheet by setting password. But we may sometimes forget the password and we can't read the excel data information neither. So, if this happens we need decrypt Excel worksheet. It’s not easy to decrypt Excel Worksheet which has been encrypted because the reason why we encrypt it is to prevent it from reading by people without password. And now, we do not have password.

How to Use C#/VB.NET to Decrypt Excel Worksheet?

Spire.XLS for .NET, a .NET Excel component which enables .NET applications to fast generate, read, write and modify Excel document without Microsoft Office Excel Automation can help us decrypt Excel worksheet by using C#/VB.NET.

Spire.XLS for .NET presents a very easy solution for developers/programmers decrypting Excel worksheet. We just need sheet.Unprotect( "password" ) method and normal encrypted excel worksheet can be decrypted.

Download Spire.XLS for .NET (or Spire.Office) with .NET framework 2.0 (or above) together. The sample code below will show you how to use C#/VB.NET decrypt Excel worksheet through Spire.XLS for .NET.

[C#]
Workbook workbook = new Workbook();
         workbook.LoadFromFile("test.xls");
         Worksheet sheet = workbook.Worksheets[0];
         sheet.Unprotect( "password" );
         workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003);
[VB.NET]
Dim workbook As Workbook
workbook.LoadFromFile("test.xls")
            Worksheet sheet = workbook.Worksheets(0)
            sheet.Unprotect( "password" )
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003)

Spire.XLS allows users to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET Excel component, it owns the ability of inserting content into Excel document, formatting cells and converting Excel documents to popular office file formats. Spire.XLS for .NET supports Excel 97-2003, Excel 2007 and Excel 2010.

Wednesday, 10 August 2011 02:48

Protect Excel Workbook and Worksheet in C#

Written by support iceblue

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.

Tuesday, 09 August 2011 03:46

Easily Lock Excel Worksheet in C#, VB.NET

Written by support iceblue

The most important reason that people lock worksheet when perform tasks in Excel is to protect the original Excel file from editing or modifying by other people. Through Microsoft Excel, you can set the entire Excel worksheet by setting its property as Read Only or just set partial region area cells as Read Only via protecting worksheet. While how to lock worksheet with C#, VB.NET will be the core topic in this section.

Spire.XLS for .NET, as a fast and reliable excel component, enables you to lock your worksheet by setting Worksheet class property: Worksheet.Range.Style.Locked = true. By this component, you can lock any worksheet that you need. In this solution, worksheet one and worksheet two are locked as you view in below picture:

Lock Excel Worksheet

Now, before the detail code, you have to add Spire.Xls dll by download Spire.XLS for .NET.

[C#]
using Spire.Xls;

namespace lock_excel_worksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\lock worksheet.xls");
            workbook.Worksheets[0].Range.Style.Locked = true;
            workbook.Worksheets[1].Range.Style.Locked = true;
            workbook.Worksheets[0].Protect("", SheetProtectionType.All);
            workbook.Worksheets[1].Protect("", SheetProtectionType.All);
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003);
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace lock_excel_worksheet
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\lock worksheet.xls")
            workbook.Worksheets(0).Range.Style.Locked = true
            workbook.Worksheets(1).Range.Style.Locked = true
            workbook.Worksheets(0).Protect("", SheetProtectionType.All)
            workbook.Worksheets(1).Protect("", SheetProtectionType.All)
            workbook.SaveToFile("result.xls",ExcelVersion.Version97to2003)
        End Sub
    End Class
End Namespace

Spire.XLS allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET/Silverlight Excel component, it owns the ability of inserting content into Excel document, formatting cells and converting Excel documents to popular office file formats. Spire.XLS for .NET supports Excel 97-2003, Excel 2007 and Excel 2010.

Thursday, 28 July 2011 05:23

Unlock Sheet in Excel File in C#, VB.NET

Written by support iceblue

This section aims at providing developers a solution to unlock sheet in Excel workbook with C#, VB.NET via this Excel library Spire.XLS for .NET.

Spire.XLS for .NET enables you to unlock any sheet in Excel file only by one line of key code: Spire.Xls.Worksheet.Unprotect(string password); Besides, as an MS Excel component, Spire.XLS for .NET also enables you to create, read and handle Excel files with fast speed. Below is an Excel file with protected worksheets which will be unlocked in my task.

Unlock Excel Worksheet

Since you will use Spire.XLS for .NET, you have to download Spire.XLS for .NET and install it on system. When you create your project, please do not forget to add Spire.XLS.dll as reference from Bin folder. The default path is "..\Spire.XLS\Bin\NET4.0\Spire.XLS.dll". Please note that Spire.XLS for .NET supports .NET Framework 2.0 and above. Here is the whole code for unlocking Excel sheet:

[C#]
namespace UnlockExcelSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize an instance of Workbook
            Workbook workbook = new Workbook();
            //Load an Excel file with protected worksheet
            workbook.LoadFromFile(@"..\Unlock Excel Worksheet.xlsx");
            //get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Unprotect worksheet
            sheet.Unprotect("iceblue");
            //Save the file
            workbook.SaveToFile("Sample.xlsx",ExcelVersion.Version2010);
            //Launch the file
            System.Diagnostics.Process.Start("Sample.xlsx");
        }
    }
}
          
[VB.NET]
Namespace UnlockExcelSheet
	Class Program
		Private Shared Sub Main(args As String())
			'initialize an instance of Workbook
			Dim workbook As New Workbook()
			'Load an Excel file with protected worksheet
			workbook.LoadFromFile("..\Unlock Excel Worksheet.xlsx")
			'get the first worksheet
			Dim sheet As Worksheet = workbook.Worksheets(0)
			'Unprotect worksheet
			sheet.Unprotect("iceblue")
			'Save the file
			workbook.SaveToFile("Sample.xlsx",ExcelVersion.Version2010)
			'Launch the file
			System.Diagnostics.Process.Start("Sample.xlsx")
		End Sub
	End Class
End Namespace
          

After executing above code, you can see that the protected worksheet in the original Excel file has been unlocked, we can edit it also. Please see following image.

Unlock Excel Worksheet

In this section, I have introduced the solution to unlock any sheet in Excel file via Spire.XLS for .NET. I hope it can help you. If you have any questions, feedbacks and advice, you can put them on E-iceblue Forum. We will promise a prompt reply.