CODEWITHSUNDEEP
asp.netexcelexport
Keith Myers
Keith Myers

Reputation: 1369

ASP.NET export to Excel shows html markup in spreadsheet

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

Upvotes: 0

Views: 2951

Answers (3)

Sharique Hussain Ansari
Sharique Hussain Ansari

Reputation: 1456

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

Upvotes: 0

Antonio Bakula
Antonio Bakula

Reputation: 20693

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

Upvotes: 1

K Mehta
K Mehta

Reputation: 10533

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

Upvotes: 1

Related Questions