469,353 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,353 developers. It's quick & easy.

Export GridView to Excel cause System.OutOfMemoryException - ASP.NET VB

15
Hello everyone,

I want to export GridView (25000 records) to Excel. But i got an error message like : System.OutOfMemoryException was Thrown.

But i need to export the records (even more than 25000 records) to excel

How to solve this ?
Oct 27 '08 #1
11 12132
ulai
15
Hello everyone,

I want to export GridView (25000 records) to Excel. But i got an error message like : System.OutOfMemoryException was Thrown.

But i need to export the records (even more than 25000 records) to excel

How to solve this ?
Oct 27 '08 #2
kenobewan
4,871 Expert 4TB
Why don't you create the excel spreadsheet directly from the database? Why would you need to create a spreadsheet that is 25000 records long, how useful is that spreadsheet going to be?
Oct 27 '08 #3
Curtis Rutland
3,256 Expert 2GB
Well, you're trying to load more data than you have RAM. That's why you would get an OutOfMemoryException.

Also, that is getting close to Excel's row limit, I think.
Oct 27 '08 #4
ulai
15
Thanks for replying

For the reason is I want to list the data with the spesific criteria that was given. And for 25000 records is just a matter of the USER BAD-HABIT that i want to handle.

And is it depend on memory that i have ?
my memory is 2004 MB RAM, and i checked the usage of memory is so low
Oct 28 '08 #5
kenobewan
4,871 Expert 4TB
If it is a user bad habit you are wanting to handle, then you either need to set a timeout for the transaction and/or enable custom error handling in your application.
Oct 28 '08 #6
jhardman
3,406 Expert 2GB
Hello everyone,

I want to export GridView (25000 records) to Excel. But i got an error message like : System.OutOfMemoryException was Thrown.

But i need to export the records (even more than 25000 records) to excel

How to solve this ?
Would you be opposed to exporting it as a csv or xml file (either of which are compatible with excel)? I know XML files are virtually limitless, and csv files can be written one line at a time - this wouldn't use much system memory. Now that I think about it, to get through that many records, you might need to add it a row at a time, just because of size restrictions.

BTW, in ASP we usually don't talk about GridViews, that is a term used in ASP.NET. Are you using ASP.NET (file extension .aspx) or ASP (file extension .asp)?

Jared
Oct 28 '08 #7
ulai
15
If it is a user bad habit you are wanting to handle, then you either need to set a timeout for the transaction and/or enable custom error handling in your application.
I am new in asp.net. And i don't know how to do that. Would you tell me how to do that ?
Oct 29 '08 #8
ulai
15
I got this code (in .aspx) to export Table into Excel :

Expand|Select|Wrap|Line Numbers
  1. If (Not (gridExport.HeaderRow) Is Nothing) Then
  2.     virTable.Rows.Add(gridExport.HeaderRow)
  3. End If
  4.  
  5. For i = Val(txtPageFrom.Text) To Val(txtPageTo.Text)
  6.                 gridExport.PageIndex = i
  7.                 gridExport.DataBind()
  8.  
  9.                 For Each row As GridViewRow In gridExport.Rows
  10.                     virTable.Rows.Add(row)
  11.                 Next
  12.  
  13.                 If (Not (gridExport.FooterRow) Is Nothing) Then
  14.                     virTable.Rows.Add(gridExport.FooterRow)
  15.                 End If
  16. Next
  17.  
  18. virTable.RenderControl(oHtmlTextWriter)
  19. Response.Write(oStringWriter)
  20. Response.End()
Is it what you mean ?
Or i should append row into excel. If so, would you tell me how to do it ?
Oct 29 '08 #9
jhardman
3,406 Expert 2GB
Moved your thread to the .NET forum - this spot has a lot of experts that are better than me at this type of thing. If they don't give you a response in a day or two we'll ask a couple of them to help out.

It looks like your code puts all of the data in a temporary table before saving it - and it is the temporary table that is probably having the problem since Excel has a limit of something like 65,000 rows. Instead of forming a temporary table, we should be trying to add each row directly to the Excel file.

So would you say you are opposed to saving the file in a XML or CSV format? The reason I ask is they are both compatible with Excel (excel can open them just fine) but there are many methods available to string them together and even write the files a line at a time - so much so that it might be more flexible than trying to save a .xls file

Jared
Oct 29 '08 #10
kenobewan
4,871 Expert 4TB
These actions are not mutually exclusive, you could set a timeout in code before the transaction and then throw an error if timeout occurs. Custom error handling is set up if the web.config, assuming this is asp.net.
Oct 29 '08 #11
Curtis Rutland
3,256 Expert 2GB
Please don't double post your questions. If you made a mistake and need to change your question, you can click the Edit button to edit your post, or you can post your corrections in a reply to your original thread. If you can't find your original thread, click the "My Subscriptions" link near the top of the page. If you feel that your question has been overlooked, post a reply to it to "bump" it back to the top of the forum. We ask that you do this only once every 24 hours.

So there is no reason to double post. It makes it hard on the Experts and you to keep track of what help you've already been given.

Threads merged.

MODERATOR
Oct 29 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Not Me | last post: by
6 posts views Thread by Paul Zanbaka | last post: by
2 posts views Thread by wubin_98 | last post: by
3 posts views Thread by =?Utf-8?B?bWFuaWthMDI=?= | last post: by
reply views Thread by Mike | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.