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

一、引子

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

二、适用领域

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

三、逻辑

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

四、类的组成

注：标题是依据模型属性的 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         #endregion 111  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         #endregion 148  149         #region SetTitle 150         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         #endregion 218  219         #region AddValue 220         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         #endregion 274  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         #endregion 299  300         #region ExcelToDataTable 301  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             try 319             { 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                                 else 369                                 { 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         #endregion 404     } 405  406     public class BuildContext 407     { 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 }  View Code

六、总结

看似简单的逻辑在具体实施还是会碰到的许多问题，尤其是NPOI的数据类型与想要的类型的不符的处理；通用的实现等等，不过幸运的是最后还是出一个满意的版本，这应该算自己第一个面向接口的编程的例子了。

如果你发现什么问题或者有更好的实现方式麻烦留言或者与我联系！

项目地址： https://github.com/aa317016589/ExcelHelper/