2

I have an sql statements that selects a table of data that i want to export to excel in the .xls format, i added this table to a grid view then rendered that grid view to create an html writer and write it on excel file using asp.net.

But i keep having this warning that the file format and extension does not match. The issue is that the file you are creating is not a genuine Excel file. It's HTML with a .xls extension.

Please, i need to know what is the best way to export these selected data to the xls file without the warning.

2 Answers 2

1

I Have also tried exporting from the dataTable directly, but i still get the warning when tying to open the excel.

   // these namespaces need to be added to your code behind file
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MySpot.UserPages
{
    public partial class Journal : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySpotDBConnStr"].ConnectionString);
        DataTable dt = new DataTable();

        // regular page_load from .aspx file
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
            }
        }

        // added a button with ID=btnDownload and double clicked it's onclick event to auto create method
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string queryStr = "SELECT * from table";
            SqlDataAdapter sda = new SqlDataAdapter(queryStr, conn);
            sda.Fill(dt);
            ExportTableData(dt);
        }

        // this does all the work to export to excel
        public void ExportTableData(DataTable dtdata)
        {
            string attach = "attachment;filename=journal.xls";
            Response.ClearContent();
            Response.AddHeader("content-disposition", attach);
            Response.ContentType = "application/ms-excel";
            if (dtdata != null)
            {
                foreach (DataColumn dc in dtdata.Columns)
                {
                    Response.Write(dc.ColumnName + "\t");
                    //sep = ";";
                }
                Response.Write(System.Environment.NewLine);
                foreach (DataRow dr in dtdata.Rows)
                {
                    for (int i = 0; i < dtdata.Columns.Count; i++)
                    {
                        Response.Write(dr[i].ToString() + "\t");
                    }
                    Response.Write("\n");
                }
                Response.End();
            }
        }
    }
}
Sign up to request clarification or add additional context in comments.

1 Comment

The alert is a new security feature in Excel 2007 called Extension Hardening, which ensures that the file content being opened matches the extension type specified in the shell command that is attempting to open the file. ... This issue is still being investigated, but a fix is not likely until Office 14 given the nature of the complexity of the code, and the fact that Excel does not want to lower the security measure to workaround IE open behaviors without a full understanding of the consequences for other browser users.
0

http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx

The current design does not allow you to open HTML content from a web site in Excel unless the extension of the URL is .HTM/.HTML/.MHT/.MHTML. So ASP pages that return HTML and set the MIME type to something like XLS to try to force the HTML to open in Excel instead of the web browser (as expected) will always get the security alert since the content does not match the MIME type. If you use an HTML MIME type, then the web browser will open the content instead of Excel. So there is no good workaround for this case because of the lack of a special MIME type for HTML/MHTML that is Excel specific. You can add your own MIME type if you control both the web server and the client desktops that need access to it, but otherwise the best option is to use a different file format or alert your users of the warning and tell them to select Yes to the dialog.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.