34

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')
Jack Pettersson
  • 1,606
  • 4
  • 17
  • 28
  • 1
    Have you looked into the `xlutils` made by the same author and documented in the same document you're linking to? In the 2nd subsection of the "Writing Excel files", it is written that `Worksheets Worksheets are created with the add_sheet method of the Workbook class. To retrieve an existing sheet from a Workbook, use its get_sheet method. This method is particularly useful when the Workbook has been instantiated by xlutils.copy. ` – Oliver W. Nov 16 '14 at 14:25
  • Hello Oliver, and thanks for the advice. I looked into `xlutils` and it sure seems to be what i'm looking for. I still, however, can't seem to accomplish my goal. When i use `get_sheet()` i get 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.` – Jack Pettersson Nov 16 '14 at 14:55
  • Thanks, because you provide also the import clause in your code (it is often missing) – herve-guerin Jan 09 '18 at 14:12

2 Answers2

52

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.

abaldwin99
  • 903
  • 1
  • 8
  • 26
Jack Pettersson
  • 1,606
  • 4
  • 17
  • 28
  • 11
    Thanks, but this solution works only for xls format. I tried using this for xlsx format but that corrupts the file. Luckily, I had a backup of the file so I could take that risk, but for those who don't, please make sure you use xls format. – BajajG Feb 17 '15 at 08:45
  • 6
    Also it's worth noting that this process removes all color formatting and formulas from the spreadsheet. – abaldwin99 Jul 30 '15 at 18:02
  • 1
    @abaldwin99... Do you know any other method that can keep format and formula unchanged ? – Sagar Mehta Apr 26 '16 at 19:08
  • 1
    @SagarMehta Been awhile but I believe I went the VBA macro route. I have not personally used it but xlwings looks interesting depending on your use case... http://docs.xlwings.org/en/stable/udfs.html edit: Ah you need formatting too. I think you need to go VBA unfortunately. – abaldwin99 Apr 26 '16 at 19:38
  • @SagarMehta Saving cell format (font, styles, colors) is simple http://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy . But looks like it is impossible to keep images and sheet formating, colontitles e.g. – Ivan Borshchov Oct 05 '16 at 12:42
  • Note that if your Workbook contains images, they will not be preserved in the new/edited workbook :( – Benjamin James Jan 12 '18 at 23:04
16

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')
Kenly
  • 24,317
  • 7
  • 44
  • 60
nda
  • 541
  • 7
  • 18