POI setting Cell Background to a Custom Color

Question

I want to set custom color to a cell's background.
I use HSSFWorkbook (can't use anything else).

HSSFPalette palette = aWorkBook.getCustomPalette();              Color col = new Color(backgroundColor);                      HSSFColor myColor  = palette.addColor((byte) 10, (byte) 11, (byte) 12);  

I get this error: java.lang.RuntimeException: Could not find free color index

Answer 1

You get this error because pallete is full. What you need to do is override preset color. Here is an example of function I'm using:

public 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.LAVENDER.index, r, g,b);             hssfColor = palette.getColor(HSSFColor.LAVENDER.index);         }     } catch (Exception e) {         logger.error(e);     }      return hssfColor; } 

And later use it for background color:

HSSFColor lightGray =  setColor(workbook,(byte) 0xE0, (byte)0xE0,(byte) 0xE0); style2.setFillForegroundColor(lightGray.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); 

Answer 2

See http://poi.apache.org/spreadsheet/quick-guide.html#CustomColors.

Custom colors

HSSF:

HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFRow row = sheet.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("Default Palette");  //apply some colors from the standard palette, // as in the previous examples. //we'll use red text on a lime background  HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font);  cell.setCellStyle(style);  //save with the default palette FileOutputStream out = new FileOutputStream("default_palette.xls"); wb.write(out); out.close();  //now, let's replace RED and LIME in the palette // with a more attractive combination // (lovingly borrowed from freebsd.org)  cell.setCellValue("Modified Palette");  //creating a custom palette for the workbook HSSFPalette palette = wb.getCustomPalette();  //replacing the standard red with freebsd.org red palette.setColorAtIndex(HSSFColor.RED.index,         (byte) 153,  //RGB red (0-255)         (byte) 0,    //RGB green         (byte) 0     //RGB blue ); //replacing lime with freebsd.org gold palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);  //save with the modified palette // note that wherever we have previously used RED or LIME, the // new colors magically appear out = new FileOutputStream("modified_palette.xls"); wb.write(out); out.close(); 

XSSF:

XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell( 0); cell.setCellValue("custom XSSF colors");  XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128))); style1.setFillPattern(CellStyle.SOLID_FOREGROUND);