///// <summary> ///// 判断数组中是否有参数 ///// </summary> ///// <param name="Risks"></param> ///// <param name="ReskList"></param> ///// <param name="value"></param> ///// <returns></returns> //public bool GetCountTrue(string Risks, IEnumerable<DataItemDetailEntity> ReskList, out string Riskvalue) //{ // string value = ""; // Riskvalue = ""; // foreach (string r in Risks) // { // if (GetIsTrue(ReskList, r, out value)) // { // if (Riskvalue == "") // { // Riskvalue = value; // } // else // { // Riskvalue += "," + value; // } // } // else // { // return false; // } // } // return true; //} /// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { 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 ha." + where; } DataTable dt = hazardfactorsbll.GetTable(queryJson, wheresql); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][0] = i + 1; } string FileUrl = @"\Resource\ExcelTemplate\职业病危害因素清单_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业病危害因素清单", "职业病危害因素列表"); return(Success("导出成功。")); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { 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 = occupatioalstaffbll.GetTable(queryJson, whereSql); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][0] = (i + 1).ToString(); dt.Rows[i]["INSPECTIONTIME"] = Convert.ToDateTime(dt.Rows[i]["INSPECTIONTIME"]).ToString("yyyy-MM-dd"); } string FileUrl = @"\Resource\ExcelTemplate\职业健康体检列表_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业健康体检列表", "职业健康体检列表"); return(Success("导出成功。")); }
public ActionResult HallIndex(DateTime?beginTime, DateTime?endTime, bool export = false) { // 初始化日期 DateTimeInit(ref beginTime, ref endTime); // 获取数据 var result = STAT_WARN_ANALYSIS_DAL.GetStatistics_WarnAnalysisChart(UserState.UserID, beginTime, endTime); const string titleName = "预警分析"; var mainTielt = GetOrgName(null, OrgHighLevel); ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); //导出 if (export) { var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var index = 0; var data = result == null ? new DataTable() : result.Select(x => new { 序号 = ++index, 务厅 = x.HALL_NAM, 预警级别 = x.L_Name, 等候超时 = x.Count_T1, 等候超时率 = x.Count_T2, 窗口饱和度 = x.Count_T3, 大厅饱和度 = x.Count_T4, 超时办结率 = x.Count_T5, 超时业务笔数 = x.Count_T6, 弃号率 = x.Count_T7, 差评笔数预警 = x.Count_T8, 连续工作时长超界 = x.Count_T9, }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(data, "预警分析报表-服务厅", exceltitle)); } var chartData = result == null ? new DataTable() : result.Where(r => r.L_Code == "H").Select(x => new { 务厅 = x.HALL_NAM, 等候超时率 = x.Count_T2, 超时办结率 = x.Count_T5, 差评笔数预警 = x.Count_T8, }).ToList().ToDataTable(); ViewBag.BeginTime = !beginTime.HasValue ? DateTime.Now.ToString("yyyy-MM-dd") : beginTime.Value.ToString("yyyy-MM-dd"); ViewBag.EndTime = !endTime.HasValue ? DateTime.Now.ToString("yyyy-MM-dd") : endTime.Value.ToString("yyyy-MM-dd"); // 输出图表 ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 420, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle); return(View(result)); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { //string column = "IDNUM,OCCDETAILID,USERNAME,USERNAMEPINYIN,INSPECTIONTIME,ISSICK,SICKTYPE"; //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.GetStaffList(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 < dt.Rows.Count; i++) { dt.Rows[i][0] = i + 1; //if (Convert.ToInt32(dt.Rows[i]["ISSICK"]) == 1) //{ // foreach (DataItemDetailEntity item in didList) // { // if (item.ItemValue == dt.Rows[i]["SICKTYPE"].ToString()) // { // dt.Rows[i]["SICKTYPE"] = item.ItemName; // } // } //} //else //{ // dt.Rows[i]["SICKTYPE"] = ""; //} } string FileUrl = @"\Resource\ExcelTemplate\职业病人员清单_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, FileUrl, "职业病人员清单", "职业病人员列表"); return(Success("导出成功。")); }
public ActionResult ExportCompareNumber(string queryJson) { var dt = legbll.GetLllegalCompareDataGrp(queryJson); string fileUrl = @"\Resource\ExcelTemplate\违章比较数量_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, fileUrl, "违章数量", "违章对比统计数量"); return(Success("导出成功。")); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { DepartmentBLL departmentBLL = new DepartmentBLL(); string sqlwhere = "Account!='System' and us is not null"; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { sqlwhere = "1=1 and us is not null"; } else { var queryParam = queryJson.ToJObject(); if (queryParam["datatype"].IsEmpty()) { string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "departmentcode", "organizecode"); if (!string.IsNullOrEmpty(where) && (queryParam["code"].IsEmpty() || !queryJson.Contains("code"))) { sqlwhere += " and " + where; } } } var data = new HazardfactoruserBLL().GetPageList(sqlwhere, queryJson); foreach (DataRow dr in data.Rows) { if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组") { DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from BASE_DEPARTMENT where encode=(select encode from BASE_DEPARTMENT t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"])); if (dt.Rows.Count > 0) { string name = ""; foreach (DataRow dr1 in dt.Rows) { name += dr1["fullname"].ToString() + "/"; } dr["deptname"] = name.TrimEnd('/'); } } } for (int i = 0; i < data.Rows.Count; i++) { data.Rows[i][0] = i + 1; } string FileUrl = @"\Resource\ExcelTemplate\接触职业危害因素人员_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(data, FileUrl, "接触职业危害因素人员清单", "接触职业危害因素人员列表"); return(Success("导出成功。")); }
private void cmsDatabaseItem_Click(object sender, EventArgs e) { var tripItem = sender as ToolStripItem; var currentNode = tvDatabase.SelectedNode; if (tripItem == null) { return; } switch (tripItem.Text) { case Constants.CONTEXT_MENU_DATABASE_EXPORT_DICTIONARY: try { LoadingFormService.CreateForm(); LoadingFormService.SetFormCaption(Constants.EXPORT_CAPTION); List <DbTable> tableList = DataAccess.GetDbTableListWithColumns(_connBuilder, currentNode.Text); //Workbook workbook = ExcelHelper.GenerateWorkbook(tableList); IExcelHelper helper = new AsposeExcelHelper(); //LoadingFormService.CloseFrom(); var dia = new SaveFileDialog { Filter = Constants.SAVE_FILE_DIALOG_FILTER, FileName = currentNode.Text + " Data Dictionary" }; if (dia.ShowDialog() == DialogResult.OK) { helper.GenerateWorkbook(tableList, dia.FileName); //workbook.Save(dia.FileName); } } catch (Exception ex) { MessageBox.Show(ex.Message, Constants.ERROR_CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { LoadingFormService.CloseFrom(); } break; case Constants.CONTEXT_MENU_DATABASE_REFRESH: LoadTableTreeNode(currentNode); break; } }
public ActionResult Export(string queryJson, string sortname, string sortorder) { var pagination = new Pagination() { page = 1, rows = 100000, sidx = string.IsNullOrWhiteSpace(sortname) ? "createdate" : sortname, sord = string.IsNullOrWhiteSpace(sortorder) ? "asc" : sortorder }; var dt = stdsysfilesbll.GetList(pagination, queryJson); string fileUrl = @"\Resource\ExcelTemplate\标准制度_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, fileUrl, "标准制度", string.Format("标准制度_{0}", DateTime.Now.ToString("yyyyMMddHHmmss"))); return(Success("导出成功。")); }
public ActionResult Export(string queryJson, string sortname, string sortorder) { var pagination = new Pagination() { page = 1, rows = 100000, sidx = string.IsNullOrWhiteSpace(sortname) ? "createdate" : sortname, sord = string.IsNullOrWhiteSpace(sortorder) ? "asc" : sortorder }; var dt = nosaworksbll.GetList(pagination, queryJson); string fileUrl = @"\Resource\ExcelTemplate\NOSA元素工作清单_导出模板.xlsx"; AsposeExcelHelper.ExecuteResult(dt, fileUrl, "工作清单", "NOS元素工作清单"); return(Success("导出成功。")); }
public ActionResult TaxpayerAction_Person( DateTime?beginTime, DateTime?endTime, string orgId, bool export = false) { // 初始化日期 base.DateTimeInit(ref beginTime, ref endTime); // 获取数据 var bll = new Statistics_DAL(); var result = bll.GetStatistics_TaxpayerActionChat_Person(beginTime, endTime, orgId); // 控制标题 var MainTitle = base.GetOrgName(null, 3); var nodeTitle = string.Empty; MainTitle += "纳税人评价分析"; nodeTitle += "(" + beginTime.Value.ToString("yyyy年MM月dd日"); nodeTitle += " - " + endTime.Value.ToString("yyyy年MM月dd日") + "))"; ViewBag.MainTitle = MainTitle; ViewBag.NodeTitle = nodeTitle; if (export) { var index = 0; var data = result.Select(x => new { 序号 = ++index, 工号 = x.PersonNo, 姓名 = x.PersonName, 业务大类 = x.DLS_SERIALNAME, 城业务量 = x.LOCAL_CNT, 二次办税业务量 = x.SECOND_SVR_CNT }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(data, MainTitle + "报表")); } SetChart_Person(result.GroupBy(x => x.PersonNo), MainTitle, nodeTitle); // 所有事项大类 var itemTypeList = SYS_DLSERIAL_Dao.FindList(); ViewData["itemTypeList"] = itemTypeList; return(View(result)); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult Excel(string queryJson) { string wheresql = ""; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { wheresql = "1=1"; } else { string where = new ERCHTMS.Busines.AuthorizeManage.AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value); wheresql += " and " + where; } DataTable dt = laborinfobll.GetTable(queryJson, wheresql); DataItemDetailBLL dataItemDetailBLL = new DataItemDetailBLL(); var datadetail = dataItemDetailBLL.GetDataItemListByItemCode("'LaborName'"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][0] = i + 1; dt.Rows[i]["TimeType"] = dt.Rows[i]["TimeNum"].ToString() + dt.Rows[i]["TimeType"].ToString(); DataItemModel dm = datadetail.Where(it => it.ItemName == dt.Rows[i]["NAME"].ToString()).FirstOrDefault(); if (dm != null) { dt.Rows[i]["InStock"] = dm.ItemValue.ToString(); } else { dt.Rows[i]["InStock"] = ""; } } string FileUrl = @"\Resource\ExcelTemplate\劳动防护用品管理_导出.xls"; AsposeExcelHelper.ExecuteResult(dt, FileUrl, "劳动防护用品管理清单", "劳动防护用品管理列表"); return(Success("导出成功。")); }
public ActionResult ExportNumber(string queryJson) { var dt1 = legbll.GetLllegalLevelListGrp(queryJson); dt1.TableName = "Table1"; var dt2 = legbll.GetLllegalTypeListGrp(queryJson); dt2.TableName = "Table2"; var ds = new DataSet("ds"); ds.Tables.Add(dt1); ds.Tables.Add(dt2); string fileUrl = @"\Resource\ExcelTemplate\违章统计数量_导出模板.xlsx"; AsposeExcelHelper.ExecuteResultX(ds, fileUrl, new List <string>() { "违章级别数量", "违章类型数量" }, "违章类型、级别统计数量"); 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 Index(DateTime?beginTime, DateTime?endTime, int pageIndex = 1, int pageSize = 20, bool export = false) { base.DateTimeInit(ref beginTime, ref endTime); // 初始化日期 var orgall = new SYS_USER_DAL().GetUserORG(UserState.UserID); ViewBag.UserORG = new SelectList(orgall.Where(obj => obj.ORG_LEVEL == 4) , "ORG_ID", "ORG_NAM", ""); var orgs = orgall.Select(obj => obj.ORG_ID); var mainTielt = GetOrgName(null, null); ViewBag.MainTitle = GetTitleName(mainTielt, "排队业务分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var exceltitle = GetTitleName(mainTielt, "排队业务分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var halllist = base.UserHall.Select(x => x.HALL_NO).ToArray(); var data = new STAT_STAFF_QUEUE_BUSI_D_DAL().GetStatsInfo(pageIndex, int.MaxValue, halllist, beginTime, endTime); if (export)//导出 { var index = 0; var temp = data.Select(x => new { 序号 = ++index, 业务编码 = x.QUEUE_BUSI_CD, 业务名称 = x.Q_SERIALNAME, 呼叫量 = x.CALL_CNT, 呼叫率 = CommonHelper.DivisionOfPercent(x.CALL_CNT, data.Sum(obj => obj.CALL_CNT)), 超时等待量 = x.OVERTIME_WAIT_CNT, 超时等待率 = CommonHelper.DivisionOfTimeString(x.OVERTIME_WAIT_CNT, data.Sum(obj => obj.OVERTIME_WAIT_CNT)), 办理量 = x.HANDLE_CNT, 办理率 = CommonHelper.DivisionOfPercent(x.HANDLE_CNT, data.Sum(obj => obj.HANDLE_CNT)), 弃号量 = x.ABANDON_CNT, 弃号率 = CommonHelper.DivisionOfPercent(x.ABANDON_CNT, data.Sum(obj => obj.ABANDON_CNT)), 平均办理时间 = CommonHelper.DivisionOfTimeString(x.HANDLE_DUR, x.HANDLE_CNT), 平均等待时间 = CommonHelper.DivisionOfTimeString(x.WAIT_DUR, x.TOT_TICKET_CNT), 最长等待时间 = x.MAX_WAIT_DUR, 最长办理时间 = x.MAX_HANDLE_DUR, 超时办理量 = x.OVERTIME_HANDLE_CNT, 超时办理率 = CommonHelper.DivisionOfPercent(x.OVERTIME_HANDLE_CNT, x.TOT_TICKET_CNT), 人流量 = x.TOT_TICKET_CNT }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(temp, exceltitle)); } else { DataSet ds = new DataSet(); DataTable dt = new DataTable(); ds.Tables.Add(dt); dt.Columns.Add("Q_SERIALNAME", typeof(string)); dt.Columns.Add("呼叫量", typeof(int)); dt.Columns.Add("办理量", typeof(int)); dt.Columns.Add("弃号量", typeof(int)); foreach (var item in data) { DataRow r = dt.NewRow(); r["Q_SERIALNAME"] = item.Q_SERIALNAME; r["呼叫量"] = item.CALL_CNT; r["办理量"] = item.HANDLE_CNT; r["弃号量"] = item.ABANDON_CNT; dt.Rows.Add(r); } ViewBag.ChartColumn3DXML = CreateMSColumn3DChart("排队业务分析", ds.Tables[0], 430, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart("排队业务报分析", ds, 430, null, null, subtitle); return(View(data)); } }
/// <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("导出成功。")); }
public ActionResult Index(DateTime?beginTime, DateTime?endTime, string orgid, int pageIndex = 1, int pageSize = 20, bool export = false) { base.DateTimeInit(ref beginTime, ref endTime); // 初始化日期 var orgall = new SYS_USER_DAL().GetUserORG(UserState.UserID); ViewBag.UserORG = new SelectList(orgall.Where(obj => obj.ORG_LEVEL == 4) , "ORG_ID", "ORG_NAM", orgid); var orgs = orgall.Select(obj => obj.ORG_ID); //var mainTielt = GetOrgName(null, null); //ViewBag.MainTitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); //var title = GetTitleName(mainTielt,"业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var mainTielt = GetOrgName(null, null); ViewBag.MainTitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var exceltitle = GetTitleName(mainTielt, "业务大类分析", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var halllist = base.UserHall.Select(x => x.HALL_NO).ToArray(); var data = new STAT_STAFF_LARGE_BUSI_D_DAL().GetStatsInfo(halllist, beginTime, endTime); if (export)//导出 { var index = 0; var temp = data.Select(x => new { 序号 = ++index, 业务大类 = x.DLS_SERIALNAME, 业务笔数 = x.BUSI_CNT, 业务折合量 = x.CONVERT_BUSI_CNT, 平均版办理时间 = (((int)(x.BUSI_CNT == 0 ? 0 : (x.HANDLE_DUR / x.BUSI_CNT))).ToTimeString()) + "%", 超时办理时间 = x.OVERTIME_HANDLE_CNT, 超时率 = ((x.BUSI_CNT == 0 ? 0 : (x.OVERTIME_HANDLE_CNT / x.BUSI_CNT)).ToString("P")) + "%", 城业务笔数 = x.LOCAL_CNT, 城办理率 = ((x.LOCAL_CNT * 100.0 / x.BUSI_CNT)).ToString("f2") + "%", }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(temp, exceltitle)); } else { DataSet ds = new DataSet(); DataTable dt = new DataTable(); ds.Tables.Add(dt); dt.Columns.Add("NAME", typeof(string)); dt.Columns.Add("业务笔数", typeof(int)); dt.Columns.Add("业务折合量", typeof(int)); dt.Columns.Add("总办理时长", typeof(int)); dt.Columns.Add("超时办理量", typeof(int)); dt.Columns.Add("同城业务量", typeof(int)); foreach (var item in data) { var r = dt.NewRow(); r["NAME"] = item.DLS_SERIALNAME; r["业务笔数"] = item.BUSI_CNT; r["业务折合量"] = item.CONVERT_BUSI_CNT; r["总办理时长"] = item.HANDLE_DUR; r["超时办理量"] = item.OVERTIME_HANDLE_CNT; r["同城业务量"] = item.LOCAL_CNT; dt.Rows.Add(r); } ViewBag.ChartColumn3DXML = CreateMSColumn3DChart("业务大类分析", ds.Tables[0], 430, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart("业务大类分析", ds, 430, null, null, subtitle); return(View(data)); } }
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("导出成功。")); }
public ActionResult HallIndex(DateTime?beginTime, DateTime?endTime, bool export = false) { // 初始化日期 DateTimeInit(ref beginTime, ref endTime); // 获取数据 var result = STAT_COMPLAINT_REPORT_DAL.GetStatistics_ComplaintReportChart(UserState.UserID, beginTime, endTime); const string titleName = "投诉举报分析"; var mainTielt = GetOrgName(null, OrgHighLevel); ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var dataTable = new DataTable(); dataTable.Columns.Add("序号"); dataTable.Columns.Add("服务厅编号"); dataTable.Columns.Add("服务厅"); dataTable.Columns.Add("处理情况"); dataTable.Columns.Add("处理情况类型"); dataTable.Columns.Add("投诉类型编号"); //获取投诉类型集合 var complaints = new Dictionary <int, string>(); if (result != null && result.Any()) { foreach (var re in result.Select(r => new { COMPLAIN_TYP_ID = r.COMPLAIN_TYP_ID, COMPLAIN_NAM = r.COMPLAIN_NAM }).Distinct()) { if (!complaints.ContainsKey(re.COMPLAIN_TYP_ID)) { complaints.Add(re.COMPLAIN_TYP_ID, re.COMPLAIN_NAM); dataTable.Columns.Add(re.COMPLAIN_NAM); } } } ViewBag.Complaints = complaints; if (result != null && result.Any()) { var index = 0; var complaintCount = complaints.Count; for (var mIndex = 0; mIndex < result.Count; mIndex++) { var itemHall = result[mIndex]; var levelCodes = new[] { "N", "R", "U", "H" }; foreach (var levelCode in levelCodes) { var itemLevels = result.Where(r => r.HALL_NO == itemHall.HALL_NO && r.L_Code == levelCode).ToList(); var itemLevel = itemLevels.First(); var dr = dataTable.NewRow(); dr["序号"] = (++index); dr["服务厅"] = itemHall.HALL_NAM; dr["服务厅编号"] = itemHall.HALL_NO; dr["处理情况"] = itemLevel.L_Name; dr["处理情况类型"] = itemLevel.L_Value; foreach (var complaint in complaints) { var itemComplaint = itemLevels.First(r => r.COMPLAIN_TYP_ID == complaint.Key); dr["投诉类型编号"] = itemComplaint.COMPLAIN_TYP_ID; dr[itemComplaint.COMPLAIN_NAM] = itemComplaint.Count_Value; } dataTable.Rows.Add(dr); } mIndex += (complaintCount * 4 - 1); } } //导出 if (export) { var exportData = dataTable.Copy(); exportData.Columns.Remove("服务厅编号"); exportData.Columns.Remove("处理情况类型"); exportData.Columns.Remove("投诉类型编号"); return(AsposeExcelHelper.OutFileToRespone(exportData, "投诉举报分析报表-服务厅", exceltitle)); } var dataList = dataTable.Rows.Cast <DataRow>().Where(r => r["处理情况"].ToString() == "合计").ToList(); var chartData = dataTable.Clone(); foreach (var dataRow in dataList) { chartData.Rows.Add(dataRow.ItemArray); } //不需要显示的列名 var noShowColumns = new[] { "序号", "服务厅编号", "处理情况", "处理情况类型", "投诉类型编号" }; foreach (var noShowColumn in noShowColumns) { chartData.Columns.Remove(noShowColumn); } // 输出图表 ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 420, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle); return(View(dataTable)); }
/// <summary> /// 导出数据 /// </summary> /// <param name="title"></param> /// <param name="fileName"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <returns></returns> public ActionResult ExportData(string title, string Listname, DateTime beginTime, DateTime endTime, string orgId) { var typeList = CHK_QUALITY_CON_Dao.FindList(); var itemTypeList = SYS_DLSERIAL_Dao.FindList(); var lineTable = new DataTable(); lineTable.Columns.Add("序号", typeof(string)); lineTable.Columns.Add(Listname, typeof(string)); lineTable.Columns.Add("事项大类", typeof(string)); foreach (var item in typeList) { lineTable.Columns.Add(item.QUALITY_NAM, typeof(int)); } if (Listname == "服务厅") { var data = new Statistics_DAL().GetStatistics_ServiceSlipAnalysis(beginTime, endTime, UserState.UserID); var dics = data.GroupBy(m => m.HALL_NAM); var i = 1; foreach (var dic in dics) { var items = dic.ToList(); var statistics = items.FirstOrDefault(); for (var j = 1; j < itemTypeList.Count(); j++) { var r = lineTable.NewRow(); r["序号"] = i; r[Listname] = statistics.HALL_NAM; r["事项大类"] = itemTypeList[j].DLS_SERIALNAME; foreach (var item in typeList) { var def = items.FirstOrDefault( m => m.QUALITY_CD == item.QUALITY_CD && m.SSDLSERIALID == itemTypeList[j].DLS_SERIALID); r[item.QUALITY_NAM] = def == null ? 0 : def.AMOUNT; } lineTable.Rows.Add(r); i++; } } } else { var listStatistics = new Statistics_DAL().GetStatistics_ServiceSlipAnalysisList(beginTime, endTime, UserState.UserID, orgId, null); var dics = listStatistics.GroupBy(m => m.STAFF_NAM); var i = 1; foreach (var dic in dics) { var items = dic.ToList(); var statistics = items.FirstOrDefault(); for (var j = 1; j < itemTypeList.Count(); j++) { var r = lineTable.NewRow(); r["序号"] = i; r[Listname] = statistics.STAFF_NAM; r["事项大类"] = itemTypeList[j].DLS_SERIALNAME; foreach (var item in typeList) { var def = items.FirstOrDefault( m => m.QUALITY_CD == item.QUALITY_CD && m.DLS_SERIALID == itemTypeList[j].DLS_SERIALID); r[item.QUALITY_NAM] = def == null ? 0 : def.AMOUNT; } lineTable.Rows.Add(r); i++; } } } return(AsposeExcelHelper.OutFileToRespone(lineTable, title)); }
public ActionResult TaxpayerEval( DateTime?beginTime, DateTime?endTime, bool export = false) { // 判断当前用户组织结构 var power = new SYS_HALL_DAL().GetList(UserState.UserID, "4"); if (power.Count == 1) { var hallNo = power.FirstOrDefault().HALL_NO; return(Redirect(Url.Action("TaxpayerEval_Person", "Statistics", new { @beginTime = beginTime, @endTime = endTime, @orgId = hallNo }))); } // 初始化日期 base.DateTimeInit(ref beginTime, ref endTime); // 获取数据 var bll = new Statistics_DAL(); var result = bll.GetStatistics_TaxpayerEvalChart(beginTime, endTime, UserState.UserID); // 控制标题 const string titleName = "纳税人评价分析"; var mainTielt = base.GetOrgName(null, 3); ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); if (export) { var index = 0; var data = result.Select(x => new { 序号 = ++index, 务厅编码 = x.HALL_NO, 务厅名称 = x.ORG_NAM, 很满意 = x.VERY_SATISFY_CNT, 很满意率 = x.VERY_SATISFY_CNT_BFB + "%", 满意 = x.SATISFY_CNT, 满意率 = x.SATISFY_CNT_BFB + "%", 基本满意 = x.COMMON_CNT, 基本满意率 = x.COMMON_CNT_BFB + "%", 满意 = x.UNSATISFY_CNT, 满意率 = x.UNSATISFY_CNT_BFB + "%", 未评价 = x.NON_EVAL_CNT, 未评价率 = x.NON_EVAL_CNT_BFB + "%", 满意度 = x.ManYiDu_BFB + "%" }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(data, "纳税人评价分析报表-服务厅", exceltitle)); } var chartData = result.Select(x => new { 务厅名称 = x.ORG_NAM, 很满意 = x.VERY_SATISFY_CNT, 满意 = x.SATISFY_CNT, 基本满意 = x.COMMON_CNT, 满意 = x.UNSATISFY_CNT, 未评价 = x.NON_EVAL_CNT, }).ToList().ToDataTable(); // 输出图表 ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 430, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 430, null, null, subtitle); return(View(result)); }
public ActionResult TaxpayerEval_Person( DateTime?beginTime, DateTime?endTime, string orgId, bool export = false) { // 初始化日期 base.DateTimeInit(ref beginTime, ref endTime); // 获取数据 var bll = new Statistics_DAL(); var result = bll.GetStatistics_TaxpayerEvalChart_Person(beginTime, endTime, orgId); // 控制标题 const string titleName = "纳税人评价分析"; var mainTielt = GetOrgName(orgId, null); ViewBag.MainTitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault()); var subtitle = GetTitleName(mainTielt, "", beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); var exceltitle = GetTitleName(mainTielt, titleName, beginTime.GetValueOrDefault(), endTime.GetValueOrDefault(), false); if (export) { var index = 0; var data = result.Select(x => new { 序号 = ++index, 工号 = x.PersonNo, 姓名 = x.PersonName, 很满意 = x.VERY_SATISFY_CNT, 很满意率 = x.VERY_SATISFY_CNT_BFB + "%", 满意 = x.SATISFY_CNT, 满意率 = x.SATISFY_CNT_BFB + "%", 基本满意 = x.COMMON_CNT, 基本满意率 = x.COMMON_CNT_BFB + "%", 满意 = x.UNSATISFY_CNT, 满意率 = x.UNSATISFY_CNT_BFB + "%", 未评价 = x.NON_EVAL_CNT, 未评价率 = x.NON_EVAL_CNT_BFB + "%", 满意度 = x.ManYiDu_BFB + "%" }).ToList().ToDataTable(); return(AsposeExcelHelper.OutFileToRespone(data, "纳税人评价分析报表-员工", exceltitle)); } var chartData = result.Select(x => new { 姓名 = x.PersonName, 很满意 = x.VERY_SATISFY_CNT, 满意 = x.SATISFY_CNT, 基本满意 = x.COMMON_CNT, 满意 = x.UNSATISFY_CNT, 未评价 = x.NON_EVAL_CNT, }).ToList().ToDataTable(); // 输出图表 ViewBag.ChartColumn3DXML = CreateMSColumn3DChart(titleName, chartData, 430, subtitle); ViewBag.ChartSplineXML = CreateMSSplineChart(titleName, chartData, 420, null, null, subtitle); return(View(result)); }
/// <summary> /// 导出 /// </summary> /// <returns></returns> //[HttpPost] //[AjaxOnly] //[HandlerAuthorize(PermissionMode.Ignore)] //[HandlerMonitor(0, "导出消防设施excel")] public ActionResult ExportExcel(string condition, string queryJson) { Pagination pagination = new Pagination { page = 1, rows = 100000000, p_kid = "Id", p_fields = @"CategoryName,RankName,FileName,DutyDept, to_char(PutDate,'yyyy-MM-dd') as PutDate,NormName,Clause,ApplyScope, case when IsConform = '0' then '符合' when IsConform = '1' then '不符合' else '' end as IsConform, Describe,Opinion,to_char(FinishTime,'yyyy-MM-dd hh24:mi') as FinishTime,EvaluatePerson", p_tablename = "HRS_EVALUATEDETAILS", conditionJson = "1=1", sidx = "IsConform desc,CreateDate", sord = "desc" }; DataTable data = evaluatedetailsbll.GetPageList(pagination, queryJson); //设置导出格式 //ExcelConfig excelconfig = new ExcelConfig //{ // Title = "合规性评价", // TitleFont = "宋体", // TitleHeight = 30, // TitlePoint = 25, // FileName = "合规性评价" + ".xls", // IsAllSizeColumn = true, // //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 // ColumnEntity = new List<ColumnEntity>(){ // new ColumnEntity() {Column = "categoryname", ExcelColumn = "大类", Alignment = "center"}, // new ColumnEntity() {Column = "rankname", ExcelColumn = "小类", Alignment = "center"}, // new ColumnEntity() {Column = "filename", ExcelColumn = "文件编号及名称", Alignment = "center"}, // new ColumnEntity() {Column = "dutydept", ExcelColumn = "颁布部门", Alignment = "center"}, // new ColumnEntity() {Column = "putdate", ExcelColumn = "实施日期", Alignment = "center"}, // new ColumnEntity() {Column = "normname", ExcelColumn = "纳入企业标准的名称", Alignment = "center"}, // new ColumnEntity() {Column = "clause", ExcelColumn = "适用条款", Alignment = "center"}, // new ColumnEntity() {Column = "applyscope", ExcelColumn = "适用范围", Alignment = "center"}, // new ColumnEntity() {Column = "isconform", ExcelColumn = "符合性", Alignment = "center"}, // new ColumnEntity() {Column = "describe", ExcelColumn = "现状符合性描述", Alignment = "center"}, // new ColumnEntity() {Column = "opinion", ExcelColumn = "整改意见", Alignment = "center"}, // new ColumnEntity() {Column = "finishtime", ExcelColumn = "整改截止时间", Alignment = "center"}, // new ColumnEntity() {Column = "evaluateperson", ExcelColumn = "评价人", Alignment = "center"} // } //}; ////调用导出方法 ////ExcelHelper.ExportByAspose(data, excelconfig.FileName, excelconfig.ColumnEntity); //ExcelHelper.ExcelDownload(data, excelconfig); //return Success("导出成功。"); for (int i = 0; i < data.Rows.Count; i++) { data.Rows[i][0] = i + 1; //data.Rows[i]["TimeType"] = data.Rows[i]["TimeNum"].ToString() + data.Rows[i]["TimeType"].ToString(); } string FileUrl = @"\Resource\ExcelTemplate\合规性评价导出.xls"; AsposeExcelHelper.ExecuteResult(data, FileUrl, "合规性评价", "合规性评价"); return(Success("导出成功。")); }