from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
START_ROW = 297 # 0 based (subtract 1 from excel row number)
col_age_november = 1
col_summer1 = 2
col_fall1 = 3
rb = open_workbook(file_path,formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
for row_index in range(START_ROW, r_sheet.nrows):
age_nov = r_sheet.cell(row_index, col_age_november).value
if age_nov == 3:
#If 3, then Combo I 3-4 year old for both summer1 and fall1
w_sheet.write(row_index, col_summer1, 'Combo I 3-4 year old')
w_sheet.write(row_index, col_fall1, 'Combo I 3-4 year old')
wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])
Here's some sample code I used recently to do just that.
It opens a workbook, goes down the rows, if a condition is met it writes some data in the row. Finally it saves the modified file.
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
START_ROW = 297 # 0 based (subtract 1 from excel row number)
col_age_november = 1
col_summer1 = 2
col_fall1 = 3
rb = open_workbook(file_path,formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
for row_index in range(START_ROW, r_sheet.nrows):
age_nov = r_sheet.cell(row_index, col_age_november).value
if age_nov == 3:
#If 3, then Combo I 3-4 year old for both summer1 and fall1
w_sheet.write(row_index, col_summer1, 'Combo I 3-4 year old')
w_sheet.write(row_index, col_fall1, 'Combo I 3-4 year old')
wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])
#!C:\Python343\python
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
from xlwt import easyxf # http://pypi.python.org/pypi/xlwt
file_path = 'C:\Dev\Test_upd.xls'
rb = open_workbook('C:\Dev\Test.xls',formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
w_sheet.write(1, 1, 'Value')
wb.save(file_path)
I had the same problem. My customer ordered me Python 3.4 script that updates XLS (not XLSX) Excel files.
The 1st package xlrd was installed by "pip install" without problems in my Python home.
The 2nd one xlwt needed to say "pip install xlwt-future" to be compatible.
The 3rd one xlutils has no support for Python 3, but I adapted it a little bit and now it works at least for dummy script:
#!C:\Python343\python
from xlutils.copy import copy # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
from xlwt import easyxf # http://pypi.python.org/pypi/xlwt
file_path = 'C:\Dev\Test_upd.xls'
rb = open_workbook('C:\Dev\Test.xls',formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
w_sheet.write(1, 1, 'Value')
wb.save(file_path)
P.S.: Some functions are not called in the dummy example, so maybe they will need for an adaptation also. Who wants to do it, fix exceptions one-by-one with a google help. It's not a very difficult task, because the package code is small...
发布评论
评论（5）
这是我最近用来执行此操作的一些示例代码。
它打开一个工作簿，向下浏览行，如果满足条件，它会在该行中写入一些数据。最后保存修改后的文件。
Here's some sample code I used recently to do just that.
It opens a workbook, goes down the rows, if a condition is met it writes some data in the row. Finally it saves the modified file.
您需要 xlutils.copy。尝试这样的操作：
请记住，默认情况下您无法覆盖单元格，如 这个问题。
You need
xlutils.copy. Try something like this:Keep in mind you can't overwrite cells by default as noted in this question.
代码示例正是这样：
您需要创建 book1.xls 进行测试，但您已经明白了。
The code example is exactly this:
You'll need to create book1.xls to test, but you get the idea.
openpyxl
openpyxl
我也有同样的问题。我的客户向我订购了用于更新 XLS（而非 XLSX）Excel 文件的 Python 3.4 脚本。
第一个包 xlrd 是通过“pip install”在我的 Python 主目录中安装的，没有出现任何问题。
第二个 xlwt 需要说“pip install xlwt-future”才能兼容。
第三个 xlutils 不支持 Python 3，但我对其进行了一些修改，现在它至少适用于虚拟脚本：
我在此处附加了文件：http://ifolder.su/43507580
写入[电子邮件受保护]（如果已过期）。
PS：有些函数在虚拟示例中没有被调用，所以也许它们也需要进行调整。谁愿意这样做，通过谷歌帮助一一修复异常。这不是一个非常困难的任务，因为包代码很小......
I had the same problem. My customer ordered me Python 3.4 script that updates XLS (not XLSX) Excel files.
The 1st package xlrd was installed by "pip install" without problems in my Python home.
The 2nd one xlwt needed to say "pip install xlwt-future" to be compatible.
The 3rd one xlutils has no support for Python 3, but I adapted it a little bit and now it works at least for dummy script:
I attached the file here: http://ifolder.su/43507580
Write to [email protected] if it got expired.
P.S.: Some functions are not called in the dummy example, so maybe they will need for an adaptation also. Who wants to do it, fix exceptions one-by-one with a google help. It's not a very difficult task, because the package code is small...