How do I get the (Java Apache POI HSSF) Background Color for a given cell?

Question

Hello,

I have an existing excel spreadsheet, which I am accesssing and reading values from, I am using Apache POI HSSF.

It is initialised like this:

HSSFSheet sheet;
FileInputStream fis = new FileInputStream(this.file);
POIFSFileSystem fs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(fs);
this.sheet = wb.getSheet(exsheet);

I am iterating over all the cells that exist in the sheet, which makes a cell object:

HSSFCell cell = (HSSFCell) cells.next();

Please can someone familiar with the framework explain how to create an (HSSFColor) object to represent the backround color of each cell in the sheet.

Many thanks

EDIT, UPDATE

To be clear what I want to know is: how do I create/get an HSSFColor object for the background color of an existing cell?

cell.getCellStyle().getFillBackgroundColor();

This code only returns a short number, not an HSSFColor object. Thanks for the answers so far.

Answer 1

You would do something like:

HSSFCell myCell = ...;
HSSFCellStyle myStyle = workbook.createCellStyle();
myStyle.setFillBackgroundColor(HSSFColor.BLUE);

myCell.setCellStyle(myStyle);

I believe there is a limited number of styles for a given workbook; you will want to reuse the same style object where possible.

[Edit: Sorry, that would be to set the color on a cell. To get the color, use like:

myCell.getCellStyle().getFillBackgroundColor();

]

[Edit 2: Looking at the custom color information craig posted, maybe you can try:

HSSFColor.getIndexHash().get(myCell.getCellStyle().getFillBackgroundColor())

]

Answer 2

There are static color classes provided by the HSSFCell class, listed here:

http://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html

If you want to create your own custom colors, you will need to create and modify a custom palette. Apache provides a very clear guide to this as well:

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

Answer 3

To get the color : The short value returbned by the getFillBackgourndColor is the Excel index of the color. You can get the color corresponding to the index in the HSSFColor HashTable, using the last code RMorrisey indicated

To set a color : You create a custom palette, and change the color at a given index. You then apply the color to the style.

//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
);
// or creating a new Color
HSSFColor myColor palette.addColor((byte) 153, (byte) 0, (byte) 0) 
HSSFCellStyle style = wb.createCellStyle();

style.setFillForegroundColor(myColor);

Regards

Guillaume