Sometimes users want to slice and dice data as they wish. In such scenarios, it’s usual to export the data in a tabular format so your users can use any spreadsheet editor and do whatever they want.

Usually, we do that using CSV, right? OpenOffice and other editors can open CSV files flawlessly. Just double-click an CSV file and voilà.

However, Excel has some gotchas. If you double-click a CSV file, Excel will open your data with everything in just one column. Of course you can go to Tools > Import, browse to the file, set up comma as separator and then after some clicks you get your data as you wish. This is not user-friendly at all and this is not something we want to explain to our clients. Our clients must be able to simply double-click the my_data.csv file and see the data well structured.

So, here’s what we do at Plataforma to deal with Excel formats.

It’s TSV, not CSV, dude!

First of all, Excel expects your data with tabulations as fields separator. So, what you need is actually a TSV (tab-separated values).

If you are using FasterCSV, you just need to do:

tsv_str = FasterCSV.generate(:col_sep => "\t") do |tsv|
  tsv << headers
  # append your beautiful data here
end

Keep this in mind. Excel demands tabulations, not commas! But there are worse things to come…

No newline in fields

Excel doesnt’ like when you put “\n” inside fields. Although fields are separated by tabulations, it appears Excel can’t cope with extra newlines.

So, if you have some text fields in your model, beware that it may contain newlines and you need to strip those before exporting your data to Excel.

Forget about UTF-8. Use UTF-16!

One of the most hidden specifications of Excel is that it expects our TSV files to be encoded using UTF-16 Little Endian. Did you know that? Well, we didn’t!

Some sources even say that this is the only Unicode format supported in Excel.

What’s the difference between UTF-8 and UTF-16? UTF-8 is a variable byte encoding in which characters may use up to 4 bytes, however for western languages it usually uses one or two bytes. UTF-16 characters always uses at least 2 bytes (remember that characters and bytes are two different things in Unicode “slang”). Generally speaking, UTF-16 takes more space in the filesystem (most of the time).

There’s also the Little Endian part. UTF-16 always uses a pair of bytes to represent a character, however we need to know what’s the correct order of those bytes. We won’t get into details here, but the order is indicated by Byte-order Mark (BOM). In practice, the BOM in UTF-16 will add two bytes in the beginning of a file (you can see that in a hexeditor).

One way to convert your TSV string (generated by FasterCSV) is with Iconv, a tool written for the GNU C Library. Luckly, Iconv is well wrapped in pure Ruby joy. It’s inside Ruby’s standard library and you just need to require it.

However, if you convert an string to UTF-16 Little Endian, Iconv will NOT put the BOM in the beginning. This is compatible with the Unicode FAQ. But since Excel is way out of the standards, you must manually insert the BOM.

You can use Iconv just before sending the file to the user in your controller. It will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
require 'iconv'
 
class ProjectsController < ActionController::Base
  BOM = "\377\376" #Byte Order Mark
 
  def index
    @projects = Project.all
    respond_to do |format|
      format.html
      format.csv { export_csv(@projects) }
    end
  end
 
protected
 
  def export_csv(projects)
    filename = I18n.l(Time.now, :format => :short) + "- Projects.csv"
    content = Project.to_csv(projects)
    content = BOM + Iconv.conv("utf-16le", "utf-8", content)
    send_data content, :filename => filename
  end
end

You can see that we are still using CSV as an extension to the file. This is because TSV files are not usually associated with Excel. This excerpt uses a timestamp as the filename, which is generally a good practice.

Wrapping up

So, these are the three rules for dealing with Excel-friendly-CSV:

  1. Use tabulations, not commas.
  2. Fields must NOT contain newlines.
  3. Use UTF-16 Little Endian to send the file to the user. And include a Little Endian BOM manually.

Keep this in mind and you’ll never have to explain to your clients how to open the data export in Excel.

There is one last issue: OpenOffice will NOT open files in the Excel “specification” easily. Google Analytics solves this by showing two links to the user: “Export to CSV” and “Export to Excel”. The first is a regular CSV file and the second is the specially crafted Excel-friendly TSV file.

And blame Microsoft for this odd behaviour.

UPDATE: @danielvlopes pointed us an already existing solution which encapsulates the process described in this post, called csv_builder. You just need to set the @output_encoding to use the “utf-16″ (beware of the BOM).

@jncoward also provided a link to the spreadsheet gem, which handles directly Excel formats. TSV is a much simpler and faster format, but the spreadsheet gem might be useful to you in more complex cases.

