Solved

Export to Excel

Posted on 2010-11-18
19
930 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
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: …

636 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