writing to existing workbook using xlwt

Question

I am unable to find examples where xlwt is used to write into existing files. I have a existing xls file that I need to write to. When I use xlrd to read the file, I cant seem to figure out how to transform the "Book" type returned into a xlwt.Workbook. I would appreciate if someone can point me to an example.

Answer 1

You need xlutils.copy. Try something like this:

from xlutils.copy import copy
w = copy('book1.xls')
w.get_sheet(0).write(0,0,"foo")
w.save('book2.xls')

Keep in mind you can't overwrite cells by default as noted in this question.

Answer 2

Here's some sample code I used recently to do just that.

It opens a workbook, goes down the rows, if a condition is met it writes some data in the row. Finally it saves the modified file.

from xlutils.copy import copy #http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook #http://pypi.python.org/pypi/xlrd
from xlwt import easyxf #http://pypi.python.org/pypi/xlwt

START_ROW = 297 #0 based (subtract 1 from excel row number

rb = open_workbook(file_path,formatting_info=True)
r_sheet = rb.sheet_by_index(0) #read only copy to introspect the file
wb = copy(rb) #a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) #the sheet to write to within the writable copy

for row_index in range(START_ROW,r_sheet.nrows):
    age_nov = r_sheet.cell(row_index,col_names.age_november).value
    if age_nov == 3:
        #If 3, then Combo I 3-4 year old  for both summer1 and fall1
        w_sheet.write(row_index,col_names.summer1,'Combo I 3-4 year old')
        w_sheet.write(row_index,col_names.fall1,'Combo I 3-4 year old')

wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])

Answer 3

"""I am unable to find examples where xlwt is used to write into existing files."""

There are no examples. It is not possible. Not with xlwt, nor with any other software. The XLS file structure is complicated, and doesn't act like a database to which you can append rows in a table of your choice.

Whatever software you use has to make like a user with a copy of Excel and a keyboard: (1) "open the file" i.e. load the contents into memory (2) manipulate the in-memory information (3) "save" (which blows away the existing file and replaces it with a new file) or "save as" (which writes a new file and leaves the existing file unchanged).

I told you this about 12 hours ago but here it is again:

Visit this summary site.

Points of interest:

(1) xlutils package

(2) tutorial on xlrd, xlwt, and xlutils ... contains examples

(3) google-group / mailing-list for asking questions like this (helps to have worked through the tutorial first)