Finally, Kieran pointed in the comments that we can have XML spreadsheets, while Niko and Chris told us that we can you can also work with HTML tables in Excel, which is simple as well and even allows formatting.

Tags: , , , ,

This entry was posted on Thursday, September 24th, 2009 at 4:43 pm and is filed under English. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

  • http://k776.tumblr.com Kieran P

    On a project I’m working on, that needs to export the staff data to excel, we use the XML spreadsheet. It works well since Rails already supports XML mime type, and xml builder. Excel and OpenOffice have no trouble opening this type of file.

    http://arydjmal.com/2008/6/8/export-to-excel-in-rails-2 <- Based on that post, but use xml, not xls.

  • http://k776.tumblr.com Kieran P

    On a project I’m working on, that needs to export the staff data to excel, we use the XML spreadsheet. It works well since Rails already supports XML mime type, and xml builder. Excel and OpenOffice have no trouble opening this type of file.

    http://arydjmal.com/2008/6/8/export-to-excel-in-rails-2 <- Based on that post, but use xml, not xls.

  • http://restorm.com Niko

    The easiest way I know is to generate an HTML file with a table and give it an XLS extension. The HTML charset is respected, you can have linebreaks as much as you want, little or big endian is not an issue, th-tags define the column-headers and there are already plenty of solutions to build the file.

    Does anybody know any downsides of this approach?

  • http://restorm.com Niko

    The easiest way I know is to generate an HTML file with a table and give it an XLS extension. The HTML charset is respected, you can have linebreaks as much as you want, little or big endian is not an issue, th-tags define the column-headers and there are already plenty of solutions to build the file.

    Does anybody know any downsides of this approach?

  • Chris

    On my project I save the html table into a file, but give it the xls extension instead of html. e.g table.xls. I then send it to the browser with the Excel mime type, and Excel will open this file and will also format the table like the html table is, so you’ll get boldened headings and cell background colors etc..

    I embed some css styling in the html document so excel can pick it up. Excel doesn’t support all css styles though so youll have to do some playing around to get it right.

    My solution works well for me. OpenOffice will open the file too but only do very minimal formatting, but it still works.

  • Chris

    On my project I save the html table into a file, but give it the xls extension instead of html. e.g table.xls. I then send it to the browser with the Excel mime type, and Excel will open this file and will also format the table like the html table is, so you’ll get boldened headings and cell background colors etc..

    I embed some css styling in the html document so excel can pick it up. Excel doesn’t support all css styles though so youll have to do some playing around to get it right.

    My solution works well for me. OpenOffice will open the file too but only do very minimal formatting, but it still works.

  • http://iain.nl iain

    Beware though if you have to support decimal numbers. Some languages use a comma as decimal seperator (like Dutch) and others a dot (English). Depending on which language you export your csv/xls and which language Excel you have installed, you might end up with “7557″ instead of “75.57″.

    I heard, that the only way to get this right is to generate a real, binary, xls file, and not HTML-table or CSV.

    Yeah, that’s really bad.

  • http://iain.nl iain

    Beware though if you have to support decimal numbers. Some languages use a comma as decimal seperator (like Dutch) and others a dot (English). Depending on which language you export your csv/xls and which language Excel you have installed, you might end up with “7557″ instead of “75.57″.

    I heard, that the only way to get this right is to generate a real, binary, xls file, and not HTML-table or CSV.

    Yeah, that’s really bad.

  • George Guimarães

    Thanks @Kieran, yet another solution to export to Excel. But you must admit that @Niko and @Chris solution is the most attractive so far. :)

    Thanks for the excellent feedback guys, I will update the post!

  • George Guimarães

    Thanks @Kieran, yet another solution to export to Excel. But you must admit that @Niko and @Chris solution is the most attractive so far. :)

    Thanks for the excellent feedback guys, I will update the post!

  • http://atog.be Koen Van der Auwera

    Some time ago I created a Rails plugin to generate xls documents by using rxls templates. It’s based on the Spreadsheet gem and works by adding just a simple template.

    http://github.com/10to1/spreadsheet_on_rails

  • http://atog.be Koen Van der Auwera

    Some time ago I created a Rails plugin to generate xls documents by using rxls templates. It’s based on the Spreadsheet gem and works by adding just a simple template.

    http://github.com/10to1/spreadsheet_on_rails