Solved

Export to Excel

Posted on 2010-11-18
19
915 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:Wildone63
  • 9
  • 8
  • 2
19 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34167207
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
 
LVL 1

Author Comment

by:Wildone63
ID: 34167766
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34167855
And it opens in Excel?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Wildone63
ID: 34168077
Yes it opens in excel.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34174197
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
 
LVL 1

Author Comment

by:Wildone63
ID: 34175873
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
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34176005
Put at the top of your reference on the code behind

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text
0
 
LVL 1

Author Comment

by:Wildone63
ID: 34176035
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
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34176107
Add the Reference to your project

Project -> Add Reference -> .NET -> Microsoft.Office.Interop.Excel
0
 
LVL 1

Author Comment

by:Wildone63
ID: 34176253
I don't have Microsoft.Office.Interop.Excel as an option in Add Reference...
0
 
LVL 1

Author Comment

by:Wildone63
ID: 34176306
I had to add Microsoft Excel 12.0 Object Library from the Com Tab...
0
 
LVL 1

Author Comment

by:Wildone63
ID: 34176316
Thank You! but....
Now I have errors that DataGridView1 is not declared and ReleaseObject is not declared.

0
 
LVL 1

Author Comment

by:Wildone63
ID: 34176327
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
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34176441
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
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 500 total points
ID: 34176503
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
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34176509
Do not forgot to put this line of code


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

Expert Comment

by:Jesus Rodriguez
ID: 34176515
Was the same as you have before but I think that you forgot to do that I told you that do not forgot
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 34176581
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
 
LVL 1

Author Closing Comment

by:Wildone63
ID: 34176806
Thank YOU... It works great!
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.net -  GroupBox / Panel DataBinding and behavior 3 23
Why use this lambda? 12 60
SSIS GUID Variable 2 30
Convert VB web project to C# 3 32
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question