I'm attempting to create a spreadsheet that has conditionally formatted cells. I'd like to create the sheet so that the rows are alternately shaded (grey, white, grey, white, etc..) and normally in Excel I'd create a conditional format using a formula like =MOD(ROW(), 2)=1. I'm lost in the API on this problem though.
I'm using version 5.0.1.0 of the product.
APIMate isn't helping. Creating a minimal sheet in Excel with this formatting, and then opening in APIMate shows me nothing having to do with this condition.
Conditional formatting is only mentioned in passing in the API Guide.
The API Reference does point me at ConditionallyModifyFormat(TFlxFormat, row, col), but I can't seem to figure out how I relate my formula to the Format.
Do anyone have an example of a conditionally formatted cell, or other documentation that might shed light on this?
Thanks.
Hi,
Sadly adding Cond Fmt with the API isn't supported yet, this is scheduled for next release.
ConditionallyModifyFormat does the opposite, it takes a cond fmt cell and evaluates the conditions and if they are true, it modifies the format accordingly. It is used by the rendering engine to render conditional formats.
Support for adding cfs was almost included around the 4.0 timeframe (if you look there are classes like "conditionalFormatRule" still there that will be used when this is finally implemented). But then Ms released excel 2007 with a completely new file format and incompatible conditional formats, and we decided not to make those method public because they would have to be deprecated when we added 2007 conditional formats.
Well, after that we just spend a lot of time implementing the xlsx support, and at this very moment we are approaching the missing bits of cfs again. Actually cfs should have been in 5.3, but it was a little late for release.
At the moment, solutions are to either use normal formatting if possible (for example in your case, you might format one way odd rows and other even rows). Other solution might be to copy the formats from other place, because even when you can't currently enter them, you can copy them from other place (even from other file) and they will be copied. There is a new "CopyFormats" mode to insertAndCopyRanges() in v5.3 that might help with this.
I wish I could give a better answer, but until we finish this support this is the best I can say.
Regards,
Adrian.
This should at least be noted in the documentation, as there's enough in there to indicate that Conditional Formatting might be supported in pre-2007 sheets. Also, Intellisense finding the references to Conditional Formatting methods is a little frustrating as well.
Fixing this will save someone else the hassle of finding out by asking. :)
you are completely right here, it should be noted in documentation.
It is just an oversight, to be sincere our plans have been from long time ago to add this missing bits (there is very little missing, most of the hard work is done), and well, we didn't added at the beginning because it was coming (and adding something to the docs that is going to change in next version can be very bad if you forget to fix it when you add the feature. Docs say it isn't possible, but it is). But Cf has been delayed a couple of times and I have just forgot about making a note.
But well, the idea is not to put it in docs "you can't do this", but to actually allow you to do it for next version. If for whatever reason it had to be delayed once again (and I really hope it won't) we will mention it in the docs.
Regards,
Adrian.