Bug 48877 - XSSFRichTextString: applyFont removes line break
XSSFRichTextString: applyFont removes line break
Status: RESOLVED FIXED
Product: POI
Classification: Unclassified
Component: XSSF
3.7-FINAL
PC All
: P2 normal (vote)
: ---
Assigned To: POI Developers List
:
Depends on:
Blocks:
  Show dependency tree
 
Reported: 2010-03-09 12:50 UTC by sm01
Modified: 2011-06-25 08:46 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description sm01 2010-03-09 12:50:36 UTC
If you use an XSSFWorkbook together with a RichTextString, that applies a font to a string containing a new line char ("\n"), the line-break is ignored. 
Here is a slightly modified code sample of the "Quick-Guide - Using newlines in cells" http://jakarta.apache.org/poi/hssf/quick-guide.html#NewLinesInCells:

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        
        Font font1 = wb.createFont();
        font1.setColor((short) 20);

        Row row = sheet.createRow(2);
        Cell cell = row.createCell(2);
        
        RichTextString richTextString = wb.getCreationHelper().createRichTextString("Use \n with word wrap on to create a new line");
        richTextString.applyFont(0, 3, font1);
        cell.setCellValue(richTextString);

        //to enable newlines you need set a cell styles with wrap=true
        CellStyle cs = wb.createCellStyle();
        cs.setWrapText(true);
        cell.setCellStyle(cs);

        //increase row height to accomodate two lines of text
        row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));

        //adjust column width to fit the content
        //sheet.autoSizeColumn((short)2);

        FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
        wb.write(fileOut);
        fileOut.close();
Comment 1 sm01 2010-03-09 12:55:31 UTC
After running the sample code and opening the generated xslx-File, there is no line break in the Cell with value "Use \n with word wrap on to create a new line";
Comment 2 Nick Burch 2010-03-09 14:40:10 UTC
XmlBeans will sometimes eat linebreaks if you don't tell it the right options

Any chance you could create a short junit testcase that creates a new file with the line breaks supposed to be there, saves it and loads it again, and checks for the line break? That'll give us something to debug with, as well as a unit test to verify it stays fixed once we track down the issue
Comment 3 Jack 2010-12-31 15:04:23 UTC
POI 3.7 has the same issue.
Comment 4 Nick Burch 2011-03-04 12:06:59 UTC
I've just tried this with svn trunk. I've added a slightly extended versionn of your unit test, which shows that the text remains unchanged on save / reload, and the newlines (and even trailing spaces) are fine. When I save the file out and load it up, I see the newlines in the cell as expected

If you still have problems on a recent svn / nightly build, please include an updated unit test that shows the problem.
Comment 5 Graham Smith 2011-04-20 07:07:36 UTC
I've just run into this issue in 3.7 so I upgraded to 3.8-beta2 and I'm still experiencing what seems to be strange behaviour. The code below shows the problem which I think might be related to the line-break "\n" being treated as two characters.

For example, on row zero it's impossible to completely style the word "Incorrect" and also end up with a line break. I would have expected "Incorrect" to be fully styled when i == 2 then a line-break and then the text "Line-Breaking" what actually happens is "Incorrect" is styled, the line-break is removed or ignored and "Line-Breaking" is placed hard up against "Incorrect". Interestingly when i == 4 so that the first character of "Line-Breaking" is styled the line-break re-appears.

I've worked around this issue by putting spaces around the line-break character which lets me style each part individually at the cost of some extra space but that's not so noticeable when the text is centred.

XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();
headerStyle.setWrapText(true);

Font largeHeaderfont = wb.createFont();
largeHeaderfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
largeHeaderfont.setFontHeightInPoints((short) 14);

CreationHelper createHelper = wb.getCreationHelper();

Row row0 = sheet.createRow(0);
for( int i = 0, n = 7; i < n; i++ ) {
	RichTextString headerString = createHelper.createRichTextString("Incorrect\nLine-Breaking");
	headerString.applyFont(0, 7 + i, largeHeaderfont);

	Cell cell = row0.createCell(i);
	cell.setCellStyle(headerStyle);
	cell.setCellValue(headerString);
}

Row row1 = sheet.createRow(1);
for( int i = 0, n = 7; i < n; i++ ) {
	RichTextString headerString = createHelper.createRichTextString("Incorrect \n Line-Breaking");
	headerString.applyFont(0, 7 + i, largeHeaderfont);

	Cell cell = row1.createCell(i);
	cell.setCellStyle(headerStyle);
	cell.setCellValue(headerString);
}
Comment 6 Nick Burch 2011-05-06 08:05:28 UTC
I've beefed up the unit test in TestXSSFBugs, and I can do the styling you're after. With it, I can create a string with a default styling, and two different styles one of which runs to a new line.

Can you give that test a go (uncomment the file output bit) and see if that gives what you need?
Comment 7 Yegor Kozlov 2011-06-25 08:46:47 UTC
Fixed in r1139505

XSSFRichTextString didn't respect leading or trailing line breaks. The problem was in how POI preserved space characters: each format run is represented by a xml element and if a run starts or ends with a space, it is important to set the  xml:space="preserve" attribute, otherwise XML serializer will strip the space off.

The old code properly handled the whitespace character (' '), but not line breaks (actually any characters from the 'space' family should be preserved - line breaks, tabs, etc.).

The old code looked something like this:
  if (text.startsWith(" ") || text.endsWith(" ")) {
     // set  xml:space="preserve"
  }

It evidently ignored line breaks. I changed it as follows and it did the trick:

  if(Character.isWhitespace(firstChar) || Character.isWhitespace(lastChar)) {
     // set  xml:space="preserve"
  }


Yegor