Issue with escaping CR when opening xlsx file with LibreOffice Calc

[citel-tim]

Hi,

I am using XlsxWriter to do set a column heading that includes a line break.
e.g.

sheet.merge_range(r, 6, r, 7, "Department charges\r\n@ Department pricing")

I am running Linux and using LibreOffice Calc to open the file that is created.
I see _x000D_ displayed in the cell for the CR from the \r
I understand that this is due to escaping the CR (in _write_si in sharedstrings.py)

If I open the xlsx file in Excel the cell displays as intended.
If I then save the file from Excel and open it in LibreOffice Calc then the cell displays as intended.

Comparing the file written by xlsxwriter and the file saved from Excel shows that Excel does not escape the CR but leaves it as the single byte with the value 0x0D

I understand that this may be a failing of LibreOffice Calc, but I believe that if xlsxwriter were modified to NOT escape CR then the result would be accepted by both LibreOffice Cal and Excel, and would agree with what Excel writes itself.

I am using Python version 2.7.15 and XlsxWriter 0.9.6 and LibreOffice Calc 6.0.7.3

Of course I should update xlsxwriter, but the escaping appears to be the same in the latest version.

Thank you for a very useful library!

[jmcnamara]

Hi,

You probably won't want to hear, or believe, this but this isn't an XlsxWriter issue.

Xlsxwriter is correctly encoding the character \r as_x000D_. Excel also does this if you generate the \r in the file (which is generally only possible using VBA). There is a test for this in the code base against a file created in Excel that contains all the characters from 0-127 and \r is definitely encoded as _x000D_ in that Excel file.

Also if you google https://www.google.com/search?q=_x000D_+excel you will see that a lot of people complain about Excel (not xlsxwriter) doing this when they dump data from SQL or other sources.

So you should sanitise your data to not include \r unless you specifically want that character (in which case it will be encoded in the file as _x000D_.

If I open the xlsx file in Excel the cell displays as intended.

The is because Excel translates \r\n to \n at the C IO layer.

Closing.

Comparing the file written by xlsxwriter and the file saved from Excel shows that Excel does not escape the CR but leaves it as the single byte with the value 0x0D

I.e, it removed the \r.

So, all in all, this isn't an XlsxWriter issue. Just sanitise your data and you'll get the output you expect in Excel and LibreOffice.

Closing,

John

[drag05]

Just sanitise your data and you'll get the output you expect in Excel and LibreOffice.

... or use Excel or free WPS Spreadsheets which render correctly.