用xlrd和xlwt编辑现有的excel工作簿和工作表

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (24)

xlrdxlwt我了解到以下情况:

如何阅读现有工作页:

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

如何创建新的工作工作表:

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

我现在要做的是在现有工作簿中打开现有工作表,然后写入该工作表。

我试过这样的方法:

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 module, not xlwt...

有什么想法吗?

我研究了一下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')

然而,这将给出以下错误消息:

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.

错误消息是由于不正确地使用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')
提问于
用户回答回答于

正如我在OP的编辑中所写的,要编辑现有的excel文档,必须使用xlutils模块

以下是正确的方法:

#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”,然后保存文档。

用户回答回答于

下面是使用openpyxl与xlsx和xlsm兼容的模块。据我所见,它也保持格式。

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

扫码关注云+社区