你的位置:首页 > ASP.net教程

[ASP.net教程]分享:一个基于NPOI的excel导入导出组件(强类型)


一、引子

  新进公司被安排处理系统的数据报表任务——对学生的考试成绩进行统计并能导出到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 }

View Code

 

六、总结

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

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

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