C# 导出HTML为Excel
最近在项目中需要Excel导出，有多个Sheet页，每个Sheet页的内容比较多，且不是规整的表格，绑定值是个比较麻烦的事，便考虑直接将HTML转换为Excel文件进行导出。
一、使用JS方法将HTML导出为Excel
原理就是获取需要导出到Excel的HTML代码，然后利用JS方法进行导出。此代码可以兼容IE8及主流浏览器，但是不支持多个Sheet页的导出，在IE8下也不能自定义Sheet页的名字。
<li> <button id="btnExport" class="btn btn-primary" onclick="javascript:HtmlExportToExcel('mainContent')"> 导出 </button> <a id="dlink" style="display: none;"></a> </li>
//jQuery HTML导出Excel文件(兼容IE及所有浏览器) function HtmlExportToExcel(tableid) { var filename = $('#divTitle').text(); var sheetName = "已开展工作情况"; if (getExplorer() == 'ie' || getExplorer() == undefined) { HtmlExportToExcelForIE(tableid, filename,sheetName); } else { HtmlExportToExcelForEntire(tableid, filename,sheetName) } } //IE浏览器导出Excel function HtmlExportToExcelForIE(tableid, filename,sheetName) { try { var winname = window.open('', '_blank', 'top=10000'); var strHTML = $("#" + tableid).html(); winname.document.open('application/vnd.ms-excel', 'export excel'); winname.document.writeln(strHTML); winname.document.execCommand('saveas', '', filename + '.xls'); winname.close(); } catch (e) { alert(e.description); } } //非IE浏览器导出Excel var HtmlExportToExcelForEntire = (function () { var uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>', base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) } return function (table, name,sheetName) { if (!table.nodeType) { table = $("#" + table); } var ctx = { worksheet: sheetName || 'Worksheet', table: table.html() }; document.getElementById("dlink").href = uri + base64(format(template, ctx)); document.getElementById("dlink").download = name + ".xls"; document.getElementById("dlink").click(); } })() function getExplorer() { var explorer = window.navigator.userAgent; //ie if (explorer.indexOf("MSIE") >= 0) { return 'ie'; } //firefox else if (explorer.indexOf("Firefox") >= 0) { return 'Firefox'; } //Chrome else if (explorer.indexOf("Chrome") >= 0) { return 'Chrome'; } //Opera else if (explorer.indexOf("Opera") >= 0) { return 'Opera'; } //Safari else if (explorer.indexOf("Safari") >= 0) { return 'Safari'; } }
另外，由于生成的不是真正的Excel文件，只是把HTML转换为Excel的文件，会出现如下提示，用户体验不好。
二、利用后台方法将HTML导出为Excel
原理与第一种方法类似，也是将HTML代码导出为Excel，只是改用后台文件流方式，避免了浏览器兼容性问题。
由于也是HTML导出为Excel文件，也会出现文件扩展名不一致的提示。
思路是：点击导出按钮时，获取要导出内容的HTML代码，并放到Hidden控件中，利用Form Post提交方式传送到后台Action方法，在Action方法中构建Sheet页的Dictionary，key是SheetName，Value是Sheet的HTML代码，然后循环Dictionary生成多个Sheet页，并导出。
<form id="form1" method="post">
@Html.Hidden("tbBMXXHTML")
@Html.Hidden("tbBMXXTitle")
@Html.Hidden("FileTitle")
</form> ------------- <li> <button id="btnExport" class="btn btn-primary" type="submit" onclick="ExportExcel()" > 导出 </button> </li>
//导出Excel function ExportExcel() { var URL = '@Url.Action("GreenCarSummaryExport", "GreenCar", new { area = "GreenCar" })'; var FileTitle = $("#divTitle").text(); $("#FileTitle").val(FileTitle); $("#tbBMXXHTML").val(encodeURI($("#tbBMXX").html())); $("#tbBMXXTitle").val("已开展工作情况"); window.form1.action = URL; window.form1.submit(); }
public void GreenCarSummaryExport(FormCollection collection) { string tbBMXXHTML = HttpUtility.UrlDecode(collection["tbBMXXHTML"]); string tbBMXXTitle = collection["tbBMXXTitle"]; string FileTitle = collection["FileTitle"]; Dictionary<string, string> dicSheet = new Dictionary<string, string>(); dicSheet.Add(tbBMXXTitle, tbBMXXHTML); //把HTML转换为Excel HTMLToExcelHelper.ExportHTMLToExcel(dicSheet, FileTitle); }
/// <summary> /// 导出HTML为Excel文件 /// </summary> /// <param name="dicSheet">导出内容：key是SheetName，Value是HTML代码</param> /// <param name="fileTitle">文件名</param> public static void ExportHTMLToExcel(Dictionary<string, string> dicSheet, string fileTitle) { StringBuilder sbBody = new StringBuilder(); StringBuilder sbSheet = new StringBuilder(); //定义Excel头部 sbBody.AppendFormat( "MIME-Version: 1.0\r\n" + "X-Document-Type: Workbook\r\n" + "Content-Type: multipart/related; boundary=\"-=BOUNDARY_EXCEL\"\r\n\r\n" + "---=BOUNDARY_EXCEL\r\n" + "Content-Type: text/html; charset=\"gb2312\"\r\n\r\n" + "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" + "<head>\r\n" + "<xml>\r\n" + "<x:ExcelWorkbook>\r\n" + "<x:ExcelWorksheets>\r\n"); //定义Sheet foreach (KeyValuePair<string, string> kv in dicSheet) { string gid = Guid.NewGuid().ToString(); sbBody.AppendFormat("<x:ExcelWorksheet>\r\n" + "<x:Name>{0}</x:Name>\r\n" + "<x:WorksheetSource HRef=\"cid:{1}\"/>\r\n" + "</x:ExcelWorksheet>\r\n" , kv.Key , gid); sbSheet.AppendFormat( "---=BOUNDARY_EXCEL\r\n" + "Content-ID: {0}\r\n" + "Content-Type: text/html; charset=\"gb2312\"\r\n\r\n" + "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" + "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" + "<head>\r\n" + "<xml>\r\n" + "<x:WorksheetOptions>\r\n" + "<x:ProtectContents>False</x:ProtectContents>\r\n" + "<x:ProtectObjects>False</x:ProtectObjects>\r\n" + "<x:ProtectScenarios>False</x:ProtectScenarios>\r\n" + "</x:WorksheetOptions>\r\n" + "</xml>\r\n" + "</head>\r\n" + "<body>\r\n" , gid); sbSheet.Append("<table border='1'>"); sbSheet.Append(kv.Value); sbSheet.Append("</table>"); sbSheet.Append("</body>\r\n" + "</html>\r\n\r\n"); } //定义Excel尾部 StringBuilder sb = new StringBuilder(sbBody.ToString()); sb.Append("</x:ExcelWorksheets>\r\n" + "</x:ExcelWorkbook>\r\n" + "</xml>\r\n" + "</head>\r\n" + "</html>\r\n\r\n"); sb.Append(sbSheet.ToString()); sb.Append("---=BOUNDARY_EXCEL--"); //导出文件 HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.Buffer = true; bool isFireFox = false; if (HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1) { isFireFox = true; } if (isFireFox) { HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileTitle + ".xls"); } else { HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(fileTitle)) + ".xls"); } HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); HttpContext.Current.Response.Write(sb.ToString()); HttpContext.Current.Response.End(); }
三、利用第三方插件导出
比如利用NPOI，Aspose，ExcelReport等，需要从数据库重新获取数据并绑定
