导出EXCEL
/// <summary> /// ModelList 转换为DataTable /// </summary> /// <typeparam name="TModel">model实体</typeparam> /// <typeparam name="TField">字段实体</typeparam> /// <typeparam name="TView">字段实体</typeparam> /// <param name="modelList">要转换的Model列表</param> /// <param name="fieldList">要转换的表头实体列表</param> /// <param name="titleName">表头中列名--TField表中列头显示名称的字段</param> /// <param name="fieldName">表头中列名--TField表中列头 名称的字段</param> /// <returns></returns> public static DataTable ConvertListToDataTable<TModel, TField>(List<TModel> modelList, List<TField> fieldList = null, string titleName = "", string fieldName = "") { #region 判空处理 if (null != fieldList && fieldList.Count > 0 && (string.IsNullOrEmpty(titleName) || string.IsNullOrWhiteSpace(titleName) || string.IsNullOrWhiteSpace(fieldName) || string.IsNullOrEmpty(fieldName))) { return null; } #endregion var table = new DataTable(); DataColumn column; DataRow row; #region 有表头数据的时候，需要处理表头数据，而且处理表数据的时候需要根据传入的表头数据来获取相关表数据 if (null != fieldList && fieldList.Count > 0) { var titlePropertyList = typeof(TField).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).ToList(); var titleProperty = titlePropertyList.Find(_ => _.Name == titleName); var fieldProperty = titlePropertyList.Find(_ => _.Name == fieldName); if (null == titleProperty || null == fieldProperty) { return null; } #region 追加表头 fieldList.ForEach(_ => { var title = titleProperty.GetValue(_, null); var fieldNameValues = fieldProperty.GetValue(_, null); column = new DataColumn(title.ToString(), titleProperty.PropertyType); table.Columns.Add(column); }); #endregion #region 表数据 int count = 0; if (null != modelList && modelList.Count > 0) { modelList.ForEach(model => { row = table.NewRow(); fieldList.ForEach(_ => { var fieldNameValues = fieldProperty.GetValue(_, null); var title = titleProperty.GetValue(_, null); row[title.ToString()] = GetFiledValue<TModel>(model, null != fieldNameValues ? fieldNameValues.ToString() : null); count++; }); table.Rows.Add(row); }); } #endregion return table; } #endregion #region 没有表头实体的时候，直接处理model列表 var propertyList = typeof(TModel).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).ToList(); modelList.ForEach(model => { if (null != model) { row = table.NewRow(); propertyList.ForEach(property => { string name = property.Name; if (table.Columns[name] == null) { column = new DataColumn(name, property.PropertyType); table.Columns.Add(column); } row[name] = property.GetValue(model, null); }); table.Rows.Add(row); } }); return table; #endregion } /// <summary> /// 根据filed名称获取数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <param name="filedName"></param> /// <param name="extraFiled"></param> /// <param name="hyevent">会议实体</param> /// <returns></returns> public static string GetFiledValue<T>(T model, string filedName) { if (ReflectionHelper.GetFields(typeof(T)).Contains(filedName)) { var propertyHead = model.GetType().GetProperty(filedName); if (filedName.Contains("CreateTime")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { long dt = (long)o; if (dt > 0) { return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt)); } return ""; } else return ""; } else if (filedName.Contains("Time")&&filedName!= "LogisticsTime") { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { long dt = (long)o; if (dt > 0) { return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt)); } return ""; } else return ""; } if (filedName.Contains("Date")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { long dt = (long)o; return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt)); } else return ""; } if (filedName.Contains("Link")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { return CommonHelper.GetTrueURL(o.ToString()); } else return ""; } if (propertyHead.PropertyType.FullName.Contains("System.DateTime")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { DateTime dt = (DateTime)o; return dt.ToString("yyyy-MM-dd HH:mm:ss"); } else return ""; } else if (propertyHead.PropertyType.FullName.Contains("System.Boolean")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { if (filedName.Equals("IsEnable")) { return (bool)o == true ? "启用" : "禁用"; } return (bool)o == true ? "是" : "否"; } else return ""; } else if (propertyHead.PropertyType.FullName.Contains("System.Collections.Generic.List")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false) { if (propertyHead.PropertyType.FullName.Contains("System.String")) { var list = (List<String>)o; return string.Join(",", list); } else if (propertyHead.PropertyType.FullName.Contains("System.Int64")) { var list = (List<long>)o; return string.Join(",", list); } else if (propertyHead.PropertyType.FullName.Contains("Int32")) { var list = (List<int>)o; return string.Join(",", list); } else { return string.Join(",", o); } } else return ""; } else if (propertyHead.PropertyType.FullName.Contains("Enum")) { object o = propertyHead.GetValue(model); if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false && !o.ToString().Equals("0")) { var enumvalue = (System.Enum)o; return CommonHelper.GetDescription(enumvalue); } else { if (filedName.Equals("CompleteStatus")) { return "未完成"; } return ""; } } else return Convert.ToString(propertyHead.GetValue(model)); } return ""; }
var dt = ExportHelper.ConvertListToDataTable(serviceList, fieldList, "DisplayName", "FieldName");
json = JsonConvert.SerializeObject(dt);
var data = JsonConvert.DeserializeObject<List<JObject>>(json);
var excel = BuildExcel(data);
///导出设置
public byte[] BuildExcel(List<JObject> data) { using (ExcelPackage package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("sheet1"); var colums = data[0].Properties().ToList(); for (int j = 1; j <= colums.Count; j++) { worksheet.Cells[1, j].Value = colums[j - 1].Name; } string cellName = string.Empty; for (int i = 1; i <= data.Count; i++) { var row = data[i - 1]; for (int j = 1; j <= colums.Count; j++) { cellName = row[colums[1].Name].ToString(); worksheet.Cells[i + 1, j].Value = row[colums[j - 1].Name].ToString(); } } var num = data.Count+2; worksheet.Cells[num, 1].Style.ShrinkToFit = false; int total = 0; if (!string.IsNullOrWhiteSpace(cellName)) { total = (num > 2 ? num - 2 : 0); } worksheet.Cells[num,1].Value = "总计 "+ total + " 条记录"; return package.GetAsByteArray(); } }
