Conditional Formatting

[cpi...@gmail.com]

Hello,

I would like to use conditional formatting.

for instance:

   psudeo code:

    if cell value < A$10   color  blue

    if cell value between A$11 and A$10 color orange

    if cell value >= A$11 color yellow 

I manually entered those rules into my xlsx but I'd like openpyxl to be able to enter them in for me.

Part two of the question, if the above isn't possible with openpyxl then how can I write to an existing xlsx with openpyxl and preserve the conditional formatting and all other formatting that is already there? When I write to an existing xlsx all the formatting gets remove and there is also weird behavior were my border styles will continue to write for 80 cells down the page (just continues border styles on empty cells 80 cells down the y axis, this only happens if I update an xlsx that wasn't created with openpyxl).

Thanks

[Charlie Clark]

Hiya,

Am 02.11.2013, 04:06 Uhr, schrieb <cpi...@gmail.com>:

> Hello,
> I would like to use conditional formatting.
> for instance:
> psudeo code:
> if cell value < A$10 color blue
> if cell value between A$11 and A$10 color orange
> if cell value >= A$11 color yellow
> I manually entered those rules into my xlsx but I'd like openpyxl to be
> able to enter them in for me.

This isn't possible in quite that way but you should be able to use ranges.

> Part two of the question, if the above isn't possible with openpyxl then
> how can I write to an existing xlsx with openpyxl and preserve the
> conditional formatting and all other formatting that is already there?

I'm not sure if existing files can have "conditional formatting".

> When
> I write to an existing xlsx all the formatting gets remove and there is
> also weird behavior were my border styles will continue to write for 80
> cells down the page (just continues border styles on empty cells 80 cells
> down the y axis, this only happens if I update an xlsx that wasn't
> created
> with openpyxl).

Adam Morris recently did a lot of work improving the handling of styles
and this has now been incorporated in trunk and will be in the forthcoming
1.7 release.

If you still have problems then please submit a bug report with a sample
file.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[cpi...@gmail.com]

Hi Charlie,

Thanks for the quick reply.

> This isn't possible in quite that way but you should be able to use ranges. 

I looked online for docs and other posts by people and started looking in the source code for how I can do this. I'm not sure how to go about doing this. This isn't referring to 

checking in my python code if the cell value is < A$10 and then changing the style to the color desired color is it? I don't think it is but I want to

make sure because I'd like to have the user be able to see the colors change if he edits the excel file.

> I'm not sure if existing files can have "conditional formatting". 

In my excel file I wrote those rules by doing "Conditional Formatting -> Create new rule" in MS Excel. I think I may be misunderstanding what

your saying, sorry in advance.

> Adam Morris recently did a lot of work improving the handling of styles   

> and this has now been incorporated in trunk and will be in the forthcoming   

> 1.7 release. 

Nice, thanks. I'm glad to hear about this.

Thanks.

[Charlie Clark]

Am 02.11.2013, 19:54 Uhr, schrieb <cpi...@gmail.com>:

> Hi Charlie,
> Thanks for the quick reply.
>
>> This isn't possible in quite that way but you should be able to use
> ranges.

> I looked online for docs and other posts by people and started looking in
> the source code for how I can do this. I'm not sure how to go about doing
> this. This isn't referring to
> checking in my python code if the cell value is < A$10 and then changing
> the style to the color desired color is it? I don't think it is but I
> want
> to
> make sure because I'd like to have the user be able to see the colors
> change if he edits the excel file.

I really am not sure what you're trying to. Are you writing a kind of GUI
program for editing Excel files?

The important thing is that in Excel cells are atomic so that changes must
be applied to individual cells.

>> I'm not sure if existing files can have "conditional formatting".
> In my excel file I wrote those rules by doing "Conditional Formatting ->
> Create new rule" in MS Excel. I think I may be misunderstanding what
> your saying, sorry in advance.

Is this something in Excel? If so, you won't find it in openpyxl. openpyxl
is concerned with reading and writing the Excel file format and not with
how Excel functions.

[cpi...@gmail.com]

>I really am not sure what you're trying to. Are you writing a kind of GUI   
>program for editing Excel files?

