//导出报表 public JsonResult ExportReport() { #region 权限控制 int[] iRangePage = { AddPageNodeId, EditPageNodeId, DetailPageNodeId }; int iCurrentPageNodeId = RequestParameters.Pint("NodeId"); int iCurrentButtonId = (int)EButtonType.Export; var tempNoAuth = Utits.IsOperateAuth(iRangePage, iCurrentPageNodeId, iCurrentButtonId); if (tempNoAuth.ErrorType != 1) { return(Json(tempNoAuth)); } #endregion #region 查询条件 var searchCondition = new ConditionModel(); var WhereList = new List <WhereCondition>(); string UserName = RequestParameters.Pstring("UserName"); if (UserName.Length > 0) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "UserName"; whereCondition.FieldValue = UserName; whereCondition.FieldOperator = EnumOper.Contains; WhereList.Add(whereCondition); } string RealName = RequestParameters.Pstring("RealName"); if (RealName.Length > 0) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "RealName"; whereCondition.FieldValue = RealName; whereCondition.FieldOperator = EnumOper.Contains; WhereList.Add(whereCondition); } Guid?RoleID = RequestParameters.PGuidNull("RoleID"); if (RoleID != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "RoleID"; whereCondition.FieldValue = RoleID.Value; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } Guid?DeptID = RequestParameters.PGuidNull("DeptID"); if (DeptID != null) { //List<Guid?> ids_guid=new List<Guid?>(){DeptID,Guid.NewGuid()}; //string[] ids_guid = {DeptID.ToString(),Guid.NewGuid().ToString()}; var whereCondition = new WhereCondition(); whereCondition.FieldName = "DeptID"; whereCondition.FieldValue = DeptID; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } int?IsValid = RequestParameters.PintNull("IsValid"); if (IsValid != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "IsValid"; whereCondition.FieldValue = IsValid; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } searchCondition.WhereList = WhereList; var OrderList = new List <OrderCondition>(); string sortfield = RequestParameters.Pstring("sortfield"); if (sortfield.Length <= 0) { sortfield = "OperateDate"; } var orderCondition = new OrderCondition(); orderCondition.FiledOrder = sortfield; orderCondition.Ascending = RequestParameters.Pstring("sorttype") == "asc" ? true : false; OrderList.Add(orderCondition); searchCondition.OrderList = OrderList; #endregion var cBll = new UsersBll(); int iTotalRecord = 0; var searchList = cBll.SearchVByPageCondition(0, 65536, ref iTotalRecord, searchCondition); if (searchList == null || searchList.Count == 0) { var sRetrunModel = new ResultMessage(); sRetrunModel.ErrorType = 0; sRetrunModel.MessageContent = "操作失败:无导出数据."; return(Json(sRetrunModel)); } try { string fileName = "用户信息导出" + DateTime.Now.ToString("yyyyMMMMddHHmmss") + ".xls"; string fileUrl = Server.MapPath("~/Upload") + "/temp/"; if (!Directory.Exists(fileUrl)) { Directory.CreateDirectory(fileUrl); } var filePath = fileUrl + fileName; const int rowHeight = 20; const int colCount = 9; //导出数据的总列数 HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Sheet1"); #region 对齐格式 //左对齐格式 ICellStyle styleLeft = workbook.CreateCellStyle(); styleLeft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; styleLeft.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleLeft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleLeft.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleLeft.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleLeft.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //右对齐格式 ICellStyle styleRight = workbook.CreateCellStyle(); styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right; styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleRight.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleRight.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleRight.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleRight.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //居中对齐格式 ICellStyle styleCenter = workbook.CreateCellStyle(); styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleCenter.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleCenter.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleCenter.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleCenter.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; #endregion #region 标题和表头 //标题 ICellStyle styleTitle = workbook.CreateCellStyle(); styleTitle.Alignment = HorizontalAlignment.Center; styleTitle.VerticalAlignment = VerticalAlignment.Center; styleTitle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleTitle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleTitle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; var fontTitle = workbook.CreateFont(); fontTitle.FontHeightInPoints = 16; fontTitle.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; fontTitle.FontName = "微软雅黑"; styleTitle.SetFont(fontTitle); //表头 ICellStyle styleTh = workbook.CreateCellStyle(); styleTh.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleTh.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleTh.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleTh.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; styleTh.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; styleTh.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; var fontTh = workbook.CreateFont(); fontTh.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; styleTh.SetFont(fontTh); #endregion IRow row1 = sheet1.CreateRow(0); row1.HeightInPoints = rowHeight; //CellRangeAddress(int, int, int, int) //参数:起始行号,终止行号, 起始列号,终止列号 sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, colCount - 1)); row1.CreateCell(0).SetCellValue("用户信息"); row1.GetCell(0).CellStyle = styleCenter; IRow row2 = sheet1.CreateRow(1); row2.HeightInPoints = rowHeight; #region 创建表头 row2.CreateCell(0).SetCellValue("序号"); row2.CreateCell(1).SetCellValue("用户编号"); row2.CreateCell(2).SetCellValue("用户名称"); row2.CreateCell(3).SetCellValue("角色名称"); row2.CreateCell(4).SetCellValue("部门名称"); row2.CreateCell(5).SetCellValue("真实姓名"); row2.CreateCell(6).SetCellValue("操作时间"); row2.CreateCell(7).SetCellValue("状态"); row2.CreateCell(8).SetCellValue("备注"); for (int i = 0; i < colCount; i++) { row2.GetCell(i).CellStyle = styleTh; } #endregion int rowNumber = 2;//行号索引 IRow row; foreach (var item in searchList) { row = sheet1.CreateRow(rowNumber); row.HeightInPoints = rowHeight; row.CreateCell(0).SetCellValue(rowNumber - 1); row.GetCell(0).CellStyle = styleCenter; row.CreateCell(1).SetCellValue(item.UserCode); row.GetCell(1).CellStyle = styleLeft; row.CreateCell(2).SetCellValue(item.UserName); row.GetCell(2).CellStyle = styleLeft; row.CreateCell(3).SetCellValue(item.RoleName); row.GetCell(3).CellStyle = styleLeft; row.CreateCell(4).SetCellValue(item.DeptName); row.GetCell(4).CellStyle = styleLeft; row.CreateCell(5).SetCellValue(item.RealName); row.GetCell(5).CellStyle = styleLeft; row.CreateCell(6).SetCellValue(item.OperateDate == null ? "" : item.OperateDate.Value.ToString("yyyy-MM-dd HH:mm")); row.GetCell(6).CellStyle = styleLeft; row.CreateCell(7).SetCellValue(item.IsValid == 1 ? "有效" : "无效"); row.GetCell(7).CellStyle = styleLeft; row.CreateCell(8).SetCellValue(item.Remark); row.GetCell(8).CellStyle = styleLeft; rowNumber++; } for (int i = 0; i < colCount; i++) { sheet1.AutoSizeColumn(i); } using (var file = new MemoryStream()) { workbook.Write(file); using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = file.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } var sRetrunModel = new ResultMessage { ErrorType = 1, MessageContent = fileName }; return(Json(sRetrunModel)); } catch (Exception ex) { MessageLog.WriteLog(new LogParameterModel { LogLevel = ELogLevel.Error, Title = "导出数据", Message = ex.Message, ClassName = this.GetType().FullName }); var exRetrunModel = new ResultMessage { ErrorType = 0, MessageContent = "操作失败:系统性异常." }; return(Json(exRetrunModel)); } }
public JsonResult SearchList() { #region 权限控制 int[] iRangePage = { ListPageNodeId, ListPageFNodeId }; int iCurrentPageNodeId = RequestParameters.Pint("NodeId"); var tempAuth = Utits.IsNodePageAuth(iRangePage, iCurrentPageNodeId); if (tempAuth.ErrorType != 1) { return(Json(tempAuth)); } #endregion //当前页 int iCurrentPage = RequestParameters.Pint("currentPage"); iCurrentPage = iCurrentPage <= 0 ? 1 : iCurrentPage; //索引页 int iPageIndex = iCurrentPage - 1; //一页的数量 int iPageSize = RequestParameters.Pint("pageSize"); iPageSize = iPageSize <= 0 ? 5 : iPageSize; iPageSize = iPageSize > 100 ? 100 : iPageSize; //总记录数量 int iTotalRecord = 0; #region 查询条件 var searchCondition = new ConditionModel(); var WhereList = new List <WhereCondition>(); var UserName = RequestParameters.Pstring("UserName"); if (UserName.Length > 0) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "UserName"; whereCondition.FieldValue = UserName; whereCondition.FieldOperator = EnumOper.Contains; WhereList.Add(whereCondition); } var RealName = RequestParameters.Pstring("RealName"); if (RealName.Length > 0) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "RealName"; whereCondition.FieldValue = RealName; whereCondition.FieldOperator = EnumOper.Contains; WhereList.Add(whereCondition); } var RoleID = RequestParameters.PGuidNull("RoleID"); if (RoleID != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "RoleID"; whereCondition.FieldValue = RoleID.Value; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } var DeptID = RequestParameters.PGuidNull("DeptID"); if (DeptID != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "DeptID"; whereCondition.FieldValue = DeptID; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } var UserType = RequestParameters.PintNull("UserType"); if (UserType != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "UserType"; whereCondition.FieldValue = UserType; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } var super = Config.UserNameSuper; if (super.Length > 0) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "UserName"; whereCondition.FieldValue = super; whereCondition.FieldOperator = EnumOper.ExclamationEqual; WhereList.Add(whereCondition); } var welfareCentreId = Utits.WelfareCentreID; if (welfareCentreId != null) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "WelfareCentreID"; whereCondition.FieldValue = welfareCentreId; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } var IsValid = RequestParameters.PintNull("IsValid"); if (IsValid != null) { if (IsValid == 1) { var whereCondition = new WhereCondition(); whereCondition.FieldName = "IsValid"; whereCondition.FieldValue = IsValid; whereCondition.FieldOperator = EnumOper.Equal; WhereList.Add(whereCondition); } else { var whereCondition = new WhereCondition(); whereCondition.FieldName = "IsValid"; whereCondition.FieldValue = 1; whereCondition.FieldOperator = EnumOper.ExclamationEqual; WhereList.Add(whereCondition); } } searchCondition.WhereList = WhereList; var OrderList = new List <OrderCondition>(); var sortfield = RequestParameters.Pstring("sortfield"); if (sortfield.Length <= 0) { sortfield = "OperateDate"; } var orderCondition = new OrderCondition(); orderCondition.FiledOrder = sortfield; orderCondition.Ascending = RequestParameters.Pstring("sorttype") == "asc" ? true : false; OrderList.Add(orderCondition); searchCondition.OrderList = OrderList; #endregion var cBll = new UsersBll(); var list = cBll.SearchVByPageCondition(iPageIndex, iPageSize, ref iTotalRecord, searchCondition); iPageSize = iPageSize == 0 ? iTotalRecord : iPageSize; int pageCount = iTotalRecord % iPageSize == 0 ? iTotalRecord / iPageSize : iTotalRecord / iPageSize + 1; var sReturnModel = new ResultList(); sReturnModel.ErrorType = 1; sReturnModel.CurrentPage = iCurrentPage; sReturnModel.PageSize = iPageSize; sReturnModel.TotalRecord = iTotalRecord; sReturnModel.PageCount = pageCount; sReturnModel.Data = list; return(Json(sReturnModel, JsonRequestBehavior.AllowGet)); }