将 UTF8 数据导出到 Excel 的最佳方法是什么? [英] What's the best way to export UTF8 data into Excel?
问题描述
所以我们有这个支持 UTF8 数据的网络应用程序.万岁UTF8.我们可以将用户提供的数据导出为 CSV 没问题 - 那时它仍然是 UTF8.问题是当您在 Excel 中打开典型的 UTF8 CSV 时，它会将其读取为 ANSII 编码文本，并相应地尝试将 ø 和 ü 等两字节字符读取为两个单独的字符，但最终会失败.
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.
所以我做了一些挖掘(Intervals 的人有一个有趣的帖子 关于它在这里)，并且有一些有限的，如果可笑的令人讨厌的选项.其中:
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:
- 提供 UTF-16 Little Endian TSV 文件，Excel 可以正确解释该文件，但不支持多行数据
- 以 Excel MIME 类型或文件扩展名提供 HTML 表格中的数据(不确定此选项是否支持 UTF8)
- 有大约三到四种方法可以将 XML 数据导入各种最新版本的 excel，理论上这些方法都支持 UTF8.SpreadsheetML，使用自定义 XSLT，或通过模板生成新的 Excel XML 格式.
看起来无论如何，我可能会继续为那些无论如何都没有将它用于 Excel 的人提供一个普通的 CSV 文件，并为 Excel 提供一个单独的下载选项.
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.
生成正确支持 UTF8 的 Just-For-Excel 文件的最简单方法是什么，亲爱的 Stack Overflowers?如果最简单的选项只支持最新版本的 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.
我在 Rails 堆栈上执行此操作，但很好奇 .Net 人员和任何框架上的人员如何处理此问题.我自己在几个不同的环境中工作，这绝对是一个会再次出现的问题.
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.
2010-10-22 更新:我们一直在时间跟踪系统 Tempo 中使用 Ruport gem 在我第一次发布这个问题时提供 CSV 导出.我的一位同事 Erik Hollensbee 为 Ruport 拼凑了一个快速过滤器，为我们提供了实际的 Excel XSL 输出，我想我会在这里与任何其他红宝石爱好者分享:
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:
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
推荐答案
您忘记了创建 OleDB 数据源和 Excel Interop，但这些也存在问题.
You're forgetting creating an OleDB datasource and Excel Interop, but there are issues with those as well.
我推荐 SpreadsheetML 选项.它工作得很好，很可能你的平台有一些不错的工具来构建 xml 文件，并且早在 OfficeXP 就完全支持它.不支持Office2000，但个人经验是它的工作方式有限.
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.
这篇关于将 UTF8 数据导出到 Excel 的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
