Excel 数据导出
Web controller
1 /// <summary> 2 /// 导出数据 3 /// </summary> 4 /// <param name="UserID"></param> 5 /// <param name="UserName"></param> 6 /// <param name="UserStatus"></param> 7 /// <param name="Gender"></param> 8 /// <param name="Mobile"></param> 9 /// <param name="bAddTime"></param> 10 /// <param name="eAddTime"></param> 11 /// <param name="bLastLoginTime"></param> 12 /// <param name="eLastLoginTime"></param> 13 public void CreateExcel(string UserID, string UserName, string UserStatus, string Gender, string Mobile, string bAddTime, string eAddTime, string bLastLoginTime, string eLastLoginTime) 14 { 15 16 SearchItem.UserSearchItem item = new SearchItem.UserSearchItem(); 17 if (UserName != null && !UserName.Equals("")) 18 item.UserName = UserName; 19 if (UserID != null && !UserID.Equals("") && UserID != "undefined") 20 item.UserID = int.Parse(UserID); 21 //******* 22 if (UserStatus != null && !UserStatus.Equals("") && !UserStatus.Equals("null")) 23 item.UserStatus = int.Parse(UserStatus); 24 if (Gender != null && !Gender.Equals("") && !Gender.Equals("null")) 25 item.Gender = int.Parse(Gender); 26 if (Mobile != null && !Mobile.Equals("")) 27 item.Mobile = Mobile; 28 //************ 29 if (bAddTime != "null" && bAddTime != null && !bAddTime.Equals("") && bAddTime != "undefined") 30 item.bAddTime = Convert.ToDateTime(bAddTime); 31 if (eAddTime != null && !eAddTime.Equals("") && eAddTime != "undefined") 32 item.eAddTime = Convert.ToDateTime(eAddTime); 33 if (bLastLoginTime != null && !bLastLoginTime.Equals("") && bLastLoginTime != "undefined") 34 item.bLastLoginTime = Convert.ToDateTime(bLastLoginTime); 35 if (eLastLoginTime != null && !eLastLoginTime.Equals("") && eLastLoginTime != "undefined") 36 item.eLastLoginTime = Convert.ToDateTime(eLastLoginTime); 37 //if (Status != null && !Status.Equals("") && Status != "null") 38 // item.Status = Convert.ToInt32(Status); 39 40 Dictionary<string, string> columnInfo = new Dictionary<string, string>(); 41 columnInfo.Add("UserID", "编号"); 42 columnInfo.Add("UserName", "用户名"); 43 columnInfo.Add("TrueName", "真实姓名"); 44 columnInfo.Add("Gender", "性别"); 45 columnInfo.Add("Email", "邮箱"); 46 columnInfo.Add("Mobile", "手机"); 47 columnInfo.Add("Area", "地域"); 48 columnInfo.Add("UserStatus", "状态"); 49 columnInfo.Add("AddTime", "注册时间"); 50 columnInfo.Add("RegisterIP", "注册IP"); 51 columnInfo.Add("LastLoginTime", "最后登录时间"); 52 columnInfo.Add("LastLoginIP", "最后登录IP"); 53 54 var objList = new UserInfoDAL().GetListExcel(item); 55 var time = DateTime.Now; 56 var strtime = time.ToString("yyyyMMddHHmm"); 57 string FileName = "用户列表导出" + strtime + ".xls"; 58 //生成EXCEL的HTML 59 string excelStr = ""; 60 Type myType = objList[0].GetType(); 61 //根据反射从传递进来的属性名信息得到要显示的属性 62 List<System.Reflection.PropertyInfo> myPro = new List<System.Reflection.PropertyInfo>(); 63 foreach (string cName in columnInfo.Keys) 64 { 65 System.Reflection.PropertyInfo p = myType.GetProperty(cName); 66 if (p != null) 67 { 68 myPro.Add(p); 69 excelStr += columnInfo[cName] + "\t"; 70 } 71 } 72 //如果没有找到可用的属性则结束 73 //if (myPro.Count == 0) { return; } 74 excelStr += "\n"; 75 foreach (UserInfo obj in objList) 76 { 77 foreach (System.Reflection.PropertyInfo p in myPro) 78 { 79 var d = p.GetValue(obj, null); 80 if (p.Name == "Gender" && p.GetValue(obj, null) != null) 81 { 82 var status = p.GetValue(obj, null).ToString(); 83 string sta = ""; 84 if (status == "0") 85 sta = "女"; 86 else if (status == "1") 87 sta = "男"; 88 else 89 sta = "未定义"; 90 excelStr += sta + "\t"; 91 } 92 else if (p.Name == "UserStatus" && p.GetValue(obj, null) != null) 93 { 94 var status = p.GetValue(obj, null).ToString(); 95 string sta = ""; 96 if (status == "1") 97 sta = "正常"; 98 else if (status == "2") 99 sta = "禁止"; 100 else 101 sta = "未定义"; 102 excelStr += sta + "\t"; 103 } 104 else 105 excelStr += p.GetValue(obj, null) + "\t"; 106 } 107 excelStr += "\n"; 108 } 109 //输出EXCEL 110 HttpResponse rs = System.Web.HttpContext.Current.Response; 111 rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 112 rs.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); 113 rs.ContentType = "application/ms-excel"; 114 rs.Write(excelStr); 115 rs.End(); 116 117 }
UserInfoDAL()
1 public List<UserInfo> GetListExcel(SearchItem.UserSearchItem item) 2 { 3 /* 4 var order = extitem.Sort + " " + extitem.Dir; 5 6 7 var search = item.UserStatus < 0 ? QueryBuilder.Create<UserInfo>() 8 .Like(c => c.UserName, item.UserName) 9 .Like(c => c.Mobile, item.Mobile) 10 .Equals(c => c.UserID, item.UserID) 11 .Equals(c => c.Gender, item.Gender) 12 .Between(c => c.AddTime, item.bAddTime.HasValue ? item.bAddTime.Value : DateTime.MinValue.AddYears(1990), item.eAddTime.HasValue ? item.eAddTime.Value.AddDays(1) : DateTime.MaxValue) 13 .Between(c => c.LastLoginTime, item.bLastLoginTime.HasValue ? item.bLastLoginTime.Value : DateTime.MinValue.AddYears(1990), item.eLastLoginTime.HasValue ? item.eLastLoginTime.Value.AddDays(1) : DateTime.MaxValue) 14 .LessThan(c => c.UserStatus, 0) : 15 QueryBuilder.Create<UserInfo>() 16 .Like(c => c.UserName, item.UserName) 17 .Like(c => c.Mobile, item.Mobile) 18 .Equals(c => c.Gender, item.Gender) 19 .Equals(c => c.UserStatus, item.UserStatus) 20 .Between(c => c.AddTime, item.bAddTime.HasValue ? item.bAddTime.Value : DateTime.MinValue.AddYears(1990), item.eAddTime.HasValue ? item.eAddTime.Value.AddDays(1) : DateTime.MaxValue) 21 .Between(c => c.LastLoginTime, item.bLastLoginTime.HasValue ? item.bLastLoginTime.Value : DateTime.MinValue.AddYears(1990), item.eLastLoginTime.HasValue ? item.eLastLoginTime.Value.AddDays(1) : DateTime.MaxValue) 22 .Equals(c => c.UserID, item.UserID); 23 */ 24 25 26 27 var search = item.UserStatus < 0 ? QueryBuilder.Create<UserInfo>() 28 .Like(c => c.UserName, item.UserName) 29 .Like(c => c.Mobile, item.Mobile) 30 .Equals(c => c.UserID, item.UserID) 31 .Equals(c => c.Gender, item.Gender) 32 .Between(c => c.AddTime, item.bAddTime.HasValue ? item.bAddTime.Value : DateTime.MinValue.AddYears(1990), item.eAddTime.HasValue ? item.eAddTime.Value.AddDays(1) : DateTime.MaxValue) 33 .Between(c => c.LastLoginTime, item.bLastLoginTime.HasValue ? item.bLastLoginTime.Value : DateTime.MinValue.AddYears(1990), item.eLastLoginTime.HasValue ? item.eLastLoginTime.Value.AddDays(1) : DateTime.MaxValue) 34 .LessThan(c => c.UserStatus, 0) : 35 QueryBuilder.Create<UserInfo>() 36 .Like(c => c.UserName, item.UserName) 37 .Like(c => c.Mobile, item.Mobile) 38 .Equals(c => c.Gender, item.Gender) 39 .Equals(c => c.UserStatus, item.UserStatus) 40 .Between(c => c.AddTime, item.bAddTime.HasValue ? item.bAddTime.Value : DateTime.MinValue.AddYears(1990), item.eAddTime.HasValue ? item.eAddTime.Value.AddDays(1) : DateTime.MaxValue) 41 .Between(c => c.LastLoginTime, item.bLastLoginTime.HasValue ? item.bLastLoginTime.Value : DateTime.MinValue.AddYears(1990), item.eLastLoginTime.HasValue ? item.eLastLoginTime.Value.AddDays(1) : DateTime.MaxValue) 42 .Equals(c => c.UserID, item.UserID); 43 //int OrderStatus = -4; 44 //if (!int.TryParse(osi.OrderStatus, out OrderStatus)) 45 //{ 46 // search.In(c => c.OrderStatus, OrderStatus); 47 //} 48 var list = (from o in Udc.tb_UserInfo 49 select new UserInfo 50 { 51 //OrderID = t.OrderID, 52 //OrderTime = t.OrderTime, 53 //UserName = Udc.tb_UserInfo.SingleOrDefault(r => r.UserID.Equals(t.UserID)).UserName, 54 UserID = o.UserID, 55 UserName = o.UserName, 56 Email = o.Email, 57 Gender = o.Gender, 58 Mobile = o.Mobile, 59 Provice = o.Provice, 60 City = o.City, 61 Prefecture = o.Prefecture, 62 Area = o.Provice + o.City, 63 UserImage = o.UserImage, 64 LearnIndex = o.LearnIndex, 65 TeachIndex = o.TeachIndex, 66 GoodComments = o.GoodComments, 67 UserStatus = o.UserStatus, 68 TrueName = o.TrueName, 69 IdentyNum = o.IdentyNum, 70 Introduce = o.Introduce, 71 ActiveInfo = o.ActiveInfo, 72 BuyGoodComments = o.BuyGoodComments, 73 Attention = o.Attention, 74 Extend = o.Extend,//************** 75 Img = o.Img, 76 ReferrerID = o.ReferrerID,//************ 77 AddTime = o.AddTime, 78 StrGrade = o.Grade, 79 U_School = o.School, 80 PassWord = o.Password, 81 Birthday = o.birthday, 82 RegisterIP = o.RegisterIP, 83 LastLoginIP = o.LastLoginIP, 84 LastLoginTime = o.LastLoginTime 85 86 }).Where(search.Expression).ToList();//.ToPagedList(esi.Start / esi.Limit + 1, esi.Limit); 87 return list; 88 }
