如何在excel表中下载多行 [英] How to download several rows in excel's sheet

问题描述

我想在单页中下载80,000行作为C＃和后端SQL Server中的.xlxs（Excel）文件。但我搜索了互联网，我无法确定哪个程序是正确的。大多数程序员都说在excel表中拆分数据并下载文件。但是我想在单页中下载最少80,000行。



我尝试了什么：



我尝试了很多代码。但我得到的错误如

I want to download 80,000 rows in single sheet as .xlxs (Excel) file in C# and the backend SQL Server. But I searched the internet I can't identify which program is correct. Most of the programmers says split the data in excel sheet and download the file. But I want to download Minimum of 80,000 rows in single sheet.

What I have tried:

I tried lots of code. But I got error like

Quote：

'System.OutOfMemoryException'

'System.OutOfMemoryException'

推荐答案

下载它不是问题，只要它是一个现代excel文件 - 2007年之前的版本有64K行的限制，但是增加到超过一百万。你的是一个XLXS文件，所以它应该能够处理它。



使用它可能是......这根本不是一个好的人类友好文件。



下载大文件不是问题，也不是将它们存储在SQL中并检索它们（虽然这是一个非常糟糕的主意 - 你应该考虑将它们存储为文件和存储文件在SQL中的位置）。



因此，问题必须出在您创建或访问Excel数据的代码中，或者代码中在客户端处理它。



我们看不到任何这些。所以它将取决于你。

首先，找出内存不足错误来自哪里：什么应用程序产生错误，哪个运行 - 客户端或服务器？它发生时有什么作用？你做了什么让它到达那里？



然后使用该应用程序上的调试器找出导致错误的代码，以及何时。它的投入是什么？输出？它究竟做了什么 - 确切地说 - 什么时候它失败了？



除非你有这个，否则每个人 - 包括你 - 只是猜测问题可能是什么，没人能解决它。

Downloading it isn't a problem, provided it's an "modern" excel file - pre 2007 versions had a limit of 64K rows, but that was increased to "over a million". Yours is an XLXS file, so it should be able to handle it.

Using it may well be ... That's not a good "human friendly" file at all.

And downloading large files isn't a problem, nor is storing them in SQL and retrieving them (although is a very bad idea - you should consider storing them as files and storing where the file is in SQL instead).

So the problem has to be either in your code which creates or access the Excel data, or in the code with handles it at the client end.

And we can't see any of that. So it's going to be down to you.
First, work out where that "out of memory"error is coming from: what application is generating the error, and where is that running - client or server? What is it doi9gn when it occurs? What did you do to cause it to get there?

Then use the debugger on that application to find out which code is causing the error, and when. What are its inputs? Outputs? What is it doing - exactly - when it fails?

Until you have that, everybody - including you - are just guessing what the problem might be and nobody can solve it.

Quote：

'System.OutOfMemoryException'

'System.OutOfMemoryException'

这意味着您的查询不适合内存，唯一的解决方案是将您的单个查询拆分为多个部分查询，无论如何。

首先查询是1到1000的记录

第二个查询是从1001到2000的记录

...

https://www.sqlshack.com/introduction-pagination-sql-server/ [ ^ ]

使用OFFSET / FETCH进行分页：更好的方法 [ ^ ]



至于在Excel工作表中写字，你知道你在上一篇文章中的位置，只需继续下一行。

This means that your query do not fit in memory, the only solution is to split your single query into multiple partial queries, no matter what.
First query is records from 1 to 1000
Second query is records from 1001 to 2000
...
https://www.sqlshack.com/introduction-pagination-sql-server/[^]
Pagination with OFFSET / FETCH : A better way[^]

As to write in Excel Sheet, you know where you stop on previous piece, just continue on next row.

这篇关于如何在excel表中下载多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！