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.

So, you converted your xls file to xlsx, right? What do you call "icon sets", can you give us an example? — Laurent LAPORTE
– Laurent LAPORTE, Commented Dec 20, 2016 at 10:31
I only work with xlsx files. No xls involved. Icon sets are e.g. the traffic lights you can add to a cell: link — littleHue
– littleHue, Commented Dec 20, 2016 at 11:16

Laurent LAPORTE · Accepted Answer · 2016-12-20 11:39:09Z

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)

answered Dec 20, 2016 at 11:39

Laurent LAPORTE

23.2k7 gold badges64 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

littleHue Over a year ago

Sorry, I think I didn't describe the problem well: The conditional formatting added with openpyxl works just fine and I see the icon sets. But all other conditional formats which were in the original file are lost.

Laurent LAPORTE Over a year ago

It sounds like a bug in OpenPyXl :-(

littleHue Over a year ago

I've been afraid of that. Thanks for your help and time!

Charlie Clark Over a year ago

Why should it be a bug in openpyxl? Could be something else. Anyway. I wouldn't try and mix the two libraries like this. Probably makes more sense to create the file in openpyxl.

Collectives™ on Stack Overflow

Add icon sets to an existing excel file with python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related