Export GridView Data into Excel

Question

Hello I am trying to export gridview data into excel here are parts of my code , and well it creates the file but when i open the file exel says its not exl file and when i force open it shows the info as needed ( altough with blank area all around the grid ) beside this i followed the code and i noticed it catches an exeption ex which says something like this :

"'ctl00_ContentPlaceHolder1_approvalGrid' 'GridView' runat=server." and it talks about tag type and putting ( its kinda messy because it shows it in my language )

Code-behind:

 Protected Sub exportExelBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles exportExelBtn.Click    
    If approvalGrid.Rows.Count > 0 Then
            Try    

                Response.ClearContent()
                Response.Buffer = True
                Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "poView.xls"))
                Response.ContentEncoding = Encoding.UTF8
                Response.ContentType = "application/ms-excel"
                ' Dim sw As New stringwriter()
                Dim tw As New IO.StringWriter()
                Dim htw As New HtmlTextWriter(tw)
                approvalGrid.RenderControl(htw)
                Response.Write(tw.ToString())
                Response.[End]()

            Catch ex As Exception

            End Try
  END IF
 END SUB

Markup:

 <%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="PoViewTable.aspx.vb" Inherits="PoViewTable" %>

    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

                    <asp:GridView ID="approvalGrid" runat="server" AllowSorting="True" 
                AutoGenerateColumns="False" DataSourceID="poViewSql" Visible="False">
                <Columns>
                    <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                        ReadOnly="True" SortExpression="id" />
                    <asp:BoundField DataField="poID" HeaderText="poID" SortExpression="poID" visible = "false"/>
                    <asp:BoundField DataField="companyID" HeaderText="companyID" 
                        SortExpression="companyID" />
                    <asp:BoundField DataField="requesterID" HeaderText="requesterID" 
                        SortExpression="requesterID" />
                    <asp:BoundField DataField="departmentID" HeaderText="departmentID" 
                        SortExpression="departmentID" />
                    <asp:BoundField DataField="subDepartmentID" HeaderText="subDepartmentID" 
                        SortExpression="subDepartmentID" />
                    <asp:BoundField DataField="date" HeaderText="date" SortExpression="date" />
                    <asp:BoundField DataField="amount" HeaderText="amount" 
                        SortExpression="amount" />
                    <asp:BoundField DataField="supplierID" HeaderText="supplierID" 
                        SortExpression="supplierID" />
                    <asp:BoundField DataField="comments" HeaderText="comments" 
                        SortExpression="comments" />
                    <asp:BoundField DataField="managerID1" HeaderText="managerID1" 
                        SortExpression="managerID1" />
                    <asp:BoundField DataField="managerStatus1" HeaderText="managerStatus1" 
                        SortExpression="managerStatus1" />
                    <asp:BoundField DataField="managerID2" HeaderText="managerID2" 
                        SortExpression="managerID2" />
                    <asp:BoundField DataField="managerStatus2" HeaderText="managerStatus2" 
                        SortExpression="managerStatus2" />
                    <asp:BoundField DataField="poStatus" HeaderText="poStatus" 
                        SortExpression="poStatus" />
                </Columns>
            </asp:GridView>
            <br />

...
...   

</asp:Content>

check this out hope you find what you are looking for what you are trying to do is actually very easy if the answer is not to your liking i suggest you google there is probably 10 other ways to do what you are attempting http://stackoverflow.com/questions/680199/how-to-export-datagridview-to-excel-using-vb-net — Wolf, May 29 '14 at 12:07
possible duplicate of [simple export from asp.net gridview into exel vb.net](http://stackoverflow.com/questions/23930391/simple-export-from-asp-net-gridview-into-exel-vb-net) — InbetweenWeekends, May 29 '14 at 12:41

Sain Pradeep · Answer 1 · 2014-05-29T12:26:49.917

Please add this function in the code.

 Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Verifies that the control is rendered
End Sub

For more details please visit.here

or you can use another method to export gridview to excel

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Confirms that an HtmlForm control is rendered for the specified ASP.NET
'           server control at run time.     

End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs)


    Using sw As New StreamWriter("c:\test.xls")
        Using hw As New HtmlTextWriter(sw)
            GridView1.RenderControl(hw)
        End Using
    End Using

End Sub

score 0 · Answer 2 · answered May 29 '14 at 14:27

this code works, just modify it, regards

Dim sb As StringBuilder = New StringBuilder() 
Dim SW As System.IO.StringWriter = New System.IO.StringWriter(sb) 
Dim htw As HtmlTextWriter = New HtmlTextWriter(SW)
Dim Page As Page = New Page() 
Dim form As HtmlForm = New HtmlForm()
Me.GridView.EnableViewState = False
Page.EnableEventValidation = False
Page.DesignerInitialize() 
Page.Controls.Add(form) 
form.Controls.Add(Me.GridView)
Page.RenderControl(htw)
Response.Clear()
Response.Buffer = True 
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;filename=data.xls") Response.Charset = "UTF-8"
Response.ContentEncoding = Encoding.Default
Response.Write(sb.ToString())
Response.End()

score 0 · Accepted Answer · answered May 29 '14 at 14:37

A few things to note.

You're catching exceptions, but you're not doing anything with them. That's a code smell. Don't do it. Ever. Log an exception, or create a notification message. Don't just swallow it.

You're not actually creating an Excel file. You're creating HTML and giving it an Excel extension. That's why Excel is freaking out. Excel knows how to read HTML tables, but since it's the wrong extension it wants to verify with you that it's okay that it does that. None of the other answers posted here address that.

In general, exporting data to excel with HTML is a bad idea. You can't control the output easily, and it's hard to avoid the warning message you get when opening the file.

When you post an error to Stack Overflow, include all relevant details. You never actually told us the name of the exception, or which line threw it. That's important information.

Instead, you should use a library that is specifically meant for exporting data to Excel. And instead of exporting a GridView, you should export the underlying data. My favorite library for this is EPPlus. It makes it trivial to create an actual .xlsx (Open Office XML Spreadsheet) file, and you can use good formatting.

Export GridView Data into Excel

3 Answers3