使用Java apache POI在excel行中填充背景颜色

I am trying to read a excel sheet and fill the background color for rows using following code:

我正在尝试阅读excel表格，并使用以下代码为行填充背景颜色:

....
HSSFCellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setFillForegroundColor(new HSSFColor.BLACK().getIndex());
cellStyle1.setFillBackgroundColor(new HSSFColor.RED().getIndex());
cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

for(int i=0;i < rowCount;i++){
     sheet.getRow(i).setRowStyle(cellStyle1);
}
...

when I run my code, the color is getting filled for only blank cells. For all cells which contains data there is no change in color. Can someone tell me why it is happening?

当我运行代码时，颜色只会被空单元格填充。对于所有包含数据的单元格，颜色没有变化。有人能告诉我为什么会这样吗?

2 个解决方案

#1

As it turns out, set setFillForegroundColor is for setting cell background color. Comment out setFillBackgroundColor and it should work.

结果显示，设置setFillForegroundColor用于设置单元格背景颜色。注释掉setFillBackgroundColor，它应该可以工作。

CellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setFillForegroundColor(IndexedColors.RED.index);
//cellStyle1.setFillBackgroundColor(IndexedColors.RED.index);
cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

Edited**

编辑* *

Working test code

工作测试代码

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class TestPoi {
    public static void main(String[] args) throws Exception {
        System.out.println("Started");
        Workbook workbook = new HSSFWorkbook(new FileInputStream("input.xls"));

        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setFillForegroundColor(IndexedColors.RED.index);
        //cellStyle1.setFillBackgroundColor(IndexedColors.RED.index);
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Sheet sheet = workbook.getSheet("Sheet1");

        Iterator<Row> rowIterator = sheet.rowIterator();
        while(rowIterator.hasNext()){
             Row row = rowIterator.next();
             Iterator<Cell> cellIterator = row.cellIterator();
             while(cellIterator.hasNext()) {
                 Cell cell = cellIterator.next();
                 cell.setCellStyle(cellStyle1);
                 /*HSSFCellStyle style = (HSSFCellStyle)cell.getCellStyle();
                 style.setFillBackgroundColor(IndexedColors.RED.index);*/
                 System.out.println(cell.getStringCellValue());
             }
        }
        workbook.write(new FileOutputStream("output.xls"));
        System.out.println("Ended");
    }
}

#2

In short: setRowStyle is not doing what you assume it does.

简而言之:setRowStyle并没有做你认为它会做的事情。

All it does (see source) is register the style as the rows default style.

它所做的(参见源代码)是将样式注册为行默认样式。

  row.setFormatted(true);
  row.setXFIndex(style.getIndex());

It does not iterate over all the cells in a row and changes their style. Thus the style only applies to not existing cells(1) and newly created cells which reference the row style by default.

它不会对一行中的所有单元格进行迭代并更改它们的样式。因此，样式只适用于不存在的单元格(1)和新创建的单元，它们默认引用行样式。

As you can see from the code above styles are simply referenced by their index. In order to allow for different styles on the row and various cells, the cells must be able to reference a different style. The cell style then logically supersedes the row style. Thus to apply a style to all cells you must assign the style not only to the row but to all existing cells as well.

您可以从上面的代码中看到，样式只是由它们的索引引用的。为了允许不同类型的行和不同的单元格，单元格必须能够引用不同的样式。然后，单元格样式在逻辑上取代行样式。因此，要对所有单元格应用样式，您必须不仅将样式分配给行，还分配给所有现有单元格。

You said in your question that you are reading the document and then you try to color the row. So I will assume that your not actually creating new cells yourself, because POI should then copy the row style and this would probably be a bug.

你在你的问题中说你正在阅读文件然后你试着给这行涂上颜色。我假设你不是自己创建新单元格，因为POI应该复制行样式，这可能是个错误。

In your case it's probably a bit like this (simplified):
The existing cells in your document reference the style with index 0. You now create a new style with index 1 and apply it to the row via setRowStyle.
All not existing(1) and new cells will then use the style with index 1. The existing cells however still point to the style with index 0, because they have not automatically been assigned the new style.

在您的例子中，它可能有点像这样(简化了):文档中的现有单元格引用了索引0的样式。现在使用索引1创建一个新的样式，并通过setRowStyle将其应用到行中。所有不存在的(1)和新的单元格将使用索引1的样式。但是，现有的单元格仍然指向索引0的样式，因为它们还没有自动分配新的样式。

You probably expected setRowStyle to behave like the Excel application, where you can select the entire row and set the style on it. But that's not how it works. You have to iterate manually and apply the change to all cells.

您可能希望setRowStyle的行为类似于Excel应用程序，您可以选择整个行并在其上设置样式。但事实并非如此。您必须手动迭代并将更改应用到所有单元格。

1: Cells that exist only logically in the Excel application but not yet physically in the data structure in order to save space. This is why getCell can return null and doesn't just return CELL_TYPE_BLANK by default.

1:为了节省空间，只在Excel应用程序中逻辑存在但在数据结构中还没有物理存在的单元格。这就是为什么getCell可以返回null，而不是默认返回CELL_TYPE_BLANK。

#1