//导出报表
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));
}