CODEWITHSUNDEEP
javaexcelcolorsapache-poi
jompi
jompi

Reputation: 360

Change excel cell color with java

I have to validate cells with a simple formula and if that is not valid, change the cell color like yellow or red, it doesn't matter yet.

So far, I've seen and try this : 

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

 // formula = =SUMA(L13+M13+N13+O13)
    //=SUMA(M4+N4+O4+P4+L:L
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "4");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("L2:L5")};

    sheetCF.addConditionalFormatting(regions, rule2);
}

That changes me the color if the value is four, i know, but

How can I apply the formula of the sum of :

enter image description here

I need to validate in all the rows of column L, the sum of m2+n2+o2+p2 and m3 + n3+ o3+p3 etc...

FYI, I get the values from a db, and the values are like in the picture with decimal but I get them in Java as int like this :

while((j <  fallas) && rset.next() ){
  row = sheet.createRow(j+1);
  row.createCell(0).setCellValue((rset.getBigDecimal(1)).toString());
  row.createCell(1).setCellValue(rset.getString(2));
  row.createCell(2).setCellValue(rset.getString(3));
  row.createCell(3).setCellValue(rset.getString(4));
  row.createCell(4).setCellValue(rset.getString(5));
  row.createCell(5).setCellValue(rset.getString(6));
  row.createCell(6).setCellValue(rset.getString(7));
  row.createCell(7).setCellValue((rset.getBigDecimal(8)).toString());
  row.createCell(8).setCellValue((rset.getBigDecimal(9)).toString());
  row.createCell(9).setCellValue((rset.getBigDecimal(10)).toString());
  row.createCell(10).setCellValue(rset.getString(11));

  //row.createCell(11).setCellFormula("SUMA(L13+M13+N13+O13)");

  row.createCell(11).setCellValue(String.valueOf((rset.getBigDecimal(12)).toString()));
  row.createCell(12).setCellValue(String.valueOf((rset.getBigDecimal(13)).toString()));
  row.createCell(13).setCellValue(String.valueOf((rset.getBigDecimal(14)).toString()));
  row.createCell(14).setCellValue(String.valueOf((rset.getBigDecimal(15)).toString()));
  row.createCell(15).setCellValue(String.valueOf((rset.getBigDecimal(16)).toString()));
  row.createCell(16).setCellValue((rset.getString(17)));
  row.createCell(17).setCellValue((rset.getString(18)));
  row.createCell(18).setCellValue(rset.getString(19));
  row.createCell(19).setCellValue(rset.getString(20));
  row.createCell(20).setCellValue("-");
  row.createCell(21).setCellValue(new java.sql.Date(rset.getDate(22).getTime()).toString());
  row.createCell(22).setCellValue((rset.getBigDecimal(23)).toString());
  row.createCell(23).setCellValue("-");
  row.createCell(24).setCellValue("-");
  row.createCell(25).setCellValue("-");
  row.createCell(26).setCellValue("-");
  row.createCell(27).setCellValue((rset.getBigDecimal(28)).toString());
  row.createCell(28).setCellValue((rset.getBigDecimal(29)).toString());
  row.createCell(29).setCellValue(rset.getString(30));
  row.createCell(30).setCellValue(rset.getString(31));
  row.createCell(31).setCellValue(rset.getString(32));

L, M, N, O, P are createCell(11), 12, 13, 14 etc.

if I just put the formula on the "createConditionalFormattingRule" It doesn't do anything :(, help me please!

Upvotes: 2

Views: 6382

Answers (1)

Automate This
Automate This

Reputation: 31364

What are you validating the sum against?

Assuming the sum of those cells is comparred against some number you can try this:

// Condition 1: Formula Is   =SUM($M2+$N2+$O2+$P2) > 75   
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("SUM($M2+$N2+$O2+$P2)>75");

All together

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

// Condition 1: Formula Is   =SUM($M2+$N2+$O2+$P2) > 75   
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("SUM($M2+$N2+$O2+$P2)>75");

PatternFormatting fill1 = rule1.createPatternFormatting();

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

CellRangeAddress[] regions = {
    CellRangeAddress.valueOf("L2:L5")
};

sheetCF.addConditionalFormatting(regions, rule1);

Here is a link that shows multiple ways to create conditional formatting using java.

Upvotes: 2

Related Questions