Writing xlsx file using POI

Question

I have an app that generates an XSSFWorkbook and fills it with budget data. When it is done it writes out a file:

try {
 FileOutputStream fileOut = new FileOutputStream("/path/to/my/file/myfilename.xlsx");
 wb.write(fileOut);
 fileOut.close();
} catch (IOException ioe) {
 logger.error("Error writing spreadsheet", ioe);
}

So far so good. I'm working on a Linux box, but the output is intended for people on Windows boxes, so it has to be an xlsx. The workbook generates as expected, and when I open it up in LibreOffice it looks exactly as I expect. Then I send it to my coworkers, and when they open it up in Excel, most of the cell formatting is gone. The cells with currency formatting are just raw numbers. Cells formatted to be percentages (most of them) are just raw numbers.

Any ideas what I might be doing wrong? Thanks!

One of the cell styles:

XSSFCellStyle percent = wb.createCellStyle();
percent.setDataFormat(wb.createDataFormat().getFormat("0.0%"));

Later used here:

header.createCell(cn).setCellValue("%");
sheet.setDefaultColumnStyle(cn, percent);

There are a bunch of these, but I suspect whatever I'm doing wrong with one is the same problem with all of them, so I'll just present one instead of 200 lines of them. ;)

Update: At rgettman's suggestion I tried setting the style to each individual cell after creating it. This works. Thanks rgettman!

Answer 1

I'm not sure why LibreOffice would open that with formatting intact, because when you use the setDefaultColumnStyle method, the documentation states that it only applies the formatting to cells created after you call that method.

POI will only apply this style to new cells added to the sheet.

Try making the call to setDefaultColumnStyle before you create your cell. Let's see if that resolves the issue.