Solved

Export to Excel

Posted on 2010-11-18
19
904 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now