New line in cell can create _x000D_

[yinyangxu]

Some types of new lines in excel-cell looks ok in excel, but adds
x000D when imported with Roo::Excelx

which much like the issue here tidyverse/readxl#51

[stevendaniels]

Can you create a gist for this issue (sample gist)? Here are some instructions for creating such a gist.

1. Create a gist with code that creates the error.
2. Clone the gist repo locally, add a stripped down version of the offending spreadsheet to the gist repo, and push the gist's changes master.
3. Paste the gist url here.

[yinyangxu]

Hi, here is my gist https://gist.github.com/yinyangxu/4fb84a0c6fd44784c3a4.

[stevendaniels]

@yinyangxu Actually, Roo didn't add the _x000D_, it was in the XML inside of your sample XLSX file. The program you used to create this Excel file added these values. What program was used to create the file?

I've submitted a pull request (#231) that fixes the issue.

[simonoff]

What the reason then in #231 ?

[stevendaniels]

I don't know for certain what causes the _x000D_ issue, but it's not Roo. It's possibly caused by the program that created the xlsx file. I saw related issues in OOXML here.

From my understanding, these characters are invalid representations of normal strings or characters. In this case _x000D_ == \n .

It's also possible that more of these issues might appear in the future. It seems pretty rare though.

[yinyangxu]

@stevendaniels I use 'writeexcel' lib to create the Excel file which contains the new line in cell. Then i open it use MS Excel 2007 and save it as xlsx format. It's ok when reading the xlsx file with python xlrd lib or MS Excel 2007, but it shows the bug character x000D when i using roo lib.

[stevendaniels]

This sounds like it may be an issue with that library (and the library it is based on).
Can you post a sample of the code you use when creating the line using writeexcel?

[yinyangxu]

Maybe. But the bug.xlsx file looks ok when reading it with MS Excel 2007 or python xlrd lib except for roo.
I just use "worksheet.write(row, 8, line)" to create a xls format Excel file. Then open it with MS Excel 2007 and add some new line in a cell. Finally i save it as xlsx format. Is it possible that the problem results from converting xls to xlsx?

[stevendaniels]

@yinyangxu Sounds to me like xls to xlsx conversion with Chinese text might be what's causing the issue. Thanks for your help.

I've merged a fix to master.

[kaluznyo]

I Just have same bug, but with Excel 2011 ou 2016 for mac. I create a file, with 2 sheet, One with data, a text with new line. In my first file i use VLOOKUP to get text from the other sheet. I save with xls then xlsx.
And this add X00D (I can see it directly in worksheets/sheet1.xml. The fix (#231) don't work because it's not in sharing string, but directly in sheet. If I use your patch in SheetDoc.extract_celles, it's work fine (But I don't know if it's the good place to do this)

Could you use this patch for all sheet ?

[jarredholman]

This issue was fixed incorrectly.
x000D is an escape for \u000D aka Carriage Return (\r not \n)

[jcnaud]

This issue was fixed incorrectly.

I have the save ploblem and the issue concern the "\r" implementation on xlsx.
Case 1 with "\n":
When i write in cell: "KM\nSTART"
On LibreOffice , i have :

"KM
START"

On Excel i have:

KMSTART

Case 2 with "\r\n":
On LibreOffice i have :

KM_x000D_
START

On Excel i have:

KMSTART