How do I get the (Java Apache POI HSSF) Background

Question

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

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 2

To get the color : The short value returned by the getFillBackgroundColor 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. Then, you 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

Answer 3

The following is for XSSF and is in Scala but it does show exactly how to get the colour from the object model. I wanted to instantiate a java.awt.Color object from the actual rgb values (which is useful partly because my debugger displays for me the actual colour of the object when I stop at breakpoints, and partly because this is for export to systems that have nothing to do with Excel). I'm ignoring the colour's alpha value and my Scala may be a bit naive. I'd suggest that if this doesn't work for you, you should set a break-point and examine the result of closely related method calls such as getFillBackgroundColorColor()

    val rgb: Array[Byte] = cell.getCellStyle.getFillForegroundColorColor.getRgb
    def toInt(b: Byte): Int = {
      if (b<0) 256+b else b
    }
    val rgbInts = rgb.map(toInt)
    val color = new Color(rgbInts(0),rgbInts(1),rgbInts(2))

Answer 4

To get the background color of the specific cell in HEX, use this:

cell.getCellStyle().getFillForegroundColorColor().getARGBHex()

Notice the word Color is used twice

Answer 5

import java.io.File;    
import java.io.FileInputStream;       
import java.io.FileNotFoundException;   
import java.io.FileOutputStream;   
import java.io.IOException;   
import java.util.Iterator;    
import org.apache.poi.hssf.usermodel.HSSFPalette;    
import org.apache.poi.hssf.usermodel.HSSFSheet;    
import org.apache.poi.hssf.usermodel.HSSFWorkbook;    
import org.apache.poi.hssf.util.HSSFColor;    
import org.apache.poi.ss.usermodel.Cell;    
import org.apache.poi.ss.usermodel.CellStyle;    
import org.apache.poi.ss.usermodel.Row;    

/**
 * @author [e-mail] 
 *
 */

public class ExcelPractice {

    /**
     *  Must Read :     
     *  
     *  Code to get the background color from an excel sheet in RGB Format and display on the console    
     *  Save the content of the xls file into another OUTPUT.xls file.    
     *  Using a sample sheet with only first row filled with background color.    
     *  Code uses HSSF which means i am only using xls format.    
     *  Using poi-3.5-FINAL.jar    
     *  Solution with the output provided      
     *  Observation : Some Custom color's are not recognized as they may not be defined    
     *  in the excel color palette thus the code returns the almost similar color code.    
     */
    public static void main(String[] args) {
        try {
            FileInputStream fileInputStream=new FileInputStream(new     File("D:\\Excel_File.xls"));

            HSSFWorkbook workbook=new HSSFWorkbook(fileInputStream);
            HSSFSheet  sheet=workbook.getSheetAt(0);
            Iterator<Row> rowIterator= sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row=rowIterator.next();

                Iterator<Cell> cellIterator=row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = (Cell) cellIterator.next();

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.println(cell.getBooleanCellValue()+"\t\t");  
                        System.out.println(cell.getCellStyle().getFillForegroundColor());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.println(cell.getNumericCellValue()+"\t\t");
                        System.out.println(cell.getCellStyle().getFillForegroundColor());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.println(cell.getStringCellValue()+"\t\t");
                        //System.out.println(HSSFColor.getIndexHash().get(cell.getCellStyle().getFillBackgroundColor()));   
                        int num=cell.getColumnIndex();
                        Cell cell1 = row.getCell(num);
                        CellStyle cellStyle = cell1.getCellStyle();          
                        getColorPattern(cellStyle.getFillForegroundColor());
                        break;
                    default:
                        break;
                    }
                }
                System.out.println();

                fileInputStream.close();
                FileOutputStream fileOutputStream=new FileOutputStream(new File("D:\\OUTPUT.xls"));
                workbook.write(fileOutputStream);
                fileInputStream.close();
            }


    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.toString();
    }
    catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

//Method to identify the color pattern
private static short[] getColorPattern(short colorIdx){        
    short[] triplet = null;
    HSSFWorkbook workbook=new HSSFWorkbook();
    HSSFPalette palette = workbook.getCustomPalette();
    HSSFColor color = palette.getColor(colorIdx);
    triplet = color.getTriplet();       
    System.out.println("color : " + triplet[0] +"," + triplet[1] + "," +     triplet[2]);
    return triplet;
}
}

/** Output of the above code as executed in my system 
 S.NO.      
color : 255,255,0
VTU Number      
color : 0,128,0
First Name      
color : 51,204,204
Middle Name     
color : 255,0,0
Last Name       
color : 102,102,153
Branch      
color : 255,102,0
E-mail id       
color : 0,255,0
Mobile Number       
color : 255,255,255 
*/

Answer 6

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())

]