Multi-line text #156
Comments
It is possible to do that with libxlsxwriter by using a format with If 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". |
|
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... |
In that case libxlsxwriter does the same thing that Excel would do and converts "\r" to the XML escape You can test that yourself by inserting "\r" or "\r\n" into a cell in Excel and looking at the output XML. |
|
If I add linebreak in Excel, I see 0D 0A in XML. Why I can not do this with lib?... |
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: $ xmllint --format shared_strings01/xl/sharedStrings.xml | head -50
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="126" uniqueCount="126">
<si>
<t>_x0000_</t>
</si>
<si>
<t>_x0001_</t>
</si>
<si>
<t>_x0002_</t>
</si>
<si>
<t>_x0003_</t>
</si>
<si>
<t>_x0004_</t>
</si>
<si>
<t>_x0005_</t>
</si>
<si>
<t>_x0006_</t>
</si>
<si>
<t>_x0007_</t>
</si>
<si>
<t>_x0008_</t>
</si>
<si>
<t xml:space="preserve"> </t>
</si>
<si>
<t xml:space="preserve">
</t>
</si>
<si>
<t>_x000B_</t>
</si>
<si>
<t>_x000C_</t>
</si>
<si>
<t>_x000D_</t>
</si>
<si>
<t>_x000E_</t>
</si>
<si>
<t>_x000F_</t>And here is the hex: |
|
test.xlsx |
|
And here is the hex: |
|
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". |
|
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 |
|
Let's take a step back and look at this from a different point of view. I created a small program like this: #include "xlsxwriter.h"
int main() {
lxw_workbook *workbook = workbook_new("wrap.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_format *wrap_format = workbook_add_format(workbook);
format_set_text_wrap(wrap_format);
worksheet_write_string(worksheet, 0, 0, "123\n456", wrap_format);
workbook_close(workbook);
return 0;
}When I run it it creates the attached file: Which looks like this:
What happens when you open it in your version of Excel? |
|
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 |
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. |
|
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 |
I did check this. I create a file with "abc\r\ndef" in a cell using the following Excel macro: Here is the file: And here is the output: So "\r" is converted to 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 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. |
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?
The text was updated successfully, but these errors were encountered: