pyExcelerator - Protecting Cells

[Chris]

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.

I decided to import Formatting into the Worksheet module like this:
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. The code has been written as
such:

if protection:
work_sheet.set_protect(protection)
work_sheet.set_password(password)
else:
pass

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
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.

Any advice or help would be most appreciated.
Chris

 

[John Machin]

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