一个基于NPOI的excel导入导出组件(强类型)

jopen 5年前

一、引子

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

来自: http://www.cnblogs.com/sxfy/p/5137712.html