一、引子

　　新进公司被安排处理系统的数据报表任务——对学生的考试成绩进行统计并能导出到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         /// 
 81         /// 这里添加数据，循环添加，主要应对由多个组成的 82         /// 
 83         ///  84         ///  85         /// 
       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
      
        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
       
         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
        
         ();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         /// 
         
277         /// 设置单元格278         /// 
279         /// 
         280         /// 
         281         /// 
         282         /// 
         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         /// 
         
303         /// 导入304         /// 
305         /// 
         
          具体对象
         306         /// 
         307         /// 
         308         /// 
         309         /// 
         310         public static IEnumerable
         
           ExcelToDataTable
          
           (Stream fs, bool isFirstRowColumn = false) where T : new()311         {312             List
           
             ts = new List
            
             ();313 314 Type type = typeof(T);315 316 IList
             
               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 }
             
            
           
          
         
        
       
      

 

六、总结

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

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

　　项目地址：