使用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>

 

posted @ 2015-01-27 14:38  徐航  阅读(2764)  评论(5)  编辑  收藏  举报