Apache POI fills XSSF cell with black instead of d

2019-02-27 21:14发布

问题:

I am writing a program that is supposed to make an Excel spreadsheet with some cells that are filled orange, given text, and given a border using a PropertyTemplate. I have successfully written the code to do this in HSSF, but I am now working on XSSF and cannot get it to work.

What is happening is the cells are getting filled with the correct orange color, and the text is going into the cells properly as well, but applying the PropertyTemplate makes the orange cells become black. Does anyone know a way around this?

Here is the code that I have.

XSSFCellStyle orangeFillStyle = wb.createCellStyle();
orangeFillStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 192, 0)));
orangeFillStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

Then there is a whole bunch of code/algorithm to figure out which cells to fill orange and which cells to put text in. I highlight cells using the code:

currCell.setCellStyle(orangeFillStyle);

At the very end of the sheet creation is the PropertyTemplate (border) code:

BorderStyle dividerStyle = BorderStyle.THIN;
PropertyTemplate borderTemplate = new PropertyTemplate();
borderTemplate.drawBorders(new CellRangeAddress(0, 0, 0, 13), dividerStyle, BorderExtent.BOTTOM);
borderTemplate.drawBorders(new CellRangeAddress(0, 0, 1, 13), dividerStyle, BorderExtent.TOP);
borderTemplate.drawBorders(new CellRangeAddress(0, rowI, 1, 13), dividerStyle, BorderExtent.VERTICAL);
borderTemplate.drawBorders(new CellRangeAddress(rowI, rowI, 0, 13), dividerStyle, BorderTextent.BOTTOM);
borderTemplate.applyBorders(sheet);

If I comment out the borderTemplate.applyBorders(sheet); line, the fill color looks just fine. Obviously I don't have any borders on my sheet if I do that, however. It's like I can't have a custom fill color and borders at the same time. Does anyone know why this might be happening or a way around it?

I will note that if I use an IndexedColor instead of a custom color, then the fill and borders work just fine. The only problem is I don't like any of the indexed colors. Here is the code I use for IndexedColor:

XSSFCellStyle orangeFillStyle = wb.createCellStyle();
orangeFillStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
orangeFillStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

回答1:

Well, looks like it's a bug with Apache POI. I noticed someone posted about it in their bug tracker a while ago and it still exists in version 3.16.

In case anyone else comes across this issue, my workaround was to put the PropertyTemplate (the borders) on before filling any cells with my custom orange. It meant significantly modifying my code, but it works now.