将UTF8数据导出到Excel的最佳方式是什么?

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.

我们有一个支持UTF8数据的web应用程序。万岁UTF8。我们可以将用户提供的数据导出到CSV中，没问题，那时它还在UTF8中。问题是当你打开一个典型UTF8 CSV在Excel中,它读取ANSII编码文本,因此试图读取两字节字符ø和u等两个单独的人物和你最终失败。

So I've done a bit of digging (the Intervals folks have a interesting post about it here), and there are some limited if ridiculously annoying options out there. Among them:

所以我做了一些挖掘(这里的interval用户有一个关于它的有趣的帖子)，并且有一些有限的，甚至是非常烦人的选项。其中包括:

supplying a UTF-16 Little Endian TSV file which Excel will interpret correctly, but which won't support multi-line data
提供一个UTF-16小Endian TSV文件，Excel将正确解释，但不支持多行数据
supplying the data in an HTML table with an Excel mime-type or file extension (not sure if this option supports UTF8)
使用Excel mime类型或文件扩展名在HTML表中提供数据(不确定此选项是否支持UTF8)
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.
有三到四种方法可以将XML数据转换到excel的各种最新版本中，这些方法理论上支持UTF8。使用自定义XSLT，或者通过模板生成新的Excel XML格式。

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.

无论如何，我可能会继续为那些不使用它的人提供一个普通的CSV文件，并为Excel提供一个单独的下载选项。

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.

最简单的生成支持UTF8(我亲爱的栈顶花)的excel文件的方法是什么?如果这个最简单的选项只支持最新版本的Excel，那么仍然值得关注。

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.

我在Rails堆栈上做这个，但是我想知道. net -ers和任何框架上的人是如何处理这个问题的。我自己在不同的环境中工作，这绝对是一个会再次出现的问题。

Update 2010-10-22: We had been using the Ruport gem in our time-tracking system Tempo 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:

更新2010-10-22:当我第一次发布这个问题时，我们一直在我们的时间跟踪系统节奏中使用Ruport gem来提供CSV导出。我的同事埃里克·霍伦斯比(Erik Hollensbee)为Ruport提供了一个快速过滤器，为我们提供了实际的Excel XSL输出。

require 'rubygems'
require 'ruport'
require 'spreadsheet'
require 'stringio'

Spreadsheet.client_encoding = "UTF-8"

include Ruport::Data

class Ruport::Formatter::Excel < Ruport::Formatter
  renders :excel, :for => 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 => 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 => :blue,
          :weight => :bold,
          :size => 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

8 个解决方案

#1

You're forgetting creating an OleDB datasource and Excel Interop, but there are issues with those as well.

您可能忘记了创建OleDB数据源和Excel互操作，但是这些也有问题。

I recommend the SpreadsheetML option. It works pretty well, odds are your platform has some decent tools for building xml files, and it's fully supported as far back as OfficeXP. Office2000 is not supported, but personal experience is that it works in a limited way.

我建议使用SpreadsheetML选项。它工作得很好，很有可能您的平台有一些构建xml文件的合适工具，而且可以追溯到OfficeXP。Office2000是不支持的，但是个人经验是它的工作方式是有限的。

#2

I found that if you set the charset encoding of the web page to utf-8, and then Response.BinaryWrite the UTF-8 Byte Order Mark (0xEF 0xBB 0xBF) at the top of the csv file, then Excel 2007 (not sure about other versions) will recognize it as utf-8 and open it correctly.

我发现，如果将web页面的字符集编码设置为utf-8，然后进行响应。binary写入csv文件顶部的UTF-8字节顺序标记(0xEF 0xBB 0xBF)，然后Excel 2007(不确定其他版本)将识别为UTF-8并正确地打开它。

#3

After struggling with the same problem for a few hours I found this excellent post on the subject

在与同样的问题挣扎了几个小时之后，我发现了这篇关于这个问题的优秀文章

http://blog.plataformatec.com.br/2009/09/exporting-data-to-csv-and-excel-in-your-rails-app/ quote :

http://blog.plataformatec.com.br/2009/09/exporting-data-to-csv-and-excel-in-your-rails-app/报价:

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

这就是处理超友好csv的三个规则:

Use tabulations, not commas.

使用表格,而不是逗号。

Fields must NOT contain newlines.

字段不能包含换行符。

Use UTF-16 Little Endian to send the file to the user. And include a Little Endian BOM manually.

使用UTF-16 Little Endian将文件发送给用户。并包括一个小的Endian BOM手动。

However, if you're using ruby, you problem is solved: first you have the FasterCSV gem

但是，如果您正在使用ruby，那么问题就解决了:首先，您拥有FasterCSV gem

