分享：一个基于NPOI的excel导入导出组件（强类型）

2019-11-14 13:32:45

字体：大中小

来源：转载

供稿：网友

一、引子

　　新进公司被安排处理系统的数据报表任务——对学生的考试成绩进行统计并能导出到Excel。虽然以前也有弄过，但感觉不是很好，所以这次狠下心，多花点时间作个让自己满意的插件。

二、适用领域

　　因为需求是基于学生成绩，可能更多的是按这样的需求去考虑。如下图（请不要计较数据）：

三、逻辑

　　一个excel文件 --> N个工作表 --> N个数据容器-->N个数据内容

四、类的组成　

WorkbookWrapper（抽象类）	excel容器，一个实例代表一个excel文件
BuildContext（数据上下文）	在事件中获取对象的上下文
WorkbookExtensions（扩展类）	WorkbookWrapper的扩展，有2个方法，一个保存到本地，一个是http下载
XSSFWorkbookBuilder（Excel2007）	继承WorkbookWrapper提供2007的版本的实现类
HSSFWorkbookBuilder（Excel2003）	同上，版本为2003
ExcelModelsPRopertyManage	对生成的的数据结构的管理类
ISheetDetail（工作表接口）	每一个ISheetDetail都代表一张工作表（包含一个SheetDataCollection）
ISheetDataWrapper（内容容器接口）	每一个ISheetDataWrapper都代表ISheetDetail里的一块内容
SheetDataCollection（数据集合）	内容容器的集合
IExcelModelBase（内容模型的基类接口）	ISheetDataWrapper里的内容数据模型均继承此接口（包含一个IExtendedBase集合）
IExtendedBase（扩展内容接口）	如上图中的科目1-科目3属于不确定数量的内容均继承此接口
IgnoreAttribute（忽略标记）	不想输出到excel的打上此标记即可
CellExtensions（列的扩展）	格式化列的样式
EnumService（枚举服务类）	输出枚举对象里的DescriptionAttribute特性的值

　　注：标题是依据模型属性的 DisplayName 特性标记来实现的。

