Export Gridview to Excel more than 65535 record

[ulai]

Thanks for your help. It really help to solve my work.
But now i have a bigger problem :
I had more than 65535 records in gridview divide by 10/pages. And when i tried to export the gridview in excel on every way :
1. I disabled the allowpaging in gridview and got and error messages like : System.OutOfMem oryException Was Thrown
2. My idea is to store row in gridview to virtable per pages. I create virtable as Table. Added row to virtable from gridview/page. But its only displayed the first page of gridview. Here is the code:
For i = 1 To grView.PageCoun t
grView.PageInde x = i
virTable.GridLi nes = grView.GridLine s
If (Not (grView.HeaderR ow) Is Nothing) Then
virTable.Rows.A dd(grView.Heade rRow)
End If
For Each row As GridViewRow In grView.Rows
virTable.Rows.A dd(row)
Next
If (Not (grView.FooterR ow) Is Nothing) Then
virTable.Rows.A dd(grView.Foote rRow)
End If
Next
virTable.Render Control(oHtmlTe xtWriter)
Response.Write( oStringWriter)
Response.End()
3. I tried like number 2 but i changed the position like :
For i = 1 To grView.PageCoun t
grView.PageInde x = i
virTable.GridLi nes = grView.GridLine s
If (Not (grView.HeaderR ow) Is Nothing) Then
virTable.Rows.A dd(grView.Heade rRow)
End If
For Each row As GridViewRow In grView.Rows
virTable.Rows.A dd(row)
Next
If (Not (grView.FooterR ow) Is Nothing) Then
virTable.Rows.A dd(grView.Foote rRow)
End If
virTable.Render Control(oHtmlTe xtWriter)
Response.Write( oStringWriter)
Next
Response.End()
But it gives an error message like number 1 : System.OutOfMem oryException Was Thrown

[dnnddane]

-Number 1:
Exporting data from GridVview to excel, it should be done like this:

//export to excel
Response.Clear( );
Response.Buffer = true;
Response.Conten tType = "applicatio n/vnd.ms-excel";
Response.Charse t = "";
this.EnableView State = false;
System.IO.Strin gWriter oStringWriter = new System.IO.Strin gWriter();
System.Web.UI.H tmlTextWriter oHtmlTextWriter = new System.Web.UI.H tmlTextWriter(o StringWriter);
this.ClearContr ols(dg);
dg.RenderContro l(oHtmlTextWrit er);
Response.Write( oStringWriter.T oString());
Response.End();

But in case you have Non-LiteralControl in girdview you may get run time exception, so let me know which one are you working now.


-Number 2: You got System.OutOfMem oryException. I guess you are calling Response.write( ) in loop for many time. Continate your output string(oStringW riter) then do Response.Write( ) outside loop.

Dane

[ulai]

Thx for your att,

Right now, i'm focusing 2

But in 2 i got no error message, cause i place the response.write outside looping. The problem in 2 is its only displayed the first page of gridview from 2400 pages.

In 3, i tried to fix the problem in 2. I placed the response.write inside the looping. And as you say its got an error : System.OutOfMem oryException

Here is the code in 2 :

Dim Str As String = "attachment ; filename=ExcelF ileName.xls"

If (File.Exists(sr cPath)) Then
File.Delete(src Path)
End If

Response.ClearC ontent()
Response.AddHea der("content-disposition", Str)
Response.Conten tType = "applicatio n/ms-excel"

Dim oStringWriter As New StringWriter
Dim oHtmlTextWriter = New HtmlTextWriter( oStringWriter)

Dim virTable As Table = New Table
Dim i As Integer

For i = 1 To grView.PageCoun t
grView.PageInde x = i

virTable.Rows.A dd(grView.Heade rRow)

For Each row As GridViewRow In grView.Rows
virTable.Rows.A dd(row)
Next
Next

virTable.Render Control(oHtmlTe xtWriter)
Response.Write( oStringWriter)
Response.End()


The problem is :
I assign the row on gridview (grView) at first page to last page To virTable. But when i debugged, i only got 10 rows in virTable (not accumulated from the page before).

So, if you have time, would you like to find the solution for me ?

[dnnddane]

You can not loop through all rows in gridview with paging enabled by just changing pageindex of gridview unless you rebind(DataBind ()) your gridview though you will get only the current page.

Try following solutions:

-If you are using datatable as as datasource, try this:
//Suppose we binded DataTable to the gridview
DataTable dt = GridView1.DataS ource as DataTable;
foreach (DataRow dr in dt.Rows)
{
//do adding row, virTable.Rows.A dd(row)
}

-If you are using SqlDataSource control as a datasource , try this:

DataSourceSelec tArguments dsaArgs = new DataSourceSelec tArguments();
//SqlDataSource1 is a SqlDataSource control is used to bind to gridview
DataView view = (DataView)SqlDa taSource1.Selec t(dsaArgs);
DataTable dt = view.ToTable();
foreach (DataRow dr in dt.Rows)
{
//do adding row, virTable.Rows.A dd(row)
}

Dane,
Hey, Don't forget to go to my blog and give some comments.

http://savotdane.blogspot.com/