How to change only one style property in xssf by keeping all the other properties same

Question

How to change only font color of one cell without altering the workbooks previous style properties. Please look at the attachment "workbook" for clear understanding. The column delta contribution font color should be changed but its background style properties should not be altered.

EDIT: I have changed the code.

The columns rank and mean contribution in the template have a predefined design of some alternate colors which are set in the excel itself. The template is designed by my team and I am afraid I can't change it from Java.

My work is to populate the last column Delta Contribution whose background styles should be same as the total sheet provided the color change according to the conditions.

 String deltaContribution = line.getDeltaContribution() != null
                        ? Double.parseDouble(line.getDeltaContribution()) + "" : "";
                if (!deltaContribution.equals("")) {

                    XSSFCell cell = (XSSFCell) row.getCell(8);
                    XSSFCellStyle style = cell.getCellStyle();
                    XSSFFont redFont = style.getFont();
                    XSSFFont blueFont = style.getFont();
                    XSSFFont greenFont = style.getFont();
                    if(Double.parseDouble(deltaContribution) >= 0.20) {
                        redFont.setColor(IndexedColors.RED.getIndex());
                        CellUtil.setFont(cell, workbook, redFont);
                        //log.info("The colour is " + colour.getARGBHex());
                    }
                    else if(Double.parseDouble(deltaContribution) <= -0.20) {
                        greenFont.setColor(IndexedColors.GREEN.getIndex());
                        CellUtil.setFont(cell, workbook, greenFont);
                        //log.info("The colour is " + colour.getARGBHex());
                    }
                    else {
                        blueFont.setColor(IndexedColors.BLUE.getIndex());
                        CellUtil.setFont(cell, workbook, blueFont);
                        //log.info("The colour is " + colour.getARGBHex());
                    }

                    row.getCell(8).setCellValue(line.getDeltaContribution() != null
                            ? formatDecimalPlaces(line.getDeltaContribution()) : "");
                }

I should not change the previous styles applied to the sheet, I should just edit one property of style. After changing the code, whole column is populated with green color.Last column

workbook:

All your numbers seems to be text strings instead of really numeric values. Is this really necessary? This should be avoided since Excel cannot using such text strings in formula calculation. — Axel Richter, Feb 12 '17 at 09:05
Like in my code example, your `Workbook` needs a `Font` for each color which is independent of the `CellStyle`. This font you can set then using `CellUtil.setFont(cell, redFont)` for example. What you are doing is changing the font which is set for the already applied `CellStyle`. But as you see, this leads to changing the font in all cells which are using this `CellStyle`. — Axel Richter, Feb 13 '17 at 11:18
But do not creating the fonts multiple times inside the loop. Do creating the fonts one time each font outside the loop at workbook level. — Axel Richter, Feb 13 '17 at 11:23

Axel Richter · Answer 1 · 2017-02-12T09:43:32.650

There are two approaches.

First approach is using conditional formatting. This is my preferred approach.

Example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;

class ConditionalFormattingCellValues {

 public static void main(String[] args) throws Exception {

   Workbook wb = new XSSFWorkbook();

   Sheet sheet = wb.createSheet("Sheet1");
   SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

   ConditionalFormattingRule cfRule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GE, "0.20");
   FontFormatting fontFormatting = cfRule2.createFontFormatting();
   fontFormatting.setFontStyle(false, false);
   fontFormatting.setFontColorIndex(IndexedColors.RED.index);

   ConditionalFormattingRule cfRule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "0.20");
   fontFormatting = cfRule1.createFontFormatting();
   fontFormatting.setFontStyle(false, false);
   fontFormatting.setFontColorIndex(IndexedColors.BLUE.index);

   ConditionalFormattingRule [] cfRules = {cfRule1, cfRule2};

   CellRangeAddress[] regions = {CellRangeAddress.valueOf("I2:I10")};

   sheetCF.addConditionalFormatting(regions, cfRules);

   for (int r = 1; r < 10; r++) {
    Row row = sheet.createRow(r);
    Cell cell = row.createCell(8);
    cell.setCellValue(1d/Math.sqrt(r)-0.2);
   }

   FileOutputStream fileOut = new FileOutputStream("ConditionalFormattingCellValues.xlsx");
   wb.write(fileOut);
   wb.close();

 }
}

Second approach is using CellUtil. This provides "Various utility functions that make working with a cells and rows easier. The various methods that deal with style's allow you to create your CellStyles as you need them. When you apply a style change to a cell, the code will attempt to see if a style already exists that meets your needs. If not, then it will create a new style. This is to prevent creating too many styles. there is an upper limit in Excel on the number of styles that can be supported."

Example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellUtil;

import java.io.FileOutputStream;

class DirectlyFormattingCellValues {

 public static void main(String[] args) throws Exception {

   Workbook wb = new XSSFWorkbook();

   Font redFont = wb.createFont();
   redFont.setColor(IndexedColors.RED.getIndex());

   Font blueFont = wb.createFont();
   blueFont.setColor(IndexedColors.BLUE.getIndex());

   Sheet sheet = wb.createSheet("Sheet1");

   for (int r = 1; r < 10; r++) {
    Row row = sheet.createRow(r);
    Cell cell = row.createCell(8);
    String deltaContribution = String.valueOf(1d/Math.sqrt(r)-0.2);

    if(Double.parseDouble(deltaContribution)>=0.20) {
     CellUtil.setFont(cell, redFont);
    } else {
     CellUtil.setFont(cell, blueFont);
    }

    cell.setCellValue(Double.valueOf(deltaContribution));
   }

   FileOutputStream fileOut = new FileOutputStream("DirectlyFormattingCellValues.xlsx");
   wb.write(fileOut);
   wb.close();

 }
}

As said already, using conditional formatting should be preferred.

But according your screen-shot all your numbers seems to be text strings instead of really numeric values. This should be avoided since Excel cannot using such text strings in formula calculation. And without changing that, only second approach will be usable since conditional formatting also needs really numeric values for comparison.

Thanks alot for taking your time. May be my question was not clear enough earlier, I have updated the requirement. I request you to please have a look. — Bhavana Manchana, Feb 13 '17 at 10:35
@bhavana manchana: Like in my code example, your `Workbook` needs a `Font` for each color which is independent of the `CellStyle`. This font you can set then using `CellUtil.setFont(cell, redFont)` for example. What you are doing is changing the font which is set for the already applied `CellStyle`. But as you see, this leads to changing the font in all cells which are using this `CellStyle`. — Axel Richter, Feb 13 '17 at 11:18
But do not creating the fonts multiple times inside the loop. Do creating the fonts one time each font outside the loop at workbook level. — Axel Richter, Feb 13 '17 at 11:24

How to change only one style property in xssf by keeping all the other properties same

1 Answers1