This is the basic idea of what I'm doing: http://www.bestrank.com/files/uploads/39/image/several-conditional-formulas-conditional-formatting-excel.png

That image isn't mine but it has the basic idea where once the rules are set if you change a cell then the color will change according to the rules.

>Is this something in Excel?

Yes, here is a good description http://spreadsheets.about.com/od/excelformatting/qt/conditn_format.htm

> If so, you won't find it in openpyxl. openpyxl   
>is concerned with reading and writing the Excel file format and not with   
>how Excel functions. 

Okay, thanks. I was afraid of that. I assume then you can't preserve existing conditional formatting rules of a spreadsheet that you append to with openpyxl?

Thanks

[Charlie Clark]

Am 04.11.2013, 20:38 Uhr, schrieb <cpi...@gmail.com>:

> This is the basic idea of what I'm
> doing:
> http://www.bestrank.com/files/uploads/39/image/several-conditional-formulas-conditional-formatting-excel.png
> That image isn't mine but it has the basic idea where once the rules are
> set if you change a cell then the color will change according to the
> rules.

That looks like they are set by formulae & probably some visual basic
(shudder).

>> Is this something in Excel?
> Yes, here is a good
> description
> http://spreadsheets.about.com/od/excelformatting/qt/conditn_format.htm

>> If so, you won't find it in openpyxl. openpyxl
>> is concerned with reading and writing the Excel file format and not with
>> how Excel functions.

> Okay, thanks. I was afraid of that. I assume then you can't preserve
> existing conditional formatting rules of a spreadsheet that you append to
> with openpyxl?

Not sure. I merged John Bovey's support for VBA recently, and, together
with my support for preserving formulae, it might work. Check out from the
1.8 branch and let us know how you get on.

We're not there yet but I hope that we might one day at least preserve
Excel files. At the moment pretty much anything that openpyxl doesn't know
how to import just gets ignored.

[Adam Morris]

As Charlie mentioned, conditional formatting isn't supported yet by openpyxl.

However, it's neither VBA or a formula - it's a mechanism of it's own, and one that appears very simple.  I've always just added it manually after generating a report, but I'll take a stab at this, as I use it in one of my reports as well...

I'll take a stab at it.

http://msdn.microsoft.com/en-us/library/gg278326(v=office.14).aspx

http://stackoverflow.com/questions/16225694/how-to-add-conditional-formatting-to-an-xml-excel-file

--Adam

[Adam Morris]

Hi - I added in preliminary support for conditional formatting (both read / write).  Still need to add tests, and make sure all types of conditional formatting are handled, but should be functional.  If you have time, would you be able to test, to see if this works sensibly?

You can do this by installing from the branch @  https://bitbucket.org/amorris/openpyxl

Note, this isn't finalized in any way, and is subject to change, but you'd use it like:

from openpyxl import Workbook

from openpyxl.style import Color

wb = Workbook()

ws = wb.get_active_sheet()

ws.conditional_formatting.add2ColorScale('A1:A10', 'min', None, 'FFAA0000', 'max', None, 'FF00AA00')

ws.conditional_formatting.add3ColorScale('B1:B10', 'percentile', 10, 'FFAA0000', 'percentile', 50, 'FF0000AA', 'percentile', 90, 'FF00AA00')

ws.conditional_formatting.addCustomRule('C1:C10', {'type': 'colorScale', 'colorScale': {'cfvo': [{'type': 'min'}, {'type': 'max'}], 'color': [Color('FFFF7128'), Color('FFFFEF9C')]}})

--Adam

[cpi...@gmail.com]

Wow, thanks Adam for doing that. Your awesome!

Okay, I installed from the branch that you gave. I tried running it with my existing code before making any changes using conditional formatting (just to make sure existing functionality still worked). I think there is potentially a bug with self._style_properties, here are my details and what I'm seeing:

The code I have written reads, writes, and updates xlsx files with openpyxl-1.6.2. When rerunning with the provided branch I get the following stack trace:

in style on line 268 of:

def _write_dxfs(self):

        if 'dxf_list' in self._style_properties

self._style_properties is of NoneType: None

