How to avoid the Excel prompt window when exporting data to Excel 2007
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 Method
directly 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
