Export GridView to Excel cause System.OutOfMem oryException - ASP.NET VB

[ulai]

Hello everyone,

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

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

How to solve this ?

[ulai]

Hello everyone,

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

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

How to solve this ?

[kenobewan]

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?

[Curtis Rutland]

Well, you're trying to load more data than you have RAM. That's why you would get an OutOfMemoryExce ption.

Also, that is getting close to Excel's row limit, I think.

[ulai]

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

[kenobewan]

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.

[jhardman]

Hello everyone,

I want to export GridView (25000 records) to Excel. But i got an error message like : System.OutOfMem oryException 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

[ulai]

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 ?

[ulai]

I got this code (in .aspx) to export Table into Excel :

If (Not (gridExport.HeaderRow) Is Nothing) Then
    virTable.Rows.Add(gridExport.HeaderRow)
End If
 
For i = Val(txtPageFrom.Text) To Val(txtPageTo.Text)
                gridExport.PageIndex = i
                gridExport.DataBind()
 
                For Each row As GridViewRow In gridExport.Rows
                    virTable.Rows.Add(row)
                Next
 
                If (Not (gridExport.FooterRow) Is Nothing) Then
                    virTable.Rows.Add(gridExport.FooterRow)
                End If
Next
 
virTable.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter)
Response.End()

Is it what you mean ?
Or i should append row into excel. If so, would you tell me how to do it ?

[jhardman]

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