Vaadin Flow Spreadsheet conditional formatting doesn't apply colors to a cell style

[AndriiRevaEpicor]

Description

When we try to use Conditional Formatting spreadsheet feature we faced with an issue that color does not applies on the cell,

We try to use #,##0_);[Red](#,##0) string value as dataFormat which can be described as: when value less than zero then will be applied red color and parenthesis to the value. Parenthesis is rendered as expected but color is missing.

If we try to export this spreadsheet as excel file and open it with Excel app then we can see that color is applied as should be.

So we assume that issue on the Spreadsheet lib.

Also we attempted to use SheetConditionalFormatting but the same result in the end.

Added link to a demo project which help to reproduce this issue.

Expected outcome

I'd like to expect that Conditional Formatting has color feature.

Minimal reproducible example

Example related to dataFormat as string value:

    Spreadsheet spreadsheet = new Spreadsheet();
    spreadsheet.setSizeFull();

    CellStyle cellStyle = spreadsheet.getWorkbook().createCellStyle();
    org.apache.poi.ss.usermodel.DataFormat dataFormat = spreadsheet.getWorkbook().createDataFormat();
    cellStyle.setDataFormat(dataFormat.getFormat("#,##0_);[Red](#,##0)"));

    Cell cell = spreadsheet.createCell(0, 0, "-1");
    cell.setCellStyle(cellStyle);

    add(spreadsheet);

Another one, about SheetConditionalFormatting approach:

    Spreadsheet spreadsheet = new Spreadsheet();
    spreadsheet.setSizeFull();

    add(spreadsheet);

    spreadsheet.createCell(0, 0, "-1");

    SheetConditionalFormatting sheetCF = spreadsheet.getActiveSheet().getSheetConditionalFormatting();
    ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "0");

    FontFormatting fontFmt = rule.createFontFormatting();
    fontFmt.setFontStyle(true, true);
    fontFmt.setFontColorIndex(IndexedColors.RED.index);

    ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};

    CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:D10")};
    sheetCF.addConditionalFormatting(regions, cfRules);

Steps to reproduce

1. Run application;
2. Proceed to /dataformatexample endpoint and make sure that spreadsheet is opened;
3. Enter number value which should be less than zero to A1 cell to make sure that cell style is applied.
4. Figure out that color is not applied.

The same steps for the /sheetconditionalformattingexample endpoint.

Environment

Vaadin version(s): 24.5.4
OS: Windows 11

Browsers

Issue is not browser related

[TatuLund]

Related to vaadin/docs#3905

[sissbruecker]

In order to make conditional formatting work, it seems there are two things missing from the provided example.

First, in order to use a numeric comparison (less than) for the formatting rule, the cell value needs to be explicitly set as a number as well, for example with:

cell.setCellValue(-1);

There is an explicit check in the formatting implementation that checks if the type of the cell works with the comparison of the rule. That doesn't work with spreadsheet.createCell(0, 0, "-1"), which creates a cell of type text, whereas the comparison requires a cell of type number.

The second issue is that the CSS needs to be explicitly regenerated after changing formatting rules, with:

spreadsheet.getConditionalFormatter().createConditionalFormatterRules();

Apache POI instances are not observable, so Spreadsheet can not know whether the formatting has changed, so this needs to be triggered manually. This is similar to just setting a cell style, where a manual refresh is also necessary to trigger an update.

---

The one issue I could find is that font colors don't work. Using setFontColorIndex stores a color on the font rule, but the code that generates CSS rules doesn't properly retrieve the color. Font style, border style and other formatting features seem to work.

[sissbruecker]

It doesn't look like conditional formatting through data formats is supported by the component, so using the POI conditional formatting API is the way to go.

I've opened a PR (#6824) for fixing the issue with setting colors, which would then resolve this issue.