Issues with conditional formatting in a table

Feb 17, 2010 at 7:27 PM

I recently stumbled across this project looking for an easier solution for reporting than I am currently using.  So far it's been a complete joy to work with as dealing with tables in Excel is much easier than dealing with the previous reporting tools.

That being said.  I'm having an issue applying conditional formatting to cells inside of a table in an Excel template.  For example, I have pricing information inside the table which includes the current price as well as the new price.  I want to change the font and/or background color of the cells when the new price is less than the current price.  I understand I can't do this using ExtremeML from code without a lot of work, so I was attempting to do the formatting inside of the template itself using Excel.  However, every time I use the "fill" function to fill the table with data, Excel then complains that the file that is produced has errors in it and refuses to open it.  If I remove the conditional formatting, everything works just like it should.  Also, if I open the file in Excel and manually enter data into the table, it works just fine with the conditional formatting.

Is there something I'm missing here?  Or does ExtremeML currently not support conditional formatting in tables inside of the Excel template file?

Coordinator
Feb 18, 2010 at 8:46 AM

Applying formatting (and defining all other static content) in the template is definitely the recommended approach. Using this technique allows ExtremeML to exploit lots of Excel features that "just work".

However, from what you have described, it sounds like you have discovered a new limitation in the current version of ExtremeML. I haven't personally studied the OpenXML implementation of conditional formatting, but it would seem that it requires an active approach - in other words, ExtremeML needs to test for the existence of conditional formatting and do something specific to propagate in when a table is filled.

I will take a look at the implications of adding this feature and post a follow-up response here when I have more information. In the meantime, I apologize for the inconvenience, but I can't suggest any workaround solution.

 

Feb 19, 2010 at 3:59 PM

I figured out a workaround by adding a column that holds a calculated field.  Conditional formatting is not a huge issue since it's mainly for aesthetic purposes anyways.

I'll be on the lookout for new versions.

Thanks!

Coordinator
Feb 20, 2010 at 8:47 AM

Update:

After a little experimentation, I've realized that ExtremeML's method of applying formatting is somewhat ill-conceived (as a number of other users have also told me :)).

The problem is that, when inserting rows into a table, ExtremeML currently only propagates formatting that is applied to the entire worksheet column, whereas, it should ideally copy the formatting that is present in the upper displaced row (as Excel does). This limitation means that it is not currently possible to apply individual cell formatting within a template table, which is obviously essential for conditional formatting.

I am now looking again at the implications of fully emulating what Excel does, with the hope that this will also provide a robust solution to the specific problem you have reported.

 

Coordinator
Feb 22, 2010 at 2:55 PM

Update:

ExtremeML has now been updated to fully emulate Excel's method of propagating cell styles within a table. This means that styling should be applied to the cells of the empty row in a template table, and these styles will be automatically copied to newly inserted rows.

Similarly, support for conditional formatting has now been added. A tutorial showing its usage may be found here.