CODEWITHSUNDEEP
javaexcelapache-poi
Alexiz Hernandez
Alexiz Hernandez

Reputation: 579

How to use colors not in IndexedColors for Apache POI XSSF Excel?

I am looking at an excel sheet that I am wanting to duplicate and the only issue I am having is with colors. The colors I am looking to duplicate are Blue, Accent 5, Lighter 40% and Light Green from the Standard Colors section. I am looking at the docs for using custom colors in an XSSF Workbook and it states that the way to do it is like this:

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), new DefaultIndexedColorMap()));
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);

When I try to use style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap())); I get an error, since the only parameter that .setFillForegroundColor() only takes one parameter, and that is a short and not an XSSFColor.

Has anyone had any luck with this? I have been searching for hours and can't find anything that isn't 8 years old or doesn't work.

Upvotes: 5

Views: 13977

Answers (1)

Axel Richter
Axel Richter

Reputation: 61925

Using current apache poi 4.1.1 there is public void setFillForegroundColor(XSSFColor color) in XSSFCellStyle.

The XSSFColor should be created using constructor public XSSFColor(byte[] rgb, IndexedColorMap colorMap) since all other constructors are either deprecated or marked TEST ONLY or not usable for creating a custom color.

The RGB values for the needed color can be got from Excel by setting the color from the palette and then choose Fill Color - More Colors - Custom. Unfortunately apache poi's IndexedColors are no more exact the colors of current Excel versions. They are of version 2007. So they also can be used but later Excel versions might show different colors then.

Complete example using current apache poi 4.1.1:

import java.io.FileOutputStream;

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;

public class CreateExcelXSSFCellFillColor {

 public static void main(String[] args) throws Exception {
  XSSFWorkbook workbook = new XSSFWorkbook();

  java.util.List<XSSFCellStyle> cellStyles = new java.util.ArrayList<XSSFCellStyle>();
  XSSFCellStyle cellStyle; byte[] rgb; XSSFColor color;

  //Your custom color #800080
  //create cell style on workbook level
  cellStyle = workbook.createCellStyle();
  //set pattern fill settings
  cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  //create the RGB byte array
  rgb = new byte[3];
  rgb[0] = (byte) 128; // red
  rgb[1] = (byte) 0; // green
  rgb[2] = (byte) 128; // blue
  //create XSSFColor
  color = new XSSFColor(rgb, new DefaultIndexedColorMap());
  //set fill color to cell style
  cellStyle.setFillForegroundColor(color);

  cellStyles.add(cellStyle);

  //Light Green
  cellStyle = workbook.createCellStyle();
  cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  rgb = new byte[3];
  rgb[0] = (byte) 146; // red
  rgb[1] = (byte) 208; // green
  rgb[2] = (byte) 80; // blue
  color = new XSSFColor(rgb, new DefaultIndexedColorMap());
  cellStyle.setFillForegroundColor(color);
  cellStyles.add(cellStyle);

  //Blue, Accent 5, Lighter 40%
  cellStyle = workbook.createCellStyle();
  cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  rgb = new byte[3];
  rgb[0] = (byte) 155; // red
  rgb[1] = (byte) 194; // green
  rgb[2] = (byte) 230; // blue
  color = new XSSFColor(rgb, new DefaultIndexedColorMap());
  cellStyle.setFillForegroundColor(color);
  cellStyles.add(cellStyle);

  Sheet sheet = workbook.createSheet();
  for (int r = 0; r < cellStyles.size(); r++) {
   Row row = sheet.createRow(r);
   row.setHeight((short)(20*20));
   Cell cell = row.createCell(0);
   cell.setCellValue("cell style " + (r+1));
   cell.setCellStyle(cellStyles.get(r));
  }
  sheet.setColumnWidth(0, 20*256);

  FileOutputStream out = new FileOutputStream("CreateExcelXSSFCellFillColor.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Upvotes: 5

Related Questions