locked
Downloading DataSet to Excel RRS feed

  • Question

  • User-1720307078 posted

    Hello everyone.  I am having a problem with how Excel reads a file once it is saved.  In a number of places in my ASP.NET/C# app I need to export data to Excel files.  I have come up with a way to get the data in Excel fine, but I would like to fix the bugs along the way.  The code will follow.

    The first bug is the application used in the Response.  The file download window shows the file type as Microsoft Excel 97-2003 Worksheet.  I would like to have at least Excel 2007.

    The second bug is when I open the file in Excel I get an error 'The file format and extension of '**filename**' don't match.  The file could be corrupted or unsafe.  Unless you trust its source, don't open it.  Do you want to open it anyway?'.  When I open the file everything looks fine, all the data that is supposed to be there is there.

    If anyone has a solution to these it would be greatly appreciated.  Thanks.

    Code:

            string path = "E:\\Temp\\" + ru.UserLastName + "-download.xls";
            if (!File.Exists(path))
            {
                using (StreamWriter sw = File.CreateText(path))
                {
                    sw.Write("");
                }
            }

            DataTable dt = new DataTable();

            //Filling DataTable

            DataSet ds = new DataSet("Crack Data");
            ds.Tables.Add(dt);
            Save(path, ds);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=\"" + path + "\"");
            Response.TransmitFile(path);
            Response.End();

    Friday, September 19, 2014 1:04 PM

All replies

  • User103196646 posted

    Hello Usftubaz! Thanks for your post!

    Please take a look at this post and see the correct answer by "bullpit": http://forums.asp.net/t/1221467.aspx?Export+GridView+to+Excel+xlsx+Excel+2007+Format

    protected void bttnExportXL_Click(object sender, EventArgs e)
        {
           gridViewMaster.AllowPaging = false;
           gridViewMaster.AllowSorting = false;
            Response.Clear();
    
            //Response.AddHeader("content-disposition", "attachment;filename=Report_"+ DateTime.Now.ToShortDateString() +".xlsx");
            Response.AddHeader("content-disposition", "attachment;filename=Report.xlsx");
            Response.Charset = "";
    
            // If you want the option to open the Excel file without saving than
            // comment out the line below
            // Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    
            //Response.ContentType = "application/vnd.xls";
            
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    
            foreach (GridViewRow r in gridViewMaster.Rows)
            {
                if (r.RowType == DataControlRowType.DataRow)
                {
                    for (int columnIndex = 0; columnIndex < r.Cells.Count; columnIndex++)
                    {
                        r.Cells[columnIndex].Attributes.Add("class", "text");
                    }
                }
            }
            
            gridViewMaster.RenderControl(htmlWrite);
            
            string style = @"&lt;style> .text { mso-number-format:\@; } </style> ";
            Response.Write(style);
          
            Response.Write(stringWrite.ToString());
            Response.End();
            
        }

    Regards!

    Friday, September 19, 2014 2:31 PM
  • User1176121428 posted

    Hi usftubaz,

    Thanks for your post.

    To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:

    var ds = new DataSet();
                var dt = new DataTable("TableName For Sheet1");
                dt.Columns.Add("col1");
                dt.Columns.Add("col2");
                dt.Rows.Add("Value1", "Value2");
    
                var dt2 = new DataTable("TableName For Sheet2");
                dt2.Columns.Add("col1");
                dt2.Columns.Add("col2");
                dt2.Rows.Add("Value1", "Value2");
                ds.Tables.Add(dt);
                ds.Tables.Add(dt2);
                ExcelHelper.ToExcel(ds, "test.xls", Page.Response);    

    More information,you can check Source link:

    #Export DataSet to Multiple Excel Sheets

    http://www.codeproject.com/Articles/31516/Export-DataSet-to-Multiple-Excel-Sheets

    Hope this can be helpful.

    Best Regards,

    Eileen

    Sunday, September 28, 2014 1:05 AM
  • User-1320219558 posted

    you can try https://zetexcel.com/. it has the ability to generate, modify, convert, render and print spreadsheets without using Microsoft Excel.

    Wednesday, April 29, 2020 10:48 AM