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

一、引子

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

二、适用领域

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

[flash=500,400]http://s1.knowsky.com/20160211/u3n2mn4m0q216.png[/flash]

三、逻辑

一个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 特性标记来实现的。

五、主要实现类

[flash=500,400]http://s1.knowsky.com/20160211/xglcqiflydm16.gif[/flash][flash=500,400]http://s1.knowsky.com/20160211/n1bfcz1cesw16.gif[/flash]1usingNPOI.HSSF.UserModel;2usingNPOI.SS.UserModel;3usingSystem;4usingSystem.Collections.Generic;5usingSystem.IO;6usingSystem.Linq;7usingSystem.Reflection;8usingSystem.ComponentModel;9usingSystem.Collections;101112namespaceExcelHelper.Operating13{14publicabstractclassWorkbookBuilder15{16protectedWorkbookBuilder()17{18currentWorkbook =CreateWorkbook();1920buildContext =newBuildContext() { WorkbookBuilder =this, Workbook =currentWorkbook };21}2223publicdelegatevoidBuildEventHandler(BuildContext context);2425protectedabstractIWorkbook CreateWorkbook();2627publicIWorkbook currentWorkbook;2829privateICellStyle _centerStyle;3031publicICellStyle CenterStyle32{33get34{35if(_centerStyle ==null)36{37_centerStyle =currentWorkbook.CreateCellStyle();3839_centerStyle.Alignment =HorizontalAlignment.Center;4041_centerStyle.VerticalAlignment =VerticalAlignment.Center;42}4344return_centerStyle;45}46}4748privateInt32 StartRow =0;//起始行495051privateBuildContext buildContext;5253publiceventBuildEventHandler OnHeadCellSetAfter;5455publiceventBuildEventHandler OnContentCellSetAfter;565758#regionDataTableToExcel5960publicvoidInsert(ISheetDetail sheetDetail)61{62ISheet sheet;6364if(sheetDetail.IsContinue)65{66sheet = currentWorkbook.GetSheetAt(currentWorkbook.NumberOfSheets -1);6768StartRow = sheet.LastRowNum +1;69}70else71{72sheet =currentWorkbook.CreateSheet(sheetDetail.SheetName);73}7475buildContext.Sheet =sheet;7677sheet =DataToSheet(sheetDetail.SheetDetailDataWrappers, sheet);7879}80///<summary>81///这里添加数据，循环添加，主要应对由多个组成的82///</summary>83///<param name="sheetDetailDataWrappers"></param>84///<param name="sheet"></param>85///<returns></returns>86privateISheet DataToSheet(SheetDataCollection sheetDetailDataWrappers, ISheet sheet)87{88foreach(varsheetDetailDataWrapperinsheetDetailDataWrappers)89{90if(sheetDetailDataWrapper.Datas ==null|| sheetDetailDataWrapper.Datas.Count() ==0)91{92continue;93}9495Type type = sheetDetailDataWrapper.Datas.GetType().GetGenericArguments()[0];9697if(sheetDetailDataWrapper.HaveTitle)98{99sheet =SetTitle(sheet, sheetDetailDataWrapper, type);100}101102sheet =AddValue(sheet, sheetDetailDataWrapper, type);103104StartRow = StartRow +sheetDetailDataWrapper.EmptyIntervalRow;105}106107returnsheet;108}109110#endregion111112#region设置值113114privatevoidSetCellValue(ICell cell,objectobj)115{116if(obj ==null)117{118cell.SetCellValue("");return;119}120121if(objisString)122{123cell.SetCellValue(obj.ToString());return;124}125126if(objisInt32 || objisDouble)127{128cell.SetCellValue(Math.Round(Double.Parse(obj.ToString()),2));return;129}130131if(obj.GetType().IsEnum)132{133cell.SetCellValue(EnumService.GetDescription((Enum)obj));return;134}135136if(objisDateTime)137{138cell.SetCellValue(((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss"));return;139}140141if(objisBoolean)142{143cell.SetCellValue((Boolean)obj ?"√":"×");return;144}145}146147#endregion148149#regionSetTitle150privateISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)151{152IRow titleRow =null;153154ICell titleCell =null;155156if(!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))157{158titleRow =sheet.CreateRow(StartRow);159160buildContext.Row =titleRow;161162StartRow++;163164titleCell = SetCell(titleRow,0, sheetDetailDataWrapper.DataName);165166if(OnHeadCellSetAfter !=null)167{168OnHeadCellSetAfter(buildContext);169}170}171172IRow row =sheet.CreateRow(StartRow);173174buildContext.Row =row;175176IList<PropertyInfo> checkPropertyInfos =ExcelModelsPropertyManage.CreatePropertyInfos(type);177178inti =0;179180foreach(PropertyInfo propertyincheckPropertyInfos)181{182DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute),false).SingleOrDefault()asDisplayNameAttribute;183184if(dn !=null)185{186SetCell(row, i++, dn.DisplayName);187continue;188}189190Type t =property.PropertyType;191192if(t.IsGenericType)193{194if(sheetDetailDataWrapper.Titles ==null|| sheetDetailDataWrapper.Titles.Count() ==0)195{196continue;197}198199foreach(variteminsheetDetailDataWrapper.Titles)200{201SetCell(row, i++, item.TypeName);202}203}204}205206if(titleCell !=null&& i >0)207{208titleCell.MergeTo(titleRow.CreateCell(i -1));209210titleCell.CellStyle =this.CenterStyle;211}212213StartRow++;214215returnsheet;216}217#endregion218219#regionAddValue220privateISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)221{222IList<PropertyInfo> checkPropertyInfos =ExcelModelsPropertyManage.CreatePropertyInfos(type);223224Int32 cellCount =0;225226foreach(variteminsheetDetailDataWrapper.Datas)227{228if(item ==null)229{230StartRow++;231continue;232}233234IRow newRow =sheet.CreateRow(StartRow);235236buildContext.Row =newRow;237238foreach(PropertyInfo propertyincheckPropertyInfos)239{240Object obj = property.GetValue(item,null);241242Type t =property.PropertyType;243244if(t.IsGenericType && t.GetGenericTypeDefinition() ==typeof(IEnumerable<>))245{246varssd = ((IEnumerable)obj).Cast<IExtendedBase>();247248if(ssd ==null)249{250continue;251}252253foreach(varvinsheetDetailDataWrapper.Titles)254{255IExtendedBase sv = ssd.Where(s => s.TypeId ==v.TypeId).SingleOrDefault();256257SetCell(newRow, cellCount++, sv.TypeValue);258}259260continue;261}262263SetCell(newRow, cellCount++, obj);264}265266StartRow++;267cellCount =0;268}269270returnsheet;271}272273#endregion274275#region设置单元格276///<summary>277///设置单元格278///</summary>279///<param name="row"></param>280///<param name="index"></param>281///<param name="value"></param>282///<returns></returns>283privateICell SetCell(IRow row,intindex,objectvalue)284{285ICell cell =row.CreateCell(index);286287SetCellValue(cell, value);288289buildContext.Cell =cell;290291if(OnContentCellSetAfter !=null)292{293OnContentCellSetAfter(buildContext);294}295296returncell;297}298#endregion299300#regionExcelToDataTable301302///<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>310publicstaticIEnumerable<T> ExcelToDataTable<T>(Stream fs,boolisFirstRowColumn =false)whereT :new()311{312List<T> ts =newList<T>();313314Type type =typeof(T);315316IList<PropertyInfo> checkPropertyInfos =ExcelModelsPropertyManage.CreatePropertyInfos(type);317318try319{320IWorkbook workbook =WorkbookFactory.Create(fs);321322fs.Dispose();323324ISheet sheet = workbook.GetSheetAt(0);325326if(sheet !=null)327{328IRow firstRow = sheet.GetRow(0);329330intcellCount = firstRow.LastCellNum;//一行最后一个cell的编号 即总的列数331332Int32 startRow = isFirstRowColumn ?1:0;333334introwCount = sheet.LastRowNum;//行数335336intlength =checkPropertyInfos.Count;337338length = length > cellCount +1? cellCount +1: length;339340Boolean haveValue =false;341342for(inti = startRow; i <= rowCount; ++i)343{344IRow row =sheet.GetRow(i);345346if(row ==null)continue;//没有数据的行默认是null347348T t =newT();349350for(intf =0; f < length; f++)351{352ICell cell =row.GetCell(f);353354if(cell ==null||String.IsNullOrEmpty(cell.ToString()))355{356continue;357}358359objectb =cell.ToString();360361if(cell.CellType ==CellType.Numeric)362{363//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型364if(HSSFDateUtil.IsCellDateFormatted(cell))//日期类型365{366b =cell.DateCellValue;367}368else369{370b =cell.NumericCellValue;371}372}373374PropertyInfo pinfo =checkPropertyInfos[f];375376if(pinfo.PropertyType.Name != b.GetType().Name)//类型不一样的时候，强转377{378b =System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());379}380381type.GetProperty(pinfo.Name).SetValue(t, b,null);382383if(!haveValue)384{385haveValue =true;386}387}388if(haveValue)389{390ts.Add(t); haveValue =false;391}392}393}394395returnts;396}397catch(Exception ex)398{399returnnull;400}401}402403#endregion404}405406publicclassBuildContext407{408publicWorkbookBuilder WorkbookBuilder {get;set; }409410publicIWorkbook Workbook {get;set; }411412publicISheet Sheet {get;set; }413414publicIRow Row {get;set; }415416publicICell Cell {get;set; }417418}419}

View Code

六、总结

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

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

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