[Bug 61048] New: Newlines in cells not rendering with SXSSF

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] New: Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

            Bug ID: 61048
           Summary: Newlines in cells not rendering with SXSSF
           Product: POI
           Version: 3.15-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SXSSF
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 34957
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34957&action=edit
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 "
" 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 {
    @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).

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

--- Comment #1 from Michael Smith <[hidden email]> ---
Created attachment 34958
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34958&action=edit
SXSSF - newline not rendered

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

--- Comment #2 from Michael Smith <[hidden email]> ---
Created attachment 34959
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34959&action=edit
SXSSF - &#xa; changed to &#xd; - newline renders correctly

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
             Status|NEW                         |NEEDINFO

--- Comment #3 from Dominik Stadler <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

Ivan <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #4 from Ivan <[hidden email]> ---
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?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

--- Comment #5 from Ivan <[hidden email]> ---
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'

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Bug 61048] Newlines in cells not rendering with SXSSF

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61048

PJ Fanning <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |FIXED
             Status|NEW                         |RESOLVED

--- Comment #6 from PJ Fanning <[hidden email]> ---
Added a fix using https://svn.apache.org/viewvc?view=revision&revision=1804596

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...