面临 XLWT 和 XLRD 的问题 - 同时读写
我面临 xlrd 和 xlwt 的问题。粘贴示例代码 以下。
from xlwt import Workbook, Formula, XFStyle
import xlrd
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
myFontStyle = XFStyle()
myFontStyle.num_format_str = '0.00'
sheet1.write(0,0,10, myFontStyle)
sheet1.write(0,1,20, myFontStyle)
sheet1.write(1,0,Formula('AVERAGE(A1:B1)'), myFontStyle)
book.save('formula.xls')
wb = xlrd.open_workbook('formula.xls')
sh = wb.sheet_by_index(0)
for rownum in range(sh.nrows):
print sh.row_values(rownum)
这个想法是将一些值写入Excel文件，有一些excel LogNormal、StdDev 等特定函数并读取计算结果 使用 XLRD 的值。
通过运行上面的代码，我得到以下结果： 不可取的：-
[10.0, 20.0]
[u'', '']
理想情况下我应该在第二排有 15 个。它写入 Excel 当我打开它时完美，但 XLRD 不返回结果。我是 对于一个非常关键的项目坚持这样做。恳请您 尽早回复。
感谢和问候 塔伦·帕斯里加
I am facing a problem with xlrd and xlwt. Pasting the sample code
below.
from xlwt import Workbook, Formula, XFStyle
import xlrd
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
myFontStyle = XFStyle()
myFontStyle.num_format_str = '0.00'
sheet1.write(0,0,10, myFontStyle)
sheet1.write(0,1,20, myFontStyle)
sheet1.write(1,0,Formula('AVERAGE(A1:B1)'), myFontStyle)
book.save('formula.xls')
wb = xlrd.open_workbook('formula.xls')
sh = wb.sheet_by_index(0)
for rownum in range(sh.nrows):
print sh.row_values(rownum)
The idea is to write some values to Excel file, have some excel
specific functions like LogNormal, StdDev etc and read the calculated
values using XLRD.
By running the above code, I get the following results which are
undesirable:-
[10.0, 20.0]
[u'', '']
Ideally I should have got 15 on the second row. It writes the Excel
perfectly when I open it but XLRD does not return the results. I am
stuck with this for a very critical project. Request you to kindly
respond earliest.
Thanks and Regards
Tarun Pasrija
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是我昨天在 python-excel google-group 上对同一问题给出的答案。
[背景：我是 xlrd 的作者/维护者和 xlwt 的维护者]
xlrd 和 xlwt 都不包含公式评估引擎。这与其他免费（在任何意义上）并且用解释性语言编写的软件包是相同的。这已记录在教程中，您可以通过 http://www.python-excel.org 下载。 ..参见第17页和第36页。
如果您将脚本分成两部分，请执行第一个，用Excel/OOo calc/Gnumeric打开结果XLS文件，[可能需要在此处按F9重新计算]，再次保存，执行第二个脚本片段：xlrd 将显示结果。
其他可能性：
(1) 在 gnumeric 邮件列表上询问是否可以以编程方式将 gnumeric 驱动到以下范围：打开命名的 XLS 文件，[重新]计算所有公式，保存为命名的 XLS 文件（包括重新计算的公式结果） -- 有必要强调这一点，因为我上次询问时，本机 gnumeric 文件格式不包含计算公式结果）。
(2) 你可以对 Openoffice.org 的 calc 程序提出同样的问题，也许在 news:comp.lang.python 和/或 www.stackoverflow.com 上......它有一组名为“PyUNO”的 API；上次我查看时，大多数人放弃了尝试浏览和理解数量巨大的文档。如果有任何更好的消息，我肯定会在 Python+电子表格世界中得到感激。
(3)“LogNormal”（我猜你指的是 LOGNORMDIST 和/或 LOGINV）和“StdDev”几乎不是 Excel 特定的。你可以用Python计算你自己的结果； xlwt 需要进行增强，以允许调用者为公式单元格提供结果值，而不是像您注意到的那样插入不变的零长度字符串。
(4) 告诉我们您的更高层次的目标……然后我们也许可以提出其他建议。
这个“非常关键的项目”是学术/慈善/商业企业吗？您是否考虑过 Resolver One (http://www.resolversystems.com/products/resolver-one/ ) ? AFAIK，他们提供折扣，而且他们的产品非常便宜，每个许可证大约 100 美元。
Here is the answer I gave to the same question on the python-excel google-group yesterday.
[Background: I'm the author/maintainer of xlrd and the maintainer of xlwt]
Neither xlrd nor xlwt contains a formula evaluation engine. This is in common with other packages which are free (in any sense) and written in an interpreted language. This is documented in the tutorial that you can download via http://www.python-excel.org ... see pages 17 and 36.
If you break up your script into two pieces, execute the first, open the result XLS file with Excel/OOo calc/Gnumeric, [may need to hit F9 here to recalculate], save again, execute the 2nd script piece: xlrd will display the results.
Other possibilities:
(1) Ask on the gnumeric mailing list if it is possible to drive gnumeric programmatically to the extent of: open named XLS file, [re-]calculate all formulas, save as named XLS file (with the recalculated formula results included -- it is necessary to stress this because the last time I asked, the native gnumeric file format did NOT included the calculated formula results).
(2) You could ask the same question about Openoffice.org's calc program, perhaps on news:comp.lang.python and/or www.stackoverflow.com ... it has a set of APIs called "PyUNO"; last time I looked, most folk gave up trying to wade through and understand the humungous quantity of documentation. Any better news would I'm sure be gratefully received in the Python+spreadsheet world.
(3) "LogNormal" (I presume you mean LOGNORMDIST and/or LOGINV) and "StdDev" are scarcely Excel-specific. You could calculate your own results in Python; xlwt would need to be augmented to allow the caller to supply a result value for a Formula cell, instead of plugging in an invariant zero-length string as you noticed.
(4) Tell us your higher-level objectives ... we may be then able to come up with other suggestions.
Is this "very critical project" academic / for a charity / for a commercial enterprise? Have you considered Resolver One (http://www.resolversystems.com/products/resolver-one/) ? AFAIK, they offer discounts and their product is dirt-cheap anyway at about USD 100 per licence.
在运行第二位之前，您需要在 Excel 中打开它并重新保存。 xlwt/xlrd 实际上本身并不计算出公式，因此您需要 Excel 来执行此操作。我刚刚用 OpenOffice 进行了测试，重新保存文件后它就可以工作了。
另外，请告诉我你使用这种方法不只是为了计算平均值吗？您可以在 Python 中非常轻松地做到这一点：（
在 Python 3 中，您可以省略 float()）
You need to open it in Excel and resave it before running the second bit. xlwt/xlrd don't actually work out the formula themselves, so you'd need Excel to do that. I've just tested with OpenOffice, and it works after resaving the file.
Also, please tell me you are not using this method just to calculate an average? You can do that in Python very easily:
(In Python 3, you can leave out the float())