Export to Excel

Hi,
I am using VS 2008 VB.net. I want to export a grid view to excel but cannot seem to figure this out.

Here is what I am trying

    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        GridView1.RenderControl(oHtmlTextWriter)

        Response.Write(oStringWriter.ToString())
        Response.[End]()

    End Sub

But I get and error on the GridView1.RenderControl line that says Gridview must be in a form tag with run at server.

Which it is....
    <form id="form1" runat="server">
    <div>
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            DataKeyNames="id_col" DataSourceID="SqlDataSource1">

Any ideas?

Thank You.
LVL 1
Wildone63Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jesus RodriguezConnect With a Mentor IT ManagerCommented:
I got this one too, Try this one if the other dosn't work


Partial Class how_to_Export_GridView_to_Excel
    Inherits System.Web.UI.Page

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    End Sub

    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        GridView1.RenderControl(oHtmlTextWriter)

        Response.Write(oStringWriter.ToString())
        Response.[End]()

    End Sub

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
'Button with the excel Icon        
ExportToExcel("Report.xls", GridView1)
    End Sub
End Class

Open in new window

0
 
CodeCruiserCommented:
Excel files are not HTML files (which is what RenderControl would generate). You need to either have Office on the server and use Office Interop Assemblies or use a third party control (some of them do not require office to be installed)  and export to excel file. You can then stream the file to the client.
0
 
Wildone63Author Commented:
well actually all I needed to do was add this

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)


    End Sub

it now works.

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CodeCruiserCommented:
And it opens in Excel?
0
 
Wildone63Author Commented:
Yes it opens in excel.
0
 
Jesus RodriguezIT ManagerCommented:
Try this
Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next
        'xlWorkSheet.PrintPreview()
        If System.IO.File.Exists("C:\MyBookFromGridView.xlsx") Then
            System.IO.File.Delete("C:\MyBookFromGridView.xlsx")
        End If

        xlWorkSheet.SaveAs("C:\MyBookFromGridView.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()
        xlApp.Workbooks.Open("C:\MyBookFromGridView.xlsx")
        xlApp.Visible = True

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

Open in new window

0
 
Wildone63Author Commented:
k-designers,

This looks good but I get an error, Excel.Application not defined. Also I get an error that rowcount is not a member of system.web.ui.webcontrols.gridview


Thanks
0
 
Jesus RodriguezIT ManagerCommented:
Put at the top of your reference on the code behind

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text
0
 
Wildone63Author Commented:
I get this error

Microsoft.Office.Interop.Excel does not contain any public member or can not be found. I do have office 2007 installed.
0
 
Jesus RodriguezIT ManagerCommented:
Add the Reference to your project

Project -> Add Reference -> .NET -> Microsoft.Office.Interop.Excel
0
 
Wildone63Author Commented:
I don't have Microsoft.Office.Interop.Excel as an option in Add Reference...
0
 
Wildone63Author Commented:
I had to add Microsoft Excel 12.0 Object Library from the Com Tab...
0
 
Wildone63Author Commented:
Thank You! but....
Now I have errors that DataGridView1 is not declared and ReleaseObject is not declared.

0
 
Wildone63Author Commented:
I changed to GridView1 instead of DataGridView1 and now I get the error RowCount is not a member of System.web.ui.webcontrols.gridview

0
 
Jesus RodriguezIT ManagerCommented:
Change the line where saids

 For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

by this ones

 For i = 0 To GridView1.Rows.Count - 2
            For j = 0 To GridView1.Columns.Count - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    GridView1(j, i).Value.ToString()
            Next
        Next
0
 
Jesus RodriguezIT ManagerCommented:
Do not forgot to put this line of code


 Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    End Sub
0
 
Jesus RodriguezIT ManagerCommented:
Was the same as you have before but I think that you forgot to do that I told you that do not forgot
0
 
Jesus RodriguezIT ManagerCommented:
This is the Whole Code on a new page and works, I try it before posted to you here

Is a Form with a gridview and a button for export
HTML Part

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        CellPadding="4" DataKeyNames="Id" DataSourceID="SqlDataSource1" 
        ForeColor="#333333" GridLines="None" Height="99px" PageSize="20" Width="222px">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" 
                ReadOnly="True" SortExpression="Id" />
            <asp:BoundField DataField="ReqstID" HeaderText="ReqstID" 
                SortExpression="ReqstID" />
            <asp:BoundField DataField="IdDpt" HeaderText="IdDpt" SortExpression="IdDpt" />
            <asp:BoundField DataField="ItemId" HeaderText="ItemId" 
                SortExpression="ItemId" />
            <asp:BoundField DataField="Descrip" HeaderText="Descrip" 
                SortExpression="Descrip" />
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:Button ID="Button1" runat="server" Text="Button" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:TonerRequestConnectionString %>" 
        SelectCommand="SELECT [Id], [ReqstID], [IdDpt], [ItemId], [Descrip] FROM [Request_Details]">
    </asp:SqlDataSource>
    </form>
</body>
</html>



Code Behind Part
Partial Class Default4
    Inherits System.Web.UI.Page
    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    End Sub
    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        GridView1.RenderControl(oHtmlTextWriter)

        Response.Write(oStringWriter.ToString())
        Response.[End]()

    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        ExportToExcel("Report.xls", GridView1)
    End Sub
End Class

Open in new window

0
 
Wildone63Author Commented:
Thank YOU... It works great!
0
All Courses

From novice to tech pro — start learning today.