通过 C# Web 服务将大量数据从 sql server 流式传输到 Excel 文件
因此,我尝试将存储过程(200k 行以上)的结果从 ASP.NET 获取到 Excel 文件中,但遇到了一些困难。我不认为 csv 是一个选项,因为客户希望数字格式正确。我尝试过三个第三方 Excel 库,但都因数据量太大而崩溃,并且占用了千兆字节的内存。
我编写了一些代码来生成 Excel XML 文件,它运行得非常快,但文件超过 300megs。如果我打开并另存为本机 Excel 文件,它会减少到 30megs。目前我最好的解决方案是在服务器上压缩这个 xml 文件,这样可以将其减小到 7megs,但用户在解压缩后仍然会得到一个巨大的文件。理想情况下,我想找到一个第三方 Excel 库,可以编写包含 200,000 多行的本机 Excel 文件,而无需终止服务器,有什么想法吗?
So I'm trying to get the results from a stored proc (200k rows+) into an Excel file from ASP.NET but having a few difficulties. I don't think csv is an option as the client want the numbers formatted correctly. I've tried three third party Excel libraries but all have fallen over with so much data and are using gigabytes of memory.
I've wrote some code to generate an Excel XML file and it runs very quickly but the file is over 300megs. If I open and save as a native Excel file it gets it down to 30megs. At the moment my best solution is to zip this xml file on the server which gets it down to 7megs but the user is still going to end up with a huge file once unzipped. Ideally I'd like to find a third party Excel library that can write a native Excel file with 200,000+ rows without killing the server, any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这是我制作的一个非常快速的 POC,它写入 3 列,每列 255 个字符 200,000 次(600,000 个单元格)。最终文件在我的机器上大小为 4.85MB。
在服务器上,我不太确定需要什么,但您可能必须安装这个:
http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
Here's a really quick POC I made that writes 3 columns of 255 characters 200,000 times (600,000 cells). The final file comes in at 4.85MB on my machine.
On a server I'm not all sure what's needed but you might have to install this:
http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
最终 Excel 是一个平面文件系统,而 SQL 并没有使兼容性变得有趣。我希望克里斯·哈斯的代码对您有用。这看起来确实很奇怪,“我不认为 csv 是一个选项,因为客户希望数字格式正确。” ' 如果他们有 SQL 数据库,他们不会引用/查询该数据库,而是拥有 Excel 版本的数据库?
Ultimately excel is a flat file system and SQL isn't making compatibility interesting. I'm hoping Chris Haas' code will work for you. It does seem odd, 'I don't think csv is an option as the client want the numbers formatted correctly. ' if they have an SQL database they don't refer/query that instead of having an excel version of the database?
我最终自己生成了一个xlsx,结果发现格式非常简单
I ended up generating a xlsx myself, it turns out the format is pretty simple