使用apache poi改变单元颜色。

I'm using Apache POI to read data in a spreadsheet of part numbers. I look up the part number in our database, if we have a CAD drawing of the part I color the part number cell green, if we don't I color it red. After the processing is done the spreadsheet is saved. The problem I'm having is that every cell in that column comes out green. I've stepped through the code, the logic to look up the part number is working fine and the logic to determine what color the cell should be and setting the color and fill appears to work as well. Any ideas what I'm doing wrong here?

我正在使用Apache POI读取零件编号电子表格中的数据。我在我们的数据库中查找零件编号，如果我们有零件I的CAD图，我把零件编号涂成绿色，如果我们不把它涂成红色。处理完成后，将保存电子表格。我遇到的问题是这个列中的每个单元格都是绿色的。我已经遍历了代码，查找零件号的逻辑运行良好，确定单元格应该是什么颜色的逻辑以及设置颜色和填充的逻辑似乎也可以工作。知道我做错了什么吗?

Thanks.

谢谢。

//Check the parts
for(int r=1;r<sheet.getPhysicalNumberOfRows();r++) {
    String partNumber = null;
    switch(cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            long pNum = (long) cell.getNumericCellValue();
            partNumber = String.valueOf(pNum);
            break;
        case HSSFCell.CELL_TYPE_STRING:
            partNumber = cell.getStringCellValue();
            break;
        default:
            logger.info("Part Number at row " + r + " on sheet " + partList.getSheetName(s) + "is of an unsupported type");
    }

    try {
        List<String> oldMaterialNumbers = getOldMaterialNumbers(partNumber);

        boolean gotDrawing = checkPartNumber(oldMaterialNumbers, partNumber);
        //If there's a drawing then color the row green, if not red.
        short bgColorIndex = gotDrawing
                                ?HSSFColor.LIGHT_GREEN.index //42
                                :HSSFColor.RED.index; //10

        HSSFCell curCell = row.getCell(partNumberColumn);
        HSSFCellStyle curStyle = curCell.getCellStyle();

        curStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        curStyle.setFillForegroundColor(bgColorIndex);

        curCell.setCellStyle(curStyle);

    }catch(Exception e) {
        throw e;
    }
}

4 个解决方案

#1

Short version: Create styles only once, use them everywhere.

短版本:只创建一次样式，在任何地方都使用它们。

Long version: use a method to create the styles you need (beware of the limit on the amount of styles).

长版本:使用一个方法创建所需的样式(注意样式数量的限制)。

private static Map<String, CellStyle> styles;

private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        DataFormat df = wb.createDataFormat();

        CellStyle style;
        Font headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 12);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(headerFont);
        styles.put("style1", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("date_style", style);
        ...
        return styles;
    }

you can also use methods to do repetitive tasks while creating styles hashmap

您还可以使用方法在创建样式hashmap时执行重复任务

private static CellStyle createBorderedStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

then, in your "main" code, set the style from the styles map you have.

然后，在“主”代码中，从您拥有的样式映射中设置样式。

Cell cell = xssfCurrentRow.createCell( intCellPosition );       
cell.setCellValue( blah );
cell.setCellStyle( (CellStyle) styles.get("style1") );

#2

To create your cell styles see: http://poi.apache.org/spreadsheet/quick-guide.html#CustomColors.

要创建单元格样式，请参见:http://poi.apache.org/spreadsheet/quick guide.html#CustomColors。

Custom colors

自定义颜色

HSSF:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("Default Palette");

//apply some colors from the standard palette,
// as in the previous examples.
//we'll use red text on a lime background

HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);

cell.setCellStyle(style);

//save with the default palette
FileOutputStream out = new FileOutputStream("default_palette.xls");
wb.write(out);
out.close();

//now, let's replace RED and LIME in the palette
// with a more attractive combination
// (lovingly borrowed from freebsd.org)

cell.setCellValue("Modified Palette");

//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
);
//replacing lime with freebsd.org gold
palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);

//save with the modified palette
// note that wherever we have previously used RED or LIME, the
// new colors magically appear
out = new FileOutputStream("modified_palette.xls");
wb.write(out);
out.close();

XSSF:

 XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell( 0);
    cell.setCellValue("custom XSSF colors");

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));
    style1.setFillPattern(CellStyle.SOLID_FOREGROUND);

#3

I believe it is because cell.getCellStyle initially returns the default cell style which you then change.

我相信是因为细胞。getCellStyle最初返回默认的单元格样式，然后进行更改。

Create styles like this and apply them to cells:

创建这样的样式并将其应用到单元格:

cellStyle = (XSSFCellStyle) cell.getSheet().getWorkbook().createCellStyle();

Although as the previous poster noted try and create styles and reuse them.

尽管正如前面的海报所提到的，尝试创建样式并重用它们。

There is also some utility class in the XSSF library that will avoid the code I have provided and automatically try and reuse styles. Can't remember the class 0ff hand.

XSSF库中还有一些实用程序类，它们将避免我提供的代码，并自动尝试和重用样式。我记不起那个班了。

#4

checkout the example here

结账的例子

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java

style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());

#1