c# mvc 导出数据到execl

前台代码：

@{

Layout = null;
}

<meta name="viewport" content="width=device-width" />
<title>考核统计</title>
<!--jquery库-->
<script src="~/Scripts/jquery-1.8.2.min.js"></script>
<!--easyui库-->
<link href="~/jquery-easyui-1.3.1/themes/default/easyui.css" rel="stylesheet" />
<link href="~/jquery-easyui-1.3.1/themes/icon.css" rel="stylesheet" />
<link href="~/Content/css/table_dw1.css" rel="stylesheet" />
<link href="~/Content/css/tableinfo1.css" rel="stylesheet" />
<script src="~/jquery-easyui-1.3.1/jquery.easyui.min.js"></script>
<script src="~/jquery-easyui-1.3.1/locale/easyui-lang-zh_CN.js"></script>

<script src="~/Content/js/Jquery.easyui.tooltip.js"></script>
<script src="~/Content/js/common.js"></script>
<script src="~/Content/js/jquery.upload.js"></script>
<link href="~/Content/css/uploadfile.css" rel="stylesheet" />
<!--设置datagrid的字体和行高-->
<style type="text/css">
    .datagrid-row {
        height: 30px;
    }

    .datagrid-cell {
        font-size: 12px;
    }

    a:link, a:visited {
        text-decoration: none; /*超链接无下划线*/
    }

    a:hover {
        text-decoration: underline; /*鼠标放上去有下划线*/
    }

    .tablecombobox .combo {
        border: 1px solid white;
    }



    .tableForm th {
        font-weight: 500;
        text-align: left;
        padding: 3px;
        background-color: white;
    }

    .tableForm td {
        text-align: left;
        padding: 3px;
        background-color: white;
    }

    .datagrid-toolbar {
        height: 25px;
        background: #eef7fc;
        padding: 0px 20px;
        /*border-top:1px solid #bdd1f6;*/
        border-bottom: 1px solid #bdd1f6;
    }

    .datagrid-td-rownumber {
        background: #eef7fc;
    }

    .datagrid-header {
        background: #eef7fc;
        border-color: #bdd1f6;
    }

    .datagrid-body td {
        border-style: dotted;
        border-color: #bdd1f6;
    }

    .datagrid-pager {
        background: #eef7fc;
        border-top: 1px solid #ccc;
        position: relative;
    }
    /*.datagrid-pager{
            background:white;
            border-top:1px solid #ccc;
            position:relative;
        }*/
</style>


<script type="text/javascript">

    var dgSafeAQY = null;



    $(document).ready(function () {
       
       // init_datagrid();//初始化表
        //xmbh = $('#xmbh').val();
        $.ajax({     //请求当前用户可以操作的按钮
            url: "/SystemManager/GetUserAuthorizeButton2",
            type: "post",
            data: { "KeyCode": "KHTJ" },
            dataType: "json",
            success: function (data) {
                if (data.success) {


                    if (data.search) {     //判断是否有浏览权限
                        toolbar = getToolBar(data);      //common.js
                        init_datagrid();//初始化表
                        init_form();

                    }
                    else {
                        $.messager.alert("提示", "无权限，请联系管理员！");
                    }
                }
                else {
                    $.messager.alert("错误", "请求按钮错误！");
                }



            }
        });

    });


    //初始化表
    function init_datagrid() {


        dgSafeAQY = $("#datagrid").datagrid({
            url: "/SafeKHPJ/KHDFTJManager",
            //title: "通知列表",
            // iconCls: "icon-save",
            fit: true,
            singleSelect: true,
            pagination:false,//显示分页工具栏
            pagePosition: "bottom",
            pageSize: 50,
            pageList: [15, 20, 25,50],
            fitColumns: true,
            checkOnSelect: false,
            nowrap: false,
            rownumbers: true, //行号
            toolbar: toolbar.length == 0 ? null : toolbar,
            idField: "Id",
            sortName: "Id",
            sortOrder: "asc",
            selectOnCheck: true,
            striped: true,
            remoteSort: false,
            columns: [[
               { field: 'Id', title: 'Id', width: 50, checkbox: true, align: 'center',hidden:true },

               {
                   field: 'BMMC', title: '部门名称', width: 50, align: 'center', sortable: "true",
                   sorter: function (a, b) {
                       return (a > b ? 1 : -1);
                   }
               },
                { field: 'BMID', title: '部门ID', width: 50, checkbox: true, align: 'center', hidden: true },
               {
                   field: 'GZGW', title: '工作岗位', width: 100, align: 'center', sortable: "true",
                   sorter: function (a, b) {
                       return (a > b ? 1 : -1);
                   }
               },
               {
                   field: 'XM', title: '姓名', width: 50, align: 'center', sortable: "true",
                   sorter: function (a, b) {
                       return (a > b ? 1 : -1);
                   }
               },
               {
                   field: 'DF', title: '最终得分', width: 100, align: 'center', sortable: "true",
                   sorter: function (a, b) {
                       return (a > b ? 1 : -1);
                   }
               },
               {
                   field: 'Grade', title: '等级', width: 100, align: 'center', sortable: "true",
                   sorter: function (a, b) {
                       return (a > b ? 1 : -1);
                   }
               },
            ]]

        });
    }
    //考核统计
    function KHTJStatistic()
    {
       
            
            $.ajax({

                url: "/SafeKHPJ/KHTJDF",
                dataType: 'text',
                type: "post",
                data:
                    {
                        
                    },
                success: function (data) {

                    if (data == "OK") {

                        $.messager.alert("提示", "统计成功！");
                        //刷新主表
                        dgSafeAQY.datagrid('reload');

                    }
                    else {

                        $.messager.alert("提示", "统计失败！");

                    }
                }
        })
    }
    function init_form() {
        form_hid = $('#hidview').find('form');
    }

    //导出到Excel（这里必须要用form表单提交，用ajax提交导出不了）
    function KHTJExport()
    {
        $.messager.confirm('询问', "是否导出考核统计得分表", function (b) {
            if (b) {
                form_hid.form('submit', {
                    url: "/SafeKHPJ/ExportKHDF_TJ",

                    success: function (data) {
                        if (data != null) {
                            $.messager.alert("导出失败！");
                        }
                    }

                })
            }

        })
    }
    function GetYear() {

            var currQuarter1 = "";
            var nowyear = new Date();
            year = nowyear.getFullYear();
            var month = nowyear.getMonth();//获取的月份是0-11
            currQuarter = Math.floor((month % 3 == 0 ? (month / 3) : (month / 3 + 1)));//获得当前季度
            if (currQuarter == 1||currQuarter==0) {
                currQuarter1 = "一";
            }
            else if (currQuarter == 2) {
                currQuarter1 = "二";
            }
            else if (currQuarter == 3) {
                currQuarter1 = "三";
            }
            else {
                currQuarter1 = "四";
            }

            $("#yearmonth").text(year + "年" + "第" + currQuarter1 + "季度");



    }

