如何将GridView导出到Excel 2007 [英] How to export gridview to excel 2007

问题描述

您好，我想将数据从GridView导出到Excel 2007文件(xlsx).这是我的代码.但是，我收到一条错误消息:您尝试打开的文件格式与文件扩展名指定的格式不同.打开文件之前，请验证文件是否未损坏且来自受信任的源.您要立即打开文件吗?

帮助.:困惑:

 受保护的 子 Button1_Click( ByVal 发​​件人 As  对象， ByVal  e  As  System.EventArgs)句柄 Button1.Click
    Response.Clear()
    Response.Buffer = 真
    Response.Charset = " 
    Response.AppendHeader(" ，" 附件； filename = statistic.xlsx")
    Response.ContentEncoding = System.Text.Encoding.UTF7
    Response.ContentType = " 
     Dim  oStringWriter  As  System.IO.StringWriter = 新 System.IO.StringWriter
     Dim  oHtmlTextWriter  As  System.Web.UI.HtmlTextWriter = 新 System.Web.UI.HtmlTextWriter(oStringWriter)
    我 .GridView1.RenderControl(oHtmlTextWriter)
    Response.Output.Write(oStringWriter.ToString)
    Response.Flush()
    响应.结束()
结束 子
公共 重载 替代 子 VerifyRenderingInServerForm( As  System.Web.UI.Control )

结束 子 

解决方案

你好lizhunji，

恐怕Excel 2007无法读取像2003-2005版本那样的纯html，您有两个可以立即想到的选项，而且我肯定还有很多其他选择.

您可以将gridview编写为具有文件扩展名xls的excel 2005电子表格，当您在excel 2007中打开该文件时，系统会显示警告消息您要打开的文件FileName.xls的格式与.由文件扩展名指定.在打开文件之前，请确保该文件没有损坏并且来自受信任的来源.您要立即打开该文件吗?"这是一条很长的A **信息，非常不专业，但是！有用.

或者，您也可以降低将文件编写为CSV并将扩展名更改为excel 2007的xlsx的方法，这肯定很烦人！但是恐怕Microsoft想要降级功能(不讨厌Microsoft！)

我还没有CSV方法的代码，但是如果您想使用第一种方法，则您的代码几乎已经存在.尝试对此进行调整.

 Response.Clear()

       ' 设置类型和文件名
       Response.AddHeader(" ，" 附件； filename = FileActivity"&   _"& Format("  )& " )
       Response.Charset = " 
       Response.ContentType = " 

       ' 将GridView中的HTML添加到StringWriter中，以便我们稍后将其写出
        Dim  sw  As  System.IO.StringWriter = >新 System.IO.StringWriter
        Dim 硬件 As  System.Web.UI.HtmlTextWriter = 新 HtmlTextWriter(sw)
       grdActivity.RenderControl(hw)

       ' 写出数据
       Response.Write(sw.ToString)
       响应.结束()

请查看以下讨论/博客以解决问题:
如何:Excel 2007错误:格式与文件扩展名指定的格式不同 [如何在excel下载时抑制文件损坏警告? [ ^ ]
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

解决方案

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.

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

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[^]

这篇关于如何将GridView导出到Excel 2007的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！