Add icon sets to an existing excel file with python

Question

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.

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.

Answer 1

OpenPyXl has a support for conditional formatting and Icon Sets.

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)