</script>

    @*<div data-options="region:'north',split:false,border:false" style="padding: 0px 10px 0px 10px; height: 50px">
        <div style="padding: 0px 0px 10px;font-size:20px;text-align:center">管理工勤人员<span id="yearmonth"></span>考核表</div>
        <div style="font-size:14px;color:red">
            备注：所有参与考核的人员打分时，按照优秀(100-90分)、良好(89-80分)、合格(79-60分)、不合格(59分及以下)给出，且优秀不高于20%(6个)、合格以及下不低于20%(6个)。
        </div>
    </div>*@

    <!--显示文件data-options="fit:true"-->

    <div data-options="region:'center',border:false" style="padding: 0px 10px 0px 10px;">

        <table id="datagrid" data-options="fit:true"></table>

    </div>



</div>

<!--隐藏的导出视图  -->
<div id="hidview" closed="true">
    <form id="form4" name="form4" method="post">
        <input type="text" name="ND" id="nd1" />
    </form>
</div>

//后台代码

    public void ExportKHDF_TJ()
    {
      
        OperContext oc = OperContext.CurrentContext;
        List<T_KHJDPZ> listsafezzgj = oc.BllSession.IT_KHJDPZBLL.GetListBy1(x => true).ToList();
        var list = listsafezzgj[listsafezzgj.Count - 1];//返回最新第一条季度设置表里数据
        string ND = list.ND.ToString();//获取设置里的年度
        string JD = list.JD.ToString();//获取设置里的季度  
        List<T_KHDFTJ> lists1 = oc.BllSession.IT_KHDFTJBLL.GetListBy1(x => x.ND == ND&&x.JD==JD);
        var orderlist = (from e in lists1 orderby e.DF descending select e).ToList();
        DataTable dt = orderlist.ConvertToDataTable();
        List<T_KHDFTJ> lists = (List<T_KHDFTJ>)ModelConvertHelper<T_KHDFTJ>.ConvertToModel(dt);
        //创建工作簿对象
        HSSFWorkbook hssfworkbook;
        //打开模板文件到文件流中
        try
        {
            using (FileStream file = new FileStream(HttpContext.Server.MapPath("../File/ExcelFile/Template/") + "考核统计得分表.xls", FileMode.Open, FileAccess.Read))
            {
                //将文件流中模板加载到工作簿对象中
                hssfworkbook = new HSSFWorkbook(file);
            }
            //建立一个名为Sheet1的工作表
            ISheet sheet = hssfworkbook.GetSheet("Sheet1");
            insertdataSafeJTTJ2(sheet, lists);
            //设置响应的类型为Excel
            Response.ContentType = "application/vnd.ms-excel";
            //设置下载的Excel文件名
            string strTemp = Server.UrlEncode("考核统计得分表.xls");//解决文件名乱码
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", strTemp));
            //Clear方法删除所有缓存中的HTML输出。但此方法只删除Response显示输入信息，不删除Response头信息。以免影响导出数据的完整性。
            Response.Clear();
            using (MemoryStream ms = new MemoryStream())
            {
                //将工作簿的内容放到内存流中
                hssfworkbook.Write(ms);
                //将内存流转换成字节数组发送到客户端
                Response.BinaryWrite(ms.GetBuffer());
                Response.End();
            }


    

        }
        catch (Exception ex)
        {
            throw ex;
        }


    }
    private void insertdataSafeJTTJ2(ISheet sheet, List<T_KHDFTJ> jttjlists)
    {
        for (int i = 0; i < jttjlists.Count; i++)
        {
            IRow row = sheet.GetRow(2 + i);
            row.GetCell(0).SetCellValue(i + 1);//排名
            row.GetCell(1).SetCellValue(jttjlists[i].BMMC);//部门名称
            row.GetCell(2).SetCellValue(jttjlists[i].GZGW);//工作岗位
            row.GetCell(3).SetCellValue(jttjlists[i].XM);//姓名
            row.GetCell(4).SetCellValue(Convert.ToDouble(jttjlists[i].DF)); //得分
            row.GetCell(5).SetCellValue(jttjlists[i].Grade);//等级



        }
    }