pyExcelerator - Protecting Cells

[John Machin]

On 9/02/2007 6:36 PM, Chris wrote:
> I'm sitting with a bit of an issue with pyExcelerator and creating an
> Excel file with certain cells protected while the rest of the
> spreadsheet is password protected.
> 
> The Protection class under Formatting has 2 variables for cell_locked
> and formula_hidden, tbh I only need to alter cell_locked to 0 to make
> those cells writable but changing that on a global scale ends up with
> everything I write being "writeable" if you re-open the file after it
> has been produced.
> 

"tbh" means what?
"changing that on a global scale" means what??

Please write a small *test* script (along the lines of those in 
pyExcelerator's examples directory, without 
irrelevant/private/otherwise_inappropriate code from your app) which 
tries to set some cells to locked and some to unlocked. If you can't get 
it to work:
(1) ensure that you have checked the bug register on Sourceforge and 
applied any patch that seems relevant to your problem
(2) come back here with a copy/paste of the actual code that you have run.

> I decided to import Formatting into the Worksheet module like this:

Why? What made you think that this would achieve your goal?

>         import Formatting
>         self.Formatting = Formatting.Protection
> 
>         self.__cell_protect = 1
>         self.__formula_hidden = 0
> 
> which is the defaults in the Protection class anyway but now when I do
> my create file routine when I try to change the cell_protect variable
> to 0 it makes absolutely no effect.

Of course it would have no effect. You appear to have given Worksheet 
objects a gratuitous __cell_protect attribute -- but no code to use it.

Protection is like a pattern or a font -- you have to cram it into an 
XFStyle object which you use as the style arg of the Worksheet.write() 
method. You will need of course at least 2 different XFStyle objects: 
one locked, another unlocked.

>  The code has been written as
> such:
> 
>             if protection:
>                 work_sheet.set_protect(protection)
>                 work_sheet.set_password(password)
>             else:
>                 pass

What induced you to write the above two statements?

> 
>             for each_heading in each_work_sheet[1]:
>                 work_sheet.write(7, heading_cnt, str(each_heading),
> header_style)
>                 heading_cnt += 1
> 
>             vert_cnt = 8
> 
>             for each_set in each_work_sheet[2]:
>                 horiz_cnt = 0
> 
>                 for data_set in each_set:
>                     work_sheet.cell_protect = 1

Now the Worksheet object has *TWO* useless attributes, one named 
__cell_protect and one named cell_protect ...

>                     work_sheet.formula_hidden = 1
> 
>                     if len(str(data_set)) < 1:
>                         work_sheet.cell_protect = 0
>                         work_sheet.formula_hidden = 0
>                         work_sheet.write(vert_cnt, horiz_cnt, ' ')
>                         horiz_cnt += 1
>                     else:
>                         work_sheet.write(vert_cnt, horiz_cnt,
> str(data_set), data_style)
>                         horiz_cnt += 1
> 
>                 vert_cnt += 1
> 
> As you can see I only want to be able to write to cells that have a
> string '' which is parsed correctly through to data which was
> originally extracted from a database.  The problem is that I can only
> seem to set it to protect all written cells or not.
> 

HTH,
John