Sheetjs: Newline characters in cells of written workbooks

[notatestuser]

When values v of cells contain unix line endings \n they do not become line breaks in the corresponding cells of the written workbook. However, when the Windows carriage return newline combos \r\n are used Excel appears to properly break the lines when a cell containing them is double clicked (edited).

So there appear to be two things to potentially address here:

1. Unix line endings \n do not become new lines at all in the written file
2. Windows line endings \r\n only become newlines in Excel when the cell is edited. This may be a shortfall in Excel for Mac 2011 and may not apply to the Windows version.

[SheetJSDev]

Try writing with the shared string table (bookSST:true option when you write).

I explored Excel 2011 a bit and found:

1) Shared strings table parser apparently treat both forms of newline (literal \n as well as the escaped _x000d_) as new lines

2) Inline strings parser ignores the literal newline and only process the _x000d_ encoded newline

3) Shared strings parser assumes whitespace is preserved and inline strings parser assumes whitespace is not preserved. The xml:space="preserve" attribute is completely ignored in both cases.

You'd think that Excel would use one unified XML parser, but it appears that is not the case :/ The fix here is straightforward, but the question still remains: should the writer handle both types of newline (\n and \r\n)? A literal encoding of both in the shared string table is treated as two newlines, not one, so there is some ambiguity there

[tophsic]

Using booSST:true seemed to work for me when opening xlsx file on Linux and LibreOffice but didn't work on Excel on Win 10.

[reviewher]

@tophsic @dferer can you share either a code sample or a bad file generated by the library?

[lumio]

@reviewher here is a code example.

It works fine with macOS numbers. I didn't test it with LibreOffice yet. But Excel 15.17 under macOS ignores the line breaks at first, but adds them as soon as you double click on the cell.

[qqilihq]

Did someone find a workaround? We the same issue on Excel macOS and Excel Windows -- text is shown in a single line and changes on double click.

Apple Numbers shows the file correctly.

[zaddok]

I have the same problem. Did anyone find a workaround? \n and \r\n don't actually break in Excel on Mac at least.

[fozcode]

I could only get this working correctly using the Pro edition where the saving of cell styles is supported. It needed: \n in the string value, bookSST: true, cellStyles: true in XLSX.write, and cell.s = { alignment: { wrapText: true } } in the cell style.

[SheetJSDev]

To summarize: merely placing a newline character is insufficient. Excel requires the text wrapping style attribute to be applied to the cell, otherwise it is rendered like a whitespace character. This is a part of our Pro builds.

@fozcode I don't believe bookSST: true is required, if you have a bad case email us.

[fozcode]

@SheetJSDev I've tested again and you are correct, bookSST: true is not required for Excel.

In LibreOffice a multi-line string is displayed on one line unless you set bookSST: true, which I guess is a LibreOffice bug. When you click into the cell the lines are then shown correctly.

[SheetJSDev]

@jefleponot Reviewing the spec http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html

<table:table-cell> (9.1.4) has <text:p> (5.1.3) children

<text:line-break> (6.1.5) is a valid child of <text:p>, which was originally interpreted to be a newline character.

You're proposing we create separate paragraphs for each logical line, which is fine but I'm curious why the line break doesn't work

[jefleponot]

Could I propose a PR ? Could you accept it ?

[jefleponot]

https://bugs.documentfoundation.org/show_bug.cgi?id=43107

[lukaselmer]

To summarize: merely placing a newline character is insufficient. Excel requires the text wrapping style attribute to be applied to the cell, otherwise it is rendered like a whitespace character. This is a part of our Pro builds.

@fozcode I don't believe bookSST: true is required, if you have a bad case email us.

@SheetJSDev is there a solution for the community edition?