Append instance of a sheet to a workbook, rather than creating a new instance?
RHouse
for producing xlwt. It is just so useful.
I am creating multi-sheet spreadsheets with xlwt and I have found it
useful to sub-class Sheet and add some utility methods in my own code.
So as to avoid having to change the xlwt Worksheet class code, I
wanted to be able to add an instance of my sub-classed Worksheet to a
Workbook instance, but the current add_sheet method in Workbook.py
(around line 300) has a sheetname parameter as a string, and the
method creates a new Worksheet in the Workbook.
A slight modification to the add_sheet method has allowed me to pass
either a sheetname string or an instance of a Worksheet to be appended
to the Workbook.
def add_sheet(self, sheetname, cell_overwrite_ok=False):
import Worksheet
if isinstance(sheetname, Worksheet.Worksheet):
self.__worksheets.append(sheetname)
else:
self.__worksheets.append(Worksheet.Worksheet(sheetname,
self, cell_overwrite_ok))
return self.__worksheets[-1]
I haven't delved too deeply into the associated xlwt code, so I was
wondering whether this has any bad side effects or whether there might
be a better way of achieving my sub-classing goal.
Regards,
Richard
John Machin
> Firstly, my thanks and appreciation to everyone (and especially John)
> for producing xlwt. It is just so useful.
On behalf of everyone, thanks for the kind words. When it comes to
naming names, don't forget Roman V. Kiseliov, who wrote pyExcelerator
from which xlwt was forked -- no Roman, no xlwt. I'm just the humble
maintainer :-)
>
> I am creating multi-sheet spreadsheets with xlwt and I have found it
> useful to sub-class Sheet and add some utility methods in my own code.
Consider proposing your utility methods for inclusion in the package.
Benefits: available to others; you don't need to maintain your subclass.
> So as to avoid having to change the xlwt Worksheet class code, I
> wanted to be able to add an instance of my sub-classed Worksheet to a
> Workbook instance, but the current add_sheet method in Workbook.py
> (around line 300) has a sheetname parameter as a string, and the
> method creates a new Worksheet in the Workbook.
>
> A slight modification to the add_sheet method has allowed me to pass
> either a sheetname string or an instance of a Worksheet to be appended
> to the Workbook.
> def add_sheet(self, sheetname, cell_overwrite_ok=False):
> import Worksheet
> if isinstance(sheetname, Worksheet.Worksheet):
I'd insert a safeguard here:
if sheetname in self.__worksheets:
raise Exception('Attempt to add duplicate worksheet')
> self.__worksheets.append(sheetname)
> else:
> self.__worksheets.append(Worksheet.Worksheet(sheetname,
> self, cell_overwrite_ok))
> return self.__worksheets[-1]
>
> I haven't delved too deeply into the associated xlwt code, so I was
> wondering whether this has any bad side effects
Looks OK to me.
> or whether there might
> be a better way of achieving my sub-classing goal.
Sub-classing is not your goal (I hope). An alternative (not proposed as
better) path to the goal of getting extra Worksheet functionality would
be to monkey-patch it:
8<---
import xlwt
def bandarlog(self):
if self.name in ("Kaa", ):
print "Python is good, %s the python is bad!" % self.name
import xlwt.Worksheet
xlwt.Worksheet.Worksheet.bandarlog = bandarlog
wb = xlwt.Workbook()
ws = wb.add_sheet('Kaa')
ws.bandarlog()
8<---
Cheers,
John
danhs
My program will create a new sheet, then run a procedure which may or
may not fail. If it does fail (raises an exception), I want to remove
the sheet. If I could create sheet objects and then add them if the
procedure completes successfully, that would fix the problem entirely.
I went with a different approach because I didn't want to make changes
to the underlying library. Instead I unmangle the private
__worksheets name from the workbooks class. Then delete the last
element in the __worksheets list.
This is the actual code:
# Adds ws to self.wb and returns a ws.
ws = self.add_worksheet(sheetname=sheetname)
try:
# Try risky procedure
ws_class(worksheet=ws, data_obj=self.data_obj, *args,
**kwargs)
except:
# Remove worksheet if risky procedure fails.
worksheets = getattr(self.wb, "_" +
self.wb.__class__.__name__ + "__worksheets")
del worksheets[-1]
else:
# Save the workbook if risky procedure succeeds.
self.wb.save(self.filename)
The above code works. I just don't like mucking with private
methods. I'd much rather if there was a "remove worksheet" method.
It wouldn't be hard to add the logic in, but without commit rights to
repository, interest from maintainers, and knowledge that making a
"remove worksheets" method wouldn't interfere with other working
parts. So far, this hack doesn't seem to effect other stuff. But, I
didn't take a thorough survey and I wouldn't feel comfortable adding
that type of logic without one of the maintainers telling me that the
__worksheets attribute really is sufficiently decoupled from the rest
of the class so that removing a sheet in a sane way can be added in.
Daniel
John Machin
> I encountered a similar issue.
>
> My program will create a new sheet, then run a procedure which may or
> may not fail. If it does fail (raises an exception), I want to remove
> the sheet. If I could create sheet objects and then add them if the
> procedure completes successfully, that would fix the problem entirely.
Hi danhs,
The OP was IIRC proposing adding a sheet subclass object immediately
after creating it. It would have additional methods and possibly
additional attributes in support of those methods, but would have had no
Worksheet.write() calls made against it. It would appear to the rest of
the world as newborn. Your "create" is rather different.
>
> I went with a different approach because I didn't want to make changes
> to the underlying library. Instead I unmangle the private
> __worksheets name from the workbooks class. Then delete the last
> element in the __worksheets list.
>
> This is the actual code:
> # Adds ws to self.wb and returns a ws.
> ws = self.add_worksheet(sheetname=sheetname)
>
> try:
> # Try risky procedure
> ws_class(worksheet=ws, data_obj=self.data_obj, *args,
> **kwargs)
> except:
> # Remove worksheet if risky procedure fails.
> worksheets = getattr(self.wb, "_" +
> self.wb.__class__.__name__ + "__worksheets")
> del worksheets[-1]
> else:
> # Save the workbook if risky procedure succeeds.
> self.wb.save(self.filename)
>
> The above code works. I just don't like mucking with private
> methods. I'd much rather if there was a "remove worksheet" method.
How do you propose to designate the worksheet to be removed? What
restrictions would you place on what worksheets could be removed?
> It wouldn't be hard to add the logic in, but without commit rights to
> repository, interest from maintainers, and knowledge that making a
> "remove worksheets" method wouldn't interfere with other working
> parts. So far, this hack doesn't seem to effect other stuff. But, I
> didn't take a thorough survey and I wouldn't feel comfortable adding
> that type of logic without one of the maintainers telling me that the
> __worksheets attribute really is sufficiently decoupled from the rest
> of the class so that removing a sheet in a sane way can be added in.
Off the top of my head there's at least one problem with this; see
example below plus the "overwriting cells" discussion in this group
round about 20-22 December plus the comment on revision 3715.
C:\xlwt\del_sheet>type delws1.py
import xlwt
wb = xlwt.Workbook()
ws0 = wb.add_sheet('ok')
ws0.write(0, 0, 'boring')
ws1 = wb.add_sheet('bad')
ws1.write(0, 0, 'Top Secret FYEO')
# pretend we had a whoopsie about now
worksheets = getattr(
wb, "_" + wb.__class__.__name__ + "__worksheets")
del worksheets[-1]
wb.save('delws1.xls')
C:\xlwt\del_sheet>runxlrd.py biff_dump delws1.xls | more
[snip]
921: 00fc SST len = 0023 (35)
925: 02 00 00 00 02 00 00 00 06 00 00 62 6f 72 69 6e
?~~~?~~~?~~borin
941: 67 0f 00 00 54 6f 70 20 53 65 63 72 65 74 20 46 g?~~Top
Secret F
957: 59 45 4f YEO
[snip)
Looks like whoopsie #2 to me.
Further OTTOMH:
(1) 3D references in formulas [coming soon] -- another sheet may contain
formulas that reference cells in the to-be-deleted sheet
(2) Named cell ranges [on the enhancement list] -- defined at workbook
level but can reference sheets
(3) Have you considered pickling your workbook immediately before
starting to create a worksheet, with fallback to the pickle if creation
goes splat?
Cheers,
John
danhs
considered. I'm glad I brought the issue up.
I haevn't considered pickling the workbook, but that's a pretty
interesting idea. Is there any reason to think that pickling would be
any more effective/better choice than a deep copy?
Daniel
John Machin
> The issues you bring up are interesting ones which I haven't
> considered. I'm glad I brought the issue up.
>
> I haevn't considered pickling the workbook, but that's a pretty
> interesting idea. Is there any reason to think that pickling would be
> any more effective/better choice than a deep copy?
Maybe speed; the copy module is written in Python, not C.
BTW: What percentage of risky sheet creation attempts fail? What causes
them to fail? Is it possible to check the data before you start creating
the sheet?
Cheers,
John
danhs
In all seriousness, the way the code is designed the sheet object
"asks" for data. So to push data to the layout object would be a bit
tricky.
The design would necessarily become more centralized and less
modular. Not good things. I would have liked to have tested first
then created sheets. But, not the way the workbook module works....
which is why I was excited about the notion of "independent sheets"
which could be added to a workbook.
Daniel
John Machin
> Look before you leap instead of ask forgiveness!?!?!
Python wise sayings and bits of zen are just like granny's proverbs: for
each one that says do X, another says don't do X.
> In all seriousness, the way the code is designed the sheet object
> "asks" for data. So to push data to the layout object would be a bit
> tricky.
> The design would necessarily become more centralized and less
> modular. Not good things. I would have liked to have tested first
> then created sheets. But, not the way the workbook module works....
> which is why I was excited about the notion of "independent sheets"
> which could be added to a workbook.
Thanks for the answer. The other two questions were: What percentage of
risky sheet creation attempts fail? What causes them to fail?
The % question is relevant to the deepcopy/pickle issue.
Cheers,
John
danhs
Thanks for the help and advice.
Errr I'm not sure of the percentage that fail. I haven't kept close
track. I would say less than 10%, maybe even less than 5%.
They fail when they can't properly access/create data. If an improper
street address is passed in and the program can't lookup that address
and create a map (or in other sheets, download necessary data
associated with that address) then the sheet creation will fail.
Daniel
danhs
Thanks for the help and advice.
Errr I'm not sure of the percentage that fail. I haven't kept close
track. I would say less than 10%, maybe even less than 5%.
They fail when they can't properly access/create data. If an improper
street address is passed in and the program can't lookup that address
and create a map (or in other sheets, download necessary data
associated with that address) then the sheet creation will fail.
Daniel