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.OutOfMemoryException 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.PageCount
grView.PageIndex = i
virTable.GridLines = grView.GridLines
If (Not (grView.HeaderRow) Is Nothing) Then
virTable.Rows.Add(grView.HeaderRow)
End If
For Each row As GridViewRow In grView.Rows
virTable.Rows.Add(row)
Next
If (Not (grView.FooterRow) Is Nothing) Then
virTable.Rows.Add(grView.FooterRow)
End If
Next
virTable.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter)
Response.End()
3. I tried like number 2 but i changed the position like :
For i = 1 To grView.PageCount
grView.PageIndex = i
virTable.GridLines = grView.GridLines
If (Not (grView.HeaderRow) Is Nothing) Then
virTable.Rows.Add(grView.HeaderRow)
End If
For Each row As GridViewRow In grView.Rows
virTable.Rows.Add(row)
Next
If (Not (grView.FooterRow) Is Nothing) Then
virTable.Rows.Add(grView.FooterRow)
End If
virTable.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter)
Next
Response.End()
But it gives an error message like number 1 : System.OutOfMemoryException 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.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
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.OutOfMemoryException. I guess you are calling Response.write() in loop for many time. Continate your output string(oStringWriter) 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.OutOfMemoryException

Here is the code in 2 :

Dim Str As String = "attachment; filename=ExcelFileName.xls"

If (File.Exists(srcPath)) Then
File.Delete(srcPath)
End If

Response.ClearContent()
Response.AddHeader("content-disposition", Str)
Response.ContentType = "application/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.PageCount
grView.PageIndex = i

virTable.Rows.Add(grView.HeaderRow)

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

virTable.RenderControl(oHtmlTextWriter)
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.DataSource as DataTable;
foreach (DataRow dr in dt.Rows)
{
//do adding row, virTable.Rows.Add(row)
}

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

DataSourceSelectArguments dsaArgs = new DataSourceSelectArguments();
//SqlDataSource1 is a SqlDataSource control is used to bind to gridview
DataView view = (DataView)SqlDataSource1.Select(dsaArgs);
DataTable dt = view.ToTable();
foreach (DataRow dr in dt.Rows)
{
//do adding row, virTable.Rows.Add(row)
}

Dane,
Hey, Don't forget to go to my blog and give some comments.
http://savotdane.blogspot.com/