导入和导出 Excel

本文介绍了导入和导出 Excel - 最好的库是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在我们使用 C# 编写的其中一个 ASP.NET 应用程序中，我们获取某个数据集合(SubSonic 集合)并将其导出到 Excel.我们还想导入特定格式的 Excel 文件.我正在寻找一个可以用于此目的的库.

In one of our ASP.NET applications in C#, we take a certain data collection (SubSonic collection) and export it to Excel. We also want to import Excel files in a specific format. I'm looking for a library I can use for this purpose.

要求:

• Excel 2007 文件(Excel 2003 是否支持超过 64k 行?我需要的不止这些.)
• 不需要服务器上的 Excel
• 获取类型化集合，如果可以，尝试将数字字段作为数字放入 Excel 中.
• 处理大文件(100k 到 10M)效​​果很好 - 足够快.
• 导出 GUID 时不会崩溃！
• 不花一大笔钱(没有像 aspose 这样的企业库).免费总是很棒，但也可以成为商业图书馆.

你推荐什么图书馆?您是否将其用于大量数据?还有其他解决方案吗?

What library do you recommend? Have you used it for large quantities of data? Are there other solutions?

现在，我正在使用一个简单的工具来生成稍后由 Excel 加载的 HTML，但我失去了一些功能，而且 Excel 在我们加载时会抱怨.我不需要生成图表或类似的东西，只需导出原始数据.

Right now, I am using a simple tool that generates HTML that is loaded by Excel later on, but I am losing some capabilities, plus Excel complains when we load it. I don't need to generate charts or anything like that, just export raw data.

我正在考虑平面 CSV 文件，但 Excel 是客户要求.如果我有一个工具来转换 Excel，我可以直接使用 CSV.鉴于 Excel 2007 是一种基于 xml(和压缩)的文件格式，我猜这种库应该很容易找到.但是，对我来说最重要的是您的评论和意见.

I am thinking of flat CSV files, but Excel is a customer requirement. I can work with CSV directly, if I had a tool to convert to and from Excel. Given Excel 2007 is an xml-based (and zipped) file format, I am guessing this kind of library should be easy to find. However, what matters most to me are your comments and opinions.

具有讽刺意味的是，在我看来，按照得票最多的答案，最好的 Excel 导入和导出库根本没有导出.这并非适用于所有场景，但适用于我的场景.XLS 文件仅支持 64k 行.XLSX 最大支持 1M.我尝试过的免费库的性能很差(当你有 20 万行时，一秒钟加载一行).我还没有尝试过付费的，因为我觉得当您只需要一个快速的 XLSX<->CSV 转换例程时，它们所提供的价值定价过高.

Ironically, in my opinion and following the answer with the most votes, the best Excel import&export library is no export at all. This is not the case for all scenarios, but it is for mine. XLS files support only 64k rows. XLSX supports up to 1M. The free libraries that I've tried feature bad performance (one second to load one row when you have 200k rows). I haven't tried the paid ones, as I feel they are overpriced for the value they deliver when all you need is a fast XLSX<->CSV conversion routine.

推荐答案

我将全力支持平面 csv 文件，因为您对代码有最大的控制权.只需确保您读取行并一次处理一个(将文档读到最后并拆分会耗尽您的所有内存 - 与写入相同，将其流式输出).

I'm going to throw my hand in for flat csv files, if only because you've got the greatest control over the code. Just make sure that you read in the rows and process them one at a time (reading the document to the end and splitting will eat up all of your memory - same with writing, stream it out).

是的，用户必须先在 excel 中将其另存为 CSV，然后才能处理它，但也许可以通过培训和在页面上提供清晰的说明来克服这种限制?

Yes, the user will have to save-as CSV in excel before you can process it, but perhaps this limitation can be overcome by training and providing clear instructions on the page?

最后，当您导出给客户时，如果您将 mime 类型设置为 text/csv，Excel 通常会映射到该类型，因此在用户看来它是Excel 文件".

Finally, when you export to the customer, if you set the mime type to text/csv, Excel is usually mapped to that type so it appears to the user to be 'an Excel file'.