Bug 61048

Summary: Newlines in cells not rendering with SXSSF
Product: POI Reporter: Michael Smith <michael>
Component: SXSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.15-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: XSSF - newline rendered correctly
SXSSF - newline not rendered
SXSSF - &#xa; changed to &#xd; - newline renders correctly

Description Michael Smith 2017-04-27 02:49:40 UTC
Created attachment 34957 [details]
XSSF - newline rendered correctly

Using the guide here:

http://poi.apache.org/spreadsheet/quick-guide.html#NewLinesInCells

I was able to produce a cell with an embedded newline in an XSSF. It looks correct in Excel for Mac.

Using the same code but swapping out XSSF for SXSSF, the newline is rendered as a regular space (in Excel for Mac, at least).

SXSSF serializes the newline as "&#xa;" in the worksheet XML. I found by manually changing this to "&#xd;" or "&#13;" (hex/decimal encodings of carriage return), and rezipping the xlsx, the newline was correctly rendered.

Sample code:

public class NewlineTest {
    @Test
    public void doitStreaming() throws Exception {
        Workbook workbook = new SXSSFWorkbook();
        //Workbook workbook = new XSSFWorkbook();

        CellStyle commentStyle = workbook.createCellStyle();
        commentStyle.setVerticalAlignment(VerticalAlignment.TOP);
        commentStyle.setWrapText(true);

        Sheet sheet = workbook.createSheet("blah");

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellStyle(commentStyle);
        cell.setCellValue("one two three four\nnewline six seven eight");

        workbook.write(new FileOutputStream("/tmp/sxssf.xlsx"));

        workbook.dispose();
    }
}

I'm attaching the output from XSSF (xssf.xlsx), SXSSF (sxssf.xlsx), and the manually adjusted file (sxssf_with_cr.xlsx).
Comment 1 Michael Smith 2017-04-27 02:50:04 UTC
Created attachment 34958 [details]
SXSSF - newline not rendered
Comment 2 Michael Smith 2017-04-27 02:50:35 UTC
Created attachment 34959 [details]
SXSSF - &#xa; changed to &#xd; - newline renders correctly
Comment 3 Dominik Stadler 2017-06-16 20:10:05 UTC
On Linux using LibreOffce "XSSF - newline rendered correctly" and " SXSSF - newline not rendered" are shown correctly, and " SXSSF - &#xa; changed to &#xd; - newline renders correctly" is shown without newline. So here the situation is the other way round, the ones created by POI are both shown correctly, the one with Mac Newlines not.

So it seems there is some platform-dependence introduced.

As you add "\n" in your sample-code, what happens if you use "\r" or "\n\r"? I expect that both ways would show correctly on Mac, the second maybe also on Windows and Linux. This would indicate that you can control this in the string that you set.
Comment 4 Ivan 2017-08-08 15:27:35 UTC
I'm experiencing the same issue.

I can confirm that \r or \n\r does not help.

in XSSF \n is encoded as &#xa; - this is ignored on Mac 
in HSSF \n is presented as binary '0A' - this works as new line on Mac

if you open xls document generated by HSSF and save it as xlsx then you get '0D0A' combination for line brakes that works on Mac.

But if I put \r\n in original string, then in XSSF I get '&#xa;&#xa;' (why both \r and \n are converted to the same &#xa;?) which again ignored by Mac.

-----------------

BTW Can you suggest any workaround for now?
Comment 5 Ivan 2017-08-09 16:33:13 UTC
Here is the cause:

org.apache.poi.xssf.streaming.SheetDataWriter#outputQuotedString

case '\n':
case '\r':
  if (counter > last) {
     _out.write(chars, last, counter - last);
  }
  _out.write("&#xa;");
  last = counter + 1;
  break;


There should be _out.write("&#xd;") for '\r'
and _out.write("&#xa;") for '\n'
Comment 6 PJ Fanning 2017-08-09 18:38:34 UTC
Added a fix using https://svn.apache.org/viewvc?view=revision&revision=1804596