Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Export to Excel

Posted on 2010-11-18
19
Medium Priority
?
934 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 2000 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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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