Export Gridview to Excel

Posted on 2011-10-25
Last Modified: 2012-05-12
When Export Gridview to Excel. I kept getting this error. What would cause it?    

 Dim attachment As String = "attachment; filename="test.xls"


        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"

        Dim sw As New StringWriter()

        Dim htw As New HtmlTextWriter(sw)

        ' Create a form to contain the grid

        Dim frm As New HtmlForm()


        frm.Attributes("runat") = "server"





Question by:VBdotnet2005
    LVL 17

    Accepted Solution

    Hello, that is because you are sending back html data to the client browser with a wrong file extension (xls instead html), when MS Excel is invoked to open that file it can see the file is not a real Excel work book, so its warning you about this irregularity. If you want to avoid that you will need to send back a real excel workbook file.

    Also you can try changing this line of your code:
    Dim attachment As String = "attachment; filename=""test.xls"""

    Open in new window

    Dim attachment As String = "attachment; filename=""test.html"""

    Open in new window

    Of course, the file will be opened with the default html document viewer instead of MS Excel.
    LVL 20

    Assisted Solution

    I spent quite a bit of time doing the exact same thing a few months back...exporting from GridView to excel.

    The process was not so difficult but I had the same type of issues. It always bugged me that the process worked but it would always deliver a warning when trying to open in excel.

    I researched and read tons of good articles from well known developers addressing this very issue. It boils down to this... the warning is something you have to put up with unless you build the header of the spreadsheet perfectly (or that's as close an approximation as my understanding allowed). It seemed something that was much more complex than what I had already undertaken.

    Anyway because of performance issues as well as professionality I decided to try open office sdk to create my spreadsheet and it solved my issues completely (including the warning).

    You might want to give it a shot:

    The documentation that comes with it will give you examples of how to use it.

    The link to the sdk is at the bottom under additional resources.
    LVL 17

    Assisted Solution

    by:Carlos Villegas
    Hello, some time ago I offered a solution to export the content of a gridview to an excel work book by using oledb and Microsoft Jet, this is the link to the solution (C#):

    I have adapted and improved it to VB.NET, here the method:
    Sub ExportToExcel(ByVal sheetName As String, ByVal myGridView As GridView)
        If String.IsNullOrEmpty(sheetName) Then
            Throw New ArgumentException("sheetName is required.", "sheetName")
        End If
        If myGridView Is Nothing Then
            Throw New ArgumentException("myGridView is required.", "myGridView")
        End If
        Dim myTempFile As String = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() & ".xls")
            Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & myTempFile & "';Extended Properties='Excel 8.0;HDR=Yes'")
                ' Get the column names.
                Dim myColumnNames As New System.Collections.Specialized.StringCollection
                Dim colCount As Integer = 0
                For Each cell As TableCell In myGridView.HeaderRow.Cells
                    colCount += 1
                    ' The column name can't be empty, I check that.
                    Dim columName As String = Server.HtmlDecode(cell.Text).Trim()
                    myColumnNames.Add("[" & IIf(String.IsNullOrEmpty(columName), "Col" & colCount, columName) & "]")
                ' Build Sql for sheet creation                                
                Dim sbSql As New System.Text.StringBuilder()
                For Each columnName As String In myColumnNames
                    If sbSql.Length > 0 Then sbSql.Append(", ")
                    sbSql.Append(columnName).Append(" string")
                sbSql.Insert(0, "CREATE TABLE [" & sheetName & "] (")
                ' Create the work sheet
                Dim cmd As New System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn)
                ' Build Sql for data insertion                                
                sbSql.Length = 0
                Dim sbSqlParameters As New System.Text.StringBuilder()
                For Each columnName As String In myColumnNames
                    If sbSql.Length > 0 Then sbSql.Append(", ")
                    If sbSqlParameters.Length > 0 Then sbSqlParameters.Append(", ")
                sbSql.Insert(0, "INSERT INTO [" & sheetName & "] (")
                sbSql.Append(") VALUES (")
                cmd = New System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn)
                ' Create parameters
                For Each columnName As String In myColumnNames
                    cmd.Parameters.Add(columnName, System.Data.OleDb.OleDbType.BSTR)
                ' Copy data from your GridView to the Excel file
                For Each row As GridViewRow In myGridView.Rows
                    For i As Integer = 0 To myColumnNames.Count - 1
                        cmd.Parameters(i).Value = Server.HtmlDecode(row.Cells(i).Text)
            End Using
            ' Send the excel file to the client.
            Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls")
            Response.ContentType = "application/ms-excel"
            ' Delete the temp file for cleanup purposes
        End Try
    End Sub

    Open in new window

    To use it you only need to do this (example with a button):
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        ExportToExcel("MyData", GridView1)
    End Sub

    Open in new window

    In the previous example, GridView1 is the gridview on your page that you want to export.

    The method ExportToExcel will send back to your browser a real excel file, with your gridview data.

    I hope this help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
    One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on could not pull conte…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    21 Experts available now in Live!

    Get 1:1 Help Now