Downloading DataSet to ExcelRSS

• • Reply
  usftubaz

  Member

  5 Points

  29 Posts

  Downloading DataSet to Excel

  Sep 19, 2014 01:04 PM|usftubaz|LINK

  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();

• • Reply
  csharpgreg

  Member

  670 Points

  188 Posts

  Re: Downloading DataSet to Excel

  Sep 19, 2014 02:31 PM|csharpgreg|LINK

  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!

• • Reply
  Eileen ni -...

  All-Star

  19899 Points

  2016 Posts

  Re: Downloading DataSet to Excel

  Sep 28, 2014 01:05 AM|Eileen ni - MSFT|LINK

  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

  We are trying to better understand customer views on social support experience. Click HERE to participate the survey. Thanks!