I have some code that creates an Excel worksheet from a gridview control. It works pretty well, except that it puts a blank row at the top of the worksheet. I am going to use the Excel worksheet as a mail merge data source for Word. The first row needs
to contain the field (column) names, but instead it is blank and row 2 has the column names. I know I can delete the first row, but this is for users and I don't want them to have to delete the first row everytime they produce this file. So, can anyone look
at the code below and tell me why it would be adding a blank row to the Excel worksheet generated from the gridview. Incidentlally, the gridview does not have a blank line at top.
Sub ExcelTest(ByVal MyGridView
As GridView) With MyGridView If .Rows.Count + 1 < 65536
Then
.AllowPaging = "False" Dim tw As New StringWriter() Dim hw As New System.Web.UI.HtmlTextWriter(tw) Dim frm As HtmlForm =
New HtmlForm() Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition",
"attachment;filename=Test.xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(MyGridView)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
.AllowPaging = "True"
.DataBind() Else
LblError.Text = "Too many rows - Export to Excel not possible" End If
End With
End Sub
Dr. Douglas Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
Thanks for the reply. I made just the changes you mentioned, but got the error, "Control 'Report' of type 'GridView' must be placed inside a form tag with runat=server." on the red line below. Here is the code. What am I missing? Thanks for some explicit
help.
.AllowPaging = "False"
Dim tw As New StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=" + FileName)
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(MyGridView)
MyGridView.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
.AllowPaging = "True"
.DataBind()
Dr. Douglas Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
That code is in C. I am having a dickens of a time converting it to VB. I know how to remove the ; but what about the stuff below in red?
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
Response.Charset = ""
' If you want the option to open the Excel file without saving then
' comment out the line below
' Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls"
System.IO.StringWriter stringWrite = new System.IO.StringWriter()
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite)
GridView1.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.End()
Any help?
Dr. Douglas Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
Okay, I think I made the C# to VB translation. However, I get the error, "Control 'ctl00_ContentPlaceHolder1_MyGridView of type 'GridView' must be placed inside a form tag with runat=server." on the red line below. Any Ideas?
Response.Clear()
Response.AddHeader("content-disposition",
"attachment;filename=" + FileName)
Response.Charset = "" ' If you want the option to open the Excel file without saving then
' comment out the line below
' Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.xls" Dim SWrite As New StringWriter() Dim htmlWrite As New System.Web.UI.HtmlTextWriter(SWrite)
MyGridView.RenderControl(htmlWrite)
Response.Write(SWrite.ToString())
Response.End()
Dr. Douglas Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
Chaplain Dou...
Member
305 Points
937 Posts
Undesired Blank Row Added to GridView
Jun 19, 2008 07:29 PM|LINK
VWD 2008 Express. Visual Basic.
I have some code that creates an Excel worksheet from a gridview control. It works pretty well, except that it puts a blank row at the top of the worksheet. I am going to use the Excel worksheet as a mail merge data source for Word. The first row needs to contain the field (column) names, but instead it is blank and row 2 has the column names. I know I can delete the first row, but this is for users and I don't want them to have to delete the first row everytime they produce this file. So, can anyone look at the code below and tell me why it would be adding a blank row to the Excel worksheet generated from the gridview. Incidentlally, the gridview does not have a blank line at top.
Sub ExcelTest(ByVal MyGridView As GridView)
With MyGridView
If .Rows.Count + 1 < 65536 Then
.AllowPaging = "False"
Dim tw As New StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm() Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=Test.xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(MyGridView)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
.AllowPaging = "True"
.DataBind()
Else
LblError.Text = "Too many rows - Export to Excel not possible"
End If
End With
End Sub
Good News Jail & Prison Ministry
www.goodnewsjail.org
vinz
All-Star
128483 Points
18150 Posts
MVP
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 07:46 PM|LINK
Try to remove the + 1 in this line If .Rows.Count + 1 < 65536.. so its something like below
If .Rows.Count < 65536 Then
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
Chaplain Dou...
Member
305 Points
937 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 07:49 PM|LINK
Did as you suggested. That did not change anything. The blank row is still there at the top of the Excel worksheet. Any other thoughts?
Good News Jail & Prison Ministry
www.goodnewsjail.org
saifi_hasan@...
Participant
856 Points
146 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 07:56 PM|LINK
I got it dis is because u are using form view.. why dont u directly do it with GridView ,since Form view leave some space in HTML as well
MyGridView.RenderControl(hw)
Response.Write(tw.ToString())
jdstuart
Member
228 Points
60 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 07:59 PM|LINK
Have a loot at the following website. It's opensource and helped me a lot!
http://aspalliance.com/771
Chaplain Dou...
Member
305 Points
937 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 08:19 PM|LINK
Thanks for the reply. I made just the changes you mentioned, but got the error, "Control 'Report' of type 'GridView' must be placed inside a form tag with runat=server." on the red line below. Here is the code. What am I missing? Thanks for some explicit help.
.AllowPaging = "False" Dim tw As New StringWriter() Dim hw As New System.Web.UI.HtmlTextWriter(tw) Dim frm As HtmlForm = New HtmlForm() Response.ContentType = "application/vnd.ms-excel" Response.AddHeader("content-disposition", "attachment;filename=" + FileName) Response.Charset = "" EnableViewState = False Controls.Add(frm) frm.Controls.Add(MyGridView) MyGridView.RenderControl(hw) Response.Write(tw.ToString()) Response.End() .AllowPaging = "True" .DataBind()Good News Jail & Prison Ministry
www.goodnewsjail.org
Chaplain Dou...
Member
305 Points
937 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 08:43 PM|LINK
That code is in C. I am having a dickens of a time converting it to VB. I know how to remove the ; but what about the stuff below in red?
Response.Clear() Response.AddHeader("content-disposition", "attachment;filename=FileName.xls") Response.Charset = "" ' If you want the option to open the Excel file without saving then ' comment out the line below ' Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.xls" System.IO.StringWriter stringWrite = new System.IO.StringWriter() System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite) GridView1.RenderControl(htmlWrite) Response.Write(stringWrite.ToString()) Response.End()Any help?Good News Jail & Prison Ministry
www.goodnewsjail.org
Chaplain Dou...
Member
305 Points
937 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 08:57 PM|LINK
Okay, I think I made the C# to VB translation. However, I get the error, "Control 'ctl00_ContentPlaceHolder1_MyGridView of type 'GridView' must be placed inside a form tag with runat=server." on the red line below. Any Ideas?
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=" + FileName)
Response.Charset = ""
' If you want the option to open the Excel file without saving then
' comment out the line below
' Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls"
Dim SWrite As New StringWriter()
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(SWrite)
MyGridView.RenderControl(htmlWrite)
Response.Write(SWrite.ToString())
Response.End()
Good News Jail & Prison Ministry
www.goodnewsjail.org
vinz
All-Star
128483 Points
18150 Posts
MVP
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 09:05 PM|LINK
After this line MyGridView.RenderControl(htmlWrite)
add this snippet below
Form1.Controls.Add(MyGridView)
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
Chaplain Dou...
Member
305 Points
937 Posts
Re: Undesired Blank Row Added to GridView
Jun 19, 2008 10:59 PM|LINK
Vincent:
I added Form.Controls.Add(MyGridView) after the line and the same error came up. I added it before the line and the same error came up.???[^o)]
Good News Jail & Prison Ministry
www.goodnewsjail.org