Click here to Skip to main content
15,892,269 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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'
Posted
Updated 17-Nov-17 1:32am
v2
Comments
Karthik_Mahalingam 17-Nov-17 7:10am    
use .xlsx
F-ES Sitecore 17-Nov-17 7:13am    
Post the relevant code

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.
 
Share this answer
 
Quote:
'System.OutOfMemoryException'

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.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900