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