C# 导入和导出 Excel - 最好的库是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/444522/
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
Import and Export Excel - What is the best library?
提问by Jason Kealey
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.
在我们使用 C# 编写的其中一个 ASP.NET 应用程序中，我们获取某个数据集合（SubSonic 集合）并将其导出到 Excel。我们还想导入特定格式的 Excel 文件。我正在寻找一个可以用于此目的的库。
Requirements:
要求：
- Excel 2007 files (Does Excel 2003 support over 64k rows? I need more than that.)
- Does not require Excel on the server
- Takes a typed collection and, if it can, tries to put numeric fields as numeric in Excel.
- Works well with large files (100k to 10M) - fast enough.
- Doesn't crash when exporting GUIDs!
- Does not cost a crapload of money (no enterprise library like aspose). Free is always great, but can be a commercial library.
- 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?
你推荐什么图书馆？您是否将其用于大量数据？还有其他解决方案吗？
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.
现在，我正在使用一个简单的工具来生成稍后由 Excel 加载的 HTML，但是我失去了一些功能，而且 Excel 在我们加载它时会抱怨。我不需要生成图表或类似的东西，只需导出原始数据。
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.
我正在考虑平面 CSV 文件，但 Excel 是客户要求。如果我有一个工具来转换 Excel，我可以直接使用 CSV。鉴于 Excel 2007 是一种基于 xml（和压缩）的文件格式，我猜这种库应该很容易找到。但是，对我来说最重要的是您的评论和意见。
EDIT: 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.
编辑：具有讽刺意味的是，在我看来，按照得票最多的答案，最好的 Excel 导入和导出库根本没有导出。这并非适用于所有场景，但适用于我的场景。XLS 文件仅支持 64k 行。XLSX 最多支持 1M。我尝试过的免费库的性能很差（当你有 20 万行时，一秒钟加载一行）。我还没有尝试过付费的，因为当您只需要一个快速的 XLSX<->CSV 转换例程时，我觉得它们提供的价值定价过高。
采纳答案by tsimon
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).
我将投入到平面 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?
是的，用户必须先在 excel 中另存为 CSV，然后才能处理它，但也许可以通过培训和在页面上提供清晰的说明来克服这种限制？
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'.
最后，当您导出给客户时，如果您将 mime 类型设置为 text/csv，Excel 通常会映射到该类型，因此在用户看来它是“Excel 文件”。
回答by CMS
Check the ExcelPackageproject, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...
检查ExcelPackage项目，它使用 Excel 2007 的 Office Open XML 文件格式，它是轻量级和开源...
回答by Duncan
回答by marc_s
There's a pretty good article and library on CodeProject by Yogesh Jagota:
Yogesh Jagota 在 CodeProject 上有一篇很好的文章和库：
Excel XML Import-Export Library
I've used it to export data from SQL queries and other data sources to Excel - works just fine for me.
我用它来将数据从 SQL 查询和其他数据源导出到 Excel - 对我来说很好用。
Cheers
干杯
回答by cdonner
CSV export is simple, easy to implement, and fast. There is one potential issue worth noting, though. Excel (up to 2007) does not preserve leading zeros in CSV files. This will garble ZIP codes, product ids, and other textual data containing numeric values. There is one trick that will make Excel import the values correctly (using delimiters and prefix values with the = sign, if I remember correctly, e.g. ..,="02052",...). If you have users who will do post-processing tasks with the CSV, they need to be aware that they need to change the format to XLS and not save the file back to CSV. If they do, leading zeros will be lost for good.
CSV 导出简单、易于实施且速度快。不过，有一个潜在问题值得注意。Excel（直到 2007 年）不会在 CSV 文件中保留前导零。这会导致邮政编码、产品 ID 和其他包含数值的文本数据出现乱码。有一种技巧可以使 Excel 正确导入值（使用带有 = 符号的分隔符和前缀值，如果我没记错的话，例如 ..,="02052",...）。如果您的用户将使用 CSV 执行后处理任务，他们需要知道他们需要将格式更改为 XLS，而不是将文件保存回 CSV。如果他们这样做，前导零将永远丢失。
回答by cdonner
I discovered the Open XML SDKsince my original answer. It provides strongly typed classes for spreadsheet objects, among other things, and seems to be fairly easy to work with. I am going to use it for reports in one of my projects. Alas, version 2.0 is not supposed to get released until late 2009 or 2010.
自从我最初的回答以来，我发现了Open XML SDK。它为电子表格对象等提供了强类型的类，并且似乎很容易使用。我将在我的一个项目中将其用于报告。唉，2.0 版应该要到 2009 年末或 2010 年才会发布。
回答by RG.
You can use Microsoft.Jet.OLEDB.4.0
您可以使用 Microsoft.Jet.OLEDB.4.0
回答by Joe Erickson
SpreadsheetGear for .NETreads and writes CSV / XLS / XLSX and does more.
SpreadsheetGear for .NET读取和写入 CSV / XLS / XLSX 并执行更多操作。
You can see live ASP.NET samples with C# and VB source code hereand download a free trial here.
你可以看到现场ASP.NET样本C＃和VB源代码在这里和下载免费试用这里。
Of course I think SpreadsheetGear is the best library to import / export Excel workbooks in ASP.NET - but I am biased. You can see what some of our customers say on the right hand side of this page.
当然，我认为 SpreadsheetGear 是在 ASP.NET 中导入/导出 Excel 工作簿的最佳库 - 但我有偏见。您可以在此页面的右侧看到我们的一些客户所说的话。
Disclaimer: I own SpreadsheetGear LLC
免责声明：我拥有 SpreadsheetGear LLC
回答by stormwild
NPOI For Excel 2003 Open Source http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html
NPOI For Excel 2003 开源 http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html
回答by Chris Laforet
For years, I have used JExcelfor this, an excellent open-source Java project. It was also .NET-able by using J# to compile it, and I have also had great success with it in this incarnation. However, recently I needed to migrate the code to native .NET to support a 64-bit IIS application in which I create Excel output. The 32-bit J# version would not load.
多年来，我一直为此使用JExcel，这是一个出色的开源 Java 项目。通过使用 J# 编译它，它也支持 .NET，而且我在这个版本中也取得了巨大的成功。但是，最近我需要将代码迁移到本机 .NET 以支持我在其中创建 Excel 输出的 64 位 IIS 应用程序。无法加载 32 位 J# 版本。
The code for CSharpJExcel is LGPL and is available currently at thispage, while we prepare to deploy it on the JExcel SourceForge site. It will compile with VS2005 or VS2008. The examples in the original JExcel documentation will pretty well move over intact to the .NET version.
CSharpJExcel 的代码是 LGPL，目前可在此页面获得，同时我们准备将其部署在 JExcel SourceForge 站点上。它将用 VS2005 或 VS2008 编译。原始 JExcel 文档中的示例将很好地完整迁移到 .NET 版本。
Hope it is helpful to someone out here.
希望对这里的人有所帮助。
