Bug 61048 - Newlines in cells not rendering with SXSSF
Summary: Newlines in cells not rendering with SXSSF
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-04-27 02:49 UTC by Michael Smith
Modified: 2017-08-09 18:38 UTC (History)
0 users

XSSF - newline rendered correctly (3.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-04-27 02:49 UTC, Michael Smith
SXSSF - newline not rendered (3.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-04-27 02:50 UTC, Michael Smith
 changed to 
 - newline renders correctly (4.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-04-27 02:50 UTC, Michael Smith

Note You need to log in before you can comment on or make changes to this bug.
Description Michael Smith 2017-04-27 02:49:40 UTC
Created attachment 34957 [details]
XSSF - newline rendered correctly

Using the guide here:


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 "
" in the worksheet XML. I found by manually changing this to "
" or "
" (hex/decimal encodings of carriage return), and rezipping the xlsx, the newline was correctly rendered.

Sample code:

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

        CellStyle commentStyle = workbook.createCellStyle();

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

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

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


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]
 changed to 
 - 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 - 
 changed to 
 - 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 
 - 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 '

' (why both \r and \n are converted to the same 
?) 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:


case '\n':
case '\r':
  if (counter > last) {
     _out.write(chars, last, counter - last);
  last = counter + 1;

There should be _out.write("
") for '\r'
and _out.write("
") 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