but i ended up using the spreadsheet gem which directly generates excell spreadsheets (i have link limitation, just google spreadsheet + rubyforge) Brilliant !

但是我最终使用了电子表格gem，它直接生成excell电子表格(我有链接限制，只有谷歌电子表格+ rubyforge)，非常棒!

#4

If you create an XML with utf encoding and save it as a .xls it will open even those two-byte chars:

如果您使用utf编码创建一个XML并将其保存为.xls，它甚至会打开那些两字节的字符:

xml version="1.0" encoding="utf-8"

xml version = " 1.0 " encoding = " utf - 8 "

#5

I had the exact same issue of sending UTF8 data to Excel. My solution:

我也遇到了同样的问题:向Excel发送UTF8数据。我的解决方案:

The current version of the Perl Spreadsheet::WriteExcel cpan code correctly writes Excel files using UTF8 data.

当前版本的Perl电子表格::WriteExcel cpan代码正确地使用UTF8数据编写Excel文件。

So I wrote a Rails plugin that a) opens a two-way pipe to a perl program b) sends the data, a row at a time, to the perl program. I use Yaml as the message data format. (Standard Ruby yaml is not UTF8, there's a special version available, ya2yaml) c) The perl program creates the excel file d) When the Rails program indicates (via a yaml message) that the last row has been sent, the perl program creates the excel file and sends the status back to the rails program.

因此我编写了一个Rails插件a)打开一个到perl程序b的双向管道b))将数据(每次一行)发送给perl程序。我使用Yaml作为消息数据格式。(标准Ruby yaml不是UTF8,有一个特殊版本可用,ya2yaml)c)perl程序创建excel文件d)当Rails程序显示(通过yaml消息),最后一行已经发送,perl程序创建excel文件和发送状态回到了Rails项目。

Of course, adding a perl program to a rails project via a parallel process and a pipe is very much in the "Engineering" spectrum rather than "Computer Science." (It gets the job done but is not elegant.) But it does work well and saved me the weeks it would take to port the WriteExcel code to Ruby. Also note that the currently available Ruby port of WriteExcel does not handle utf8.

当然，通过并行进程和管道向rails项目添加perl程序在很大程度上属于“工程”范围，而不是“计算机科学”。(它完成了工作，但并不优雅。)但是它确实工作得很好，并且为我节省了将WriteExcel代码移植到Ruby的时间。还要注意，WriteExcel当前可用的Ruby端口不能处理utf8。

My sw is permissive open source but I haven't gotten around to releasing it yet. If you want it in its current state, see http://sandbox.kluger.com/write_excel_v.5.tar

我的sw是许可的开放源码，但是我还没有开始发布它。如果希望它处于当前状态，请参见http://sandbox.kluger.com/write_excel_v.tar

Note that you'll want to create your excel files in a background process, not in the process of the Rails controller since that would block other browser clients as you grind away producing the excel file. I use DelayedJob plugin, works well.

注意，您希望在后台进程中创建您的excel文件，而不是在Rails控制器的过程中，因为这会阻塞其他浏览器客户端，因为您正在研磨生成excel文件。我使用DelayedJob插件，效果很好。

Hope this helps,

希望这有助于

Larry

拉里

#6

I fell on this post looking for the Ruby answer to why Excel wouldn't properly load a CSV with utf-8 characters. After searching around and experimenting this solution worked for me:

我在这篇文章中寻找Ruby的答案来解释为什么Excel不能正确地加载带有utf-8字符的CSV。在搜索和实验之后，这个解决方案对我起了作用:

csv_content = CSV.generate(col_sep: "\t", headers: :first_row, encoding: 'utf-8') do |csv|
  csv << ["header1", "header2"]
  csv << ["content1", "content2"]
end
write_content = Iconv.conv("utf-16le", "utf-8", "\xEF\xBB\xBF")
write_content += Iconv.conv("utf-16le", "utf-8", csv_content)
File.open("listing.csv", 'wb') {|f| f.write(write_content) }

#7

-1

Excel does not handle UTF-8 properly. You should use instead a code page that satisfy your needs

Excel不能正确处理UTF-8。您应该使用一个满足您需要的代码页

Response.ContentType = "text/plain";
// codepage: 28591, codepage name:iso-8859-1, codepage display name: Western European (ISO)
Response.ContentEncoding = System.Text.Encoding.GetEncoding(28591);

#8

-3

Try OpenOffice Calc - it's much more Unicode friendly - both Importing and Exporting CSV files with UTF-8 encoding.

试试OpenOffice Calc——它对Unicode更友好——导入和导出带有UTF-8编码的CSV文件。

#1