将大量数据从 C# 导出到 Excel 的最佳/最快方法是什么
我有使用 OpenXML 库导出数据的代码。
我有 20,000 行和 22 列，这需要很长时间（大约 10 分钟）。
有没有任何解决方案可以将数据从 C# 导出到 excel，这会更快，因为我是从 ASP.NET MVC 应用程序执行此操作，而且许多人的浏览器都超时了。
I have code that uses the OpenXML library to export data.
I have 20,000 rows and 22 columns and it takes ages (about 10 minutes).
is there any solution that would export data from C# to excel that would be faster as i am doing this from an asp.net mvc app and many people browsers are timing out.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
假设有 20'000 行和 22 列，每列约 100 字节，则仅 41 兆字节的数据。加上 xml 标签，再加上格式设置，我想说您最终会压缩（.xlsx 只是几个压缩的 xml 文件）100 mb 的数据。
当然，这需要一段时间，获取数据也需要一段时间。
我建议您使用 excel package plus 而不是 Office OpenXML 开发工具包。
http://epplus.codeplex.com/
写入中可能存在错误/性能问题 -赶紧并希望 Microsoft 代码不会很快崩溃。
Assuming 20'000 rows and 22 columns with about 100 bytes each, makes 41 megabytes data alone. plus xml tags, plus formatting, I'd say you end up zipping (.xlsx is nothing but several zipped xml files) 100 mb of data.
Of course this takes a while, and so does fetching the data.
I recommend you use excel package plus instead of the Office OpenXML development kit.
http://epplus.codeplex.com/
There's probably a bug/performance-issue in the write-in-a-hurry-and-hope-that-it-doesnt-blow-up-too-soon Microsoft code.
CSV。它是纯文本文件，但可以由任何版本的 Excel 打开。
毫无疑问，这是将数据导出到 Excel 的更简单的方法。很多网站都提供 CSV 数据导出功能。
您需要做的只是添加一个逗号 (,) 来分隔值，并添加一个换行符来分隔记录。构建 csv 文件不需要额外的资源，因此速度相当快。
CSV. It is a plain text file, but can be opened by any version of Excel.
No doubt it is a easier way to export data to excel. A lot of website provide data export as CSV.
What you need to do is just add a comma (,) to separate the values and a line break to separate the records. It won't take extra resource to build the csv file, so it is quite fast.
我最终使用了一个名为 ClosedXML 的开源解决方案，效果非常好
I wound up using an open source solution called ClosedXML that worked great
根据您所针对的 Excel 版本，您可以将数据公开为 OData 服务，Excel 2010 可以自然地使用该服务，并为您处理下载和格式设置。
Depending on what version of Excel you are targetting, you could expose the data as an OData service which Excel 2010 can naturally consume and will handle the downloading and formattting for you.
我假设这些数据需要完全发送给客户端，并且已经以某种方式进行了预过滤，但仍然需要发送回发出请求的人。
在这种情况下，您希望“异步”执行此特定操作。我不确定这是否适合您的工作流程，但是假设一个人请求这个大型 XML 格式的文档，我会：a) 将另一个工作线程排队以启动文档的生成，同时返回一个“令牌”（可能是一个请求者的 GUID）； b) 返回指向页面的链接，请求者可以在该页面上单击该链接（传递令牌），从而允许该页面查找结果。
如果线程已完成对文档的处理，则会将其放入具有唯一名称的特殊文件夹中，并将令牌及其文档位置添加到数据库表中。如果用户请求该页面，则令牌存在于数据库中并且文档存在于文件系统中，则允许他们单击并通过 HTTP 下载它。如果它不存在，他们要么被告知它不存在，要么等待结果。 （此消息可以基于收到请求的时间。）
如果该人成功下载文档（您可以通过脚本执行此操作），您可以使用该令牌删除该文档的数据库条目并删除该文件从文件系统。
我希望我正确地阅读了这个问题。
I am assuming that this data is something that needs to be completely sent to the client and has already been pre-filtered in some fashion, but still needs to be sent back to the person who made the request.
In this case, you want to perform this particular operation 'asynchronously'. I'm not sure if this would fit your workflow, but say that a person requests this large XML formatted document, I would: a) queue another worker thread to kick off the generation of the document while returning a 'token' (perhaps a GUID to the requester); b) return a link to a page where the requestor can click on the link (passing the token) allowing the page to look up results.
If the thread has completed processing the document, it places it into a special folder with a unique name and adds the token to a database table with its document location. If the person requests that page, the token exists in the database and the document exists on the file system, they are allowed to click and download it through HTTP. If it does not exist, they are either told it does not exist or to wait for the results. (This message can be based on the time the request was received.)
If the person downloads the document successfully (and you can do this through script), you can remove the entry for the database for the document with that token and delete the file from the file system.
I hope I read this question correctly.
我发现通过限制导出操作的数量可以加快将数据从数据库导出到 Excel 电子表格的速度。我发现通过在写入之前积累100行数据，创建速度至少提高了5-10倍。
I have found that I can speed up exporting data from a database into an Excel spreadsheet by limiting the number of export operations. I found that by accumulating 100 lines of data before writing, the creation speed increased by a factor of at least 5-10x.
导出数据时最常犯的错误是在工作流程中
此工作流程会导致开销，因为构建 XML DOM 需要时间，XML DOM 保存在内存中与模型一起，然后将整组数据写入文件。
处理此问题的更好方法是将模型条目直接转换为目标格式，并将其直接写入（缓冲）文件。
CSV 是一种开销较低、写入速度快且可由 Excel 读取的格式（好吧，它是传统格式，很尴尬......）。
The mistake when exporting data that is most often done when exporting data is in the workflow
This workflow leads to an overhead because building up the XML DOM needs it's time, the XML DOM is kept in memory together with the Model and then the whole bunch of data is written to a file.
A better way to handle this is to convert your model entry by entry directly to the target format and write it directly to a (buffered) file.
A format with low overhead that's fast to write and is readable by Excel is CSV (ok, it's legacy, it's awkward...).