数据导出excel

JS：

            $.ajax({
                type: 'post',
                url: '/Manager/Salary/ExportAllSaleryData?customerId=' + customerId + '&&childIncluded=' + childIncluded + '&&salaryDateStart=' + salaryDateStart + '&&salaryDateEnd=' + salaryDateEnd,
                async: false,
                success: function (data) {
                    var list = eval(data);
                    if (list.length > 0) {

                        var url = '/Manager/Salary/ExportSaleryData?customerId=' + customerId + '&&childIncluded=' + childIncluded + '&&salaryDateStart=' + salaryDateStart + '&&salaryDateEnd=' + salaryDateEnd;
                        var xhr = new XMLHttpRequest();
                        xhr.open('GET', url, true);//get请求，请求地址，是否异步
                        xhr.responseType = "blob";  // 返回类型blob
                        xhr.onload = function () {// 请求完成处理函数
                            if (this.status === 200) {
                                var blob = this.response;// 获取返回值
                                console.log(blob);
                                var a = document.createElement('a');
                                a.download = '员工薪资档案表.xls';
                                a.href = window.URL.createObjectURL(blob);
                                a.click();
                            }
                        };
                        // 发送ajax请求
                        xhr.send();

                        //window.location = '/Manager/Salary/ExportSaleryData?customerId=' + customerId + '&&childIncluded=' + childIncluded + '&&salaryDateStart=' + salaryDateStart + '&&salaryDateEnd=' + salaryDateEnd;
                        }
                    else {
                        var msgs = "该客户暂无" + salaryDateStart + "-" + salaryDateEnd + "月度数据！";
                        layer.msg(msgs, { icon: 5, time: 3000 });
                    }
                }
            })

　　

应该也可以不使用xhr,用window.locaion就完事了，一般用xhr处理图片之类的。直接<a>标签更省事？

 

后端c#代码：

        public ActionResult SalaryExport()
        {
            return View();
        }
        
        public void ExportSaleryData()
        {
            int childIncluded = Convert.ToInt32(Request["childIncluded"]);
            string customerId = string.IsNullOrEmpty(Request["customerId"]) ? "" : Request["customerId"];
            string salaryDateStart = string.IsNullOrEmpty(Request["salaryDateStart"]) ? null : Request["salaryDateStart"];
            string salaryDateEnd = string.IsNullOrEmpty(Request["salaryDateEnd"]) ? null : Request["salaryDateEnd"];//得到js传来的导出选项

            string templatePath = AppSetting.EmploySalaryInfoExportTemplate;  //文件模板路径
            var salaryDataList = _salaryService.GetExportData(customerId, childIncluded, salaryDateStart, salaryDateEnd);//获取数据


            DataTable dt = new DataTable("EmploySalaryInfoExportTable");
            dt.Columns.Add(new DataColumn("CustomerShortName", typeof(string)));//0
            dt.Columns.Add(new DataColumn("EmployeeName", typeof(string)));//1
            dt.Columns.Add(new DataColumn("IDCard", typeof(string)));//2
            dt.Columns.Add(new DataColumn("SalaryDate", typeof(string)));//3
            dt.Columns.Add(new DataColumn("SetType", typeof(string)));//4
            dt.Columns.Add(new DataColumn("TotalPayAmount", typeof(decimal)));//5
            dt.Columns.Add(new DataColumn("IncomeTax ", typeof(decimal)));//6
            dt.Columns.Add(new DataColumn("TotalDeduction", typeof(decimal)));//7
            dt.Columns.Add(new DataColumn("FinalPayAmount", typeof(decimal)));//8
            foreach (SalaryListOutput salaryListOutput in salaryDataList)
            {
                DataRow dr = dt.NewRow();
                dr[0] = salaryListOutput.CustomerShortName;
                dr[1] = salaryListOutput.EmployeeName;
                dr[2] = salaryListOutput.IDCard;
                dr[3] = salaryListOutput.SalaryDate;
                dr[4] = salaryListOutput.SetType;
                dr[5] = salaryListOutput.TotalPayAmount;
                dr[6] = salaryListOutput.IncomeTax;
                dr[7] = salaryListOutput.TotalDeduction;
                dr[8] = salaryListOutput.FinalPayAmount;
                dt.Rows.Add(dr);
            }
            string fileName = string.Format("员工薪资档案表{0}-{1}-{2}", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
            Util.RenderToBrowser(templatePath, dt, 2, fileName, Response, Request);
        }

        //返回加载的数据
        public JsonResult ExportAllSaleryData()
        {
            int childIncluded = Convert.ToInt32(Request["childIncluded"]);
            string customerId = string.IsNullOrEmpty(Request["customerId"]) ? "" : Request["customerId"];
            string salaryDateStart = string.IsNullOrEmpty(Request["salaryDateStart"]) ? null : Request["salaryDateStart"];
            string salaryDateEnd = string.IsNullOrEmpty(Request["salaryDateEnd"]) ? null : Request["salaryDateEnd"];//得到js传来的导出选项

            string templatePath = AppSetting.EmploySalaryInfoExportTemplate;
            var salaryDataList =  _salaryService.GetExportData(customerId, childIncluded, salaryDateStart, salaryDateEnd);//获取数据


            DataTable dt = new DataTable("EmploySalaryInfoExportTable");
            dt.Columns.Add(new DataColumn("CustomerShortName", typeof(string)));//0
            dt.Columns.Add(new DataColumn("EmployeeName", typeof(string)));//1
            dt.Columns.Add(new DataColumn("IDCard", typeof(string)));//2
            dt.Columns.Add(new DataColumn("SalaryDate", typeof(string)));//3
            dt.Columns.Add(new DataColumn("SetType", typeof(string)));//4
            dt.Columns.Add(new DataColumn("TotalPayAmount", typeof(decimal)));//5
            dt.Columns.Add(new DataColumn("IncomeTax ", typeof(decimal)));//6
            dt.Columns.Add(new DataColumn("TotalDeduction", typeof(decimal)));//7
            dt.Columns.Add(new DataColumn("FinalPayAmount", typeof(decimal)));//8
            foreach (SalaryListOutput salaryListOutput in salaryDataList)
            {
                DataRow dr = dt.NewRow();
                dr[0] = salaryListOutput.CustomerShortName;
                dr[1] = salaryListOutput.EmployeeName;
                dr[2] = salaryListOutput.IDCard;
                dr[3] = salaryListOutput.SalaryDate;
                dr[4] = salaryListOutput.SetType;
                dr[5] = salaryListOutput.TotalPayAmount;
                dr[6] = salaryListOutput.IncomeTax;
                dr[7] = salaryListOutput.TotalDeduction;
                dr[8] = salaryListOutput.FinalPayAmount;
                dt.Rows.Add(dr);
            }
            //JavaScriptSerializer jss = new JavaScriptSerializer();
            System.Collections.ArrayList dic = new System.Collections.ArrayList();
            foreach (DataRow dr in dt.Rows)
            {
                Dictionary<string, object> drow = new Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    drow.Add(dc.ColumnName, dr[dc.ColumnName]);
                }
                dic.Add(drow);
            }
            return Json(JsonConverter.Serialize(dic));
        }

　　

 

emmmm，比较坑的是我忘记在服务器上添加作为文件模板的.xls了，导致本地运行正常，访问服务器上则一直报500，找不到资源。