Coloring cells of a column with custom rgb to create a gradient pattern in the column with each cell having its own rgb using apache poi

Question

I am trying to create a gradient pattern in a column with each cell having its rgb value. The problem that I am facing is that the rgb color is being overwritten in other cells of that column. So the last generated rgb color is being given to all the cells in the column. I tried creating new objects in the loop for each iteration but the overwriting still persists.

public static void giveGradientToColumn(HSSFWorkbook workbook, HSSFSheet sheet, String yemi, Double minimum, Double maximum) throws IOException {

    int columnIndex = 5;
    int maxRows = sheet.getPhysicalNumberOfRows();
    Random rand = new Random();

    int i = maxRows+1;
    for(int rowIndex = maxRows-1 ; rowIndex > 0 ; rowIndex--){

        Row row = CellUtil.getRow(rowIndex, sheet);
        Cell cell = CellUtil.getCell(row, columnIndex);
        String cellContent = cell.toString();
        String percentvalue = cellContent.split("%")[0];
        if(!(percentvalue.equals("NaN")))
        {   
            FileOutputStream fileOut = new FileOutputStream(yemi);
            double value;
            HSSFWorkbook workbook1 = workbook;
            try{
                value = Double.parseDouble(percentvalue);
            }
            catch(Exception e){
                continue;
            }
            double ratio;
            if(maximum!=minimum)
                ratio = 2 * (value-minimum) / (maximum - minimum);
            else
                ratio = 1;

            int b = (max(0, 255*(1 - ratio)));
            int r = (max(0, 255*(ratio - 1)));
            int g = 255 - b - r;

            r = rand.nextInt(255);
            g = rand.nextInt(255);
            b = rand.nextInt(255);

            System.out.println(r+" "+g+" "+b);
            HSSFCellStyle style = workbook1.createCellStyle();
            HSSFPalette palette = workbook1.getCustomPalette();
            HSSFColor myColor = setColor(workbook1, (byte) r, (byte) g, (byte) b);
            short palIndex = myColor.getIndex();
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
            style.setFillForegroundColor(palIndex);

            cell.setCellStyle(style);
            workbook1.write(fileOut);
            fileOut.close();

        }
    }
}




@SuppressWarnings("deprecation")
public static HSSFColor setColor(HSSFWorkbook workbook, byte r,byte g, byte b){
    HSSFPalette palette = workbook.getCustomPalette();
    HSSFColor hssfColor = null;
    try {
        hssfColor= palette.findColor(r, g, b); 
        if (hssfColor == null)
        {
            palette.setColorAtIndex(HSSFColor.GOLD.index, r, g, b);
            hssfColor = palette.getColor(HSSFColor.GOLD.index);
        }
    } catch (Exception e) {
        System.out.println(e);
    }

    return hssfColor;
}

I am printing the rgb values for debugging and the last printed color value is being given to all the cells in the column. (rgb currently is calculated as random.)

Output:- Output Image

Where am I going wrong?

Answer 1

There are multiple issues with your code leading to multiple different problems then. So answer will be little bit lengthy.

First to how a Excel workbook is structured:

A workbook consists of at least one but maybe multiple sheets each having multiple rows each having multiple cells. Each cell may have style. But the style settings are not stored in the cell but in a style sheet (styles table) on workbook level. Thus styles are stored across cells, rows and even sheets. Multiple cells may share the same stored style if they have the same look. Same is for colors. Colors also are stored on workbook level. And unfortunately in *.xls BIFF format colors are limited to indexed colors within a color palette. In BIFF8 there are 49 (index 16 to 64) in user-defined PALETTE record plus some single more. Those 49 can be overwritten but count cannot be increased.

Now to your code:

You are writing out the whole workbook every time you have changed a cell in a row of a sheet. You should not do so. Instead you should writing out the workbook once if you are finished with it.

You are creating a new cell style for every cell you needs putting style on. You should not do so. Excel *.xls is limited to approximately 4,000 different combinations of cell formats. So you need checking whether the style you are needing not is already present in the workbook. This can be very tedious but there is CellUtil, which you have found already and do using in your code already. This provides setCellStylePropertieswhich "attempts to find an existing CellStyle that matches the cell's current style plus styles properties in properties. A new style is created if the workbook does not contain a matching style.".

You are first searching whether a needed color already exists. That's good. But if not, you are overwriting always the same color index of color GOLD. Since colors also are stored on workbook level, only the last overwritten color value will be stored as GOLD. You need overwriting different color indexes if different colors shall be stored in workbook.

Example:

Source Excel:

Code:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

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

import java.util.Map;
import java.util.HashMap;

public class ExcelSetHSSFCellStyleColors {

 static void giveGradientToColumn(HSSFSheet sheet, int columnIndex, double minimum, double maximum) throws Exception {
  DataFormatter formatter = new DataFormatter(java.util.Locale.US);

  short colorIndex = 16; //color indexes 0 to 15 should not be overwritten
  HSSFPalette palette = sheet.getWorkbook().getCustomPalette();

  for (Row row : sheet) {
   Cell cell = CellUtil.getCell(row, columnIndex);
   String cellContent = formatter.formatCellValue(cell);
System.out.println(cellContent);
   String percentValue = cellContent.split("%")[0];
   double value = Double.NaN;
   try {
    value = Double.valueOf(percentValue);
   } catch(Exception e){
    //percentValue was not numeric
   }
   if (!Double.isNaN(value) && value >= minimum && value <= maximum){
    double ratio = (value - minimum) / (maximum - minimum);
    byte r = (byte)Math.round(Math.max(0, 255 * (1 - ratio)));
    byte b = 0;
    byte g = (byte)Math.round(Math.max(0, 255 - (int)b - (int)r));
System.out.println(ratio + " " + String.format("%02X", r) + ":" + String.format("%02X", g) + ":" + String.format("%02X", b));

    HSSFColor hssfColor = palette.findColor(r, g, b); 
    if (hssfColor == null /*&& colorIndex < 64*/) {
     palette.setColorAtIndex(colorIndex, r, g, b);
     hssfColor = palette.getColor(colorIndex);
     colorIndex++;
    }
System.out.println("got color: " + ((hssfColor!=null)?hssfColor.getIndex() + ": " + hssfColor.getHexString():hssfColor)); //if not a index available, hssfColor may be null

    if (hssfColor != null) {
     Map<String, Object> styleproperties = styleproperties = new HashMap<String, Object>();
     styleproperties.put(CellUtil.FILL_FOREGROUND_COLOR, hssfColor.getIndex());
     styleproperties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
     CellUtil.setCellStyleProperties(cell, styleproperties);
    }
   }
  }
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelTest.xls"));

  HSSFSheet sheet = (HSSFSheet)workbook.getSheetAt(0);

  giveGradientToColumn(sheet, 5, 10, 90);

  workbook.write(new FileOutputStream("ExcelTestNew.xls"));
  workbook.close();
 }  
}

Result:

Disclaimer:

Code is tested and works using latest stable version 3.17 of apache poi. Your code had used older version (I know because of used HSSFColor.GOLD.index) .

Btw.:

What I really would suggest is upgrading the Excel file format to the modern *.xlsx Office Open XML file format. Not only that there the color limit has gone, there you could simply using Conditional Formatting with Color Scales which probably fulfills your requirement even better.