Locking cells

Apr 5, 2011 at 3:33 PM

Good morning,

I've been trying to lock some cells on a spreadsheet with the following method:

                    If (Not Cell Is Nothing) Then
                        If ColorVal = 42 Then
                            Cell.CellStyle.IsLocked = False
                        Else
                            Cell.CellStyle.IsLocked = True
                        End If
                    End If

Basically, if the color is 42 (greenish), the user should be allowed to edit the cell. Otherwise, the cell should be locked. The logic flows through properly and I don't receive any errors, but when I save the workbook at the end of the process, none of the cells are locked. Do I have to set an additional property somewhere at the sheet or workbook level to turn on protection?

Thanks!

Coordinator
Apr 6, 2011 at 8:23 AM

As I know, locked cell becomes readonly only if you are in protection mode. 

Take a look at this post: http://www.cnblogs.com/atao/archive/2010/07/13/1568917.html.  Although it's in Chinese, the code should make sense for you.

Apr 6, 2011 at 3:11 PM

With your created sheet do

SheetName.ProtectSheet("WhateverPassWordYouWant");

Every cell is then locked by default.  To have an unlocked cell give it a style that has IsLocked = false (like you are already doing)

Apr 6, 2011 at 3:15 PM
Edited Apr 6, 2011 at 3:18 PM

Majnun, thanks for the suggestion.

Currently, this method is returning an "Object reference not set to an instance of an object" error. However, I think is due to the fact that some cells are located within merges. I am not sure at this point, but hope to have some more time later to figure out exactly what is happening. I'll post more once I parse through the merges and actually determine if that's what's happening or if another issue exists.

 

tonyqus: thanks for the blog hint. Great info. The code is easy to figure out and was similar to what I was doing, but I now see that it's recommended not to create a cell style for each cell. The blog creates the style for lock and unlock and then reuses them, which seems to be the desired method.

Apr 8, 2011 at 2:25 PM

I have still been unable to protect the workbook I'm loading due to an "Object reference not set" error message. I completely removed the code that was locking individual cells and am basically running the following code for testing. (The one difference that I've noted between my situation and all of the examples that I'm finding is that I'm loading an existing file, while all of the examples are creating one from scratch.)

            sStep = "Loading file stream from " & _QALFilename.Replace("\", "\\")
            Me.MessageLabel.Text = sStep
            FS = New IO.FileStream("C:\Temp\tempfile.xls", IO.FileMode.Open, IO.FileAccess.ReadWrite)

            sStep = "Loading Workbook"
            Me.MessageLabel.Text = sStep
            WorkBook = New HSSFWorkbook(FS, True)
            FS.Close()

            sStep = "Enable worksheet protection..."
            Me.MessageLabel.Text = sStep
            ' lock sheet
            WorkSheet.ProtectSheet("Test")       ' <---- this line is causing the error
  FS = New IO.FileStream("C:\Temp\tempfile.xls", IO.FileMode.Create) WorkBook.Write(FS) FS.Close()

The stack trace is:

   at NPOI.HSSF.Model.InternalSheet.ProtectSheet(String password, Boolean objects, Boolean scenarios) in C:\Users\brian\Documents\NPOI\SRC\main\HSSF\Model\InternalSheet.cs:line 2045
   at NPOI.HSSF.UserModel.HSSFSheet.ProtectSheet(String password) in C:\Users\brian\Documents\NPOI\SRC\main\HSSF\UserModel\HSSFSheet.cs:line 1008
   at NPOITest._Default.LoadMaster() in c:\users\brian\documents\visual studio 2010\Projects\NPOITest\Default.aspx.vb:line 652

Any suggestions? I don't currently have the NPOI source loaded to allow debugging into it, but that will be my next step. Hopefully, I'm missing something easy.

Thanks

Apr 14, 2011 at 8:38 PM
Edited Apr 14, 2011 at 9:33 PM

Quite a bit of additional testing completed. Unfortunately, I cannot use ProtectSheet under IIS, while it seems to work fine as a console app. I've tried with application pool id as Network Service, a specific user, and as Local System. Every other method that I implement seems to work but ProtectSheet always fails with the above message, regardless of the user id, impersonation, pool id, etc. Does anyone have any suggestions on how to overcome this issue?

While debugging, when I call the Worksheet.ProtectSheet("Test") method, the following code is executed in HSSFSheet.cs (NPOI source code):

 

 * Sets the protection enabled as well as the password
 * @param password to set for protection. Pass <code>null</code> to remove protection
 */
        public void ProtectSheet(String password)
        {
            Sheet.ProtectSheet(password, true, true); //protect objs&scenarios(normal)
        }

 

Following the debug on Sheet.ProtectSheet leads to InternalSheet.cs:

 

 * protect a spReadsheet with a password (not encypted, just Sets protect
 * flags and the password.
 * @param password to Set
 * @param objects are protected
 * @param scenarios are protected
 */
        public void ProtectSheet(String password, bool objects, bool scenarios)
        {
            int protIdx = -1;
            ProtectRecord prec = Protect;
            PasswordRecord pass = Password;
            prec.Protect = true;

 

The system throws an exception on prec.Protect, showing an "Object reference not set to an instance of an object." message, with the stacktrace similar to the previous post.

This also happens if I create a new workbook, create a new worksheet, and then attempt to do ProtectSheet on the new worksheet.

I've tried this under Windows 7 and on my production Windows Server 2003 machine. It fails in the same manner on both of them.

Is there some permission or other bit of code that I'm missing that would keep this from executing under IIS?

Thanks!

Edit: A little extra info... I'm using Excel xp/2003 files that have never had protection enabled. I think that's where the problem originates from, but I'm not sure how to go about forcing a protection pattern onto the file, if that's actually the issue.

Apr 15, 2011 at 3:52 PM

I now have this working, but I had to modify the InternalSheet.cs code for the NPOI library. Basically, I added code that would initialize and add the protection and password records to the file if they don't already exist. While this might be possible through the application's source code (and is probably intended to be implemented that way), I couldn't find any documentation to support adding the password and protection blocks either here at the NPOI forum or at the POI site. I find it hard to believe that no one else has existing workbooks/sheets that they want to add cell and sheet protection to, but I simply couldn't figure out how to do this any other way.

I'm still open to insight from anyone that might have suggestions. If anyone is interested in the code changes [hacks] that I used, let me know and I'll forward them along.

Coordinator
Apr 16, 2011 at 7:20 PM

Hi Brimars,

It looks you have created a patch for the code. Can you upload via Source Code > Upload Patch? I'll take a look into the issue and confirm if the fix is correct or not. Thank you in advance!