Protecting Your Worksheet

There may be times when you want to lock your Excel worksheet so that end users cannot edit or manipulate the data in a worksheet. You can implement worksheet protection using ExcelWriter by calling the method Worksheet.Protect.

Worksheet protection is meant to lock data for the purpose of presentation only. Worksheet protection should not be confused with encryption, and it should not be used to hide or protect sensitive data. When worksheet protection is activated, users can see the worksheet and all the data within it, but will be prevented from altering the content or formatting the cells.

ExcelApplication xlw = new ExcelApplication();
Workbook wb = xlw.Create();
wb.EncryptPassword = "myPassword";>
xlw.Save(wb, outPath);

How to Activate Worksheet Protection with ExcelWriter

Using ExcelWriter, there are two ways to write-protect worksheets in your workbook:

1. Protect in Microsoft Excel and Open in ExcelWriter
2. Protect in ExcelWriter Code

Protect in Microsoft Excel and Open in ExcelWriter

Create a workbook in Microsoft Excel, open the Tools menu, and select Protection -> Protect sheet. Then, open the spreadsheet with ExcelWriter script, using either ExcelTemplate or ExcelApplication.Open. ExcelWriter will preserve all existing spreadsheet settings, including worksheet protection.

Protect in ExcelWriter Code

Use the method Worksheet.Protect to active worksheet protection. Pass the method a password for unlocking the worksheet in Excel. Pass "" or null to protect the worksheet without a password.

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create(); 
Worksheet ws = wb.Worksheet[0];
ws.Protect("MyPassword");
...

Removing Worksheet Protection

To remove worksheet protection in Microsoft Excel,

1. From the Tools menu, select Protection -> Unprotect Sheet.
2. If a password was set, enter it and click Ok.

To remove worksheet protection with ExcelWriter, call Worksheet.Unprotect:

ws.Unprotect();

Protecting Specific Cells

When you activate worksheet protection, all of the cells in the worksheet will be locked by default. However, you can unlock cells within a protected worksheet, allowing the user to fill in or modify some fields.

To unlock cells in Microsoft Excel:

1. Select a cell or cells to unlock.
2. From the Format menu select Cells...
3. Select the Protection tab.
4. Uncheck Locked.

With ExcelWriter, you can unlock cells by setting Style.CellLocked to false and applying the style to an individual cell. The following example protects all cells except for A1 and B1:

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create(); 
Worksheet ws = wb.Worksheet[0];	


//--- Turn on worksheet protection.
ws.Protect("MyPassword");


//--- Create an "unlocked" style.
Style unlockedStyle = wb.CreateStyle();
unlockedStyle.CellLocked = false;

	
//--- Apply the style.
ws.Cells["A1"].Style = unlockedStyle;
ws.Cells["B1"].Style = unlockedStyle;

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.