none
How to avoid the Excel prompt window when exporting data to Excel 2007 RRS feed

  • Question

  • 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 MethodSaddirectly 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 MethodSadStreaming 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 

     

    Saturday, June 23, 2007 12:57 AM

All replies

  • 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

     

    Tuesday, August 7, 2007 4:31 PM
  • Same problem here <bump>

     

    Thursday, August 9, 2007 10:34 AM
  • Same problem here...any luck with a solution?

    Monday, August 20, 2007 5:22 PM
  • no luck i'm afraid. needed t abandon this approach, and use a client side xml-excel conversion instead
    Monday, August 20, 2007 7:38 PM
  • One way I've found around this issue is to abandon the HTML table rendering approach and instead do the following:

     

    1) First save a single excel worksheet from Excel 2007 client app as a xls in compatibility mode - this will serve as your "template".

    2) In your aspx page - using excel interop - open the existing template and insert your rows. (eg Workbooks.Open).

    3) SaveCopyAs to your new location.

    4) Response.Redirect to the new file.

     

    Once you've created your new file alternatively you can read and binary write the new file contents to your response stream instead of Response.Redirect if you want to keep your server free from too many rogue xls files.

     

    The trick here is in opening and modifying an existing xls saved in compatibility mode. A variety of methods then can be used for inserting your new rows and saving the new workbook.

     

    - rob.

    Monday, August 27, 2007 12:06 AM
  • any updates to this thread?  Would hope there is a 'simpler' workaround than what was posted here.

    Wednesday, December 26, 2007 9:00 PM
  • But what if you are referncing the 12.0 object assembly and the user does not have Office 2007...They wont have that in their GAC.

     

    Monday, January 7, 2008 10:37 PM
  •  

    I found a reasonable solution for our CMS and posted it here. Checking back I see that my posting obviously did not make it, so here it is again. I simply add some parameter and the extension "xls" to the url as the value of a dummy parameter:

     

    string url = "Retrieve.aspx?id=" + id;

    #region excel security workaround:

    if (extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase) ||

       extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))

    {

    url += "&extension=" + extension; // you can use "&dummy=" as well

    }

    #endregion

     

    so

    "Retrieve.aspx?id=11111" becomes "Retrieve.aspx?id=11111&extension=xls"

     

    it could also be "Retrieve.aspx?id=11111&dummy=xls"

     

    The deal is just to have an url with xls at the end.

     

    This trouble has gone for us since then, maybe it works for you as well.

     

    cheers

    thomas

    Thursday, January 17, 2008 10:37 PM
  • I tried your fix (adding xls to the url) but I still get the prompt.  fyi....I'm using similar code as the sstart of this thread by arifmd2 in "Gridview Method directly exporting a gridview to excel".  Could you perhaps post your entire page that this process that works is contained in?   Maybe that would help.

    Monday, January 21, 2008 3:10 PM
  •  

    Nancy, actually it turned out that the workaround above does NOT word. I have no solution either for this stupid problem. The most funny thing is that if I answer the dialog with "NO" (do not open the file) it nevertheless opens it.

     

    MS pleeeeease remove this from Excel.

    Monday, January 21, 2008 9:52 PM
  • Just a quick response to this, Microsoft has posted a KB arcticle that shows you how to get rid of the warning.

     

    In Excel 2007 they turned the warning on by default.

     

    http://support.microsoft.com/kb/948615

     

    If in a corporate environment, you can set a GPO to apply the setting.

     

    If in a home environment, you need the end user to run a .REG file, or to apply the registry setting themselves.

     

    The process is outlined in the above link.

     

    Cheers

    Tuesday, April 15, 2008 2:36 AM
  • Maybe I'm missing parts, but what about dropping the idea of generating the workbook in Excel 2003 format, and create the .xlsx it's craving for? I think there's a patch for Excel 2003 to read xlsx format, is there?

    If so, this resolved thread proposes an interesting work-around: http://social.msdn.microsoft.com/Forums/en/clr/thread/349c981b-1488-4aa1-ac19-a8504e108a99


    Thursday, August 18, 2011 6:52 AM