首页 > 代码库 > js导出excel
js导出excel
function inportEx() { $("#btnEx").text("导出中..."); var fugNumber = "";//第几次随访 var fugid = "";//随访组Id var fudid = "";//医生id var zTree = $.fn.zTree.getZTreeObj("tree"); var nodes = zTree.getSelectedNodes(); //获取选中节点的id值 if (nodes.length > 0) { var pnode = nodes[0].getParentNode(); if (pnode != null && pnode != "") { fugid = pnode.FuGID; } // fudid = nodes[0].FuGID; } //获取需要导出excel的患者的id var chek_value_id = []; $(‘input[name="checkItem"]:checked‘).each(function () { chek_value_id.push($(this).val()); }); var treeObj = $.fn.zTree.getZTreeObj("treeExcel"); var nodes = treeObj.getCheckedNodes(true); //将患者id数组转化为字符串传到后台 var pId = chek_value_id.join(","); $.ajax({ url: ‘/Patient/SaveExcel‘,// 跳转到 action data: { pid: pId, pfid:pfid, fugid: fugid }, type: ‘get‘, cache: false, dataType: ‘json‘, success: function (result) { console.log(result); if (checkSession(result)) { if (result != null && result.length > 0) { console.log(result); downloadFile(result); } } }, complete: function (XMLHttpRequest, textStatus) { pfid = ""; $(‘#myModal‘).modal("hide"); } }); } //文件下载 function downloadFile(url) { try { var elemIF = document.createElement("iframe"); elemIF.src = url; elemIF.style.display = "none"; document.body.appendChild(elemIF); } catch (e) { } }
public async Task<JsonResult> SaveExcel(string pId, string pfid, string fugid) { //使用js导出excel string dir = Server.MapPath("/"); string serverUrl = HttpContext.Request.Url.Authority; string filePath = "Excel\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + "\\"; dir = dir + filePath; string fileName = "患者管理" + DateTime.Now.ToString("yyyyMMddhhmmss"); string path = dir + fileName + ".xls"; string rtnValue = http://www.mamicode.com/"http://" + serverUrl + "/Excel/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "/" + fileName + ".xls"; if (!Directory.Exists(dir)) { //创建文件所在目录 Directory.CreateDirectory(dir); } //获取该随访组的随访计划和问卷信息 List<FllowAndPlanName> list = new List<FllowAndPlanName>(); list = await patientBLL.SelectFllowPlan(fugid); int listGroup = (list.GroupBy(x => x.FllowPlan_id)).Count(); var _list = await patientBLL.getFllowInfoList(fugid); #region 绘制表头 string[] arr = { "序号", "姓名", "性别", "年龄", "联系电话", "随访医生", "最近一次随访", "计划次数", "共随访次数", "病历数", "咨询数", "下次随访时间", "状态" }; HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet1 ISheet sheet1 = book.CreateSheet("Sheet1"); CellRangeAddress m_region = new CellRangeAddress(0, 1, 0, arr.Length - 1); //合并0列的n--n+2行 sheet1.AddMergedRegion(m_region); IRow row = sheet1.CreateRow(0); ICell cell = row.CreateCell(0); cell.SetCellValue("基本信息"); ICellStyle cellstyle = book.CreateCellStyle();//设置垂直居中格式 cellstyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 cellstyle.Alignment = HorizontalAlignment.CENTER;//水平居中 cell.CellStyle = cellstyle; IRow row2 = sheet1.CreateRow(2); for (int i = 0; i < arr.Length; i++) { row2.CreateCell(i).SetCellValue(arr[i]); } var title = _list.GroupBy(x => x.FllowPlan_Name); List<string> planName = new List<string>();//随访计划名称 List<string> fllowName = new List<string>();//问卷名称 Dictionary<string, string> timu = new Dictionary<string, string>();//当前问卷下的问卷题目 List<string> timuList = new List<string>(); Dictionary<string, int> timuResult = new Dictionary<string, int>();//当前问卷下的答案的个数 Dictionary<string, string> fllowInfo = new Dictionary<string, string>();//问卷名称和问卷id var listInfo = list.GroupBy(x => x.FollowInfo_ID).Select(g => g.First()); foreach (var info in listInfo) { fllowInfo.Add(info.FollowInfo_Name, info.FollowInfo_ID); } //获取随访计划和问卷信息 foreach (var item in title) { planName.Add(item.Key.ToString()); foreach (var name in item) { if (!fllowName.Contains(name.FollowInfo_Name)) { fllowName.Add(name.FollowInfo_Name); } if (!timu.ContainsKey(name.Question_Name)) { timu.Add(name.Question_Name, name.FollowInfo_Name); } } } //获取当前问卷下的题目的个数 var tGroup = timu.GroupBy(x => x.Value); foreach (var item in tGroup) { foreach (var count in item) { if (!timuResult.ContainsKey(count.Value)) { timuResult.Add(count.Value, item.Count()); } } } //获取题目集合 foreach (KeyValuePair<string, string> item in timu) { timuList.Add(item.Key); } int rowOne = arr.Length; int rowTwo = arr.Length; //用来控制题目的列数 List<int> timuNum = new List<int>(); IRow row3 = sheet1.CreateRow(0); IRow row4 = sheet1.CreateRow(1); int num = 0; //绘制随访计划列 for (int i = 0; i < planName.Count; i++) { //绘制问卷列 for (int j = arr.Length; j < fllowName.Count + arr.Length; j++) { //获取当前问卷下面的题目个数 int r = timuResult[fllowName[j - arr.Length].ToString()]; sheet1.AddMergedRegion(new CellRangeAddress(1, 1, rowTwo, rowTwo + r - 1)); ICell cellFllow = row4.CreateCell(rowTwo); cellFllow.CellStyle = cellstyle; cellFllow.SetCellValue(fllowName[j - arr.Length].ToString()); rowTwo += r; } sheet1.AddMergedRegion(new CellRangeAddress(0, 0, rowOne, rowOne + timu.Count - 1)); ICell cellPlan = row.CreateCell(rowOne); cellPlan.CellStyle = cellstyle; cellPlan.SetCellValue(planName[i]); rowOne += timu.Count; num += 1; } //绘制题目列 for (int u = 0; u < planName.Count; u++) { for (int k = 0; k < timuList.Count; k++) { ICell cellTimu = row2.CreateCell(timuNum.Count + arr.Length); cellTimu.CellStyle = cellstyle; cellTimu.SetCellValue(timuList[k].ToString()); timuNum.Add(k); } } #endregion #region Databtable的建立 DataTable ExData = new DataTable(); var tablleTitle = await patientBLL.getFllowInfoList(fugid); var infoCode = tablleTitle.GroupBy(x => x.FollowInfo_ID).Select(p => (new { Infoid = p.Key })); List<string> infoCodeList = new List<string>(); foreach (var code in infoCode) { infoCodeList.Add(code.Infoid); } ExData.Columns.Add("序号", System.Type.GetType("System.String")); ExData.Columns.Add("姓名", System.Type.GetType("System.String")); ExData.Columns.Add("性别", System.Type.GetType("System.String")); ExData.Columns.Add("年龄", System.Type.GetType("System.String")); ExData.Columns.Add("联系电话", System.Type.GetType("System.String")); ExData.Columns.Add("随访医生", System.Type.GetType("System.String")); ExData.Columns.Add("最近一次随访", System.Type.GetType("System.String")); ExData.Columns.Add("计划次数", System.Type.GetType("System.String")); ExData.Columns.Add("共随访次数", System.Type.GetType("System.String")); ExData.Columns.Add("病历数", System.Type.GetType("System.String")); ExData.Columns.Add("咨询数", System.Type.GetType("System.String")); ExData.Columns.Add("下次随访时间", System.Type.GetType("System.String")); ExData.Columns.Add("状态", System.Type.GetType("System.String")); //把选择的随访计划和问卷信息做处理 string[] singleInfo = pfid.Split(new char[] { ‘|‘ }, StringSplitOptions.RemoveEmptyEntries); int timeYear = DateTime.Now.Year; string[] singlePid = pId.Split(new char[] { ‘,‘ }, StringSplitOptions.None); //当前随访组下的随访计划 for (int w = 1; w < planName.Count + 1; w++) { for (int t = 0; t < timuList.Count; t++) { ExData.Columns.Add(timuList[t] + w, System.Type.GetType("System.String")); } } //选择患者的个数 for (int p = 0; p < singlePid.Length; p++) { // var excutePlanid = await patientBLL.getExcutePlan_ID(singlePid[p]); DataRow ExRow = ExData.NewRow(); for (int m = 1; m < planName.Count + 1; m++) { #region 获取数据 var res = await patientBLL.SavaExcel(singlePid[p], fugid); ExRow["序号"] = p + 1; ExRow["姓名"] = res[0].Patient_ID.ToString(); ExRow["性别"] = res[0].Patient_Sex.ToString(); ExRow["年龄"] = (timeYear - res[0].age.Value.Year).ToString() + "岁"; ExRow["联系电话"] = res[0].Login_Account.ToString(); ExRow["随访医生"] = res[0].fudname.ToString(); ExRow["最近一次随访"] = res[0].followUp_Last_time.ToString(); ExRow["计划次数"] = res[0].planCount.ToString(); ExRow["共随访次数"] = res[0].followUpCount.ToString(); ExRow["病历数"] = res[0].AdvisoryRecordCount.ToString(); ExRow["咨询数"] = res[0].medicalCount.ToString(); ExRow["下次随访时间"] = res[0].followUp_Next_time.ToString(); ExRow["状态"] = res[0].FuP_Status == "1" ? "在访" : (res[0].FuP_Status == "0" ? "失访" : (res[0].FuP_Status == "-2" ? "死亡" : "出组")); for (int i = 0; i < singleInfo.Length; i++) { string[] info = singleInfo[i].Split(new char[] { ‘,‘ }); //选择了随访计划 if (info[3] == m.ToString() && info.Length == 6) { var quesResult = await patientBLL.SavaExcel(singlePid[p], fugid); var querRes = quesResult.Where(x => x.fllowplanid.Equals(info[0])); foreach (var item in querRes) { if (item.Question_Name != null) { string index = item.Question_Name + m; ExRow[index] = item.Choice_Content; } } } //选择了随访计划的某一个问卷 if (info[3] == m.ToString() && info.Length == 7) { var querResult_1 = await patientBLL.SavaExcel(singlePid[0], fugid); var querRes_1 = querResult_1.Where(x => x.QBClass_Code.Equals(info[4]) && x.fllowplanid.Equals(info[0])); foreach (var item in querRes_1) { if (item.Question_Name != null) { string index = item.Question_Name + m; ExRow[index] = item.Choice_Content; } } } //如果一次随访计划下有多个问卷，选择了一个或者多个问卷 if (info[3] == m.ToString() && info.Length < 6) { string[] condition = info[4].Split(new char[] { ‘*‘ }, StringSplitOptions.RemoveEmptyEntries); for (int k = 0; k < condition.Length; k++) { string[] singCondtion = condition[k].ToString().Split(new char[] { ‘@‘ }); var querResult_2 = await patientBLL.SavaExcel(singlePid[0], fugid); // List<excelModel> querRs = new List<excelModel>(); for (int n = 0; n < singCondtion.Length; n++) { var querRes_2 = querResult_2.Where(x => x.QBClass_Code.Equals(singCondtion[0]) && x.fllowplanid.Equals(info[0])).ToList(); foreach (var item in querRes_2) { if (item.Question_Name != null) { string index = item.Question_Name + m; ExRow[index] = item.Choice_Content; } } //querRs.AddRange(querRes_2); } } } } #endregion } ExData.Rows.Add(ExRow); } //将数据写入到excel for (int i = 0; i < ExData.Rows.Count; i++) { row = sheet1.CreateRow(i + 3); ExData.Rows[i][0] = (i + 1).ToString(); for (int j = 0; j < ExData.Columns.Count; j++) { ICell ce = row.CreateCell(j); ce.SetCellValue(ExData.Rows[i][j].ToString()); } } #endregion using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write)) { book.Write(fs); fs.Close(); } return Json(rtnValue, JsonRequestBehavior.AllowGet); }
js导出excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。