五、主要实现类

  1 using NPOI.HSSF.UserModel;  2 using NPOI.SS.UserModel;  3 using System;  4 using System.Collections.Generic;  5 using System.IO;  6 using System.Linq;  7 using System.Reflection;  8 using System.ComponentModel;  9 using System.Collections; 10  11  12 namespace ExcelHelper.Operating 13 { 14     public abstract class WorkbookBuilder 15     { 16         protected WorkbookBuilder() 17         { 18             currentWorkbook = CreateWorkbook(); 19  20             buildContext = new BuildContext() { WorkbookBuilder = this, Workbook = currentWorkbook }; 21         } 22  23         public delegate void BuildEventHandler(BuildContext context); 24  25         protected abstract IWorkbook CreateWorkbook(); 26  27         public IWorkbook currentWorkbook; 28  29         private ICellStyle _centerStyle; 30  31         public ICellStyle CenterStyle 32         { 33             get 34             { 35                 if (_centerStyle == null) 36                 { 37                     _centerStyle = currentWorkbook.CreateCellStyle(); 38  39                     _centerStyle.Alignment = HorizontalAlignment.Center; 40  41                     _centerStyle.VerticalAlignment = VerticalAlignment.Center; 42                 } 43  44                 return _centerStyle; 45             } 46         } 47  48         private Int32 StartRow = 0;//起始行 49  50  51         private BuildContext buildContext; 52   53         public event BuildEventHandler OnHeadCellSetAfter; 54   55         public event BuildEventHandler OnContentCellSetAfter; 56  57  58         #region DataTableToExcel 59  60         public void Insert(ISheetDetail sheetDetail) 61         { 62             ISheet sheet; 63  64             if (sheetDetail.IsContinue) 65             { 66                 sheet = currentWorkbook.GetSheetAt(currentWorkbook.NumberOfSheets - 1); 67  68                 StartRow = sheet.LastRowNum + 1; 69             } 70             else 71             { 72                 sheet = currentWorkbook.CreateSheet(sheetDetail.SheetName); 73             } 74  75             buildContext.Sheet = sheet; 76  77             sheet = DataToSheet(sheetDetail.SheetDetailDataWrappers, sheet); 78  79         } 80         /// <summary> 81         /// 这里添加数据，循环添加，主要应对由多个组成的 82         /// </summary> 83         /// <param name="sheetDetailDataWrappers"></param> 84         /// <param name="sheet"></param> 85         /// <returns></returns> 86         private ISheet DataToSheet(SheetDataCollection sheetDetailDataWrappers, ISheet sheet) 87         { 88             foreach (var sheetDetailDataWrapper in sheetDetailDataWrappers) 89             { 90                 if (sheetDetailDataWrapper.Datas == null || sheetDetailDataWrapper.Datas.Count() == 0) 91                 { 92                     continue; 93                 } 94  95                 Type type = sheetDetailDataWrapper.Datas.GetType().GetGenericArguments()[0]; 96  97                 if (sheetDetailDataWrapper.HaveTitle) 98                 { 99                     sheet = SetTitle(sheet, sheetDetailDataWrapper, type);100                 }101 102                 sheet = AddValue(sheet, sheetDetailDataWrapper, type);103 104                 StartRow = StartRow + sheetDetailDataWrapper.EmptyIntervalRow;105             }106 107             return sheet;108         }109 110         #endregion111 112         #region 设置值113 114         private void SetCellValue(ICell cell, object obj)115         {116             if (obj == null)117             {118                 cell.SetCellValue(" "); return;119             }120   121             if (obj is String)122             {123                 cell.SetCellValue(obj.ToString()); return;124             }125 126             if (obj is Int32 || obj is Double)127             {128                 cell.SetCellValue(Math.Round(Double.Parse(obj.ToString()), 2)); return;129             }130 131             if (obj.GetType().IsEnum)132             {133                 cell.SetCellValue(EnumService.GetDescription((Enum)obj)); return;134             }135 136             if (obj is DateTime)137             {138                 cell.SetCellValue(((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss")); return;139             }140 141             if (obj is Boolean)142             {143                 cell.SetCellValue((Boolean)obj ? "√" : "×"); return;144             }     145         }146 147         #endregion148 149         #region SetTitle150         private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)151         {152             IRow titleRow = null;153 154             ICell titleCell = null;155 156             if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))157             {158                 titleRow = sheet.CreateRow(StartRow);159 160                 buildContext.Row = titleRow;161  162                 StartRow++;163 164                 titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);165 166                 if (OnHeadCellSetAfter != null)167                 {168                     OnHeadCellSetAfter(buildContext);169                 }170             }171 172             IRow row = sheet.CreateRow(StartRow);173 174             buildContext.Row = row;175 176             IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);177 178             int i = 0;179 180             foreach (PropertyInfo property in checkPropertyInfos)181             {182                 DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;183 184                 if (dn != null)185                 {186                     SetCell(row, i++, dn.DisplayName);187                     continue;188                 }189 190                 Type t = property.PropertyType;191 192                 if (t.IsGenericType)193                 {194                     if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)195                     {196                         continue;197                     }198 199                     foreach (var item in sheetDetailDataWrapper.Titles)200                     {201                         SetCell(row, i++, item.TypeName);202                     }203                 }204             }205         206             if (titleCell != null && i > 0)207             {208                 titleCell.MergeTo(titleRow.CreateCell(i - 1));209 210                 titleCell.CellStyle = this.CenterStyle;211             }212 213             StartRow++;214 215             return sheet;216         }217         #endregion218 219         #region AddValue220         private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)221         {222             IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);223 224             Int32 cellCount = 0;225 226             foreach (var item in sheetDetailDataWrapper.Datas)227             {228                 if (item == null)229                 {230                     StartRow++;231                     continue;232                 }233 234                 IRow newRow = sheet.CreateRow(StartRow);235 236                 buildContext.Row = newRow;237 238                 foreach (PropertyInfo property in checkPropertyInfos)239                 {240                     Object obj = property.GetValue(item, null);241 242                     Type t = property.PropertyType;243 244                     if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>))245                     {246                         var ssd = ((IEnumerable)obj).Cast<IExtendedBase>();247 248                         if (ssd == null)249                         {250                             continue;251                         }252 253                         foreach (var v in sheetDetailDataWrapper.Titles)254                         {255                             IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();256 257                             SetCell(newRow, cellCount++, sv.TypeValue);258                         }259 260                         continue;261                     }262  263                     SetCell(newRow, cellCount++, obj);264                 }265 266                 StartRow++;267                 cellCount = 0;268             }269 270             return sheet;271         }272 273         #endregion274 275         #region 设置单元格276         /// <summary>277         /// 设置单元格278         /// </summary>279         /// <param name="row"></param>280         /// <param name="index"></param>281         /// <param name="value"></param>282         /// <returns></returns>283         private ICell SetCell(IRow row, int index, object value)284         {285             ICell cell = row.CreateCell(index);286 287             SetCellValue(cell, value);288 289             buildContext.Cell = cell;290 291             if (OnContentCellSetAfter != null)292             {293                 OnContentCellSetAfter(buildContext);294             }295 296             return cell;297         } 298         #endregion299 300         #region ExcelToDataTable301 302         /// <summary>303         /// 导入304         /// </summary>305         /// <typeparam name="T">具体对象</typeparam>306         /// <param name="fs"></param>307         /// <param name="fileName"></param>308         /// <param name="isFirstRowColumn"></param>309         /// <returns></returns>310         public static IEnumerable<T> ExcelToDataTable<T>(Stream fs, bool isFirstRowColumn = false) where T : new()311         {312             List<T> ts = new List<T>();313 314             Type type = typeof(T);315 316             IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);317 318             try319             {320                 IWorkbook workbook = WorkbookFactory.Create(fs);321 322                 fs.Dispose();323 324                 ISheet sheet = workbook.GetSheetAt(0);325 326                 if (sheet != null)327                 {328                     IRow firstRow = sheet.GetRow(0);329 330                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数331 332                     Int32 startRow = isFirstRowColumn ? 1 : 0;333 334                     int rowCount = sheet.LastRowNum; //行数335 336                     int length = checkPropertyInfos.Count;337 338                     length = length > cellCount + 1 ? cellCount + 1 : length;339 340                     Boolean haveValue = false;341 342                     for (int i = startRow; i <= rowCount; ++i)343                     {344                         IRow row = sheet.GetRow(i);345 346                         if (row == null) continue; //没有数据的行默认是null　　　　　　　347 348                         T t = new T();349 350                         for (int f = 0; f < length; f++)351                         {352                             ICell cell = row.GetCell(f);353 354                             if (cell == null || String.IsNullOrEmpty(cell.ToString()))355                             {356                                 continue;357                             }358 359                             object b = cell.ToString();360 361                             if (cell.CellType == CellType.Numeric)362                             {363                                 //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型364                                 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型365                                 {366                                     b = cell.DateCellValue;367                                 }368                                 else369                                 {370                                     b = cell.NumericCellValue;371                                 }372                             }373 374                             PropertyInfo pinfo = checkPropertyInfos[f];375 376                             if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候，强转377                             {378                                 b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());379                             }380 381                             type.GetProperty(pinfo.Name).SetValue(t, b, null);382 383                             if (!haveValue)384                             {385                                 haveValue = true;386                             }387                         }388                         if (haveValue)389                         {390                             ts.Add(t); haveValue = false;391                         }392                     }393                 }394 395                 return ts;396             }397             catch (Exception ex)398             {399                 return null;400             }401         }402 403         #endregion404     }405 406     public class BuildContext407     {408         public WorkbookBuilder WorkbookBuilder { get; set; }409         410         public IWorkbook Workbook { get; set; }411 412         public ISheet Sheet { get; set; }413 414         public IRow Row { get; set; }415 416         public ICell Cell { get; set; }417 418     }419 }