mirror of
https://github.com/jmcnamara/libxlsxwriter.git
synced 2026-05-15 14:15:54 -06:00
[GH-ISSUE #156] Multi-line text #129
Labels
No labels
awaiting user feedback
bug
cmake
cmake
docs
feature request
in progress
long term
medium term
medium term
pull-request
question
question
ready to close
short term
under investigation
wontfix
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set.
Reference: github-starred/libxlsxwriter#129
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @RaFaeL-NN on GitHub (Mar 16, 2018).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/156
Originally assigned to: @jmcnamara on GitHub.
I have a question from users of my wrapper
In Excel is possible to enter multi-line text
In sharedStrings.xml linebreak writes as 0D 0A
but if I try to do something like this by lib (write_string or another functions), 0D is replacing by lxw_escape_control_characters to some string and I get not what i want :(
Is it possible to delete changing 0D (hex) to string?
@jmcnamara commented on GitHub (Mar 16, 2018):
It is possible to do that with libxlsxwriter by using a format with
format_set_text_wrap()set: http://libxlsxwriter.github.io/format_8h.html#a56d55dd9257d8f0645c62b296d2c196dIf you want to control where the text wraps you can add "\n" to the string as shown in the example in that doc link.
Yes and no. Excel just uses "\n" 0x0A in the string to indicate a wrap (along with a format in the cell). However, this will be converted to "\r\n" 0x0D0A by the Windows C libraries when writing to a file, which is what you see in your hexdump. The "\r" is stripped when the file is read (by the same C libs) so Excel only ever sees the "\n".
So you should only use "\n" within your program/wrapper and not "\r\n".
@RaFaeL-NN commented on GitHub (Mar 16, 2018):
What a problem to pass 0D to xml? Data already may be stored in some DB as a multiline text with "\r\n" or send by some protocol...
@jmcnamara commented on GitHub (Mar 16, 2018):
In that case libxlsxwriter does the same thing that Excel would do and converts "\r" to the XML escape
_x000D_.You can test that yourself by inserting "\r" or "\r\n" into a cell in Excel and looking at the output XML.
@RaFaeL-NN commented on GitHub (Mar 16, 2018):
If I add linebreak in Excel, I see 0D 0A in XML. Why I can not do this with lib?...
@jmcnamara commented on GitHub (Mar 16, 2018):
Please attach the file you created.
Here is a file from the test suite that was created in Excel and which contains all the characters from decimal 1 to 127: https://github.com/jmcnamara/libxlsxwriter/blob/master/test/functional/xlsx_files/shared_strings01.xlsx
Here is the XML file:
And here is the hex:
@RaFaeL-NN commented on GitHub (Mar 16, 2018):
test.xlsx
I think, your test with one character is bad and 0D as one character is bad in XML too. Try 0D 0A together
@RaFaeL-NN commented on GitHub (Mar 16, 2018):
And here is the hex:
@jmcnamara commented on GitHub (Mar 16, 2018):
The file you attached doesn't contain "\r" in the cell. If I copy and paste the test into a hex editor this is what I get:
There is no "\r".
Also, if I view the file with a Microsoft tool for inspecting Open XML files that can reflect it back to code this is what it shows:
Again, no "\r".
As I said above the "\r" is added by the Windows C (io) libraries when the file is written.
However, I don't even know why we are arguing about this. If you want to wrap text just follow the example in the docs using "\n".
@RaFaeL-NN commented on GitHub (Mar 16, 2018):
0D is in hex in your test file... If, as you say, the "\r" is added by the Windows C (io) libraries when the file is written, why it's not added if file is written by lib?... Why I can not repeat test file (shared_strings01.xlsx) containg 0D 0A with lib? I think hex is more true than viewer, what replace hex characters with strings like "\n" and so on
@jmcnamara commented on GitHub (Mar 17, 2018):
Let's take a step back and look at this from a different point of view.
I created a small program like this:
When I run it it creates the attached file:
wrap.xlsx
Which looks like this:
What happens when you open it in your version of Excel?
@RaFaeL-NN commented on GitHub (Mar 17, 2018):
It works just because it works, not because it must works. 0D 0A is a standard in Windows, so multi-line data is stored with this in many and many places and... can not be directly write on the fly with lib. Yes, I can replace 0D 0A with 0A before I send it to lib, but why I can not just write this directly to xlsx without replacing in situation, where original Excel do this? May be 0D does not present in runtime (I don't know), but Excel add it on saving file (not Windows IO libs) (like it replace ";" with "," in formulas). Excel store data with 0D 0A and lib, I think, must do this. So, 0D must be replaced by string if it presents as 1 char and must NOT be replaced in pair with 0A. In addition to something else, it saves more times: first for add replacing in code for any programmers who works with multi-line data, and second on replacing at exporting data
@jmcnamara commented on GitHub (Mar 17, 2018):
So the example file works for you?
This is going around in circles and isn’t helpful. Let’s establish if there is an issue first and then fix that issue.
So first let’s look at if the above example works for you in Excel and then let’s look at if it works via you wrapper.
@RaFaeL-NN commented on GitHub (Mar 18, 2018):
I add a few lines of code into wrapper for replacing 0D 0A with 0A on the fly. I think it's a kludge, but have no another way out in situation, where all multi-line text data is stored with 0D 0A and is logically corrupted in xlsx without this kludge. I hope, you'll test not only 1 characters like in https://github.com/jmcnamara/libxlsxwriter/blob/master/test/functional/xlsx_files/shared_strings01.xlsx but 2 characters in pairs too
@jmcnamara commented on GitHub (Mar 18, 2018):
I did check this. I create a file with "abc\r\ndef" in a cell using the following Excel macro:
Here is the file:
test-25.xlsx
And here is the output:
So "\r" is converted to
_x000D_just like libxlsxwriter does, whether or not it precedes "\n".This behaviour is mentioned a few places on the internet as well such as here or here or here.
The extra "\r" before the "\n" is a factor of the IO libs and isn't related to this issue. You could get the same behaviour from libxlsxwriter on Windows by changing the file open mode from
w+b(binary) tow+orw+t(text mode) (although that would break the image file handling).So in summary I believe libxlsxwriter is behaving the same as Excel and there are tests that show that is the case.
So I'm closing this issue.