如何获取给定单元格的（Java Apache POI HSSF）背景颜色？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1499739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the (Java Apache POI HSSF) Background Color for a given cell?
提问by java
I have an existing excel spreadsheet, which I am accesssing and reading values from, I am using Apache POI HSSF.
我有一个现有的 excel 电子表格，我正在从中访问和读取值，我正在使用 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.
请熟悉该框架的人解释如何创建一个 (HSSFColor) 对象来表示工作表中每个单元格的背景颜色。
Many thanks
非常感谢
EDIT, UPDATE
编辑，更新
To be clear what I want to know is: how do I create/get an HSSFColorobject for the background color of an existing cell?
要清楚我想知道的是：如何为现有单元格的背景色创建/获取HSSFColor对象？
cell.getCellStyle().getFillBackgroundColor();
This code only returns a short number, not an HSSFColor object. Thanks for the answers so far.
此代码仅返回一个短数字，而不是 HSSFColor 对象。感谢您到目前为止的答案。
回答by RMorrisey
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:
[编辑 2：查看 craig 发布的自定义颜色信息，也许您可以尝试：
HSSFColor.getIndexHash().get(myCell.getCellStyle().getFillBackgroundColor())
]
]
回答by Craig Otis
There are static color classes provided by the HSSFCell class, listed here:
HSSFCell 类提供了静态颜色类，在此处列出：
http://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html
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:
如果要创建自己的自定义颜色，则需要创建和修改自定义调色板。Apache 也为此提供了非常清晰的指南：
http://poi.apache.org/spreadsheet/quick-guide.html#CustomColors
http://poi.apache.org/spreadsheet/quick-guide.html#CustomColors
回答by PATRY Guillaume
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.
获取颜色：getFillBackgroundColor 返回的短值是颜色的 Excel 索引。您可以使用最后一个代码RMorrisey 表示HSSFColor HashTable 中的索引对应的颜色。
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
纪尧姆
回答by Pudge
The backgroundcolor information of XSSFCellStyle can get from the method:
XSSFCellStyle的backgroundcolor信息可以从方法中获取：
XSSFCellStyle.getFillForegroundXSSFColor().getCTColor()
You can print it out and you will see it's structure.
你可以打印出来，你会看到它的结构。
回答by ScaryJoker
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
*/
回答by Ascalonian
To get the background color of the specific cell in HEX, use this:
要获取十六进制中特定单元格的背景颜色，请使用以下命令：
cell.getCellStyle().getFillForegroundColorColor().getARGBHex()
Notice the word Coloris used twice
注意这个词Color用了两次
回答by Anthony Holland
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()
以下是 XSSF 并且在 Scala 中，但它确实显示了如何从对象模型中获取颜色。我想从实际的 rgb 值实例化一个 java.awt.Color 对象（这很有用，部分是因为我的调试器在我停在断点处时为我显示了对象的实际颜色，部分是因为这是为了导出到具有与 Excel 无关）。我忽略了颜色的 alpha 值，我的 Scala 可能有点幼稚。我建议，如果这对您不起作用，您应该设置一个断点并检查密切相关的方法调用的结果，例如 getFill BackgroundColorColor()
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))
回答by Keith
For XSSF reading xlsx file (tried the HSSF as well) ,after struggle for a while, i just found getFillBackgroundXSSFColor()method actually returned the "Pattern Color" in the Fill tab of "Format Cells" in Excel, not the so-called "Background" color in that tab. I am not sure if this expected.
对于 XSSF 读取 xlsx 文件（也尝试了 HSSF），经过一段时间的挣扎，我发现getFillBackgroundXSSFColor()方法实际上返回了 Excel 中“格式单元格”的“填充”选项卡中的“图案颜色”，而不是所谓的“背景”该选项卡中的颜色。我不确定这是否符合预期。
See my below screenshot. The returned RGB is actually FF0000 ,i.e. RED.
看我下面的截图。返回的 RGB 实际上是 FF0000 ，即红色。
XSSFColor backgroundColor = cell.getCellStyle().
getFillBackgroundXSSFColor();
System.out.println("backgroundColor is "+backgroundColor.getARGBHex());
Output: FFFF0000 //the first FF should be ignored.
So right now, I do not have a way for this case and just request user to fill the "Pattern Color" as well.
所以现在，我没有办法处理这种情况，只要求用户也填写“图案颜色”。
