Python 使用 xlrd 和 xlwt 编辑现有的 Excel 工作簿和工作表

Edit existing excel workbooks and sheets with xlrd and xlwt

提问by Hyman Pettersson

In the documentationfor xlrdand xlwtI have learned the following:

在文档中xlrd，xlwt我了解到以下内容：

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_workbookis only part of the xlrdmodule, not xlwt.

但open_workbook只是xlrd模块的一部分，而不是xlwt.

Any ideas?

有任何想法吗？

Edit1: After Olivers suggestion I looked into xlutilsand tried the following:

编辑 1：在奥利弗的建议之后，我调查xlutils并尝试了以下内容：

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_sheetfunction. Finally found out how to use it:

编辑 2：错误消息是由于get_sheet函数使用不当造成的。终于知道怎么用了：

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

采纳答案by Hyman Pettersson

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

正如我在 op 的编辑中所写的那样，要编辑现有的 excel 文档，您必须使用该xlutils模块（感谢 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.

这会将位于“names.xls”第一张工作表中 a1 处的单元格内容替换为文本“a1”，然后保存文档。

回答by nda

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

这是使用openpyxl与 xlsx 兼容的模块执行上述代码的另一种方法。从我到目前为止所看到的，它也保持格式化。

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