/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { string column = "HID,AREAVALUE,RISKVALUE,LOCATION,STARTTIME,ENDTIME,STANDARD,DETECTIONUSERNAME,ISEXCESSIVE"; string stringcolumn = "ISEXCESSIVE"; string[] columns = column.Split(','); string[] stringcolumns = stringcolumn.Split(','); string whereSql = ""; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { whereSql = ""; } else { string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value); whereSql += " and " + where; } DataTable dt = hazarddetectionbll.GetDataTable(queryJson, whereSql); DataTable Newdt = AsposeExcelHelper.UpdateDataTable(dt, columns, stringcolumns);//把部分字段转换成string 方便修改 for (int i = 0; i < Newdt.Rows.Count; i++) { Newdt.Rows[i][0] = (i + 1).ToString(); //if (Newdt.Rows[i]["ENDTIME"].ToString() != "")//转换时间格式 //{ // Newdt.Rows[i]["ENDTIME"] = Convert.ToDateTime(Newdt.Rows[i]["ENDTIME"]).ToString("yyyy-MM-dd"); //} if (Newdt.Rows[i]["STANDARD"].ToString() != "") { string[] str = Newdt.Rows[i]["STANDARD"].ToString().Split(';'); string html = ""; for (var j = 0; j < str.Length; j++) { var group = str[j].Split(','); if (j == 0) { html = "指标" + group[0] + ":" + group[1]; } else { html += ";指标" + group[0] + ":" + group[1]; } } Newdt.Rows[i]["STANDARD"] = html; } if (Convert.ToInt32(Newdt.Rows[i]["ISEXCESSIVE"]) == 0) { Newdt.Rows[i]["ISEXCESSIVE"] = "否"; } else { Newdt.Rows[i]["ISEXCESSIVE"] = "是"; } } string FileUrl = @"\Resource\ExcelTemplate\职业病危害因素监测_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "职业病危害因素监测列表", "职业病危害因素监测列表"); return(Success("导出成功。")); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { string column = "IDNUM,OCCDETAILID,USERNAME,USERNAMEPINYIN,INSPECTIONTIME,ISSICK,SICKTYPE,ISENDEMIC,ISUNUSUAL,UNUSUALNOTE"; string stringcolumn = "ISSICK,SICKTYPE"; string[] columns = column.Split(','); string[] stringcolumns = stringcolumn.Split(','); string wheresql = ""; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { wheresql = ""; } else { string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value); wheresql += " and " + where; } DataTable dt = occupationalstaffdetailbll.GetTable(queryJson, wheresql); DataTable Newdt = AsposeExcelHelper.UpdateDataTable(dt, columns, stringcolumns);//把所有字段转换成string 方便修改 //先获取职业病数据 DataItemBLL di = new DataItemBLL(); //先获取到字典项 DataItemEntity DataItems = di.GetEntityByCode("SICKTYPE"); DataItemDetailBLL did = new DataItemDetailBLL(); //根据字典项获取值 IEnumerable <DataItemDetailEntity> didList = did.GetList(DataItems.ItemId); //循环修改信息 for (int i = 0; i < Newdt.Rows.Count; i++) { Newdt.Rows[i]["IDNUM"] = i + 1; Newdt.Rows[i]["INSPECTIONTIME"] = Convert.ToDateTime(Newdt.Rows[i]["INSPECTIONTIME"]).ToString("yyyy-MM-dd"); if (Convert.ToInt32(Newdt.Rows[i]["ISSICK"]) == 1) { Newdt.Rows[i]["ISSICK"] = "是"; foreach (DataItemDetailEntity item in didList) { if (item.ItemValue == Newdt.Rows[i]["SICKTYPE"].ToString()) { Newdt.Rows[i]["SICKTYPE"] = item.ItemName; } } } else { Newdt.Rows[i]["ISSICK"] = "否"; Newdt.Rows[i]["SICKTYPE"] = ""; } } string FileUrl = ""; var queryParam = queryJson.ToJObject(); string keyord = queryParam["keyword"].ToString(); if (keyord.ToInt() == 1) //查询职业病 { FileUrl = @"\Resource\ExcelTemplate\职业健康职业病人员列表_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "职业病人员列表", "职业病人员列表"); } else if (keyord.ToInt() == 2) //查询异常人员 { FileUrl = @"\Resource\ExcelTemplate\职业健康异常人员列表_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "异常人员列表", "异常人员列表"); } else if (keyord.ToInt() == 3) //查询体检人员 { FileUrl = @"\Resource\ExcelTemplate\职业健康体检人员列表_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(Newdt, FileUrl, "体检人员列表", "体检人员列表"); } return(Success("导出成功。")); }
public ActionResult Excel(string type) { try { string[] column = { "createdate" }; string[] column1 = { "type", "status" }; var dt = Session["CarIntandOutRecord"] as DataTable; dt.TableName = "Table"; dt = AsposeExcelHelper.UpdateDataTable(dt, column, "datetime"); DataTable newdt = AsposeExcelHelper.UpdateDataTable(dt, column1, "string"); for (int i = 0; i < newdt.Rows.Count; i++) { string res = newdt.Rows[i]["status"].ToString(); if (res == "0") { res = "进"; } else { res = "出"; } newdt.Rows[i]["status"] = res; int num = newdt.Rows[i]["type"].ToInt(); switch (num) { case 0: newdt.Rows[i]["type"] = "电厂班车"; break; case 1: newdt.Rows[i]["type"] = "私家车"; break; case 2: newdt.Rows[i]["type"] = "商务公车"; break; case 3: dt.Rows[i]["type"] = "拜访车辆"; break; case 4: newdt.Rows[i]["type"] = "物料车辆"; break; case 5: newdt.Rows[i]["type"] = "危化品车辆"; break; case 6: newdt.Rows[i]["type"] = "临时通行车辆"; break; } } //模板路径 string FileUrl = @"\Resource\ExcelTemplate\车辆进出厂记录_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(newdt, FileUrl, "车辆进出厂记录列表", "车辆进出厂记录列表"); } catch (Exception) { throw; } return(Content("导出成功。")); }