ASP.NET export to Excel shows html markup in spreadsheet

Question

I've tried many articles so far and found nothing that works correctly. The following method produces an excel spreadsheet, but when opened the cells contain html tags and the actual content. Obviously, I don't want in the spreadsheet...

 private static void ExportDataSetToExcel(DataTable dataTable, string filename)
      {
         HttpResponse response = HttpContext.Current.Response;

         // first let's clean up the response.object
         response.Clear();
         response.Charset = "";

         // set the response mime type for excel
         response.ContentType = "application/vnd.ms-excel";
         response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

         // create a string writer
         using(StringWriter sw = new StringWriter())
         {
            using(HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
               // instantiate a datagrid
               DataGrid dg = new DataGrid {DataSource = dataTable};
               dg.DataBind();
               dg.RenderControl(htw);
               response.Write(sw.ToString());
               response.End();
            }
         }
      }

score 1 · Answer 1 · answered Jun 27 '11 at 18:03

1

Could you not use comma separated values to export to excel?

answered Jun 27 '11 at 18:03

K Mehta

10,323
4
46
76

I'm open to anything that'll work. If you can point me to a solution for this that would be great. If not, I'll search for one later. – Keith Myers Jun 27 '11 at 18:26

Antonio Bakula · Accepted Answer · 2011-06-27T20:24:45.893

1

Stricly talking this is not export to Excel, it's a hack based on fact that Excel will read html table, and you are sending wrong parameters to client because produced content is html, not excel file. So as for most hacks it will work in some conditions.

It would be better to use component that knows to produce real excel file, for xlsx you can use EPPlus, and for xls ExcelLibrary

edited Jun 27 '11 at 20:24

answered Jun 27 '11 at 20:17

Antonio Bakula

20,445
6
75
102

score 0 · Answer 3 · answered Mar 15 '13 at 06:57

Use this code, it is working fine for me.

public void ExportToSpreadsheet(DataTable table, string name)
            {
                try
                {
                    HttpContext context = HttpContext.Current;
                    context.Response.Clear();
                    context.Response.ClearHeaders();
                    String sr = string.Empty;
                    sr += "<html><body><table>";
                    sr += "<tr style=\"background-color:gray;color:white;\">";
                    foreach (DataColumn column in table.Columns)
                    {
                        sr += "<th>";
                        sr += column.ColumnName;
                        sr += "</th>";
                    }
                    sr += "</tr>";
                    sr += Environment.NewLine;
                    foreach (DataRow row in table.Rows)
                    {
                        sr += "<tr>";
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            sr += "<td>";
                            sr += row.ItemArray[i];
                            sr += "</td>";
                        }
                        sr += "</tr>";
                        sr += Environment.NewLine;
                    }
                    sr += "</table></body></html>";               
                    context.Response.ContentType = "application/vnd.ms-excel";               
                    context.Response.AddHeader("Content-Disposition", "attachment; filename=" + name + ".xls");                        
                    context.Response.Write(sr);
                    context.Response.Flush();
                    context.Response.End();
                    context.Response.Close();
                }
                catch (Exception ex)
                {

                }
            }

Hope This Help

ASP.NET export to Excel shows html markup in spreadsheet

3 Answers3

Linked