Changing cell color using apache poi

Question

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?

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;
    }
}

Answer 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

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") );

Answer 2

To create your cell styles see: 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);

Answer 3

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

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.

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