Python Excel模板读取和重写，维护公式和格式 [英] Python Excel template read and re-write, maintaining formulae and formatting

问题描述

我已经跑了，看起来找不到我要找的东西。我发现这里的所有线程都为我死了。 xlrd，xlwt和xlutils几乎可以做我需要的，但...基本的想法是，我需要使用Python将简单数据（字符串）写入Excel模板的特定表格，并将该模板写入新的.xls文件。

阅读模板，将其复制到新的工作簿对象，写入新值，并写出新的.xls文件是没有问题的。但是，我找不到维护 公式和格式的Python Excel模块。

`

  our ='template.xls'
 
＃打开具有维护格式的只读模板
 rb = open_workbook（我们的formatting_info =真的）
 r_sheet = rb.sheet_by_index（4）
 
＃将其复制到新的工作簿对象
 wb = copy（rb）
 
＃5th sheet（index = 4）是我们需要删除的数据，我们有
 w_sheet = wb.get_sheet（4）
 
＃填充新数据！ 
 for row_index in range（0，r_sheet.nrows）：
 w_sheet.write（row_index，1，some string that is our data）
 
＃write it！ 
 wb.save（'new.xls'）
  

`

当阅读模板时，格式保持（对于某些颜色稍微改变;呃！），但是公式不是这样的，所以new.xls在整个地方都有空格公式一度站立。

任何想法如何维护 格式和公式？

请注意， '锁定到Python 2.7。

解决方案

你可以使用 openpyxl 为此 - 它只支持xlsx（和相关格式），而不支持xls（旧的Excel格式）。

https://pypi.python.org/pypi/openpyxl/

http://openpyxl.readthedocs.org/en/latest/

https://bitbucket.org/ openpyxl / openpyxl / src / 2.2 / doc / source / index.rst

打开/保存Excel工作表时，此包将保留公式。它也保留格式，但有一些小问题。

其他酷炫功能包括支持条件格式，图表，sparklines等等。

I've run the gamut and can't seem to find what I'm looking for. All threads I found here end up in dead ends for me. xlrd, xlwt, and xlutils almost do what I need, but… The basic idea is that I need to use Python to write simple data (strings) to a particular sheet of an Excel template and write out that template to a new .xls file.

Reading in the template, copying it to a new workbook object, writing in the new values, and writing out the new .xls file is no problem. However, I cannot find a Python Excel module that maintains both formulae and formatting.

`

our = 'template.xls'

# open up read-only template with formatting maintained
rb = open_workbook(our,formatting_info=True)
r_sheet = rb.sheet_by_index(4)

# copy this to a new workbook object
wb = copy(rb)

# 5th sheet (index = 4) is where we need to drop the data we have
w_sheet = wb.get_sheet(4)

# populate with new data!
for row_index in range(0, r_sheet.nrows):
    w_sheet.write(row_index, 1, "some string that is our data")

# write it!
wb.save('new.xls')

`

When reading in the template, the formatting is maintained (slightly altered for some colors; meh!), but the formulae are not, so "new.xls" is has blanks all over the place where formulae once stood.

Any idea how to maintain both formatting and formulae?

Note, I'm locked in to Python 2.7.

解决方案

You can use openpyxl for this - it only supports xlsx (and the related formats), and not xls (the old Excel format).

https://pypi.python.org/pypi/openpyxl/

http://openpyxl.readthedocs.org/en/latest/

https://bitbucket.org/openpyxl/openpyxl/src/2.2/doc/source/index.rst

This package does preserve formulas when you open/save the Excel sheet. Also it does preserve formatting, but there are some minor issues with it. Images from the original Excel file are not preserved, though.

Other cool features include support for conditional formatting, charts, sparklines, and many more.

这篇关于Python Excel模板读取和重写，维护公式和格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！