使用python向现有的excel文件添加图标集

I'm trying to add some icon sets to an existing excel file using python. The excel file is written using xlsxwriter. As xlsxwriter does not support icon sets, I close the file, reopen it with openpyxl, add the icon sets and save it again. Problem is, that I loose all conditional formatting added previously. Opening the file in openpyxl with "keep_vba=True" results in a non-readable xlsx-File.

我正在尝试使用python向现有的excel文件添加一些图标集。excel文件是使用xlsxwriter编写的。由于xlsxwriter不支持图标集，我关闭文件，使用openpyxl重新打开它，添加图标集并再次保存它。问题是，我丢失了先前添加的所有条件格式。用“keep_vba=True”在openpyxl中打开文件，结果是一个不可读的xlsx文件。

Any ideas how to achieve this?

有什么想法可以实现吗?

Thanks in advance!

提前谢谢!

P.S.: Missed some details. Sorry for that. I write xlsx files in both cases (xlsxwriter and openpyxl) and use python 2.7 and the latest versions of openpyxl and xlsxwriter on a windows machine with excel 2013. Icon sets are little symbols like arrows (up, down) which can be used in conditional formatting.

注::错过了一些细节。对不起。我在这两种情况下(xlsxwriter和openpyxl)编写xlsx文件，并使用python 2.7和最新版本的openpyxl和xlsxwriter，在windows机器上安装excel 2013。图标集是像箭头(向上，向下)这样的小符号，可以在条件格式中使用。

1 个解决方案

#1

OpenPyXl has a support for conditional formatting and Icon Sets.

OpenPyXl支持条件格式和图标集。

See the official documentation: Conditional Formatting > IconSet

参见官方文档:条件格式>图标集

Here is an example:

这是一个例子:

>>> from openpyxl.formatting.rule import IconSet, FormatObject
>>> first = FormatObject(type='percent', val=0)
>>> second = FormatObject(type='percent', val=33)
>>> third = FormatObject(type='percent', val=67)
>>> iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, percent=None, reverse=None)
>>> # assign the icon set to a rule
>>> from openpyxl.formatting.rule import Rule
>>> rule = Rule(type='iconSet', iconSet=iconset)

#1