CODEWITHSUNDEEP
javaapache-poicomparison-operators
rivax
rivax

Reputation: 51

How compare strings in ConditionalFormatting?

The following code works perfectly and background changes when you change the data:

constraint = validationHelper.createExplicitListConstraint(new String[]{"10","11"});

XSSFSheetConditionalFormatting my_cond_format_layer = sheet.getSheetConditionalFormatting();    
XSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL,"10");

PatternFormatting fill1 = my_rule.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("C1:C90")};
my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule);

XSSFSheetConditionalFormatting my_cond_format_layer2 = sheet.getSheetConditionalFormatting();
XSSFConditionalFormattingRule my_rule2 = my_cond_format_layer2.createConditionalFormattingRule(ComparisonOperator.EQUAL,"11");

PatternFormatting fill12 = my_rule2.createPatternFormatting();
fill12.setFillBackgroundColor(IndexedColors.RED.index);
fill12.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] my_data_range2 = {CellRangeAddress.valueOf("C1:C90")};     
my_cond_format_layer.addConditionalFormatting(my_data_range2,my_rule2);

While the following doesn't work and background doesn't change: 

constraint = validationHelper.createExplicitListConstraint(new String[]{"OK","ERROR"});

XSSFSheetConditionalFormatting my_cond_format_layer = sheet.getSheetConditionalFormatting();         
XSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL,"OK");

PatternFormatting fill1 = my_rule.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("C1:C90")};     
my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule);

XSSFSheetConditionalFormatting my_cond_format_layer2 = sheet.getSheetConditionalFormatting();
XSSFConditionalFormattingRule my_rule2 = my_cond_format_layer2.createConditionalFormattingRule(ComparisonOperator.EQUAL,"ERROR");

PatternFormatting fill12 = my_rule2.createPatternFormatting();
fill12.setFillBackgroundColor(IndexedColors.RED.index);
fill12.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] my_data_range2 = {CellRangeAddress.valueOf("C1:C90")};  
my_cond_format_layer.addConditionalFormatting(my_data_range2,my_rule2);

Can you please advise what's the problem here? I went through the documentation and couldn't find a problem.

EDIT:

Finally i discover the problem, when I open the excel and i check the conditional format I see that POI or Excel is inserting an equal after the string ok or error, something like this =ok and =error. If I manually in the excel remove the equal it works perfectly and the cell background change when the data validation list change.

Now the new question is, how can I remove the equal from the code? or what's the right way to compare strings in Apache POI with conditional formatting?

Upvotes: 1

Views: 2656

Answers (3)

Alex
Alex

Reputation: 11

I have found a solution which suits me well:

ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule( ComparisonOperator.EQUAL, "\"" + compareItem + "\"" );

Note that you have to wrap compareItem (the string to be compared with) with additional double quot marks

Upvotes: 1

Marios
Marios

Reputation: 1957

I did not manage to make it work like this either. But I succeeded in using a formula instead. Replace the code where you create the "my_rule" variable with the following one:

XSSFConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule("C1=\"OK\"");

The actual formula is C1="OK" so I had to escape the quotes when creating the formula String. Note that I used "C1" cell which is the first cell of the range that you specify (C1:C90). When applying the range, the formula will be adapted properly for each cell automatically so the above should work for all cells in the range.

You can do the same with the "ERROR" string.

Upvotes: 2

rivax
rivax

Reputation: 51

I found a solution. it consists on put the text in a cell, one cell for ok and another for error, i put the font in white so it doesnt appear, and finally i compare both cells with the cell that i want to change the background, its the only way that i found to make it works with string comparative. the code is: 

    constraint=validationHelper.createExplicitListConstraint(new String[]{"Ok","Error","Sin revisar"});
    dataValidation = validationHelper.createValidation(constraint, addressList);
    dataValidation.setSuppressDropDownArrow(true);


//ERROR
    XSSFConditionalFormattingRule regla2 = condicion2.createConditionalFormattingRule(ComparisonOperator.EQUAL,"$C$"+2,"$C$"+7);

    PatternFormatting fill12 = regla2.createPatternFormatting();
    fill12.setFillBackgroundColor(IndexedColors.RED.index);
    fill12.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] rango2 = {CellRangeAddress.valueOf("C3:C90")};


//OK
XSSFConditionalFormattingRule regla = condicion.createConditionalFormattingRule(ComparisonOperator.EQUAL,"$C$"+1,"$C$"+7);     

    PatternFormatting fill1 = regla.createPatternFormatting();

fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] rango = {CellRangeAddress.valueOf("C3:C90")};

Upvotes: 0

Related Questions