将数据导出到Excel 2007时如何避免Excel提示窗口 [英] How to avoid the Excel prompt window when exporting data to Excel 2007
问题描述
我是Asp.net开发人员（网站开发人员），我在Visual Studio 2005中使用Asp.net 2.0框架和VB作为语言。
我有一个必须使用Excel 2007导出和打开的数据集。
主要的是excel上的列标题不是数据表（dataset）列名，它是根据用户选择动态给出的在上一个屏幕上
我可以直接从gridview使用CSV，XSLT和aslo等几种或更多方法来实现这一点
问题IS：
当我点击事件导出到excel时，通常（常规）弹出一个窗口并询问打开，保存和取消。当我在那之后点击打开（这是问题发生）excel提示窗口并告诉（仅在Excel 2007中）
"你试图打开的文件，'filename.xls'在一个与文件扩展名指定的格式不同，在打开文件之前验证文件是否已损坏且来自受信任的源。你想现在打开文件吗？
当我点击OK然后打开它
我的用户不想得到那个excel提示窗口
我认为它是由于excel 2007设计的XML格式而发生的
有没有办法在没有excel提示的情况下这样做。
<如果我建议使用一个示例（代码片段），我将不胜感激。
为了您的信息，我正在粘贴我尝试的所有不同方法的代码。
CSV方法：
受保护的子lbtn_exp_excel_Click（ByVal sender As Object ，ByVal e As System.EventArgs）处理lbtn_exp_excel.Click
Dt_RwSum = Obj_RightofWayBll.RwSum_Retrive（view_type，est_type，rwsum_fdate，rwsum_tdate）'这是数据表
Dim ds作为新数据集
ds.Tables.Add （Dt_RwSum）
'Dim drow As DataRow = ds.Tables（0）.Rows
estdate_header = rwsum_etitle& "日期"
estamt_header = rwsum_etitle& " Amount" Dim sb As New System.Text.StringBuilder
sb.Append（vbCrLf）
sb.Append（" PROJECT No"）
sb.Append（"，"）< br> sb.Append（" Managing District"）
sb.Append（"，"）
sb.Append（" Location Description"）
sb.Append（"，"） ）
sb.Append（" Work Type"）
sb.Append（"，"）
sb.Append（estdate_header）
sb.Append（"，"）< br> sb.Append（estamt_header）
sb.Append（vbCrLf）
Dim i As Integer
Dim j As Integer
Dim report As String = String.Empty
Dim sbdatarow As New System .Text.StringBuilder
For i = 0 To ds.Tables（0）.Rows.Count - 1
Dim sb1 As New System.Text.StringBuilder
对于j = 0到ds.Tables（0）.Columns.Count - 1
sb1.Append（ds.Tables（0）.Rows（i）（j）.ToString）
sb1.Append（" ，"）
标准= sb1.ToString
sbdatarow.Append（标准）
sbdatarow.Append（vbCrLf）
Dim finalreport As String = sb.ToString& ; sbdatarow.ToString
Response.ContentType =""
Response.AppendHeader（" Content-Disposition"，" inline; filename = ExcelReport.csv"）
Response.Write（finalreport）
Response.End（）
end sub
Gridview方法直接将gridview导出为excel"
受保护的Sub lbl_exp_excel_Click（ByVal sender As Object，ByVal e As System .EventArgs）处理lbl_exp_excel.Click
gv_excel.Visible = True
cesum_ptitle = Session（" page_title"）
Dim tw As New StringWriter（）
Dim hw As New System.Web.UI。 HtmlTextWriter（tw）
Dim frm As HtmlForm = New HtmlForm（）
Dim FileName As String =" BillingWorkSheet" &安培; Date.Now.ToShortDateString
Response.ContentType =" application / vnd.ms-excel"
Response.AddHeader（" content-disposition"，" attachment; filename ="& FileName&" ; report.xls"）
Response.Charset =""
EnableViewState = False
Controls.Add（frm）
frm.Controls.Add（gv_excel（））'这是gridview control
frm.RenderControl（hw）
Response.Write（tw.ToString（））
Response.End（）
End Sub
XSLT方法将数据集中的数据流式传输到XSL文件中项目（excel_export.xsl））
受保护的子lbtn_exp_excel_Click（ByVal sender As Object，ByVal e As System.EventArgs）处理lbtn_exp_excel.Click
Dt_RwSum = Obj_RightofWayBll。 RwSum_Retr ive（view_type，est_type，rwsum_fdate，rwsum_tdate）'这是数据表
Dim ds As New DataSet
ds.Tables.Add（Dt_RwSum）
Response.ContentType =" application / vnd.ms-excel" ;
excel_export.xsl"））
xt.Transform（xdd，Nothing，Response.OutputStream）
Response.End（）
end sub
我厌倦了尝试所有的方法，请帮我避开excel 2007提示窗口。我的用户非常严格地避免此提示。
提前致谢
穆罕默德
Hello Mohamed，同样的问题在这里。我们的CMS通过Retrieve.aspx返回所有存储的文件，设置正确的MIME类型。 Excel表示URL具有aspx扩展名。
我们和我们的客户如何摆脱这种情况？
感谢您的帮助
thomas
Hi,
I am a Asp.net Developer(web developer), I am using Asp.net 2.0 framework with VB as language in visual studio 2005.
I have a dataset that has to be exported and opened with Excel 2007.
Main thing is the column header on the excel is not the datatable(dataset) column names, it is dynamically given according to the user selection on the previous screen
I was able to do that with couple or more method like CSV, XSLT and aslo directly from gridview
The Issue IS:
when ever I click the event to export into excel, As usually(regular) a window pops up and asks open, save and cancel. when I click open after that(Here is the issue occuring) excel prompts an window and tells that (Only in Excel 2007)
"The file you are trying to open, 'filename.xls'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"
when I click OK in that then it opens up
My user dont want to get that excel prompt window
I think it occurs due to the excel 2007 is designed XML format
Is there any way of doing this without excel prompt.
I would appreciate if suggest me with an example (code snippet).
For your information, I am pasting the code of all different method I tried.
CSV Method:
Protected Sub lbtn_exp_excel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtn_exp_excel.Click
Dt_RwSum = Obj_RightofWayBll.RwSum_Retrive(view_type, est_type, rwsum_fdate, rwsum_tdate) ' this is the datatable
Dim ds As New DataSet
ds.Tables.Add(Dt_RwSum)
'Dim drow As DataRow = ds.Tables(0).Rows
estdate_header = rwsum_etitle & " Date"
estamt_header = rwsum_etitle & " Amount"
Dim sb As New System.Text.StringBuilder
sb.Append(vbCrLf)
sb.Append("PROJECT No")
sb.Append(",")
sb.Append("Managing District")
sb.Append(",")
sb.Append("Location Description")
sb.Append(",")
sb.Append("Work Type")
sb.Append(",")
sb.Append(estdate_header)
sb.Append(",")
sb.Append(estamt_header)
sb.Append(vbCrLf)
Dim i As Integer
Dim j As Integer
Dim report As String = String.Empty
Dim sbdatarow As New System.Text.StringBuilder
For i = 0 To ds.Tables(0).Rows.Count - 1
Dim sb1 As New System.Text.StringBuilder
For j = 0 To ds.Tables(0).Columns.Count - 1
sb1.Append(ds.Tables(0).Rows(i)(j).ToString)
sb1.Append(",")
Next
criteria = sb1.ToString
sbdatarow.Append(criteria)
sbdatarow.Append(vbCrLf)
Next
Dim finalreport As String = sb.ToString & sbdatarow.ToString
Response.ContentType = ""
Response.AppendHeader("Content-Disposition", "inline;filename=ExcelReport.csv")
Response.Write(finalreport)
Response.End()
end sub
Gridview Methoddirectly exporting a gridview to excel)
Protected Sub lbl_exp_excel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbl_exp_excel.Click
gv_excel.Visible = True
cesum_ptitle = Session("page_title")
Dim tw As New StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()
Dim FileName As String = "BillingWorkSheet" & Date.Now.ToShortDateString
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename =" & FileName & " report.xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(gv_excel()) ' this is the gridview control
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
End Sub
XSLT MethodStreaming the data from dataset to XSL file added in the project(excel_export.xsl))
Protected Sub lbtn_exp_excel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtn_exp_excel.Click
Dt_RwSum = Obj_RightofWayBll.RwSum_Retrive(view_type, est_type, rwsum_fdate, rwsum_tdate) ' this is the datatable
Dim ds As New DataSet
ds.Tables.Add(Dt_RwSum)
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Dim xdd As XmlDataDocument = New XmlDataDocument(objDataset)
Dim xt As XslCompiledTransform = New XslCompiledTransform()
xt.Load(Server.MapPath("excel_export.xsl"))
xt.Transform(xdd, Nothing, Response.OutputStream)
Response.End()
end sub
I am tired of trying all the method, Please help me out to avoid that excel 2007 prompt window. My user is very strict in avoiding this prompt.
Thanks in advance
Mohamed
Hello Mohamed, same Problem here. Our CMS returns all stored files via Retrieve.aspx, setting the correct MIME type. Excel compains that the URL has an aspx extension.
How can we and our customers get rid of this?
thank you for any help
thomas
这篇关于将数据导出到Excel 2007时如何避免Excel提示窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
