locked
export to excel, cant open excel in 2000 RRS feed

  • Question

  • User629360359 posted

    i need to export my gridview to my excel, but have some minor problem..

    when i open in excel 2007, error message:

    "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?"

    and yes, it did open the excel after i press "Yes" button. 

    but when i try to open in excel 2000, error message with "Unable to read file."

    please advice me? below is the code i used:

    protected void button1_Click(object sender, EventArgs e)
    {
       Response.ContentType = "application/ms-excel";      
       Response.AddHeader("Content-Disposition", "inline;filename=report.xls");
    }



    Tuesday, December 18, 2012 3:41 AM

All replies

  • User-821857111 posted

    You are generating an HTML file and attempting to persuade Excel to accept it by adding a .xls extension. From memory, Excel 2000 did not natively support HTML, so it will reject the output. If you really have to work with Excel 2000 (which reached the end of its life more than 3 years ago), you can use JET to populate a worksheet through ADO.NET. 

    Tuesday, December 18, 2012 5:45 AM
  • User629360359 posted

    hi, thx for the reply, i did my research and i found this article:

    http://mudassarkhan.wordpress.com/2009/01/12/export-data-to-excel-adonet/

    if using ADO.net, i need to insert the data to insert row by row. but under the same page, i have a lot of gridviews and contain many differents type of information. if there any shortcut way of doing this? 

    Wednesday, December 19, 2012 7:52 PM
  • User-821857111 posted

    How are you populating your GridViews? SqlDataSource? Linq? DataSet?

    Thursday, December 20, 2012 12:41 AM
  • User629360359 posted

    there are 20 gridviews and 20 sqldatasources

    I believe Excel 2000 cant open because Excel 2000 cannot open xml? 

    if i open my .xls with notepad, it's a html form. 

    Thursday, December 20, 2012 1:07 AM
  • User-821857111 posted

    melvintcs

    if i open my .xls with notepad, it's a html form. 

    As I said in my first reply, you are generating HTML when you render a GridView. And as I also said, Excel 2000 cannot cope with HTML.

    Here's some sample code that takes the data from a SqlDataSource called CustomerDataSource and inserts it into an Excel spreadsheet that is in the App_Data folder. You should be able to adapt it to your needs:

    protected void btnExport_Click(object sender, EventArgs e) {
      var connString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Customers.xls;Extended Properties='Excel 8.0;HDR=Yes;'";
      using (var conn = new OleDbConnection(connString)) {
        var sql = "INSERT INTO [Sheet1$] (CustomerID, CompanyName, ContactName, ContactTitle) VALUES (?,?,?,?)";
        var cmd = new OleDbCommand(sql, conn);
        var dataView = (DataView)CustomerDataSource.Select(DataSourceSelectArguments.Empty);
        var table = dataView.Table;
        conn.Open();
        foreach (DataRow row in table.Rows) {
    
          cmd.Parameters.AddWithValue("", row["CustomerID"]);
          cmd.Parameters.AddWithValue("", row["CompanyName"]);
          cmd.Parameters.AddWithValue("", row["ContactName"]);
          cmd.Parameters.AddWithValue("", row["ContactTitle"]);
    
          cmd.ExecuteNonQuery();
          cmd.Parameters.Clear();
        }
      }
      Response.AddHeader("Content-Disposition", "attachment; filename=Customers.xls");
      Response.ContentType = "application/excel";
      Response.WriteFile(Server.MapPath("~/App_Data/Customers.xls"));
      Response.End();
    }
    

    You will need to add the following to the top of your code-behind:

    using System.Data;
    using System.Data.OleDb;



    Thursday, December 20, 2012 2:15 AM