使用C#或javascript将Table里的数据导出到Excel
原文:使用C#或javascript将Table里的数据导出到Excel
Demo效果图:
用C#将Table数据导出Excel:
本方法已经将导出excel做成分部视图,引用时只需在视图中使用如下代码即可:
@{ Html.RenderAction("Index", "ExportExcel", new { divId = "report", filename = "hidTitle" }); }
其中divId为table外层紧连的div的Id,filename为导出的excel名字。本模块使用MVC,以下为Controller部分,其中Index对应所述分布视图。
public class ExportExcelController : Controller { [HttpPost] [ValidateInput(false)] public ActionResult DownloadReport(FormCollection form) { string excelContent = form["hidTable"]; string filename = form["hidFileName"]; ExportToExcel("application/ms-excel", filename + ".xls", excelContent); return View(); } public void ExportToExcel(string FileType, string FileName, string ExcelContent) { System.Web.HttpContext.Current.Response.Charset = "UTF-8"; System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()); System.Web.HttpContext.Current.Response.ContentType = FileType; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.HttpContext.Current.Response.Output.Write(ExcelContent.ToString()); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.End(); } public ActionResult Index(string divId, string filename) { ViewBag.HidDivId = divId; ViewBag.FileName = filename; return PartialView(); } }
分布视图代码:
@{ Layout = null; } <script type="text/javascript" src="~/Scripts/jquery-1.10.2.js"></script> <form action="/ExportExcel/DownloadReport" method="post" style="display:inline"> <input type="hidden" id="hidTable" name="hidTable" value="" /> <input type="hidden" id="hidFileName" name="hidFileName" value="" /> <input type="submit" name="btn" id="btnclick" class="btn blue" value="导出Excel文件" /> </form> <script> $(function () { $("#btnclick").click(function () { $("#hidTable").val($("#@ViewBag.HidDivId").html()); $("#hidFileName").val($("#@ViewBag.FileName").val()); }); }); </script>
用javascript将Table数据导出Excel:
js文件地址:链接:http://pan.baidu.com/s/1jGwynWy 密码:kay1
使用方法:
<a download="@(ViewBag.ExcelName).xls" href="#" onclick="return ExcellentExport.excel(this, 'excelTable', 'Sheet1');">导出Excel文件</a>
其中excelTable对应table的Id值。
Demo使用源码
@{ Layout = null; ViewBag.Title = "ExcelDemo"; } <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"> <title>@ViewBag.Title</title> <style> * { margin: 0; padding: 0; } .Data { width: 100%; height: 50px; background-color: #00ffff; text-align: center; font-size: 20px; color: red; line-height: 50px; font-weight: bold; } #report { width: 100%; margin:10px 7%; } .tablereport1 tr:nth-child(2n+1) { background: #e4e4e4; } .tablereport1 td { border: none !important; padding: 0px 10px; height: 40px; text-align: center; } .Down { margin: 10px 5px; line-height: 30px; } .Down a { border: 1px solid #a4a4a4; background-color: #e5e5e5; color: #000; font-size: 14px; text-decoration: none; border-radius: 2px; } </style> <script src="~/Scripts/Excel/excellentexport.js"></script> </head> <body> <div class="Data"> 数据源 </div> <input type="hidden" name="hidTitle" id="hidTitle" value="@ViewBag.ExcelName" /> <div id="report"> <table border="1" class="tablereport1" cellpadding="0" cellspacing="0" id="excelTable"> <tr> <td colspan="5" style="text-align:center;font-size:20px"> 测试数据表 </td> </tr> @for (int i = 1; i < 6; i++) { <tr> @for (int j = 1; j < 5; j++) { <td>行@(i)列@(j)</td> } </tr> } </table> </div> <div class="Data"> 数据导出 </div> <div class="Down"> 用C#将Table里的数据导出到Excel:<br /> @{ Html.RenderAction("Index", "ExportExcel", new { divId = "report", filename = "hidTitle" }); } </div> <div class="Down"> 用js将Table里的数据导出到Excel:<br /> <a download="@(ViewBag.ExcelName).xls" href="#" onclick="return ExcellentExport.excel(this, 'excelTable', 'Sheet1');">导出Excel文件</a> </div> </body> </html>