/// <summary> /// 导出EXCEL /// </summary> /// <typeparam name="T"></typeparam> /// <param name="Data">需要被导出的数据</param> /// <param name="Types">Excel类型</param> /// <param name="SheetName">工作表名称</param> /// <param name="Footer">页脚内容</param> /// <param name="stream">流</param> /// <param name="action">自定义导出</param> /// <param name="DateFormat">事件格式</param> internal static void ExportExcel <T>(IEnumerable <T> Data, ExcelType Types, string SheetName, dynamic Footer = null, Action <Stream> action = null, Stream stream = null, string DateFormat = "yyyy-MM-dd") where T : class, new() { int Rows = Data.Count();//数据行 var PropNames = Data.WithNames(); var Cols = PropNames.Count; List <string> NotIngoreNames = new List <string>(); //获取忽略字段 Data.FirstOrDefault().GetType().GetProperties().ForArrayEach <PropertyInfo>(item => { var Ingore = Data.FirstOrDefault().ToAttr <T, OfficeAttribute>(item.Name, true)?.IngoreField; if (Ingore != null && Ingore.Value == true) { Cols -= 1; } else { NotIngoreNames.Add(item.Name); } }); if (Cols == 0) { return; } Excel excel = new Excel(Types, DateFormat); excel.CreateExportWorkBook().CreateExportSheet(SheetName); #region 创建头 excel.CreateExportRows(0); for (int Col = 0; Col < Cols; Col++) { var First = Data.FirstOrDefault(); var Index = NotIngoreNames[Col]; var Name = First.ToAttr <T, OfficeAttribute>(Index, true)?.MapperField; if (Name.IsNullOrEmpty()) { throw new NullReferenceException("实体未打上OfficeAttribute特性"); } excel.CreateExportCells(Col, Name); } excel.HeadExportStyle(Cols - 1); #endregion #region 创建内容 for (int Row = 1; Row <= Rows; Row++) { excel.CreateExportRows(Row); for (int Col = 0; Col < Cols; Col++) { var First = Data.ToArray()[Row - 1]; var Index = NotIngoreNames[Col]; var Entity = First.GetType().GetProperty(Index); var data = Entity.GetValue(First); CreateDropDwonData(excel, Types, First.GetType().GetProperty(Index), Col, Col); //枚举映射 var WasEnum = (Entity.GetCustomAttribute(typeof(OfficeAttribute)) as OfficeAttribute).Enum; if (WasEnum) { var Attr = Entity.PropertyType.GetField(data.ToString()).GetCustomAttribute(typeof(DescriptionAttribute)); if (Attr == null) { throw new NullReferenceException("导出枚举对象时没有设置对应的DescriptionAttribute"); } var result = (Attr as DescriptionAttribute).Description; excel.CreateExportCells(Col, result).BodyExportStyle(Row, Cols - 1); } //Bool映射 else if (Entity.PropertyType == typeof(bool)) { var result = ((DescriptionAttribute)(Entity.GetCustomAttribute(typeof(OfficeAttribute), false) as OfficeAttribute).BoolEnum .GetField(data.ToString().ToUpper()).GetCustomAttribute(typeof(DescriptionAttribute), false)).Description; excel.CreateExportCells(Col, result).BodyExportStyle(Row, Cols - 1); } else { excel.CreateExportCells(Col, data).BodyExportStyle(Row, Cols - 1); } } } #endregion #region 创建页脚 if (Footer != null) { excel.CreateExportRows(Rows + 1).CreateExportCells(0, Footer); var LastCol = Cols - 1; var LastRow = Rows + 1; if (LastCol != 0) { excel.MergeExportCell(Rows + 1, Rows + 1, 0, LastCol).FootExportStyle(Rows + 1, LastCol); } else { excel.FootExportStyle(Rows + 1, LastCol); } } #endregion excel.WriteExportStream(stream ?? new MemoryStream(), action); }
/// <summary> /// 导入EXCEL /// </summary> /// <typeparam name="T"></typeparam> /// <param name="fs">流</param> /// <param name="Types">类型</param> /// <param name="HasPageFooter">文档是否有页脚</param> /// <param name="SheetIndex">数据表索引</param> /// <returns></returns> internal static List <T> ImportExcel <T>(Stream fs, ExcelType Types, bool HasPageFooter = false, int SheetIndex = 0) where T : new() { Excel excel = new Excel(fs, Types, HasPageFooter); return(excel.CreateImportWorkBook().CreateImportSheet(SheetIndex).CreateImportHead <T>().CreateImportBody <T>()); }