title: What's the best way to export UTF8 data into Excel?
body: <p>So we have this web app where we support UTF8 data. Hooray UTF8. And we can export the user-supplied data into CSV no problem - it's still in UTF8 at that point. The problem is when you open a typical UTF8 CSV up in Excel, it reads it as ANSII encoded text, and accordingly tries to read two-byte chars like ø and ü as two separate characters and you end up with fail.</p>
  
  <p>So I've done a bit of digging (the Intervals folks have a interesting post <a href="http://www.myintervals.com/forum/discussion/280/making-csv-data-exports-with-utf8-more-excel-friendly/" rel="noreferrer">about it here</a>), and there are some limited if ridiculously annoying options out there. Among them:</p>
  
  <ul>
  <li>supplying a UTF-16 Little Endian TSV file which Excel will interpret correctly, but which won't support multi-line data</li>
  <li>supplying the data in an HTML table with an Excel mime-type or file extension (not sure if this option supports UTF8)</li>
  <li>there are some three or four ways to get XML data into the various recent versions of excel, and those would support UTF8, in theory. SpreadsheetML, using custom XSLT, or generating the new Excel XML format via templating.</li>
  </ul>
  
  <p>It looks like no matter what, I'm probably going to want to continue offering a plain-old CSV file for the folks who aren't using it for Excel anyway, and a separate download option for Excel. </p>
  
  <p>What's the simplest way of generating that Just-For-Excel file that will correctly support UTF8, my dear Stack Overflowers? If that simplest option only supports the latest version of Excel, that's still of interest.</p>
  
  <p>I'm doing this on a Rails stack, but curious how the .Net-ers and folks on any frameworks handle this. I work in a few different environments myself and this is definitely an issue that will becoming up again.</p>
  
  <p><em>Update 2010-10-22:</em> We had been using the Ruport gem in our time-tracking system <a href="http://keeptempo.com" rel="noreferrer">Tempo</a> to provide the CSV exports when I first posted this question. One of my coworkers, Erik Hollensbee, threw together a quick filter for Ruport to provide us with actual Excel XSL output, and I figured I'd share that here for any other ruby-ists:</p>
  
  <pre><code>require 'rubygems'
  require 'ruport'
  require 'spreadsheet'
  require 'stringio'
  
  Spreadsheet.client_encoding = "UTF-8"
  
  include Ruport::Data
  
  class Ruport::Formatter::Excel &lt; Ruport::Formatter
    renders :excel, :for =&gt; Ruport::Controller::Table
  
    def output
      retval = StringIO.new
  
      if options.workbook
        book = options.workbook
      else
        book = Spreadsheet::Workbook.new
      end
  
      if options.worksheet_name
        book_args = { :name =&gt; options.worksheet_name }
      else
        book_args = { }
      end
  
      sheet = book.create_worksheet(book_args)
  
      offset = 0
  
      if options.show_table_headers
        sheet.row(0).default_format = Spreadsheet::Format.new(
          options.format_options || 
          { 
            :color =&gt; :blue,
            :weight =&gt; :bold,
            :size =&gt; 18
          }
        )
        sheet.row(0).replace data.column_names
        offset = 1
      end
  
      data.data.each_with_index do |row, i|
        sheet.row(i+offset).replace row.attributes.map { |x| row.data[x] }
      end
  
      book.write retval
      retval.seek(0)
      return retval.read
    end
  end
  </code></pre>