Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Export Gridview to Excel

When Export Gridview to Excel. I kept getting this error. What would cause it?    

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

        Response.ClearContent()
   

        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()

        GridView1.Parent.Controls.Add(frm)

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

        frm.Controls.Add(GridView1)

        frm.RenderControl(htw)

        'GridView1.RenderControl(htw);

        Response.Write(sw.ToString())

        Response.[End]()
ee.bmp
0
VBdotnet2005
Asked:
VBdotnet2005
  • 2
3 Solutions
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

to:
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.
0
 
ddayx10Commented:
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: http://msdn.microsoft.com/en-us/library/bb448854.aspx

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

http://msdn.microsoft.com/en-us/library/bb448854.aspx

The link to the sdk is at the bottom under additional resources.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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#):
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27327904.html

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")
    Try
        Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & myTempFile & "';Extended Properties='Excel 8.0;HDR=Yes'")
            cn.Open()
                
            ' 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) & "]")
            Next
                
            ' 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")
            Next

            sbSql.Insert(0, "CREATE TABLE [" & sheetName & "] (")
            sbSql.Append(")")
                
            ' Create the work sheet
            Dim cmd As New System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn)
            cmd.ExecuteNonQuery()
                
            ' 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.Append(columnName)
                sbSqlParameters.Append("?")
            Next

            sbSql.Insert(0, "INSERT INTO [" & sheetName & "] (")
            sbSql.Append(") VALUES (")
            sbSql.Append(sbSqlParameters.ToString())
            sbSql.Append(")")
            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)
            Next
                
            ' 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)
                Next
                cmd.ExecuteNonQuery()
            Next
        End Using
            
        ' Send the excel file to the client.
        Response.Clear()
        Response.Cache.SetNoStore()
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls")
        Response.ContentType = "application/ms-excel"
        Response.BinaryWrite(System.IO.File.ReadAllBytes(myTempFile))
        Response.End()
    Finally
        ' Delete the temp file for cleanup purposes
        System.IO.File.Delete(myTempFile)
    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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now