Python XLWT attempt to overwrite cell workaround

Question

Using the python module xlwt, writing to the same cell twice throws an error:

Message File Name   Line    Position    
Traceback               
    <module>    S:\******** 
    write   C:\Python26\lib\site-packages\xlwt\Worksheet.py 1003        
    write   C:\Python26\lib\site-packages\xlwt\Row.py   231     
    insert_cell C:\Python26\lib\site-packages\xlwt\Row.py   150     
Exception: Attempt to overwrite cell: sheetname=u'Sheet 1' rowx=1 colx=12   

with the code snippet

def insert_cell(self, col_index, cell_obj):
        if col_index in self.__cells:
            if not self.__parent._cell_overwrite_ok:
                msg = "Attempt to overwrite cell: sheetname=%r rowx=%d colx=%d" \
                    % (self.__parent.name, self.__idx, col_index)
                raise Exception(msg) #row 150
            prev_cell_obj = self.__cells[col_index]
            sst_idx = getattr(prev_cell_obj, 'sst_idx', None)
            if sst_idx is not None:
                self.__parent_wb.del_str(sst_idx)
        self.__cells[col_index] = cell_obj

Looks like the code 'raise'es an exception which halts the entire process. Is removing the 'raise' term enough to allow for overwriting cells? I appreciate xlwt's warning, but i thought the pythonic way is to assume "we know what we're doing". I don't want to break anything else in touching the module.

Answer 1

The problem is that overwriting of worksheet data is disabled by default in xlwt. You have to allow it explicitly, like so:

worksheet = workbook.add_sheet("Sheet 1", cell_overwrite_ok=True)

Answer 2

What Ned B. has written is valuable advice -- except for the fact that as xlwt is a fork of pyExcelerator, "author of the module" is ill-defined ;-)

... and Kaloyan Todorov has hit the nail on the head.

Here's some more advice:

(1) Notice the following line in the code that you quoted:

if not self.__parent._cell_overwrite_ok:

and search the code for _cell_overwrite_ok and you should come to Kaloyan's conclusion.

(2) Ask questions on (and search the archives of) the python-excel google-group

(3) Check out this site which gives pointers to the google-group and to a tutorial.

Background: the problem was that some people didn't know what they were doing (and in at least one case were glad to be told), and the behaviour that xlwt inherited from pyExcelerator was to blindly write two (or more) records for the same cell, which led not only to file bloat but also confusion, because Excel would complain and show the first written and OpenOffice and Gnumeric would silently show the last written. Removing all trace of the old data from the shared string table so that it wouldn't waste space or (worse) be visible in the file was a PITA.

The whole saga is recorded in the google-group. The tutorial includes a section on overwriting cells.

Answer 3

If you:

• don't want to set the entire worksheet to be able to be overwritten in the constructor, and
• still catch the exception on a case-by-case basis

...try this:

try:
    worksheet.write(row, col, "text")
except:
    worksheet._cell_overwrite_ok = True
    # do any required operations since we found a duplicate
    worksheet.write(row, col, "new text")
    worksheet._cell_overwrite_ok = False

Answer 4

You should get in touch with the author of the module. Simply removing a raise is unlikely to work well. I would guess that it would lead to other problems further down the line. For example, later code may assume that any given cell is only in the intermediate representation once.