xml 将 UTF8 数据导出到 Excel 的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451636/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best way to export UTF8 data into Excel?
提问by Billy Gray
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 数据的网络应用程序。万岁UTF8。我们可以将用户提供的数据导出为 CSV 没问题 - 那时它仍然是 UTF8。问题是当您在 Excel 中打开典型的 UTF8 CSV 时，它会将其读取为 ANSII 编码的文本，并相应地尝试读取像 ? 和 ü 作为两个单独的字符，你最终会失败。
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:
所以我做了一些挖掘（Intervals 的人在这里有一篇关于它的有趣帖子），并且有一些有限的选项，如果非常烦人的话。他们之中：
- supplying a UTF-16 Little Endian TSV file which Excel will interpret correctly, but which won't support multi-line data
- supplying the data in an HTML table with an Excel mime-type or file extension (not sure if this option supports 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.
- 提供一个 UTF-16 Little Endian TSV 文件，Excel 可以正确解释该文件，但不支持多行数据
- 以 Excel MIME 类型或文件扩展名提供 HTML 表格中的数据（不确定此选项是否支持 UTF8）
- 有大约三到四种方法可以将 XML 数据导入各种最新版本的 excel，理论上这些方法都支持 UTF8。SpreadsheetML，使用自定义 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.
看起来无论如何，我可能会继续为那些无论如何都没有将它用于 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.
亲爱的 Stack Overflowers，生成正确支持 UTF8 的 Just-For-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 人员和任何框架上的人员如何处理此问题。我自己在几个不同的环境中工作，这绝对是一个会再次出现的问题。
Update 2010-10-22:We had been using the Ruport gem in our time-tracking system Tempoto 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 日更新：当我第一次发布这个问题时，我们一直在我们的时间跟踪系统Tempo 中使用 Ruport gem来提供 CSV 导出。我的一位同事 Erik Hollensbee 为 Ruport 组合了一个快速过滤器，为我们提供了实际的 Excel XSL 输出，我想我会在这里与任何其他 ruby-ists 分享：
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
采纳答案by Joel Coehoorn
You're forgetting creating an OleDB datasource and Excel Interop, but there are issues with those as well.
您忘记了创建 OleDB 数据源和 Excel Interop，但这些也存在问题。
I recommend the SpreadsheetMLoption. 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，但个人经验是它的工作方式有限。
回答by Andrew Csontos
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.
我发现如果你将网页的字符集编码设置为utf-8，然后Response.BinaryWrite UTF-8 Byte Order Mark (0xEF 0xBB 0xBF) 在csv文件的顶部，那么Excel 2007（不确定其他版本）将其识别为 utf-8 并正确打开它。
回答by Alexis Perrier
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:
- 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.
因此，这些是处理 Excel-friendly-CSV 的三个规则：
- 使用表格，而不是逗号。
- 字段不得包含换行符。
- 使用 UTF-16 Little Endian 将文件发送给用户。并手动包含一个 Little Endian BOM。
However, if you're using ruby, you problem is solved: first you have the FasterCSVgem
但是，如果您使用 ruby，问题就解决了：首先您拥有FasterCSVgem
but i ended up using the spreadsheet gem which directly generates excell spreadsheets (i have link limitation, just google spreadsheet + rubyforge) Brilliant !
但我最终使用了直接生成 Excel 电子表格的电子表格 gem（我有链接限制，只是 google 电子表格 + rubyforge）太棒了！
回答by Rulas
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 版本="1.0" 编码="utf-8"
回答by Larry K
I had the exact same issue of sending UTF8 data to Excel. My solution:
我在将 UTF8 数据发送到 Excel 时遇到了完全相同的问题。我的解决方案：
The current version of the Perl Spreadsheet::WriteExcel cpan code correctly writes Excel files using UTF8 data.
当前版本的 Perl Spreadsheet::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) 一次一行地将数据发送到 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.5.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
拉里
回答by Russell
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:
我在这篇文章中寻找关于为什么 Excel 无法正确加载带有 utf-8 字符的 CSV 的 Ruby 答案。在四处搜索和试验后，这个解决方案对我有用：
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) }
回答by kri
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);
回答by kri
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 文件。
