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_Cl ick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles but_export_1.Cl ick
Response.Clear( )
Response.AddHea der("content-disposition",
"attachment;fil ename=FileName. xls")
Response.Charse t = ""
'If you want the option to open the Excel file without saving than
'comment out the line below
'Response.Cache .SetCacheabilit y(HttpCacheabil ity.NoCache);

Response.Conten tType = "applicatio n/vnd.xls"
Dim strw As New System.IO.Strin gWriter
Dim htmlw As New System.Web.UI.H tmlTextWriter(s trw)
gvResult1.Rende rControl(htmlw)
Response.Write( strw.ToString() )
Response.End()
End Sub

Public Overrides Sub VerifyRendering InServerForm(By Val 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.Charse t = ""
'set the response mime type for excel
response.Conten tType = "applicatio n/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.Strin gWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.H tmlTextWriter(s tringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.W ebControls.Data Grid
'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.Ho rizontalAlign = HorizontalAlign .Left
dg.ItemStyle.He ight = 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.RenderContro l(htmlWrite)
'all that's left is to output the html
response.Write( stringWrite.ToS tring)
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.

"wizard04fr ms" 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.Charse t = ""
'set the response mime type for excel
response.Conten tType = "applicatio n/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.Strin gWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.H tmlTextWriter(s tringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.W ebControls.Data Grid
'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.Ho rizontalAlign = HorizontalAlign .Left
dg.ItemStyle.He ight = 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.RenderContro l(htmlWrite)
'all that's left is to output the html
response.Write( stringWrite.ToS tring)
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.Ole Db.OleDbConnect ion(strConn)
Dim cmd As New System.Data.Ole Db.OleDbCommand (sql, myConnection)
Dim da As New System.Data.Ole Db.OleDbDataAda pter(cmd)
'instantiate a dataset
Dim ds As New System.Data.Dat aSet

Try
'populate the dataset
da.Fill(ds)
Finally
'check on connection status
If myConnection.St ate = System.Data.Con nectionState.Op en Then
myConnection.Cl ose()
End If
'get rid of connection object
myConnection.Di spose()
End Try

'now the ds is populated