How to export gridview to excel 2007

Question

0.00/5 (No votes)

See more:

Hi, I would like to export the data from GridView to Excel 2007 file (xlsx). Here is my code. However,I get an error message: The file you are trying to open is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

Help.:confused:

VB

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.AppendHeader("Content-Disposition", "attachment;filename=statistic.xlsx")
    Response.ContentEncoding = System.Text.Encoding.UTF7
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
    Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
    Me.GridView1.RenderControl(oHtmlTextWriter)
    Response.Output.Write(oStringWriter.ToString)
    Response.Flush()
    Response.End()
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

End Sub

Posted 20-Nov-10 3:18am

Chillin92

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

trolog · Accepted Answer · 2012-02-20T23:48:00

Hello lizhunji,

Excel 2007 does not read pure html like 2003-2005 versions I'm afraid, you have two options that I can think of right now and I'm certain many others.

You can write the gridview as an excel 2005 spreadsheet with the file extension xls and when you open that up in excel 2007 you get a warning message "The file you are trying to open 'FileName.xls' is in a different format than the specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" That is one long A** message and very unprofessional, but! it works.

Or you can down the method of writing the file as a CSV and changing the extension to xlsx which is excel 2007, sure is annoying! But Microsoft wanted to downgrade functionality I'm afraid (Not hating Microsoft!)

I have no code for the CSV method as yet, but if you want to do the first method then your code is almost already there. Try adapt this.

VB

Response.Clear()

       ' Set the type and filename
       Response.AddHeader("content-disposition", "attachment;filename=FileActivity" & "_" & Format(Date.Now, "dd/MM/yyyy_HHmm") & ".xls")
       Response.Charset = ""
       Response.ContentType = "application/vnd.xls"

       ' Add the HTML from the GridView to a StringWriter so we can write it out later
       Dim sw As System.IO.StringWriter = New System.IO.StringWriter
       Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
       grdActivity.RenderControl(hw)

       ' Write out the data
       Response.Write(sw.ToString)
       Response.End()

Sandeep Mewara · Accepted Answer · 2010-11-20T03:26:00

Have a look at these discussion/blog for the resolution of the issue:
How to: Excel 2007 error: Different format than specified by the file extension[^]
How to suppress the file corrupt warning at excel download?[^]
Office: A file is in a different file format than its extension indicates[^]

How to export gridview to excel 2007

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0