Conditional range/cell formatting with FlexCel API

Hi Support,


Please could you advise how to apply conditional formatting to a range, via the FlexCel API? 

I would like to set the background colour to yellow, if the cell's value is greater than 0.


Kind Regards,

Keith Blows

Hi,

Sadly this isn't supported yet, it should become possible in the next version, which should be coming soon.
Hi,

Thank you- looking forward to the next release...!

I am really looking forward to it too :)   There is a lot of new stuff coming, but we can't release it until everything is finished. We are also delayed on it, but I hope it will be finished soon.

Hi, can you tell me if this feature was ever released?  I basically just want to use conditional formatting to color every other row to make my excel output visually appealing, but I have not figured out a way to do this with the version of flexcel I currently have (not the latest version).  Please let me know if this is possible in the latest version.

Hi,

Not yet, this was pushed down because the Android/iOS support in .NET and Delphi, which have consumed most of this year. We are just getting back on track, with a release hopefully for this week addressing other issues. Conditional formatting will be coming after this, if no other "stop the world, forget everything and focus in this" new feature appears in the meantime (and I really hope that it doesn't, I am really tired of the constants rewrites we are having to do in the Excle file format, in .NET (now with WinRT completely different again), and everywhere).

Now, the real thing about this missing support is that Excel changed completely the way CF works in xlsx. We were in the middle of adding API support for xls when xlsx was released, and suddenly we found we had to rewrite everything because xlsx had nothing in common with xls. At the time, we decided not to publish the API because it would have to be changed once we added support for xlsx CFs. After that, we've implemented most of xlsx spec (6000 pages of docs...) but there are 2 things still missing: CF and chart rendering.

So the "no support for CF" isn't 100% true. The first thing to know is if you use xls or xlsx. If it is xlsx, then sadly you'll have to wait until we release the support. But, if it is xls, you can probably get by even without the API to add them, since FlexCel fully understand them (and it will even preview and export them to pdf/html).

The workaround is: (remember, only for xls files, not xlsx)
1)Even if using the API, you can start from a "template" and not an empty document. Format the template as you want, add the conditional formatting to it with Excel, and then open that file with Xls.Open() instead of using Xls.NewFile()

2) xls.InsertAndCopyRange(..) the row with conditional formatting down to the number of records you want to add. As said, FlexCel actually fully understands CFs and will copy them correctly and adapting the formulas as expected.

3)Fill the cells as usual.

If this doesn't work, the other alternative is to manually format rows every other row using xls.SetRowFormat. While it won't "autoadapt" as CF would if you insert a row, for most cases it might be the best solution anyway. Normal formatting is  simpler and understood by every other spreadsheet app out there. Please note that I don't say this as an excuse, CF api support is one of the biggest things we miss. But even when we support it, for something like coloring even-odd rows differently, you might consider using simple row formatting.