Apache POI 4.0: XSSFColor from java.awt.Color

Question

org.apache.poi 4.0 removed the XSSFColor constructor that just uses java.awt.Color. In org.apache.poi 3.7 it was very easy to create the object by just writing

Color inputColor = Color.RED;
XSSFColor test = new XSSFColor(inputColor);

However, this constructor no longer works in 4.0. The documentation at https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFColor.html shows several other constructors, but ideally i want to change as few lines as possible.

So, my question is, what is the best way to create an XSSFColor from a java.awt.Color now (in apache poi 4.0)?

As requested in the comments, here is my test code using the suggestion style.setFillForegroundColor(new XSSFColor(java.awt.Color.RED, null)); Opening this with LibreOffice 6.1 yields an Error (Attempt to repair, which then fails). Commented out the POI 3.7 version which works normally.

@Test
public void testPOI40() throws FileNotFoundException, IOException {
    Workbook workbook = new XSSFWorkbook();
    XSSFSheet fSheet = (XSSFSheet) workbook.createSheet("new Sheet");
    XSSFRow hRow = fSheet.createRow((short) 0);
    //header
    String[] astrHeaders = new String[]{"Header1", "Header2", "Header3", "Header4"};
    for (int col = 0; col < astrHeaders.length; col++) {
        XSSFCell cell = hRow.createCell((short) col);
        XSSFCellStyle tempHeaderStyle = (XSSFCellStyle) workbook.createCellStyle();
        tempHeaderStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        tempHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellValue(astrHeaders[col]);
        cell.setCellStyle(tempHeaderStyle);
    }        
    //body
    Double[] astrContent = new Double[]{1.3, 0.3, 0.87, 1.0};     
    Color[] colors = new Color[] {Color.RED,Color.BLUE,Color.WHITE,Color.GREEN};        
    XSSFRow fRow = fSheet.createRow((short) 1);
    for (int iCol = 0; iCol < 4; iCol++) {
        XSSFCell cell = fRow.createCell((short) iCol);
        XSSFCellStyle tempBodyStyle = (XSSFCellStyle) workbook.createCellStyle();
        cell.setCellValue(astrContent[iCol]);
        //working with POI 3.17
        //tempBodyStyle.setFillForegroundColor(new XSSFColor(colors[iCol]));
        tempBodyStyle.setFillForegroundColor(new XSSFColor(colors[iCol],null));
        tempBodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(tempBodyStyle);
    }        
    FileOutputStream fileOut = new FileOutputStream(new File("testfile.xlsx"));
    BufferedOutputStream bos = new BufferedOutputStream(fileOut);
    workbook.write(bos);
    fileOut.close();
}

Solution:
Replaced fileout.close(); with bos.close(); and it works. So tempBodyStyle.setFillForegroundColor(new XSSFColor(Color.RED,null)); as suggested in the comments by Alex Richter is a good solution & will accept this as answer.

Thanks for answering, however the table resulting from the first method is reported as damaged when trying to open it. The second method fails because `setColor(Color) has protected access in ExtendedColor`. — ptstone, Nov 09 '18 at 14:56
Sorry but `style.setFillForegroundColor(new XSSFColor(java.awt.Color.RED, null));` works for me using `apache poi 4.0.0` and creating `XSSFWorkbook` from scratch. Please show minimal complete verifiable example which shows where it fails. — Axel Richter, Nov 09 '18 at 15:31
Thank you, I added my test for your suggestion to the main post now. — ptstone, Nov 09 '18 at 16:21
Just to clarify, the code doesn't cause an error, but the output file cannot be read (but the version using POI 3.7, see commented out section, is readable) — ptstone, Nov 09 '18 at 16:35
The damaging has nothing to do with the colors. You are wrapping the FileOutputStream in an BufferedOutputStream but do closing then only the inner FileOutputStream but not the BufferedOutputStream. Do `workbook.write(bos); bos.close();` and it will work. At least it works for me then. It might had worked in former `apache poi` versions because `Workbook.write` had closed all streams when it was ready. This it does not more. — Axel Richter, Nov 09 '18 at 16:36
You are right! Never looked there because it worked without causing problems with POI 3.7, but the missing `bos.close()` is indeed the culprit. If you want to put `style.setFillForegroundColor(new XSSFColor(java.awt.Color.RED, null));`as answer i would certainly accept. — ptstone, Nov 09 '18 at 16:41

Axel Richter · Accepted Answer · 2018-11-09T17:08:49.183

If you are wrapping the FileOutputStream in an BufferedOutputStream but do closing then only the inner FileOutputStream but not the BufferedOutputStream, then the BufferedOutputStream remains open and the file will not have all bytes in.

That's why the damaging of the file.

So the damaging has noting to do with constructing the XSSFColor. The constructor style.setFillForegroundColor(new XSSFColor(java.awt.Color.RED, null)); works.

Do instead:

...
FileOutputStream fileOut = new FileOutputStream(new File("testfile.xlsx"));
BufferedOutputStream bos = new BufferedOutputStream(fileOut);
workbook.write(bos); 
bos.close();
workbook.close();
...

It might had worked in former apache poi versions because XSSFWorkbook.write had closed all streams when it was ready. This it does not more. And this is correct because write should not closing streams.

But since POIXMLDocument implements java.io.Closeable at least workbook.close() should closing all streams. But that also it does not. So explicitly closing all streams is necessary in apache poi 4.0.0.

Thanks again. Could you maybeadd the line `tempBodyStyle.setFillForegroundColor(new XSSFColor(Color.RED,null));` in the answer for people who just look for a oneliner to replace the old method (and are not intersted in my example)? — ptstone, Nov 09 '18 at 17:01

Apache POI 4.0: XSSFColor from java.awt.Color

1 Answers1