java - New line ignored in XSSFRichTextString

Question

:EDIT START:

Should anyone stumble across this q then here's a small heads up.

After getting newLines to work I wanted to set the row height to match the content. To do this I simply set the row height to the number of newLines in the cellValue. However when creating a spreadsheet using poi it seems to be matter if you apply the cellStyle before or after the cellValue. In my case it only worked if I apply the cellStyle before adding the cellValue.

Oh and remember that rowHeight is defined as 1/256 of a character(!?!?!) so set rowHeight to

(short)(numberOfNewLines*256)

:EDIT END:

I have this piece of code that generates an Excel spreadsheet. The contents of some of the cells are created using

cell.setCellValue(new XSSFRichTextString(header));
cell.setCellStyle(cs);

However if I set

header = "Estimated
Duration";

then the new line is not rendered in the resulting spreadsheet. It just comes out as "EstimatedDuration".

I'm using Apache POI v3.9.

Any suggestions?

Answer 1

Let's start with how to put a newline in an Excel spreadsheet using Excel. According to this article, edit the cell contents, placing the cursor at the desired newline location, and do Alt + Enter. The effect of this is to introduce a newline character into the string and turn on "Wrap Text" in the cell.

You have the newline character in the string already, but you'll need to add to your code to modify your CellStyle to turn on wrapped text, with the setWrapText method.

String header = "Estimated
Duration";
cell.setCellValue(new XSSFRichTextString(header));

// Add this line to turn on wrapped text.
cs.setWrapText(true);

cell.setCellStyle(cs);

This appears to test successfully on .xls and .xlsx files.

It appears that Excel will only wrap text, newline present or not, if the cell style has the "wrap text" property set to true.