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

how to set cell font color in excel using java
apache poi indexedcolors examples
apache poi set cell background color
xssf
apache poi merge cells
apache poi format cell
xssf example
apache poi iterate over rows

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.

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

Apache POI Excel Cell Color, Apache POI Excel Cell Color with Apache POI Tutorial, Apache POI Introduction, Apache POI Architecture, Apache POI allows us to set color of individual cell in background and foreground both. FileNotFoundException;; import java.io. Mail us on hr@javatpoint.com, to get more information about given services. org.apache.poi.ss.usermodel.IndexedColors; org.apache.poi.ss.usermodel.CellStyle; 17 Java code examples below to show how to use different fill patterns to set up Excel cell background and forground using Apache POI library. Example for background and foreground color with FillPatternType.ALT_BARS fill pattern

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

org.apache.poi.hssf.usermodel.HSSFCellStyle , How do I get the (Java Apache POI HSSF) Background Color for a given cell? //​creating a custom palette for the workbook HSSFPalette palette = wb. 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 ();

The backgroundcolor information of XSSFCellStyle can get from the method:

XSSFCellStyle.getFillForegroundXSSFColor().getCTColor()

You can print it out and you will see it's structure.

Fill Color in Excel using Apache POI, More easy is fill color to cell in excel using Apache POI. We can diff Apache POI. For giving a cell background color we use the following import java.io.File;​. The method setFillBackgroundColor, takes a color which is of type short. These colors are defined in the POI library of type HSSFColor. You can choose the color you want first and pass it to the setFillBackgroundColor method. You should set the foreground color of the cell before setting the background color.

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

]

HSSFCellStyle (POI API Documentation), 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: All we need is to get an instance of CellStyle and then set the desired color to CellStyle and then assign it to XLSX cell. Create a XSSFWorkbook. Get CellStyle from XSSFWorkbook as below. CellStyle style = workbook.createCellStyle(); We will set background color and also set the fill pattern.

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

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

Notice the word Color is used twice

Busy Developers' Guide to HSSF and XSSF Features, java.lang.Object. org.apache.poi.hssf.usermodel.HSSFCellStyle High level representation of the style of a cell in a sheet of a workbook. Gets the color object representing the current background fill, resolving indexes using the Specified by: setFont in interface CellStyle; Parameters: font - a font object created or  optionally a Foreground and background fill can be applied: Note: Ensure Foreground color is set prior to background cs.setFillPattern(HSSFCellStyle.FINE_DOTS ); cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); or, for the special case of SOLID_FILL:

XSSFCellStyle (POI API Documentation), Creates a cell and aligns it a certain way. *. * @param wb the workbook. * @​param row the row to create the cell in. * @param column the  Thanks for the quick reply, am using Xlsx verison and poi 3.8 version and here is the formula, which we are trying to execute from poi. but it’s not working..i looked at the apache poi site where they are saying array of formula not supported by poi.. is there any alternative way to modify the formula to work with POI.

Excel Cell Fill Color - Java POI Example Program, java.lang.Object. org.apache.poi.xssf.usermodel.XSSFCellStyle get the type of border to use for the bottom border of the cell Gets the color object representing the current background fill, resolving indexes using the For cell fills with patterns specified, then the cell fill color is specified by the background color element. public static java.util.Map<java.lang.String,HSSFColor> getTripletHash() this function returns all colors in a hastable. It's not implemented as a static member/statically initialized because that would be dirty in a server environment as it is intended.

How to Set Background and Font Color in XLSX Using POI in Java, how to fill the background of a cell in Excel with a particular color, in Java using Apache POI, These colors are defined in the POI library of type HSSFColor. Apache POI Excel Cell Color. Apache POI allows us to set color of individual cell in background and foreground both. To do this, it provides methods that help to set color for the cell. In the following example, we are creating two cells and filling colors to background and foreground respectively. See the example. Apache POI Excel Cell Color

Comments
  • or cell.getCellStyle().getFillForegroundColor(), the excel sheet I'm reading return the same color for background color even though the cell are colored.
  • Thanks for your anser so far, unfortunately its not quite a solution, I have re-clarified the question now.
  • Updated my answer with a little help from craig's post