Edit existing excel workbooks and sheets with xlrd and xlwt
Solution 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.
Solution 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')
Jack Pettersson
Updated on January 08, 2020Comments
-
Jack Pettersson over 4 years
In the documentation for
xlrd
andxlwt
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 thexlrd
module, notxlwt
.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')