Traceback (most recent call last):

  File "/Users/me/eclipse/plugins/org.python.pydev_2.8.2.2013090511/pysrc/pydevd.py", line 1446, in <module>

    debugger.run(setup['file'], None, None)

  File "/Users/me/eclipse/plugins/org.python.pydev_2.8.2.2013090511/pysrc/pydevd.py", line 1092, in run

    pydev_imports.execfile(file, globals, locals) #execute the script

  File "/Users/me/git/metrics/metrics.py", line 226, in <module>

    db.create()

  File "/Users/me/git/metrics/metrics_db.py", line 52, in create

    self._create_xlsx()

  File "/Users/me/git/metrics/metrics_db.py", line 165, in _create_xlsx

    wb.save(self.xls_file)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/workbook.py", line 233, in save

    save_workbook(self, filename)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/writer/excel.py", line 159, in save_workbook

    writer.save(filename)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/writer/excel.py", line 142, in save

    self.write_data(archive)

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/writer/excel.py", line 72, in write_data

    archive.writestr(ARC_STYLE, self.style_writer.write_table())

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/writer/styles.py", line 65, in write_table

    self._write_dxfs()

  File "/Library/Python/2.7/site-packages/openpyxl-1.8.0-py2.7.egg/openpyxl/writer/styles.py", line 268, in _write_dxfs

    if 'dxf_list' in self._style_properties:

TypeError: argument of type 'NoneType' is not iterable

Thanks again for the quick turn around.

[Adam Morris]

You're welcome, and thanks for the testing - fixed the above error, and added some tests.

Should be getting closer!

--Adam

[cpi...@gmail.com]

Awesome, tested the custom rule. works here. doing more testing... 

What would it look like to implement the following rules with your api (ignore applies to section since it will be the worksheet i'm working with anyway): https://dl.dropboxusercontent.com/u/42751013/color_formatting.png

Thanks

[Adam Morris]

Added another method to make the cell comparisons easier to add.  I didn't add one for the check if the cell is blank, but added an example on adding a custom rule with this.  Let me know if this sort of method makes sense - 

from openpyxl import Workbook

from openpyxl.style import Color, Fill

wb = Workbook()

ws = wb.get_active_sheet()

ws.title = 'Data Collection Tab'

ws.cell('U6').value = 0.5

ws.cell('U7').value = 5

ws.cell('U10').value = 1

ws.cell('U11').value = 2

ws.cell('U12').value = 3

ws.cell('U13').value = 4

ws.cell('U14').value = 5

ws.cell('U15').value = 6

ws.cell('U16').value = 7

ws.cell('U17').value = 8

ws.cell('U18').value = 9

ws.cell('U19').value = 10

redFill = Fill()

redFill.start_color.index = 'FFEE1111'

redFill.end_color.index = 'FFEE1111'

redFill.fill_type = Fill.FILL_SOLID

yellowFill = Fill()

yellowFill.start_color.index = 'FFEEEE11'

yellowFill.end_color.index = 'FFEEEE11'

yellowFill.fill_type = Fill.FILL_SOLID

greenFill = Fill()

greenFill.start_color.index = 'FF11AA11'

greenFill.end_color.index = 'FF11AA11'

greenFill.fill_type = Fill.FILL_SOLID

dxfId = ws.conditional_formatting.addDxfStyle(wb, None, None, None)

ws.conditional_formatting.addCustomRule('U10:U18',  {'type': 'expression', 'formula': ['ISBLANK(U10)'], 'stopIfTrue': '1'})

ws.conditional_formatting.addCellIs('U10:U18', 'lessThan', ['U$7-U$6*U$7'], True, wb, None, None, greenFill)

ws.conditional_formatting.addCellIs('U10:U18', 'lessThan', ['U$7'], True, wb, None, None, yellowFill)

ws.conditional_formatting.addCellIs('U10:U18', 'greaterThanOrEqual', ['U$7'], True, wb, None, None, redFill)

wb.save('...')

[Adam Morris]

Correction - I missed the dxfId - for checking if the cell is blank, it should be:

dxfId = ws.conditional_formatting.addDxfStyle(wb, None, None, None)

ws.conditional_formatting.addCustomRule('U10:U18',  {'type': 'expression', 'dxfId': dxfId, 'formula': ['ISBLANK(U10)'], 'stopIfTrue': '1'})