通过 C# Web 服务将大量数据从 sql server 流式传输到 Excel 文件

发布于 2024-10-18 12:49:49 字数 360 浏览 3 评论 0原文

因此,我尝试将存储过程(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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

滥情哥ㄟ 2024-10-25 12:49:49

这是我制作的一个非常快速的 POC,它写入 3 列,每列 255 个字符 200,000 次(600,000 个单元格)。最终文件在我的机器上大小为 4.85MB。

        string ExportFile = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx");
        string DSN = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", ExportFile);

        using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
        {
            Con.Open();
            using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
            {
                Com.Connection = Con;
                Com.CommandText = "CREATE TABLE [TestSheet] (A1 varChar(255), B1 varChar(255), C1 varChar(255))";
                Com.ExecuteNonQuery();
                string A1 = new string('A', 255);
                string B1 = new string('B', 255);
                string C1 = new string('C', 255);
                Com.CommandText = string.Format("INSERT INTO [TestSheet] (A1, B1, C1) VALUES ('{0}', '{1}', '{2}')", A1, B1, C1);
                for (var i = 1; i <= 200000; i++)
                {
                    Com.ExecuteNonQuery();
                }
            }
            Con.Close();
        }

在服务器上,我不太确定需要什么,但您可能必须安装这个:

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.

        string ExportFile = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx");
        string DSN = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", ExportFile);

        using (System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection(DSN))
        {
            Con.Open();
            using (System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand())
            {
                Com.Connection = Con;
                Com.CommandText = "CREATE TABLE [TestSheet] (A1 varChar(255), B1 varChar(255), C1 varChar(255))";
                Com.ExecuteNonQuery();
                string A1 = new string('A', 255);
                string B1 = new string('B', 255);
                string C1 = new string('C', 255);
                Com.CommandText = string.Format("INSERT INTO [TestSheet] (A1, B1, C1) VALUES ('{0}', '{1}', '{2}')", A1, B1, C1);
                for (var i = 1; i <= 200000; i++)
                {
                    Com.ExecuteNonQuery();
                }
            }
            Con.Close();
        }

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

暖心男生 2024-10-25 12:49:49

最终 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?

开始看清了 2024-10-25 12:49:49

我最终自己生成了一个xlsx,结果发现格式非常简单

I ended up generating a xlsx myself, it turns out the format is pretty simple

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文