Apache POI - Theme color for formula based Conditional formatting (XSSFFontFormatting)

Question

I am trying to create conditional formatting based on formula using poi api. Font color is set by reading another (template/reference) cell. Problem is that current api does not honor theme based color for formula based conditions. Am I missing something? Is there any way to make this work?

    public void test(XSSFCellStyle style, String complexFormula){
        ....
        XSSFConditionalFormattingRule rule = (XSSFConditionalFormattingRule )
                      sheetCF.createConditionalFormattingRule(complexFormula);
        XSSFConditionalFormattingRule (rule,style);
    }

    protected void createConditionalFormatingRules(XSSFConditionalFormattingRule rule, XSSFCellStyle style) {

        XSSFFontFormatting fontFmt = (XSSFFontFormatting) rule.createFontFormatting();
        XSSFFont font = style.getFont();

        fontFmt.setFontColorIndex(font.getXSSFColor());  // BROKEN -- this doe not work for theme color

        fontFmt.setFontHeight(font.getFontHeight());
        fontFmt.setUnderlineType(font.getUnderline());
        fontFmt.setFontStyle(font.getItalic(), font.getBold());
        fontFmt.setEscapementType(FontFormatting.SS_NONE);
    }

Above, fontFmt.setFontColorIndex(font.getXSSFColor()); does not work for theme color. It works for standard colors.

poi-ooxml-4.1.0

thanks for looking into my problem !!

Answer 1

There is a bug in XSSFFontFormatting.setFontColor(Color). It sets only RGB value but not Tint value.

I solved it by adding following code to set Tint.

        XSSFColor color = font.getXSSFColor();
        if(color != null) {
            fontFmt.setFontColor(color);
            //Themed color is not handed properly by poi. Hence have to handle it below.
            if(color.isThemed()) {
                CTFont ctFont = (CTFont) getFieldValWithReflection(fontFmt,"_font");
                if(ctFont != null && ctFont.sizeOfColorArray()>0){
                    CTColor c = ctFont.getColorArray(0);
                    if(color.hasTint())
                        c.setTint(font.getXSSFColor().getTint());
                    if(color.isIndexed())
                        c.setIndexed(color.getIndexed());
                }
            }
        }

    /**
     * Helper for the very common case of having to get underlying XML data.
     */
    private Object getFieldValWithReflection(Object owner, String fieldName) {
        Field f = null;
        Object val = null;
        try {
            f = owner.getClass().getDeclaredField(fieldName);
            f.setAccessible(true);

            val = f.get(owner);
            return val;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (f != null) {
                f.setAccessible(false);
            }
        }

        return null;
    }