Blank Spreadsheet when Gridview Exported to Excel

[Seagull Ng]

Hello all,

I am doing my web-based reporting tool in ASP.Net 2.0 using Visual Web
Developer 2005.

I try to export my populated gridview to Excel spreadsheet but what I got is
just a blank spreadsheet with only <div></div> tag in the first cell.

Can anyone tell me what actually is the problem? Thanks in advance.

Below are the codes in vb:

==============================
Protected Sub but_export_1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles but_export_1.Click
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename=FileName.xls")
Response.Charset = ""
'If you want the option to open the Excel file without saving than
'comment out the line below
'Response.Cache.SetCacheability(HttpCacheability.N oCache);

Response.ContentType = "application/vnd.xls"
Dim strw As New System.IO.StringWriter
Dim htmlw As New System.Web.UI.HtmlTextWriter(strw)
gvResult1.RenderControl(htmlw)
Response.Write(strw.ToString())
Response.End()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal gvResult1 As
Control)
End Sub
==========================================

[wizard04frms]

You have to make sure the data is bound to the control again before
using RenderControl since the click event is done on the server. I had
problems with this scenario myself, and ended up making the button
redirect to a second page. On that second page, in Page_Load, I put
the code you have above plus some code to bind data to the datagrid.
Mine looks like this:

Sub Page_Load()

'... code to fill dataset ds

'first let's clean up the response.object
response.Clear()
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset ds
dg.DataSource = ds.Tables(0)

'last minute changes to formatting
dg.GridLines = GridLines.None
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Left
dg.ItemStyle.HorizontalAlign = HorizontalAlign.Left
dg.ItemStyle.Height = new Unit(17) 'set height to 17 px (12.75 pt)

'bind the datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()

End Sub

[Seagull Ng]

Thanks for your reply.

I change a bit of my previous codes to save the gridview into session. When
the Export To Excel button event is triggered, it will retrieve the gridview
from the session. But that did not work out as well ...

I am using gridview bound with sql datasource control. I can't find any
dataset associated with my gridview. Do you mind tell me where can I locate
it?

Thanks again.

"wizard04frms" wrote:

You have to make sure the data is bound to the control again before
using RenderControl since the click event is done on the server. I had
problems with this scenario myself, and ended up making the button
redirect to a second page. On that second page, in Page_Load, I put
the code you have above plus some code to bind data to the datagrid.
Mine looks like this:

Sub Page_Load()

'... code to fill dataset ds

'first let's clean up the response.object
response.Clear()
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset ds
dg.DataSource = ds.Tables(0)

'last minute changes to formatting
dg.GridLines = GridLines.None
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Left
dg.ItemStyle.HorizontalAlign = HorizontalAlign.Left
dg.ItemStyle.Height = new Unit(17) 'set height to 17 px (12.75 pt)

'bind the datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()

End Sub

[wizard04frms]

Well, I haven't actually used the new GridView control. I found a
webpage that may help you out:

http://msdn.microsoft.com/asp.net/de...idViewEx02.asp

With my DataGrid and my Access datasource, I did the following to
populate a dataset:

'sql is the query string
'strConn is the connection string

Dim myConnection As New System.Data.OleDb.OleDbConnection(strConn)
Dim cmd As New System.Data.OleDb.OleDbCommand(sql, myConnection)
Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
'instantiate a dataset
Dim ds As New System.Data.DataSet

Try
'populate the dataset
da.Fill(ds)
Finally
'check on connection status
If myConnection.State = System.Data.ConnectionState.Open Then
myConnection.Close()
End If
'get rid of connection object
myConnection.Dispose()
End Try

'now the ds is populated