java - Adding Custom colours to Excel sheet using Apache POI

Question

Can anyone explain how to add Custom colours either using (rgb values or hex values ) to an excelsheet sheet(either in foreground or background) using Cellstyle in Apche poi to a Excelsheet(XSSF Workbook)?

Answer 1

Setting custom colors depends on the kind of Excel file (Office Open XML format *.xlsx vs. BIFF format *.xls). And it might be different using different versions of apache poi because of deprecation.

Using Office Open XML format *.xlsx we can simply set new colors using constructor of XSSFColor. In apache poi 4.0.0 XSSFColor(byte[] rgb, IndexedColorMap colorMap) can be used. IndexedColorMap can be null if no additional color map shall be used instead of the default one.

Using BIFF format *.xls only indexed colors are usable. But temporary overwriting some of the indexed colors is possible.

Following code shows both used for setting a cells's fill color. The used custom color is RGB(112,134,156). Using HSSF(BIFF format *.xls) the indexed color HSSFColor.HSSFColorPredefined.LIME will be temporary overwritten.

Note, the following is tested and works using apache poi 4.0.0. No guarantee using other versions.

import java.io.FileOutputStream;

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

public class CreateExcelCustomColor {

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

  Workbook workbook = new XSSFWorkbook();
  //Workbook workbook = new HSSFWorkbook();

  CellStyle cellcolorstyle = workbook.createCellStyle();
  cellcolorstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  byte[] rgb = new byte[]{(byte)112, (byte)134, (byte)156};
  if (cellcolorstyle instanceof XSSFCellStyle) {
   XSSFCellStyle xssfcellcolorstyle = (XSSFCellStyle)cellcolorstyle;
   xssfcellcolorstyle.setFillForegroundColor(new XSSFColor(rgb, null));
  } else if (cellcolorstyle instanceof HSSFCellStyle) {
   cellcolorstyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
   HSSFWorkbook hssfworkbook = (HSSFWorkbook)workbook;
   HSSFPalette palette = hssfworkbook.getCustomPalette();
   palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LIME.getIndex(), rgb[0], rgb[1], rgb[2]);
  }

  Sheet sheet = workbook.createSheet();
  Cell cell = sheet.createRow(0).createCell(0);
  cell.setCellStyle(cellcolorstyle);

  FileOutputStream out = null; 
  if (workbook instanceof XSSFWorkbook) {
   out = new FileOutputStream("CreateExcelCustomColor.xlsx");
  } else if (workbook instanceof HSSFWorkbook) {
   out = new FileOutputStream("CreateExcelCustomColor.xls");
  }
  workbook.write(out);
  out.close();
  workbook.close();

 }

}