How do I get the (Java Apache POI HSSF) Background Color for a given cell?
Solution 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
Solution 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
Solution 3
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.
Solution 4
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 [email protected]
*
*/
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
*/
Solution 5
To get the background color of the specific cell in HEX, use this:
cell.getCellStyle().getFillForegroundColorColor().getARGBHex()
Notice the word Color
is used twice
java
Updated on December 05, 2020Comments
-
java over 3 years
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.
-
java over 14 yearsThanks for your anser so far, unfortunately its not quite a solution, I have re-clarified the question now.
-
RMorrisey over 14 yearsUpdated my answer with a little help from craig's post