Edit existing excel workbooks and sheets with xlrd and xlwt

Question

In the documentation for xlrd and xlwt I have learned the following:

How to read from existing work-books/sheets:

from xlrd import open_workbook wb = open_workbook("ex.xls") s = wb.sheet_by_index(0) print s.cell(0,0).value #Prints contents of cell at location a1 in the first sheet in the document called ex.xls 

How to create new work-books/sheets:

from xlwt import Workbook wb = Workbook() Sheet1 = wb.add_sheet('Sheet1') Sheet1.write(0,0,'Hello') wb.save('ex.xls') #Creates a document called ex.xls with a worksheet called "Sheet1" and writes "Hello" to the cell located at a1 

What I want to do now is to open an existing worksheet, in an existing workbook and write to that sheet.

I have tried something like:

from xlwt import open_workbook wb = open_workbook("ex.xls") s = wb.sheet_by_index(0) print s.cell(0,0).value 

but open_workbook is only part of the xlrd module, not xlwt.

Any ideas?

Edit1: After Olivers suggestion I looked into xlutils and tried the following:

from xlrd import open_workbook from xlwt import Workbook from xlutils.copy import copy  wb = open_workbook("names.xls") s = wb.get_sheet(0) s.write(0,0,'A1') wb.save('names.xls') 

This however, gives me the following error message:

File "C:\Python27\lib\site-packages\xlrd\book.py", line 655, in get_sheet raise XLRDError("Can't load sheets after releasing resources.") xlrd.biffh.XLRDError: Can't load sheets after releasing resources. 

Edit 2: The error message was due to improper use of the get_sheet function. Finally found out how to use it:

from xlrd import open_workbook from xlwt import Workbook from xlutils.copy import copy  rb = open_workbook("names.xls") wb = copy(rb)  s = wb.get_sheet(0) s.write(0,0,'A1') wb.save('names.xls') 

Answer 1

As I wrote in the edits of the op, to edit existing excel documents you must use the xlutils module (Thanks Oliver)

Here is the proper way to do it:

#xlrd, xlutils and xlwt modules need to be installed.   #Can be done via pip install <module> from xlrd import open_workbook from xlutils.copy import copy  rb = open_workbook("names.xls") wb = copy(rb)  s = wb.get_sheet(0) s.write(0,0,'A1') wb.save('names.xls') 

This replaces the contents of the cell located at a1 in the first sheet of "names.xls" with the text "a1", and then saves the document.

Answer 2

Here's another way of doing the code above using the openpyxl module that's compatible with xlsx. From what I've seen so far, it also keeps formatting.

from openpyxl import load_workbook wb = load_workbook('names.xlsx') ws = wb['SheetName'] ws['A1'] = 'A1' wb.save('names.xlsx')