ASP.NET导出到Excel在电子表格中显示html标记 [英] ASP.NET export to Excel shows html markup in spreadsheet

问题描述

目前为止，我已经尝试过许多文章，发现没有什么可以正常工作。以下方法生成excel电子表格，但打开时单元格包含html标签和实际内容。显然，我不想在电子表格中...

  private static void ExportDataSetToExcel（DataTable dataTable，string filename）
 {
 HttpResponse response = HttpContext.Current.Response; 
 
 //先让我们清理response.object 
 response.Clear（）; 
 response.Charset =; 
 
 //为excel设置响应mime类型
 response.ContentType =application / vnd.ms-excel; 
 response.AddHeader（Content-Disposition，attachment; filename = \+ filename +\）; 
 
 //创建一个字符串作者
使用（StringWriter sw = new StringWriter（））
 {
 using（HtmlTextWriter htw = new HtmlTextWriter（sw））
 {
 //实例化一个datagrid 
 DataGrid dg = new DataGrid {DataSource = dataTable}; 
 dg.DataBind（）; 
 dg.RenderControl（htw）; 
 response.Write（sw.ToString（））; 
 response.End（）; 
} 
} 
} 
  

解决方案

严格地说这不是导出到Excel，这是一个黑客，基于事实，Excel将读取html表，并且您向客户端发送错误的参数，因为生成的内容是html而不是excel文件。因此，对于大多数黑客，它将在某些情况下工作。

使用知道生成真正excel文件的组件会更好，对于xlsx，您可以使用 EPPlus ，而对于xls ExcelLibrary

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

解决方案

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

这篇关于ASP.NET导出到Excel在电子表格中显示html标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！