Conditional formatting

[Misiu]

I'd like to ask for adding support for conditional formatting. Use case: I'm generating a report that contains a couple of columns, in the last columns I need to calculate the average from previous ones. If it is less than 1 I'd like to set the background to red, if it is more than 4.9 I want the background to be green. I know I can calculate the value per row in my app, then put the value as the average column and apply styleId. Still, ideally, I'd like to use a formula to calculate the value (this can be done right now) and apply a conditional style to that cell, so when someone updates the values in the generated Excel file the average and the color will update.

Ref: https://stackoverflow.com/a/25724262/965722

[sveinungf]

Yes that would be a nice feature to have. There is a lot of possible options to cover for conditional formatting. Initially the feature would most likely not be implemented with all possible options, but your use case could be a good starting point.

[Misiu]

@sveinungf I'd like to help with contributing, but there must be a starting point. I've searched for API inspiration in existing libraries, for example: https://help.syncfusion.com/flutter/xlsio/working-with-conditional-formatting or https://docs.devexpress.com/WindowsForms/16190/controls-and-libraries/spreadsheet/data-presentation/conditional-formatting but not sure if similar approach would work with your library A simple conditional formatting would be a good start.

[sveinungf]

Great! Any help is appreciated.

I don't know all the ins and outs regarding conditional formatting in Excel, so I don't have an API proposal ready for it. Perhaps there could be some similarity to the way data validations work. In any case, even though we could start of with a small subset of the feature, we should have some idea of what would come later, so that the remaining parts can be added without requiring breaking changes.

We will also need to understand how conditional formatting is implemented in an Excel file. That will most likely also have a say in how the API should be. Reading the Open XML specification can be a bit daunting, so what I typically do is reverse engineering instead. I would use the feature in Excel, save it to a file, and then unzip the file to see how the generated XML will look like.