Line breaks are not supported (Excel Mac 2011)

[niallsmart]

Axlsx 2.0.1 does not support line breaks in cells, as tested in Excel Mac 2011.

Test case is here:

https://gist.github.com/niallsmart/6878157

[kbaum]

I am running into this issue as well.

[kbaum]

@niallsmart I rolled back to the merge fix #127 and your test case still doesn't work. I am also on Mac 2011.. perhaps this never worked?

[noniq]

You have to use shared strings to make multiline strings work correctly (don’t ask me why …). Just add the following line to your test case:

package.use_shared_strings = true

[niallsmart]

@noniq good to know, thanks.

[kbaum]

@niallsmart - Maybe a silly question, but how did you figure that out? What are the best resources for understanding how to get microsoft excel to do what you want? Seems very challenging to say the least.

thx!

[noniq]

Are you referring to the use of shared strings? I found that out by comparing an file created by Excel itself (linebreaks working) with the one written by Axlsx (linebreaks not working) – the only difference I noticed was that Excel’s file used shared strings.

I think it’s just a bug in Excel 2011 for Mac that it seems to not support linebreaks in files without shared strings. I guess all versions of Excel (maybe Open Office, too?) always create files with shared strings, so the bug only shows when opening files created with tools like Axlsx.

[kbaum]

@noniq - How do you go about that comparison when axlsx generates a binary file? Also, what does "shared strings" refer to?

thx!

[niallsmart]

@kbaum well, I was not actually the person who figured that out :) Personally I have found StackOverflow and the discussions on GitHub to be useful for debugging problems. Shared strings is a reference to the package level setting referred to in an earlier comment on this issue.

[kbaum]

Right.. i get that it's a setting.. but beyond that, what does it mean?

[niallsmart]

@kbaum it's a technique used to reduce file size by storing references to commonly used strings in a table, instead of repeating them inline. you can find more information here: http://msdn.microsoft.com/en-us/library/office/gg278314.aspx

[noniq]

@kbaum xlsx files are in a format called Office Open XML (OOXML) – that means they are in fact just a ZIP file containing a bunch of XML files. You can change the extension from xlsx to zip to simply extract and view the contents.

For more info about OOXML see https://en.wikipedia.org/wiki/Office_Open_XML

[kbaum]

@noniq - wow.. that is super helpful. Thanks for your help!

[noniq]

@kbaum @niallsmart To round this up: Can you confirm that multiline strings in Excel 2011 Mac work correctly for both of you if the file is written with use_shared_strings = true?

[kbaum]

@noniq - yes.. use_shared_strings = true did the trick!

[noniq]

Summary: This appears to be a bug in Excel Mac 2011, use_shared_strings = true can be used as workaround.

[futbolpal]

did anyone find this created a very very wide column?

[NoSkillGuy]

p = Axlsx::Package.new
p.use_shared_strings = true
p.workbook.add_worksheet(:name => "multiple_lines") do |sheet|
wrap = p.workbook.styles.add_style alignment: {wrap_text: true}
sheet.add_row arr, style: wrap
end

Adding this style will add new line to cell. I tried it in Mac OSX 10.11.4

[benedikt-voigt]

In case you use join to concatenate lines, take double quotes (somehow single quotes don't work):
['Foo', 'Bar'].join("\n")