public static ExcelConfig LoadExcelConfig(Excel.Worksheet worksheet) { ExcelConfig excelConfig = new ExcelConfig(); if (worksheet == null || excelConfig.Init(worksheet) == false) { return null; } return excelConfig; }
public ActionResult ExportData(string queryJson) { try { var queryParam = queryJson.ToJObject(); Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; pagination.p_kid = "c.storeid"; if (queryParam["standardtype"].ToString() == "1" || queryParam["standardtype"].ToString() == "2" || queryParam["standardtype"].ToString() == "3" || queryParam["standardtype"].ToString() == "4" || queryParam["standardtype"].ToString() == "5" || queryParam["standardtype"].ToString() == "6") { pagination.p_fields = "filename,b.name as categorycode,relevantelementname,to_char(carrydate,'yyyy-MM-dd') as carrydate,to_char(a.createdate,'yyyy-MM-dd') as createdate,consultnum"; } else { pagination.p_fields = "filename,to_char(a.createdate,'yyyy-MM-dd') as createdate,d.fullname as createuserdeptname,consultnum"; } pagination.p_tablename = "hrs_storestandard c left join hrs_standardsystem a on c.standardid = a.id left join hrs_stcategory b on a.categorycode=b.id left join base_department d on a.createuserdeptcode = d.encode"; pagination.conditionJson = "c.userid='" + user.UserId + "'"; pagination.sidx = "a.createdate"; pagination.sord = "desc"; DataTable exportTable = standardsystembll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); if (!queryParam["standardtype"].IsEmpty()) { switch (queryParam["standardtype"].ToString()) { case "1": excelconfig.Title = "技术标准体系"; excelconfig.FileName = "技术标准体系信息导出.xls"; break; case "2": excelconfig.Title = "管理标准体系"; excelconfig.FileName = "管理标准体系信息导出.xls"; break; case "3": excelconfig.Title = "岗位标准体系"; excelconfig.FileName = "岗位标准体系信息导出"; break; case "4": excelconfig.Title = "上级标准化文件"; excelconfig.FileName = "上级标准化文件信息导出.xls"; break; case "5": excelconfig.Title = "指导标准"; excelconfig.FileName = "指导标准信息导出.xls"; break; case "6": excelconfig.Title = "法律法规"; excelconfig.FileName = "法律法规信息导出.xls"; break; case "7": excelconfig.Title = "标准体系策划与构建"; excelconfig.FileName = "标准体系策划与构建信息导出.xls"; break; case "8": excelconfig.Title = "标准体系评价与改进"; excelconfig.FileName = "标准体系评价与改进信息导出.xls"; break; case "9": excelconfig.Title = "标准化培训"; excelconfig.FileName = "标准化培训信息导出.xls"; break; default: break; } } excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "filename", ExcelColumn = "文件名称", Width = 300 }); if (queryParam["standardtype"].ToString() == "1" || queryParam["standardtype"].ToString() == "2" || queryParam["standardtype"].ToString() == "3" || queryParam["standardtype"].ToString() == "4" || queryParam["standardtype"].ToString() == "5" || queryParam["standardtype"].ToString() == "6") { excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "categorycode", ExcelColumn = "类别", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "relevantelementname", ExcelColumn = "对应元素", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "carrydate", ExcelColumn = "施行日期", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createdate", ExcelColumn = "发布日期", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "consultnum", ExcelColumn = "查阅频次", Width = 300 }); } else { excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createdate", ExcelColumn = "发布日期", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createuserdeptname", ExcelColumn = "发布单位/部门", Width = 300 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "consultnum", ExcelColumn = "查阅频次", Width = 300 }); } //调用导出方法 //ExcelHelper.ExcelDownload(exportTable, excelconfig); ExcelHelper.ExportByAspose(exportTable, excelconfig.FileName, excelconfig.ColumnEntity); } catch (Exception ex) { } return(Success("导出成功。")); }
public ActionResult Export(string condition, string queryJson) { Pagination pagination = new Pagination(); queryJson = queryJson ?? ""; pagination.p_kid = "ID"; pagination.p_fields = "districtname, DANGERSOURCE, ACCIDENTNAME,DEPTNAME,JDGLZRRFULLNAME,case WHEN ISDANGER>0 then '是' else '否' end as ISDANGERNAME"; pagination.p_tablename = "HSD_HAZARDSOURCE t"; pagination.conditionJson = "1=1"; pagination.page = 1; pagination.rows = 100000000; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { pagination.conditionJson = "1=1"; } else { if (user.RoleName.Contains("省级")) { pagination.conditionJson += " and (CreateUserId='" + user.UserId + "' or DeptCode in(select encode from BASE_DEPARTMENT start with encode='" + user.NewDeptCode + "' connect by prior departmentid = parentid))"; } else { pagination.conditionJson += " and (CreateUserId='" + user.UserId + "' or DeptCode in(select encode from BASE_DEPARTMENT start with encode='" + user.DeptCode + "' connect by prior departmentid = parentid))"; } } var title = "危险源清单"; if (queryJson.Length > 0) { var queryParam = queryJson.ToJObject(); if (!queryParam["type"].IsEmpty()) { pagination.p_fields = "districtname, DANGERSOURCE, ACCIDENTNAME,DEPTNAME,JDGLZRRFULLNAME, case WHEN gradeval=1 then '一级' WHEN gradeval=3 then '三级' WHEN gradeval=2 then '二级' WHEN gradeval=4 then '四级' else '未定级' end as gradevalstr"; title = "重大危险源清单"; } } pagination.p_fields += @",case WHEN way='LEC' then 'LEC法风险辨识' else '危险化学品重大危险源辨识' end as way"; if (title == "危险源清单") { pagination.p_fields += @",case WHEN way='LEC' then ITEMA else 0 end as ITEMA ,case WHEN way='LEC' then ITEMB else 0 end as ITEMB ,case WHEN way='LEC' then ITEMC else 0 end as ITEMC ,case WHEN way='LEC' then ITEMR else 0 end as ITEMR"; } pagination.p_fields += @",case WHEN way='DEC' then ITEMDECQ else '' end as ITEMDECQ ,case WHEN way='DEC' then ITEMDECQ1 else '' end as ITEMDECQ1 ,case WHEN way='DEC' then ITEMDECB1 else '' end as ITEMDECB1 ,case WHEN way='DEC' then ITEMDECB else 0 end as ITEMDECB ,case WHEN way='DEC' then ITEMDECR else 0 end as ITEMDECR ,case WHEN way='DEC' then ITEMDECR1 else 0 end as ITEMDECR1"; var watch = CommonHelper.TimerStart(); var data = hazardsourcebll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = title; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = title + ".xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "districtname", ExcelColumn = "所属区域" }); listColumnEntity.Add(new ColumnEntity() { Column = "DANGERSOURCE".ToLower(), ExcelColumn = "危险源名称/场所" }); listColumnEntity.Add(new ColumnEntity() { Column = "ACCIDENTNAME".ToLower(), ExcelColumn = "可能导致的事故类型" }); listColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "责任部门" }); listColumnEntity.Add(new ColumnEntity() { Column = "jdglzrrfullname".ToLower(), ExcelColumn = "监督管理责任人" }); if (title == "危险源清单") { listColumnEntity.Add(new ColumnEntity() { Column = "isdangername".ToLower(), ExcelColumn = "是否为重大危险源" }); } else { listColumnEntity.Add(new ColumnEntity() { Column = "gradevalstr".ToLower(), ExcelColumn = "重大危险源级别" }); } listColumnEntity.Add(new ColumnEntity() { Column = "way".ToLower(), ExcelColumn = "风险评估方法" }); if (title == "危险源清单") { listColumnEntity.Add(new ColumnEntity() { Column = "ITEMA".ToLower(), ExcelColumn = "L" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMB".ToLower(), ExcelColumn = "E" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMC".ToLower(), ExcelColumn = "C" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMR".ToLower(), ExcelColumn = "D" }); } listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECQ".ToLower(), ExcelColumn = "危险化学品实际存在量q" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECQ1".ToLower(), ExcelColumn = "危险化学品临界量Q" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECB1".ToLower(), ExcelColumn = "校正系数β" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECB".ToLower(), ExcelColumn = "校正系数α" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECR".ToLower(), ExcelColumn = "R" }); listColumnEntity.Add(new ColumnEntity() { Column = "ITEMDECR1".ToLower(), ExcelColumn = "R1" }); excelconfig.ColumnEntity = listColumnEntity; //调用导出方法 ExcelHelper.ExcelDownload(data, excelconfig); return(Success("导出成功。")); }
/// <summary> /// 用户列表(导出Excel) /// </summary> /// <returns></returns> public void GetExportList() { try { //取出数据源 DataTable exportTable = userService.GetExportList(); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "测试用户导出"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "用户导出.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 excelconfig.ColumnEntity = new List <ColumnModel>(); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_account", ExcelColumn = "账户" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_realname", ExcelColumn = "姓名" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_gender", ExcelColumn = "性别" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_birthday", ExcelColumn = "生日" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_mobile", ExcelColumn = "手机", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_telephone", ExcelColumn = "电话", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_wechat", ExcelColumn = "微信" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_organize", ExcelColumn = "公司" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_department", ExcelColumn = "部门" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_description", ExcelColumn = "说明" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createdate", ExcelColumn = "创建日期" }); excelconfig.ColumnEntity.Add(new ColumnModel() { Column = "f_createusername", ExcelColumn = "创建人" }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { if (ex is ExceptionEx) { throw; } else { throw ExceptionEx.ThrowBusinessException(ex); } } }
public ActionResult ExportBulletinList(string condition, string queryJson) { Pagination pagination = new Pagination(); queryJson = queryJson ?? ""; pagination.page = 1; pagination.rows = 100000000; pagination.p_kid = "ID"; pagination.p_fields = @"a.accidenteventname,a.accidenteventno, b.itemname as accidenteventtype,c.itemname as accidenteventproperty,f.itemname as belongsystem,a.accidenteventcausename as accidenteventcause,a.happentime,a.district,a.belongdept,e.itemname as specialty "; pagination.p_tablename = @"BIS_POWERPLANTINSIDE a left join ( select * from base_dataitemdetail where itemid = ( select itemid from base_dataitem where parentid = (select itemid from base_dataitem where itemname = '单位内部快报' ) and itemcode = 'AccidentEventType') ) b on a.accidenteventtype = b.itemvalue left join ( select * from base_dataitemdetail where itemid = ( select itemid from base_dataitem where parentid = (select itemid from base_dataitem where itemname = '单位内部快报' ) and itemcode = 'AccidentEventProperty') ) c on a.accidenteventproperty = c.itemvalue left join ( select * from base_dataitemdetail where itemid = ( select itemid from base_dataitem where itemcode = 'SpecialtyType' ) ) e on a.Specialty = e.itemvalue left join ( select * from base_dataitemdetail where itemid = ( select itemid from base_dataitem where parentid = (select itemid from base_dataitem where itemname = '单位内部快报' ) and itemcode = 'BelongSystem') ) f on a.belongsystem = f.itemvalue"; pagination.sord = "CreateDate"; #region 权限校验 pagination.conditionJson = "1=1"; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { pagination.conditionJson = "1=1"; } else { string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "CREATEUSERDEPTCODE", "CREATEUSERORGCODE"); if (!string.IsNullOrEmpty(where)) { pagination.conditionJson += " and " + where; } } #endregion var data = powerplantinsidebll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "单位内部事故事件快报"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "单位内部事故事件快报.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "accidenteventname".ToLower(), ExcelColumn = "事故/事件名称" }); listColumnEntity.Add(new ColumnEntity() { Column = "accidenteventno".ToLower(), ExcelColumn = "编号" }); listColumnEntity.Add(new ColumnEntity() { Column = "accidenteventtype".ToLower(), ExcelColumn = "事故或事件类型" }); listColumnEntity.Add(new ColumnEntity() { Column = "accidenteventproperty".ToLower(), ExcelColumn = "事故或事件性质" }); listColumnEntity.Add(new ColumnEntity() { Column = "belongsystem".ToLower(), ExcelColumn = "所属系统" }); listColumnEntity.Add(new ColumnEntity() { Column = "accidenteventcause".ToLower(), ExcelColumn = "影响事故事件因素" }); listColumnEntity.Add(new ColumnEntity() { Column = "happentime".ToLower(), ExcelColumn = "发生时间", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "district".ToLower(), ExcelColumn = "地点(区域)" }); listColumnEntity.Add(new ColumnEntity() { Column = "belongdept".ToLower(), ExcelColumn = "所属部门/单位" }); listColumnEntity.Add(new ColumnEntity() { Column = "specialty".ToLower(), ExcelColumn = "相关专业" }); excelconfig.ColumnEntity = listColumnEntity; //调用导出方法 ExcelHelper.ExcelDownload(data, excelconfig); return(Success("导出成功。")); }
public ActionResult Export(string queryJson, string sortname, string sortorder) { var pagination = new Pagination() { page = 1, rows = 10000, sidx = string.IsNullOrWhiteSpace(sortname) ? "planfindate" : sortname, sord = string.IsNullOrWhiteSpace(sortorder) ? "asc" : sortorder }; var dt = planDetailsbll.GetList(pagination, queryJson); var now = DateTime.Now; foreach (DataRow dr in dt.Rows) { DateTime pdt = DateTime.Parse(dr["planfindate"].ToString()); object obj = dr["realfindate"]; if (obj != null && obj != DBNull.Value) { var rdt = DateTime.Parse(obj.ToString()); dr["state"] = (pdt < rdt) ? "超期完成" : "按时完成"; } else { dr["state"] = (pdt < now) ? "未完成": "待完成"; } dr["plandate"] = pdt.ToString("yyyy-MM"); } string title = string.Format("工作计划清单"); var queryParam = queryJson.ToJObject(); if (!queryParam["starttime"].IsEmpty() && !queryParam["endtime"].IsEmpty()) { title = string.Format("{0}-{1}工作计划清单", DateTime.Parse(queryParam["starttime"].ToString()).ToString("yyyy年MM月"), DateTime.Parse(queryParam["endtime"].ToString()).ToString("yyyy年MM月")); } //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = title; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = string.Format("工作计划清单_{0}.xls", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); excelconfig.IsAllSizeColumn = false;//此属性为True时,数据过大会导致Excel列宽过长报错 //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "contents".ToLower(), ExcelColumn = "工作内容", Alignment = "left", Width = 15 }); listColumnEntity.Add(new ColumnEntity() { Column = "dutydepartname".ToLower(), ExcelColumn = "责任部门", Width = 15 }); listColumnEntity.Add(new ColumnEntity() { Column = "dutyusername".ToLower(), ExcelColumn = "责任人", Width = 15 }); listColumnEntity.Add(new ColumnEntity() { Column = "plandate".ToLower(), ExcelColumn = "计划完成时间", Width = 15 }); listColumnEntity.Add(new ColumnEntity() { Column = "realfindate".ToLower(), ExcelColumn = "实际完成时间", Width = 15 }); listColumnEntity.Add(new ColumnEntity() { Column = "stdname".ToLower(), ExcelColumn = "管理标准", Width = 60 }); listColumnEntity.Add(new ColumnEntity() { Column = "state".ToLower(), ExcelColumn = "完成情况", Width = 15 }); excelconfig.ColumnEntity = listColumnEntity; //调用导出方法 ExcelHelper.ExcelDownload(dt, excelconfig); return(Success("导出成功。")); }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="queryJson"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { ExamOrderEntity para = new ExamOrderEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "membermobile": para.MemberMobile = queryParam["keyword"].ToString(); break; } } if (!queryParam["status"].IsEmpty()) { para.Status = int.Parse(queryParam["status"].ToString()); } } var list = ExamOrderBLL.Instance.GetList(para); if (list != null) { list.ForEach((o) => { if (o.Status != null) { o.StatusName = ((RCHL.Model.Enums.ExamStatus)o.Status).ToString(); } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "考试预约订单"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "考试预约订单.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "用户名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberMobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ClassName", ExcelColumn = "学车类型", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubjectName", ExcelColumn = "考试科目", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ExamDate", ExcelColumn = "通过时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <ExamOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出EXCEL /// </summary> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; SmsLogEntity para = new SmsLogEntity(); try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty()) { DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); para.StartTime = startTime.ToString(); para.EndTime = endTime.ToString(); } //类型 if (!queryParam["keyword"].IsEmpty()) { para.RecivMobile = queryParam["keyword"].ToString(); } para.NoticeType = (int)RCHL.Model.Enums.SMNoticeType.年审短信; var list = SmsLogBLL.Instance.GetList(para); if (list != null) { //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "年审车辆短信发送报表"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "年审车辆短信发送报表.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "RecivMobile", ExcelColumn = "车主号码", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CreateTime", ExcelColumn = "发送时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Exception", ExcelColumn = "发送状态", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Caption", ExcelColumn = "内容", Width = 15 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <SmsLogEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="queryJson"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { WithDrivingOrderEntity para = new WithDrivingOrderEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "drivingorderno": para.DrivingOrderNo = queryParam["keyword"].ToString(); break; case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "membermobile": para.MemberMobile = queryParam["keyword"].ToString(); break; case "schoolname": para.SchoolName = queryParam["keyword"].ToString(); break; case "teachername": para.TeacherName = queryParam["keyword"].ToString(); break; } } if (!queryParam["status"].IsEmpty()) { para.Status = int.Parse(queryParam["status"].ToString()); } } var list = WithDrivingOrderBLL.Instance.GetList(para); if (list != null) { list.ForEach((o) => { if (o.ServiceDate != null) { o.ServiceTime = Converter.ParseDateTime(o.ServiceDate).ToString("yyyy-MM-dd") + " " + o.ServiceTime; } if (o.Status != null) { o.StatusName = ((RCHL.Model.Enums.StudySubscribeStatus)o.Status).ToString(); } if (o.IsBandCar != null) { o.IsBandCarName = o.IsBandCar == (int)RCHL.Model.Enums.IsBandType.带车 ? "个人提供" : "教练提供"; } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "陪驾订单"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "陪驾订单.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "WithDrivingOrderNo", ExcelColumn = "订单号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "学员用户名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberMobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Price", ExcelColumn = "费用", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "IsBandCarName", ExcelColumn = "带车信息", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SchoolName", ExcelColumn = "预约驾校", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TeacherName", ExcelColumn = "教练", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <WithDrivingOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出 /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public int ExportGoodsInfo(string queryJson) { try { IEnumerable <GoodsinfoModel> Ienum = service.GetPageList(null, queryJson); if (Ienum == null && Ienum.Count() == 0) { return(0); } DataTable dt = new DataTable(); //添加列 dt.Columns.Add("fgoodsid", typeof(string)); //材料编号 dt.Columns.Add("funit", typeof(string)); //单位 dt.Columns.Add("fname1", typeof(string)); //名称规格 dt.Columns.Add("fcount", typeof(string)); //库存数量 dt.Columns.Add("fprice", typeof(string)); //平均价格 dt.Columns.Add("fmoney", typeof(string)); //总金额 dt.Columns.Add("fplace", typeof(string)); //存放位置 double fcount = 0; decimal fmoney = 0; foreach (GoodsinfoModel item in Ienum) { DataRow dr = dt.NewRow(); dr["fgoodsid"] = item.fgoodsid; dr["funit"] = item.funit; dr["fname1"] = item.fname1; dr["fcount"] = item.fcount; dr["fprice"] = string.Format("{0:0.##}", item.fprice); dr["fmoney"] = string.Format("{0:0.##}", item.fmoney); dr["fplace"] = item.fplace; dt.Rows.Add(dr); fcount += item.fcount; fmoney += item.fmoney; } DataRow drs = dt.NewRow(); drs["fgoodsid"] = ""; drs["funit"] = ""; drs["fname1"] = "合计"; drs["fcount"] = fcount; drs["fprice"] = ""; drs["fmoney"] = string.Format("{0:0.##}", fmoney); drs["fplace"] = ""; dt.Rows.Add(drs); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = string.Format("{0:yyyy-MM-dd}", DateTime.Now) + " 物资库存记录"; excelconfig.TitleFont = ""; excelconfig.TitlePoint = 25; excelconfig.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fgoodsid", ExcelColumn = "材料编号", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "funit", ExcelColumn = "单位", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fname1", ExcelColumn = "名称规格", Width = 25 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fcount", ExcelColumn = "库存数量", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fprice", ExcelColumn = "平均价格", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fmoney", ExcelColumn = "总金额", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "fplace", ExcelColumn = "存放位置", Width = 25 }); //调用导出方法 ExcelHelper.ExcelDownload(dt, excelconfig); return(1); } catch (Exception ex) { return(0); } }
public ActionResult ExportData(string queryJson) { try { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; pagination.p_kid = "Id"; pagination.p_fields = "FileName,Source,EmergencyType,Remark"; pagination.p_tablename = " bis_emergencylaw"; pagination.conditionJson = "1=1"; pagination.sidx = "createdate"; //排序字段 pagination.sord = "desc"; //排序方式 Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search"); if (!string.IsNullOrEmpty(authType)) { switch (authType) { case "1": pagination.conditionJson += " and (createuserid='" + user.UserId + "' or createuserorgcode='00')"; break; case "2": pagination.conditionJson += " and (createuserdeptcode='" + user.DeptCode + "' or createuserorgcode='00')"; break; case "3": pagination.conditionJson += " and (createuserdeptcode like'" + user.DeptCode + "%' or createuserorgcode='00')"; break; case "4": pagination.conditionJson += " and (createuserorgcode='" + user.OrganizeCode + "' or createuserorgcode='00')"; break; } } else { pagination.conditionJson += " and 0=1"; } } DataTable exportTable = emergencylawbll.GetPageDataTable(pagination, queryJson); foreach (DataRow item in exportTable.Rows) { var ctype = item["EmergencyType"].ToString(); if (ctype == "1") { ctype = "综合应急预案"; } else if (ctype == "2") { ctype = "专项应急预案"; } else if (ctype == "3") { ctype = "现场处置方案"; } item["EmergencyType"] = ctype; } //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "应急预案范本信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = "应急预案范本导出.xls"; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "filename", ExcelColumn = "文件和资料名称", Width = 40 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "source", ExcelColumn = "来源", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "emergencytype", ExcelColumn = "应急预案类型", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "remark", ExcelColumn = "备注", Width = 60 }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { } return(Success("导出成功。")); }
public ActionResult ExportData(string queryJson) { try { string isHrdl = new ERCHTMS.Busines.SystemManage.DataItemDetailBLL().GetItemValue("IsOpenPassword"); Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; var watch = CommonHelper.TimerStart(); pagination.conditionJson = " 1=1 "; pagination.sidx = "applydate desc,applycode"; //排序字段 pagination.sord = "desc"; //排序方式 LifthoistSearchModel search = null; if (!string.IsNullOrEmpty(queryJson)) { search = JsonConvert.DeserializeObject <LifthoistSearchModel>(queryJson); } else { search = new LifthoistSearchModel(); } #region 数据权限 Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { //根据当前用户对模块的权限获取记录 string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "a.createuserdeptcode", "a.createuserorgcode"); if (!string.IsNullOrEmpty(where)) { pagination.conditionJson += " and " + where; } } #endregion //查凭吊证 DataTable dt = null; string title = ""; if (search.pagetype == "1") { title = "准吊证申请"; dt = lifthoistcertbll.GetList(pagination, search); } else { title = "起重吊装作业申请"; dt = lifthoistjobbll.GetList(pagination, search); } DataTable exportTable = dt; DataTable excelTable = new DataTable(); excelTable.Columns.Add(new DataColumn("auditstate")); excelTable.Columns.Add(new DataColumn("applycodestr")); if (search.pagetype != "1") { excelTable.Columns.Add(new DataColumn("qualitytype")); } excelTable.Columns.Add(new DataColumn("toolname")); excelTable.Columns.Add(new DataColumn("worktime")); excelTable.Columns.Add(new DataColumn("constructionunitname")); excelTable.Columns.Add(new DataColumn("applyusername")); excelTable.Columns.Add(new DataColumn("applydate")); excelTable.Columns.Add(new DataColumn("flowdeptname")); excelTable.Columns.Add(new DataColumn("flowname")); foreach (DataRow item in dt.Rows) { DataRow newDr = excelTable.NewRow(); string auditstate = item["auditstate"].ToString(); if (auditstate == "0") { auditstate = "申请中"; } else if (auditstate == "1") { auditstate = "审核(批)中"; } else if (auditstate == "2") { auditstate = "审核(批)通过"; } newDr["auditstate"] = auditstate; newDr["applycodestr"] = item["applycodestr"]; if (search.pagetype != "1") { string qualitytype = item["qualitytype"].ToString(); if (qualitytype == "0") { qualitytype = "30T以下"; } else if (qualitytype == "1") { qualitytype = "30T以上"; } else { qualitytype = "2台起重设备共同起吊3T及以上"; } newDr["qualitytype"] = qualitytype; } newDr["auditstate"] = auditstate; newDr["toolname"] = item["toolname"]; DateTime workstartdate, workenddate, applydate; DateTime.TryParse(item["workstartdate"].ToString(), out workstartdate); DateTime.TryParse(item["workenddate"].ToString(), out workenddate); DateTime.TryParse(item["applydate"].ToString(), out applydate); string worktime = string.Empty; if (workstartdate.ToString("yyyy-MM-dd HH:mm") != "0001-01-01 00:00") { worktime += workstartdate.ToString("yyyy-MM-dd HH:mm"); } if (workenddate.ToString("yyyy-MM-dd HH:mm") != "0001-01-01 00:00") { worktime += " - " + workenddate.ToString("yyyy-MM-dd HH:mm"); } newDr["worktime"] = worktime; newDr["constructionunitname"] = item["constructionunitname"]; newDr["applyusername"] = item["applyusername"]; string adate = string.Empty; if (applydate.ToString("yyyy-MM-dd HH:mm") != "0001-01-01 00:00") { adate = applydate.ToString("yyyy-MM-dd HH:mm"); } newDr["applydate"] = adate; newDr["flowdeptname"] = item["flowdeptname"]; newDr["flowname"] = item["flowname"]; excelTable.Rows.Add(newDr); } //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = title; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = title + ".xls"; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "auditstate", ExcelColumn = "作业许可状态", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "applycodestr", ExcelColumn = "申请编号", Width = 20 }); if (search.pagetype != "1") { excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "qualitytype", ExcelColumn = "起吊质量描述", Width = 20 }); } excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "toolname", ExcelColumn = "吊装工具名称", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "worktime", ExcelColumn = isHrdl == "true" ? "作业时间" : "计划作业时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "constructionunitname", ExcelColumn = "作业单位", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "applyusername", ExcelColumn = "申请人", Width = 40 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "applydate", ExcelColumn = "申请时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "flowdeptname", ExcelColumn = "审核/批部门", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "flowname", ExcelColumn = "审核/批流程", Width = 20 }); //调用导出方法 ExcelHelper.ExcelDownload(excelTable, excelconfig); } catch (Exception ex) { } return(Success("导出成功。")); }
public ActionResult Export(string condition, string queryJson) { Pagination pagination = new Pagination(); queryJson = queryJson ?? ""; pagination.p_kid = "ID"; pagination.p_fields = "districtname, DANGERSOURCE, jkarear,jktimestart,jktimeend,JkyhzgIds,case WHEN jkskstatus>0 then '已受控' WHEN jkskstatus=0 then '不受控' else '未监控' end as jkskstatusname"; pagination.p_tablename = "V_HSD_DANGERQD_JK t"; pagination.conditionJson = "1=1"; pagination.page = 1; pagination.rows = 100000000; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (user.IsSystem) { pagination.conditionJson = "1=1"; } else { if (user.RoleName.Contains("省级")) { pagination.conditionJson += " and (CreateUserId='" + user.UserId + "' or DeptCode in(select encode from BASE_DEPARTMENT start with encode='" + user.NewDeptCode + "' connect by prior departmentid = parentid))"; } else { pagination.conditionJson += " and (CreateUserId='" + user.UserId + "' or DeptCode in(select encode from BASE_DEPARTMENT start with encode='" + user.DeptCode + "' connect by prior departmentid = parentid))"; } //pagination.conditionJson += " and (CreateUserId='" + user.UserId + "' or DeptCode like '" + user.DeptCode + "%')"; } var watch = CommonHelper.TimerStart(); var data = hazardsourcebll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "重大危险源监控检测"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "重大危险源监控检测.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "districtname", ExcelColumn = "所属区域" }); listColumnEntity.Add(new ColumnEntity() { Column = "DANGERSOURCE".ToLower(), ExcelColumn = "危险源名称/场所" }); listColumnEntity.Add(new ColumnEntity() { Column = "jkarear".ToLower(), ExcelColumn = "监控地点" }); listColumnEntity.Add(new ColumnEntity() { Column = "jktimestart".ToLower(), ExcelColumn = "监控时间起" }); listColumnEntity.Add(new ColumnEntity() { Column = "jktimeend".ToLower(), ExcelColumn = "监控时间止" }); listColumnEntity.Add(new ColumnEntity() { Column = "JkyhzgIds".ToLower(), ExcelColumn = "隐患" }); listColumnEntity.Add(new ColumnEntity() { Column = "jkskstatusname".ToLower(), ExcelColumn = "监控状态" }); excelconfig.ColumnEntity = listColumnEntity; //调用导出方法 ExcelHelper.ExcelDownload(data, excelconfig); return(Success("导出成功。")); }
/// <summary> /// 导出反违章指标信息 /// </summary> /// <param name="queryJson"></param> /// <param name="fileName"></param> /// <returns></returns> public ActionResult ExportIndexInfo(string queryJson, string fileName) { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000000; Operator curUser = ERCHTMS.Code.OperatorProvider.Provider.Current(); string p_fields = string.Empty; string p_fieldsName = string.Empty; try { var queryParam = queryJson.ToJObject(); pagination.p_kid = "id"; pagination.p_fields = @"deptname,dutyname,indexvalue,yearvalue"; pagination.p_tablename = string.Format(@"( select a.* ,b.encode ,b.sortcode,b.deptcode from bis_lllegalquantifyindex a left join base_department b on a.deptid = b.departmentid ) a"); pagination.conditionJson = string.Format(" 1=1 "); if (!queryParam["DutyId"].IsEmpty()) { pagination.conditionJson += string.Format(" and dutyid='{0}' ", queryParam["DutyId"].ToString()); } if (!queryParam["DeptId"].IsEmpty()) { pagination.conditionJson += string.Format(" and deptid ='{0}' ", queryParam["DeptId"].ToString()); } if (!queryParam["YearValue"].IsEmpty()) { pagination.conditionJson += string.Format(" and yearvalue ='{0}' ", queryParam["YearValue"].ToString()); } pagination.sord = "asc"; pagination.sidx = "yearvalue desc ,deptcode asc ,sortcode"; //取出数据源 var exportTable = lllegalregisterbll.GetGeneralQuery(pagination); string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = fileName; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = fielname; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门" }); listColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位" }); listColumnEntity.Add(new ColumnEntity() { Column = "indexvalue".ToLower(), ExcelColumn = "反违章指标" }); listColumnEntity.Add(new ColumnEntity() { Column = "yearvalue".ToLower(), ExcelColumn = "月份" }); excelconfig.ColumnEntity = listColumnEntity; exportTable.Columns.Remove("id"); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { return(Error(ex.Message)); } return(Success("导出成功!")); }
public ActionResult Export(FormCollection fc) { var user = OperatorProvider.Provider.Current(); //取出数据源 var from = fc.Get("from"); var to = fc.Get("to"); var filtertype = fc.Get("filtertype") ?? ""; var llevel = fc.Get("filtervalue1") ?? ""; var ltype = fc.Get("filtervalue2") ?? ""; var lperson = fc.Get("filtervalue3") ?? ""; var total = 0; //var data = new LllegalBLL().GetList(user.DeptId, filtertype == "全部" ? string.Empty : filtertype, filtervalue, string.IsNullOrEmpty(from) ? null : (DateTime?)DateTime.Parse(from), string.IsNullOrEmpty(to) ? null : (DateTime?)DateTime.Parse(to), page, pagesize, out total); var data = this.GetLllegalList(1000000, 1, ltype, llevel, from, to, out total); DataTable exportTable = new DataTable(); exportTable.Columns.Add("lllegalnumber"); exportTable.Columns.Add("flowstate"); exportTable.Columns.Add("lllegalperson"); exportTable.Columns.Add("lllegallevelname"); exportTable.Columns.Add("lllegaltypename"); exportTable.Columns.Add("lllegaltime"); exportTable.Columns.Add("reformfinishdate"); foreach (var item in data) { DataRow dr = exportTable.NewRow(); dr["lllegalnumber"] = item.lllegalnumber; dr["flowstate"] = item.flowstate; dr["lllegalperson"] = item.lllegalperson; dr["lllegallevelname"] = item.lllegallevelname; dr["lllegaltypename"] = item.lllegaltypename; dr["lllegaltime"] = item.lllegaltime; dr["reformfinishdate"] = item.reformfinishdate; exportTable.Rows.Add(dr); } //DataTable exportTable = bll.getExport(user.DeptId, "", ""); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); //excelconfig.Title = "违章信息"; //excelconfig.TitleFont = "微软雅黑"; //excelconfig.TitlePoint = 25; excelconfig.HeadHeight = 50; excelconfig.HeadPoint = 12; excelconfig.HeadFont = "宋体"; excelconfig.FileName = "违章信息导出.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lllegalnumber", ExcelColumn = "违章编号", Width = 12, Alignment = "fill" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "flowstate", ExcelColumn = "整改状态", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lllegalperson", ExcelColumn = "违章人员", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lllegallevelname", ExcelColumn = "违章等级", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lllegaltypename", ExcelColumn = "违章类型", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lllegaltime", ExcelColumn = "违章时间", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "reformfinishdate", ExcelColumn = "整改完成时间", Width = 15 }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); //从泛型Lis导出 //TExcelHelper<DepartmentEntity>.ExcelDownload(department.GetList().ToList(), excelconfig); return(View()); }
/// <summary> /// 导出反违章指标统计信息 /// </summary> /// <param name="queryJson"></param> /// <param name="fileName"></param> /// <returns></returns> public ActionResult ExportExcel(string queryJson, string fileName) { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000000; Operator curUser = ERCHTMS.Code.OperatorProvider.Provider.Current(); string p_fields = string.Empty; string p_fieldsName = string.Empty; try { var queryParam = queryJson.ToJObject(); pagination.p_kid = "id"; pagination.p_fields = @"username,deptname,dutyname,indexvalue,yearmonth,realnum,percents"; pagination.p_tablename = string.Format(@" ( with indexTb as ( select a.deptid,a.dutyid,a.indexvalue,a.yearvalue,(a.yearvalue ||'-'|| b.month) yearmonth from bis_lllegalquantifyindex a left join (select lpad(level,2,0) as month from dual connect by level <13) b on instr(a.monthvalue,b.month)>0 where to_date(a.yearvalue ||'-'|| b.month||'-01','yyyy-mm-dd hh24:mi:ss') < to_date('{0}','yyyy-mm-dd hh24:mi:ss') ), olduserTb as ( select count(1) pnum, b.realname username,b.userid,b.sortcode,b.deptsort,b.departmentid,b.deptname ,a.dutyid,c.fullname dutyname ,to_char(a.createdate,'yyyy-MM') yearmonth from bis_lllegalregister a inner join v_userinfo b on a.createuserid = b.userid inner join base_role c on a.dutyid =c.roleid group by b.realname ,b.userid,b.sortcode,b.deptsort,b.departmentid,b.deptname ,a.dutyid,c.fullname ,to_char(a.createdate,'yyyy-MM') ), userTb as ( select b.indexvalue,b.yearvalue,a.realname username,a.userid,a.sortcode,a.deptsort,a.departmentid,a.deptname ,a.dutyid,a.dutyname, b.yearmonth from v_userinfo a inner join indexTb b on a.departmentid=b.deptid and a.dutyid=b.dutyid union select b.indexvalue,b.yearvalue,a.username,a.userid,a.sortcode,a.deptsort,a.departmentid,a.deptname ,a.dutyid,a.dutyname, b.yearmonth from olduserTb a inner join indexTb b on a.departmentid=b.deptid and a.dutyid=b.dutyid and a.yearmonth =b.yearmonth ) select rownum id ,nvl(b.pnum,0) realnum, (to_char(round((case when nvl(a.indexvalue,0) = 0 then 0 else nvl(b.pnum,0) / nvl(a.indexvalue,0) * 100 end ),2))||'%') percents, a.indexvalue,a.yearvalue,a.username,a.userid,a.sortcode,a.deptsort,a.departmentid,a.deptname,a.yearmonth,a.dutyid,a.dutyname from userTb a left join ( select count(1) pnum, to_char(createdate,'yyyy-MM') yearmonth , createuserid,dutyid from bis_lllegalregister group by to_char(createdate,'yyyy-MM'),createuserid,dutyid ) b on a.userid =b.createuserid and a.yearmonth = b.yearmonth and a.dutyid = b.dutyid ) a", DateTime.Now.AddMonths(1).ToString("yyyy-MM-01")); pagination.conditionJson = string.Format(" 1=1 "); if (!queryParam["DutyId"].IsEmpty()) { pagination.conditionJson += string.Format(" and dutyid='{0}' ", queryParam["DutyId"].ToString()); } if (!queryParam["DeptId"].IsEmpty()) { pagination.conditionJson += string.Format(" and departmentid ='{0}' ", queryParam["DeptId"].ToString()); } if (!queryParam["YearValue"].IsEmpty()) { pagination.conditionJson += string.Format(" and yearvalue ='{0}' ", queryParam["YearValue"].ToString()); } if (!queryParam["MonthValue"].IsEmpty()) { pagination.conditionJson += string.Format(" and substr(a.yearmonth,6,2) = '{0}' ", queryParam["MonthValue"].ToString()); } pagination.sord = "desc"; pagination.sidx = "yearmonth desc,deptsort asc,sortcode asc,userid"; //取出数据源 var exportTable = lllegalregisterbll.GetGeneralQuery(pagination); string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = fileName; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = fielname; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "username".ToLower(), ExcelColumn = "姓名" }); listColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门" }); listColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位" }); listColumnEntity.Add(new ColumnEntity() { Column = "indexvalue".ToLower(), ExcelColumn = "反违章指标" }); listColumnEntity.Add(new ColumnEntity() { Column = "yearmonth".ToLower(), ExcelColumn = "月份" }); listColumnEntity.Add(new ColumnEntity() { Column = "realnum".ToLower(), ExcelColumn = "实际反违章数量" }); listColumnEntity.Add(new ColumnEntity() { Column = "percents".ToLower(), ExcelColumn = "完成率" }); excelconfig.ColumnEntity = listColumnEntity; exportTable.Columns.Remove("id"); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { return(Error(ex.Message)); } return(Success("导出成功!")); }
/// <summary> /// 员工档案导出 /// </summary> /// <param name="queryJson"></param> /// <param name="field">导出字段</param> /// <returns></returns> public int EmployinfoExport(string queryJson, string field) { //取出数据源 DataTable exportTable = service.GetListInfo(queryJson); if (exportTable != null && exportTable.Rows.Count > 0) { //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = null; excelconfig.TitleFont = ""; excelconfig.TitlePoint = 25; excelconfig.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "empid", ExcelColumn = "编号", Width = 10 }); if (!string.IsNullOrEmpty(field)) { string[] fields = field.Split(','); #region 字段判断 foreach (string item in fields) { switch (item) { case "empcode": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "empcode", ExcelColumn = "工号", Width = 10 }); break; case "empname": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "empname", ExcelColumn = "姓名", Width = 15 }); break; case "sex": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "sex", ExcelColumn = "性别", Width = 10 }); break; case "deptname": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname", ExcelColumn = "部门", Width = 20 }); break; case "indate": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "indate", ExcelColumn = "进公司时间", Width = 15 }); break; case "hiredate": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "hiredate", ExcelColumn = "合同开始日期", Width = 15 }); break; case "firedate": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "firedate", ExcelColumn = "合同结束日期", Width = 15 }); break; case "address": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "address", ExcelColumn = "家庭地址", Width = 20 }); break; case "postcode": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "postcode", ExcelColumn = "邮编", Width = 10 }); break; case "homephone": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "homephone", ExcelColumn = "家庭电话", Width = 15 }); break; case "mobilephone": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "mobilephone", ExcelColumn = "手机", Width = 15 }); break; case "identityno": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "identityno", ExcelColumn = "身份证号", Width = 20 }); break; case "birthday": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "birthday", ExcelColumn = "出身日期", Width = 15 }); break; case "p_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "p_name", ExcelColumn = "政治面貌", Width = 10 }); break; case "c_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "c_name", ExcelColumn = "职务", Width = 10 }); break; case "d_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "d_name", ExcelColumn = "学历", Width = 10 }); break; case "du_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "du_name", ExcelColumn = "职称", Width = 10 }); break; case "t_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "t_name", ExcelColumn = "技术等级", Width = 10 }); break; case "j_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "j_name", ExcelColumn = "岗位", Width = 10 }); break; case "ca_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ca_name", ExcelColumn = "职级", Width = 10 }); break; case "co_name": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "co_name", ExcelColumn = "用工来源", Width = 10 }); break; case "contracttype": excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "contracttype", ExcelColumn = "用工性质", Width = 10 }); break; default: break; } } #endregion } else { #region 全部 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "empcode", ExcelColumn = "工号", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "empname", ExcelColumn = "姓名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "sex", ExcelColumn = "性别", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname", ExcelColumn = "部门", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "indate", ExcelColumn = "进公司时间", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "hiredate", ExcelColumn = "合同开始日期", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "firedate", ExcelColumn = "合同结束日期", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "address", ExcelColumn = "家庭地址", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "postcode", ExcelColumn = "邮编", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "homephone", ExcelColumn = "家庭电话", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "mobilephone", ExcelColumn = "手机", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "identityno", ExcelColumn = "身份证号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "birthday", ExcelColumn = "出身日期", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "p_name", ExcelColumn = "政治面貌", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "c_name", ExcelColumn = "职务", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "d_name", ExcelColumn = "学历", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "du_name", ExcelColumn = "职称", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "t_name", ExcelColumn = "技术等级", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "j_name", ExcelColumn = "岗位", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ca_name", ExcelColumn = "职级", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "co_name", ExcelColumn = "用工来源", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "contracttype", ExcelColumn = "用工性质", Width = 10 }); #endregion } //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); return(1); } else { return(0); } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="productOutId"></param> /// <param name="conditions"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); MemberEntity para = new MemberEntity(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "mobile": para.Mobile = queryParam["keyword"].ToString(); break; case "schoolname": para.SchoolName = queryParam["keyword"].ToString(); break; } } //等级 if (!queryParam["lev"].IsEmpty()) { para.LevId = queryParam["lev"].ToString(); } var list = MemberBLL.Instance.GetList(para); if (list != null) { list.ForEach((o) => { if (o.StudyHour1 != null) { o.HaveStudyHours = string.Format("正班数:{0},加班数:{1}", o.StudyHour1, o.StudyHour2); } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "会员信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "会员信息.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "用户姓名", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "NikeName", ExcelColumn = "用户昵称", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Mobile", ExcelColumn = "注册手机号", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SchoolName", ExcelColumn = "所属驾校", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "HaveStudyHours", ExcelColumn = "剩余学时", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CreateTime", ExcelColumn = "注册时间", Width = 50 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Point", ExcelColumn = "账户积分", Width = 50 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "LevName", ExcelColumn = "会员等级", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <MemberEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="queryJson"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { TrainingOrderEntity para = new TrainingOrderEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "trainingorderno": para.TrainingOrderNo = queryParam["keyword"].ToString(); break; case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "membermobile": para.MemberMobile = queryParam["keyword"].ToString(); break; case "schoolname": para.SchoolName = queryParam["keyword"].ToString(); break; case "trainingcarnumber": para.TrainingCarNumber = queryParam["keyword"].ToString(); break; } } if (!queryParam["status"].IsEmpty()) { para.Status = int.Parse(queryParam["status"].ToString()); } if (!queryParam["checkedids"].IsEmpty()) { List <string> liststr = new List <string>(); string[] ids = queryParam["checkedids"].ToString().Split(','); foreach (var item in ids) { if (item != "") { liststr.Add(item); } } if (liststr.Count > 0) { para.CheckIds = liststr; } } } para.sidx = "CreateTime"; para.sord = "desc"; var list = TrainingOrderBLL.Instance.GetList(para); if (list != null) { int subcount = 0; list.ForEach((o) => { if (o.ServiceDate != null) { o.ServiceTime = Converter.ParseDateTime(o.ServiceDate).ToString("yyyy-MM-dd") + " " + o.ServiceTime; } if (o.Status != null) { o.StatusName = ((QX360.Model.Enums.TrainingStatus)o.Status).ToString(); } if (o.CashType != null) { o.CashTypeName = ((QX360.Model.Enums.CashType)o.CashType).ToString(); } if (o.UserType != null) { o.UserTypeName = ((QX360.Model.Enums.TrainingUserType)o.UserType).ToString(); } var detail = TrainingOrderDetailBLL.Instance.GetList(new TrainingOrderDetailEntity() { TrainingOrderId = o.TrainingOrderId }); int subCount = 0; detail.ForEach((d) => { subCount = subCount + 1; }); o.SubrictCount = subCount; }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "实训预约订单"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "实训预约订单.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingOrderNo", ExcelColumn = "订单号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "UserTypeName", ExcelColumn = "预约类型", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "学员/教练用户名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberMobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubrictCount", ExcelColumn = "合计预约数", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SchoolName", ExcelColumn = "预约驾校", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Price", ExcelColumn = "费用", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CashTypeName", ExcelColumn = "支付方式", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingTypeName", ExcelColumn = "实训类型", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingCarName", ExcelColumn = "实训车名称", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TrainingCarNumber", ExcelColumn = "实训车车牌", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <TrainingOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
public ActionResult Export(string queryJson, string mode = "") { Pagination pagination = new Pagination(); pagination.p_kid = "ID"; pagination.p_fields = "PLANNAME,startdate,enddate,deptname,0 num1,0 num2,0 num3,0 num4,0 num5,case WHEN status=0 then '未完成' else '已完成' end as statusstr,status,areaid,'' bsusers,'' pgusers"; pagination.p_tablename = " (select * from BIS_RISKPLAN order by CreateDate desc) t"; pagination.conditionJson = "1=1"; pagination.page = 1; pagination.rows = 100000000; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!string.IsNullOrEmpty(mode)) { //当前用户待辨识或评估的计划 if (mode == "0") { pagination.conditionJson += string.Format(" and status=0 and (',' || userids || ',' like '%,{0},%' or createuserid='{1}')", user.Account, user.UserId); } //当前用户所在部门待辨识或评估的计划 else { pagination.conditionJson += " and status=0"; } } string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search"); if (!string.IsNullOrEmpty(authType)) { switch (authType) { case "1": pagination.conditionJson += " and (createuserid='" + user.UserId + "'"; break; case "2": pagination.conditionJson += string.Format(" and (deptcode='{0}' or id in(select planid from BIS_RISKPPLANDATA where deptcode='{0}')", user.DeptCode); break; case "3": pagination.conditionJson += string.Format(" and (deptcode like '{0}%' or id in(select planid from BIS_RISKPPLANDATA where deptcode='{0}')", user.DeptCode); break; case "4": pagination.conditionJson += " and (deptcode like '" + user.OrganizeCode + "%'"; break; case "5": pagination.conditionJson += " and (1=1 "; break; } pagination.conditionJson += " or (',' || userids || ',') like '%," + user.Account + ",%'"; pagination.conditionJson += string.Format(" or id in(select planid from BIS_RISKPPLANDATA where deptcode='{0}')) ", user.DeptCode); } else { pagination.conditionJson = " (',' || userids || ',') like '%," + user.Account + ",%' or createuserid='" + user.UserId + "'"; } var watch = CommonHelper.TimerStart(); DataTable data = riskplanbll.GetPageList(pagination, queryJson); foreach (DataRow dr in data.Rows) { List <int> list = riskplanbll.GetNumbers(dr[0].ToString(), DateTime.Parse(dr[2].ToString()).ToString("yyyy-MM-dd"), DateTime.Parse(dr[3].ToString()).ToString("yyyy-MM-dd"), int.Parse(dr["status"].ToString()), dr["areaid"].ToString()); dr["num1"] = list[0]; dr["num2"] = list[1]; dr["num3"] = list[2]; dr["num4"] = list[3]; dr["num5"] = list[4]; string[] users = riskplanbll.GetUsers(dr[0].ToString()).Split('|'); dr["bsusers"] = users[0]; dr["pgusers"] = users[1]; } //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "风险辨识评估"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "风险辨识评估.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); ColumnEntity columnentity = new ColumnEntity(); listColumnEntity.Add(new ColumnEntity() { Column = "PLANNAME".ToLower(), ExcelColumn = "计划名称", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "startdate".ToLower(), ExcelColumn = "计划开始时间", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "enddate".ToLower(), ExcelColumn = "计划结束时间", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "制定部门", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "num1".ToLower(), ExcelColumn = "原有风险数量", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "num2".ToLower(), ExcelColumn = "新增风险数量", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "num3".ToLower(), ExcelColumn = "完善风险数量", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "num4".ToLower(), ExcelColumn = "消除风险数量", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "num5".ToLower(), ExcelColumn = "现有风险数量", Alignment = "Center" }); listColumnEntity.Add(new ColumnEntity() { Column = "statusstr".ToLower(), ExcelColumn = "状态", Alignment = "Center" }); excelconfig.ColumnEntity = listColumnEntity; //调用导出方法 ExcelHelper.ExcelDownload(data, excelconfig); return(Success("导出成功。")); }
public ActionResult ExportData(string queryJson) { try { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; pagination.p_kid = "ID"; pagination.p_fields = @"username,deptname,devicename,areaname,inout,devicetype,eventtype,screenshot,createdate, case when inout = 0 then '进门' when inout = 1 then '出门' else '' end inoutname, case when EVENTTYPE = 1 then '人脸通过事件' when EVENTTYPE = 2 then '车辆放行事件' when EVENTTYPE = 3 then '门禁刷卡事件' when EVENTTYPE = 4 then '门禁指纹通过事件' else '' end eventtypename "; pagination.p_tablename = @"BIS_HIKINOUTLOG"; pagination.conditionJson = " 1=1"; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); DataTable exportTable = hikinoutlogbll.GetPageList(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "人员进出记录"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = "人员进出记录导出.xls"; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "username", ExcelColumn = "姓名", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname", ExcelColumn = "部门", Width = 40 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "devicename", ExcelColumn = "门禁点", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "areaname", ExcelColumn = "门禁点区域", Width = 30 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createdate", ExcelColumn = "时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "inoutname", ExcelColumn = "出/入", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "eventtypename", ExcelColumn = "事件类型", Width = 20 }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { } return(Success("导出成功。")); }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="productOutId"></param> /// <param name="conditions"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); SchoolEntity para = new SchoolEntity(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "name": para.Name = queryParam["keyword"].ToString(); break; case "conectname": para.ConectName = queryParam["keyword"].ToString(); break; case "conecttel": para.ConectTel = queryParam["keyword"].ToString(); break; case "countyname": para.CountyName = queryParam["keyword"].ToString(); break; } } para.IsTraining = 0; var list = SchoolBLL.Instance.GetList(para); if (list != null) { list.ForEach((o) => { if (o.ProvinceId != null) { o.ProvinceName = AreaBLL.Instance.GetEntity(o.ProvinceId).AreaName; } if (o.CityId != null) { o.CityName = AreaBLL.Instance.GetEntity(o.CityId).AreaName; } if (o.CountyId != null) { o.CountyName = AreaBLL.Instance.GetEntity(o.CountyId).AreaName; } o.AddressInfo = o.ProvinceName + o.CityName + o.CountyName + o.AddressInfo; var subjectlist = SubjectBLL.Instance.GetList(new SubjectEntity() { SchoolId = o.SchoolId }); var subjectname = ""; foreach (var item in subjectlist) { subjectname += string.Format("{0}:【{1}元】,", item.SubjectName, item.Price); } if (subjectname != "") { o.SubjectName = subjectname.Substring(0, subjectname.Length - 1); } o.TeacherCount = TeacherBLL.Instance.GetList(new TeacherEntity() { SchoolId = o.SchoolId }).Count(); o.StudentCount = MemberBLL.Instance.GetList(new MemberEntity() { SchoolId = o.SchoolId }).Count(); o.ApplyOrderCount = ApplyOrderBLL.Instance.GetList(new ApplyOrderEntity() { SchoolId = o.SchoolId }).Count(); if (o.Status != null) { o.StatusName = ((RCHL.Model.Enums.UseStatus)o.Status).ToString(); } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "驾校信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "驾校信息.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Name", ExcelColumn = "驾校名称", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "AddressInfo", ExcelColumn = "地址", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ConectName", ExcelColumn = "联系人", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ConectTel", ExcelColumn = "联系电话", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SubjectName", ExcelColumn = "课程", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StudyPrice", ExcelColumn = "学车费用", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "TeacherCount", ExcelColumn = "教练数", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StudentCount", ExcelColumn = "学员数", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ApplyOrderCount", ExcelColumn = "报名订单", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <SchoolEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出EXCEL /// </summary> /// <param name="queryJson"></param> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { AuditOrderEntity para = new AuditOrderEntity(); if (!string.IsNullOrWhiteSpace(queryJson)) { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "auditorderno": para.AuditOrderNo = queryParam["keyword"].ToString(); break; case "membername": para.MemberName = queryParam["keyword"].ToString(); break; case "mobile": para.Mobile = queryParam["keyword"].ToString(); break; case "organizationname": para.OrganizationName = queryParam["keyword"].ToString(); break; } } if (!queryParam["status"].IsEmpty()) { para.Status = int.Parse(queryParam["status"].ToString()); } if (!queryParam["checkedids"].IsEmpty()) { List <string> liststr = new List <string>(); string[] ids = queryParam["checkedids"].ToString().Split(','); foreach (var item in ids) { if (item != "") { liststr.Add(item); } } if (liststr.Count > 0) { para.CheckIds = liststr; } } } var list = AuditOrderBLL.Instance.GetList(para); if (list != null) { list.ForEach((o) => { if (o.ServiceDate != null) { o.ServiceTime = Converter.ParseDateTime(o.ServiceDate).ToString("yyyy-MM-dd") + " " + o.ServiceTime; } if (o.Status != null) { o.StatusName = ((QX360.Model.Enums.StudySubscribeStatus)o.Status).ToString(); } if (o.CashType != null) { o.CashTypeName = ((QX360.Model.Enums.CashType)o.CashType).ToString(); } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "年检订单"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "年检订单.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "AuditOrderNo", ExcelColumn = "订单号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "MemberName", ExcelColumn = "用户名", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Mobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarNum", ExcelColumn = "车牌号", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarFrameNum", ExcelColumn = "车架号", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时间", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Price", ExcelColumn = "费用", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CashTypeName", ExcelColumn = "支付方式", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "OrganizationName", ExcelColumn = "年检机构", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 20 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <AuditOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
public ActionResult ExportStatisticsExcel(int year, string mode) { string jsonList = powerplantinsidebll.GetStatisticsList(year, mode);; dynamic dyObj = JsonConvert.DeserializeObject(jsonList); ; DataTable tb = JsonToDataTable(dyObj.rows.ToString()); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 switch (mode) { case "0": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件类型统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件类型统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "type", ExcelColumn = "类型", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "人身", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "设备", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "消防", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "交通", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "环保", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "职业健康", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "1": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件性质统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件性质统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "type", ExcelColumn = "性质", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "二类障碍", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "异常", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "未遂", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "小微事件", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "一类障碍", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "一般", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "2": excelconfig.Title = year + "-" + DateTime.Now.Year + "年影响事故事件因素统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年影响事故事件因素统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "TypeName", ExcelColumn = "影响因素", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "1月", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "2月", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "3月", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "4月", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "5月", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "6月", Alignment = "center" }, new ColumnEntity() { Column = "num7", ExcelColumn = "7月", Alignment = "center" }, new ColumnEntity() { Column = "num8", ExcelColumn = "8月", Alignment = "center" }, new ColumnEntity() { Column = "num9", ExcelColumn = "9月", Alignment = "center" }, new ColumnEntity() { Column = "num10", ExcelColumn = "10月", Alignment = "center" }, new ColumnEntity() { Column = "num11", ExcelColumn = "11月", Alignment = "center" }, new ColumnEntity() { Column = "num12", ExcelColumn = "12月", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "3": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件发生的部门统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件发生的部门统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "dept", ExcelColumn = "各个部门", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "营销部", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "技术支持部", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "发电部", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "办公室", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "EHS部", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "相关方", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "全厂", Alignment = "center" } }; break; case "4": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件所属专业统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件所属专业统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "type", ExcelColumn = "所属专业", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "汽机专业", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "锅炉专业", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "电气专业", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "灰硫专业", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "化学专业", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "燃料专业", Alignment = "center" }, new ColumnEntity() { Column = "num7", ExcelColumn = "热控专业", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "5": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件所属机组统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件所属机组统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "type", ExcelColumn = "所属系统", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "#1机组", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "#2机组", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "#3机组", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "#4机组", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "公用系统", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "6": excelconfig.Title = year + "-" + DateTime.Now.Year + "年事故事件月度变化统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件月度变化统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>() { new ColumnEntity() { Column = "cs", ExcelColumn = "月份", Alignment = "center" }, new ColumnEntity() { Column = "num1", ExcelColumn = "1月", Alignment = "center" }, new ColumnEntity() { Column = "num2", ExcelColumn = "2月", Alignment = "center" }, new ColumnEntity() { Column = "num3", ExcelColumn = "3月", Alignment = "center" }, new ColumnEntity() { Column = "num4", ExcelColumn = "4月", Alignment = "center" }, new ColumnEntity() { Column = "num5", ExcelColumn = "5月", Alignment = "center" }, new ColumnEntity() { Column = "num6", ExcelColumn = "6月", Alignment = "center" }, new ColumnEntity() { Column = "num7", ExcelColumn = "7月", Alignment = "center" }, new ColumnEntity() { Column = "num8", ExcelColumn = "8月", Alignment = "center" }, new ColumnEntity() { Column = "num9", ExcelColumn = "9月", Alignment = "center" }, new ColumnEntity() { Column = "num10", ExcelColumn = "10月", Alignment = "center" }, new ColumnEntity() { Column = "num11", ExcelColumn = "11月", Alignment = "center" }, new ColumnEntity() { Column = "num12", ExcelColumn = "12月", Alignment = "center" }, new ColumnEntity() { Column = "Total", ExcelColumn = "总计", Alignment = "center" } }; break; case "7": int newyear = Convert.ToInt32(DateTime.Now.ToString("yyyy")); excelconfig.Title = year + "-" + newyear + "年事故事件年度变化统计"; excelconfig.FileName = year + "-" + DateTime.Now.Year + "年事故事件年度变化统计" + ".xls"; excelconfig.ColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity.AddIfNotExists(new ColumnEntity() { Column = "cs", ExcelColumn = "年份", Alignment = "center" }); for (int i = year + 1; i <= newyear; i++) { excelconfig.ColumnEntity.AddIfNotExists(new ColumnEntity() { Column = "num" + i, ExcelColumn = i + "年", Alignment = "center" }); } break; } //调用导出方法 ExcelHelper.ExportByAspose(tb, excelconfig.FileName, excelconfig.ColumnEntity); return(Success("导出成功。")); }
public ActionResult ExportData(string queryJson) { try { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 1000000000; pagination.p_kid = "Id"; pagination.p_fields = "FileName,FileCode,IssueDept,to_char(releasedate,'yyyy-MM-dd') as releasedate,to_char(revisedate,'yyyy-MM-dd') as revisedate,to_char(carrydate,'yyyy-MM-dd') as carrydate,lawtypename"; pagination.p_tablename = " bis_safeStandards a "; pagination.conditionJson = "1=1"; pagination.sidx = "a.createdate"; //排序字段 pagination.sord = "desc"; //排序方式 DataTable exportTable = safestandardsbll.GetPageDataTable(pagination, queryJson); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "安全操作规程信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 16; excelconfig.FileName = "安全操作规程信息导出.xls"; excelconfig.IsAllSizeColumn = true; List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); //需跟数据源列顺序保持一致 excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "filename", ExcelColumn = "文件名称", Width = 40 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "filecode", ExcelColumn = "文件编号", Width = 25 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "issuedept", ExcelColumn = "发布单位(部门)", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "releasedate", ExcelColumn = "发布时间", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "revisedate", ExcelColumn = "修订时间", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "carrydate", ExcelColumn = "实施时间", Width = 10 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "lawtypename", ExcelColumn = "类型", Width = 20 }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); } catch (Exception ex) { } return(Success("导出成功。")); }
/// <summary> /// 导出用户列表 /// </summary> /// <returns></returns> public void GetExportList() { //取出数据源 DataTable exportTable = service.GetExportList(); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "测试用户导出"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 25; excelconfig.FileName = "用户导出.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "account", ExcelColumn = "账户" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname", ExcelColumn = "姓名" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender", ExcelColumn = "性别" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "birthday", ExcelColumn = "生日" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "mobile", ExcelColumn = "手机", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "telephone", ExcelColumn = "电话", Background = Color.Red }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "wechat", ExcelColumn = "微信" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "manager", ExcelColumn = "主管" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "organize", ExcelColumn = "公司" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "department", ExcelColumn = "部门" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "description", ExcelColumn = "说明" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createdate", ExcelColumn = "创建日期" }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "createusername", ExcelColumn = "创建人" }); //调用导出方法 ExcelHelper.ExcelDownload(exportTable, excelconfig); //从泛型Lis导出 //TExcelHelper<DepartmentEntity>.ExcelDownload(department.GetList().ToList(), excelconfig); }
/// <summary> /// 导出EXCEL /// </summary> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); TeacherEntity para = new TeacherEntity(); //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "name": para.Name = queryParam["keyword"].ToString(); break; case "schoolname": para.SchoolName = queryParam["keyword"].ToString(); break; case "schoolid": para.SchoolId = queryParam["keyword"].ToString(); break; case "mobile": para.Mobile = queryParam["keyword"].ToString(); break; } } if (!queryParam["schoolid"].IsEmpty()) { para.SchoolId = queryParam["schoolid"].ToString(); } if (!queryParam["iswithdriving"].IsEmpty()) { para.IsWithDriving = int.Parse(queryParam["iswithdriving"].ToString()); } if (!queryParam["istakecar"].IsEmpty()) { para.IsTakeCar = int.Parse(queryParam["istakecar"].ToString()); } if (!queryParam["provinceid"].IsEmpty()) { para.ProvinceId = queryParam["provinceid"].ToString(); } if (!queryParam["cityid"].IsEmpty()) { para.CityId = queryParam["cityid"].ToString(); } if (!queryParam["countyid"].IsEmpty()) { para.CountyId = queryParam["countyid"].ToString(); } var list = TeacherBLL.Instance.GetList(para); if (list != null) { var StartTime = Util.Time.CalculateFirstDateOfWeek(DateTime.Now).ToString(); var EndTime = Util.Time.CalculateLastDateOfWeek(DateTime.Now).ToString(); list.ForEach((o) => { if (o.TeacherId != null) { if (o.ProvinceId != null) { o.ProvinceName = AreaBLL.Instance.GetEntity(o.ProvinceId).AreaName; } if (o.CityId != null) { o.CityName = AreaBLL.Instance.GetEntity(o.CityId).AreaName; } o.AddressInfo = o.ProvinceName + o.CityName + o.ServicesAreaNames; o.StudentCount = ApplyOrderBLL.Instance.GetList(new ApplyOrderEntity() { TeacherId = o.TeacherId }).Count(); o.WeekStudyOrderCount = StudyOrderBLL.Instance.GetList(new StudyOrderEntity() { TeacherId = o.TeacherId, StartTime = Util.Time.CalculateFirstDateOfWeek(DateTime.Now).ToString(), EndTime = Util.Time.CalculateLastDateOfWeek(DateTime.Now).ToString() }).Count(); o.WeekWithDrivingOrderCount = WithDrivingOrderBLL.Instance.GetList(new WithDrivingOrderEntity() { TeacherId = o.TeacherId, StartTime = Util.Time.CalculateFirstDateOfWeek(DateTime.Now).ToString(), EndTime = Util.Time.CalculateLastDateOfWeek(DateTime.Now).ToString() }).Count(); } if (o.Status != null) { o.StatusName = ((RCHL.Model.Enums.UseStatus)o.Status).ToString(); } if (o.IsWithDriving != null) { o.IsWithDrivingName = o.IsWithDriving == 1 ? "是" : "否"; } if (o.IsTakeCar != null) { o.IsTakeCarName = o.IsTakeCar == 1 ? "是" : "否"; } }); //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "教练信息"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "教练信息.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Name", ExcelColumn = "教练名称", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "SchoolName", ExcelColumn = "所属驾校", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "LevName", ExcelColumn = "教练等级", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Mobile", ExcelColumn = "联系方式", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "IdCard", ExcelColumn = "身份证号", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarNumber", ExcelColumn = "车牌号码", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StudentCount", ExcelColumn = "学员数", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "AddressInfo", ExcelColumn = "服务范围", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "IsWithDrivingName", ExcelColumn = "是否陪驾", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "IsTakeCarName", ExcelColumn = "是否带车", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "WeekStudyOrderCount", ExcelColumn = "本周学车订单数", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "WeekWithDrivingOrderCount", ExcelColumn = "本周陪驾订单数", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "StatusName", ExcelColumn = "状态", Width = 15 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <TeacherEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
/// <summary> /// 导出EXCEL /// </summary> public void ExportExcel(string queryJson) { string cacheKey = Request["cacheid"] as string; HttpRuntime.Cache[cacheKey + "-state"] = "processing"; HttpRuntime.Cache[cacheKey + "-row"] = "0"; AuditOrderEntity para = new AuditOrderEntity(); try { //这里要url解码 var queryParam = Server.UrlDecode(queryJson).ToJObject(); if (!queryParam["StartTime"].IsEmpty() && !queryParam["EndTime"].IsEmpty()) { DateTime startTime = queryParam["StartTime"].ToDate(); DateTime endTime = queryParam["EndTime"].ToDate(); para.StartTime = startTime.ToString(); para.EndTime = endTime.ToString(); } //类型 if (!queryParam["condition"].IsEmpty() && !queryParam["keyword"].IsEmpty()) { var condition = queryParam["condition"].ToString().ToLower(); switch (condition) { case "carnum": para.CarNum = queryParam["keyword"].ToString(); break; case "carframenum": para.CarFrameNum = queryParam["keyword"].ToString(); break; case "mobile": para.Mobile = queryParam["keyword"].ToString(); break; case "organizationname": para.OrganizationName = queryParam["keyword"].ToString(); break; } } if (!queryParam["TimeSpace"].IsEmpty()) { para.ServiceTime = queryParam["TimeSpace"].ToString(); } para.NotStatus = (int)RCHL.Model.Enums.PaySatus.已取消; var list = AuditOrderBLL.Instance.GetList(para); if (list != null) { //设置导出格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = "个人年审车辆预约明细报表"; excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 10; excelconfig.FileName = "个人年审车辆预约明细报表.xls"; excelconfig.IsAllSizeColumn = true; //每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 List <ColumnEntity> listColumnEntity = new List <ColumnEntity>(); excelconfig.ColumnEntity = listColumnEntity; ColumnEntity columnentity = new ColumnEntity(); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarNum", ExcelColumn = "车牌号码", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "CarFrameNum", ExcelColumn = "车架号", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "Mobile", ExcelColumn = "联系电话", Width = 20 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceDate", ExcelColumn = "预约时间", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "ServiceTime", ExcelColumn = "预约时段", Width = 15 }); excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "OrganizationName", ExcelColumn = "年审机构", Width = 15 }); //需合并索引 //excelconfig.MergeRangeIndexArr = new int[] { 0, 1, 2, 3, 4, 5, 6, 7 }; //调用导出方法 ExcelHelper <AuditOrderEntity> .ExcelDownload(list, excelconfig); HttpRuntime.Cache[cacheKey + "-state"] = "done"; } } catch (Exception) { HttpRuntime.Cache[cacheKey + "-state"] = "error"; } }
public ActionResult DownLoadYearlyTaskTemplete(int task_type) { //模板名称 string templeteName = "全年任务指标"; //模板各列名称 string[] listColumn = new string[] { }; if (task_type == 0) { listColumn = Constant.YearlyTaskTempleteCompanyListColumn; } else { listColumn = Constant.YearlyTaskTempletePersonListColumn; } //指定excel格式 ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = Server.UrlDecode(templeteName); excelconfig.TitleFont = "微软雅黑"; excelconfig.TitlePoint = 15; excelconfig.FileName = Server.UrlDecode(templeteName) + "数据录入模板.xls"; excelconfig.IsAllSizeColumn = true; excelconfig.ColumnEntity = new List <ColumnEntity>(); //行数据 DataTable rowData = new DataTable(); //各个细项指标 for (int i = 0; i < listColumn.Length; i++) { string columnName = "YearlyTask_" + i; string excelColumnName = listColumn[i]; excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = columnName, ExcelColumn = excelColumnName, Alignment = "center" }); rowData.Columns.Add(columnName); } if (task_type == 0) { //获取所有子公司 OrganizeEntity org = organizeApp.GetForm(BlocId); if (org == null) { return(Error("配置文件中的总公司id不存在,无法生产下载模板!")); } List <OrganizeEntity> branchList = organizeApp.GetListByParentIdOrderById(org.F_Id, CompanyId); DataRow dr = rowData.NewRow(); for (int i = 0; i < branchList.Count; i++) { dr = rowData.NewRow(); dr["YearlyTask_0"] = branchList[i].F_FullName; rowData.Rows.Add(dr); } } else { //获取所有当前公司下客户经理 List <UserEntity> managerList = null; if (OperatorProvider.Provider.GetCurrent() != null) { managerList = userApp.GetByOrgIdAndCMRoleId(OperatorProvider.Provider.GetCurrent().CompanyId, CustomerManagerRoleId); if (managerList.Count == 0) { return(Error("此公司下暂无客户经理,无法生成Excel模板!")); } } else { return(Error("无法获取当前登录人信息!")); } //List<UserEntity> managerList = userApp.GetList(); for (int i = 0; i < managerList.Count; i++) { DataRow dr = rowData.NewRow(); dr = rowData.NewRow(); dr["YearlyTask_0"] = managerList[i].F_RealName; dr["YearlyTask_14"] = managerList[i].F_MobilePhone; rowData.Rows.Add(dr); } } ExcelHelper.ExcelDownload(rowData, excelconfig); return(Success("操作成功。", null)); }