使用xlrd和xlwt编辑现有的excel工作簿和工作表 [英] Edit existing excel workbooks and sheets with xlrd and xlwt

问题描述

在 xlrd 文档中>和 xlwt 我已经学到以下内容：

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

如何从现有的工作簿/表中读取： p>

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.

我已经尝试过如下：

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

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

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

任何想法？

Edit1：
在Olivers建议之后，我看了进入 xlutils 并尝试以下操作：

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.

编辑2：
错误信息是由于使用 get_sheet 函数。
最后找出如何使用它：

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

推荐答案

正如我在编辑op中编写的，要编辑现有的excel文档，您必须使用 xlutils module（Thanks Oliver）

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

这是正确的方法：

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

将第一页names.xls中位于a1的单元格的内容替换为文本a1，然后保存文件。

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.

这篇关于使用xlrd和xlwt编辑现有的excel工作簿和工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！