Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I am using the following code to export grid view to excel. Many times (not all) while selecting the Open option of grid view exported to excel message opens Unable to read file..
Pls suggest..

Protected Sub ExportToExcel(sender As Object, e As EventArgs)
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=Scope.xls")
        'Response.AddHeader("content-disposition", "attachment;filename=Scope.xlsx")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Using sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)

            'To Export all pages
            gdScope.AllowPaging = False

            Me.btnShow_Click(Me, e)

            gdScope.HeaderRow.BackColor = Color.White
            For Each cell As TableCell In gdScope.HeaderRow.Cells
                cell.BackColor = gdScope.HeaderStyle.BackColor
            Next
            For Each row As GridViewRow In gdScope.Rows
                row.BackColor = Color.White
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = gdScope.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = gdScope.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                Next
            Next

            gdScope.RenderControl(hw)
            'style to format numbers to string
            Dim style As String = "<style> .textmode { } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        End Using
    End Sub


    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered
    End Sub


XML
<connectionStrings>
   <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
   <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
 </connectionStrings >



Thanks,
Atul
Posted
Updated 13-Jun-15 2:35am
v2
Comments
F-ES Sitecore 13-Jun-15 15:45pm    
There is a lot of code out there like the code you are using that all claim to create Excel files, but I'm afraid they're all wrong. You're not creating an Excel file, you're simply writing html and telling the browser it should try and open the html in Excel if the client has it installed. Excel can show basic tables etc as rows\columns, but it is simply taking a best guess. As you are not actually creating Excel files it's not unexpected that what you write can't always be interpreted by Excel.
atul sharma 5126 14-Jun-15 10:06am    
I respect your words of experience.
Can you suggest the right code for sending grid view data to excel with caption?

Also, why the same data having interpretation issues 3 out of 6 times?
Please guide..

Thanks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900