1

I am exporting data to Excel by making an erb template for the xls file. This is following the procedure recommended in Railscast 362 for making properly encoded exports.

The output opens fine in Excel for Mac 2011, but in Excel 2007 characters like é appear as é because the UTF-8 encoded file is being interpreted by Excel as Latin1.

According to this answer Excel 2007 will correctly interpret an UTF-8 file if it starts with the right Byte Order Mark (BOM).

Taking at a look at the hex version of the output it seems that indeed it doesn’t have a BOM.

There are a number of sources (like this Plataformatec blog post) that explain how to add a BOM when rendering the data inline in the controller:

BOM = "\377\376"
BOM + Iconv.conv('utf-16le', 'utf8', data)

My question is – is there a way of getting the template correctly interpreted by Excel 2007? Or should I go back to rendering the data in the controller?

Community
  • 1
  • 1
Derek Hill
  • 5,965
  • 5
  • 55
  • 74

1 Answers1

0

I don't know why Excel makes this so hard, but they do.

I tried using that code myself when I ran into this encoding problem. I couldn't figure out how to apply it to what I was working with. So I did a workaround instead.

I downloaded the CSV file as indicated in the Railscast. I then opened the file with TextWrangler, which encodes it properly. Then, I copied and pasted the contents into a blank Excel document (which reads it as text, and thus, does not mess up the encoding). Following that, I selected the column all the data fell under, and then in the menubar selected "Data -> Text to Columns". I then selected a delimited list and entered a comma as the delimiter (it defaults to a tab). Then I finished it up and voila, there is a properly formatted Excel doc.

CodeBiker
  • 2,985
  • 2
  • 33
  • 36