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

心已赠人 2021-06-26 16:06 409阅读 0赞

## 一、引子 ##

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

## 二、适用领域 ##

因为需求是基于学生成绩，可能更多的是按这样的需求去考虑。如下图（请不要计较数据）：  
![在这里插入图片描述][20181106133614632.png]

## 三、逻辑 ##

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

## 四、类的组成 ##

![在这里插入图片描述][20181106133649247.png]

<table> 
 <thead> 
  <tr> 
   <th align="left">类名</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">WorkbookWrapper（抽象类）</td> 
   <td align="left">excel容器，一个实例代表一个excel文件</td> 
  </tr> 
  <tr> 
   <td align="left">BuildContext（数据上下文）</td> 
   <td align="left">在事件中获取对象的上下文</td> 
  </tr> 
  <tr> 
   <td align="left">WorkbookExtensions（扩展类）</td> 
   <td align="left">WorkbookWrapper的扩展，有2个方法，一个保存到本地，一个是http下载</td> 
  </tr> 
  <tr> 
   <td align="left">XSSFWorkbookBuilder（Excel2007）</td> 
   <td align="left">继承WorkbookWrapper提供2007的版本的实现类</td> 
  </tr> 
  <tr> 
   <td align="left">HSSFWorkbookBuilder（Excel2003）</td> 
   <td align="left">同上，版本为2003</td> 
  </tr> 
  <tr> 
   <td align="left">ExcelModelsPropertyManage</td> 
   <td align="left">对生成的的数据结构的管理类</td> 
  </tr> 
  <tr> 
   <td align="left">ISheetDetail（工作表接口）</td> 
   <td align="left">每一个ISheetDetail都代表一张工作表（包含一个SheetDataCollection）</td> 
  </tr> 
  <tr> 
   <td align="left">ISheetDataWrapper（内容容器接口）</td> 
   <td align="left">每一个ISheetDataWrapper都代表ISheetDetail里的一块内容</td> 
  </tr> 
  <tr> 
   <td align="left">SheetDataCollection（数据集合）</td> 
   <td align="left">内容容器的集合</td> 
  </tr> 
  <tr> 
   <td align="left">IExcelModelBase（内容模型的基类接口）</td> 
   <td align="left">ISheetDataWrapper里的内容数据模型均继承此接口（包含一个IExtendedBase集合）</td> 
  </tr> 
  <tr> 
   <td align="left">IExtendedBase（扩展内容接口）</td> 
   <td align="left">如上图中的科目1-科目3属于不确定数量的内容均继承此接口</td> 
  </tr> 
  <tr> 
   <td align="left">IgnoreAttribute（忽略标记）</td> 
   <td align="left">不想输出到excel的打上此标记即可</td> 
  </tr> 
  <tr> 
   <td align="left">CellExtensions（列的扩展）</td> 
   <td align="left">格式化列的样式</td> 
  </tr> 
  <tr> 
   <td align="left">EnumService（枚举服务类）</td> 
   <td align="left">输出枚举对象里的DescriptionAttribute特性的值</td> 
  </tr> 
 </tbody> 
</table>

**注：标题是依据模型属性的 `DisplayName` 特性标记来实现的。**

## 五、主要实现类 ##

WorkbookBuilder.cs

using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using System.ComponentModel;
    using System.Collections;
    
    
    namespace ExcelHelper.Operating
    { 
        public abstract class WorkbookBuilder
        { 
            protected WorkbookBuilder()
            { 
                currentWorkbook = CreateWorkbook();
    
                buildContext = new BuildContext() {  WorkbookBuilder = this, Workbook = currentWorkbook };
            }
    
            public delegate void BuildEventHandler(BuildContext context);
    
            protected abstract IWorkbook CreateWorkbook();
    
            public IWorkbook currentWorkbook;
    
            private ICellStyle _centerStyle;
    
            public ICellStyle CenterStyle
            { 
                get
                { 
                    if (_centerStyle == null)
                    { 
                        _centerStyle = currentWorkbook.CreateCellStyle();
    
                        _centerStyle.Alignment = HorizontalAlignment.Center;
    
                        _centerStyle.VerticalAlignment = VerticalAlignment.Center;
                    }
    
                    return _centerStyle;
                }
            }
    
            private Int32 StartRow = 0;//起始行
    
    
            private BuildContext buildContext;
     
            public event BuildEventHandler OnHeadCellSetAfter;
     
            public event BuildEventHandler OnContentCellSetAfter;
    
    
            #region DataTableToExcel
    
            public void Insert(ISheetDetail sheetDetail)
            { 
                ISheet sheet;
    
                if (sheetDetail.IsContinue)
                { 
                    sheet = currentWorkbook.GetSheetAt(currentWorkbook.NumberOfSheets - 1);
    
                    StartRow = sheet.LastRowNum + 1;
                }
                else
                { 
                    sheet = currentWorkbook.CreateSheet(sheetDetail.SheetName);
                }
    
                buildContext.Sheet = sheet;
    
                sheet = DataToSheet(sheetDetail.SheetDetailDataWrappers, sheet);
    
            }
            /// <summary>
            /// 这里添加数据，循环添加，主要应对由多个组成的
            /// </summary>
            /// <param name="sheetDetailDataWrappers"></param>
            /// <param name="sheet"></param>
            /// <returns></returns>
            private ISheet DataToSheet(SheetDataCollection sheetDetailDataWrappers, ISheet sheet)
            { 
                foreach (var sheetDetailDataWrapper in sheetDetailDataWrappers)
                { 
                    if (sheetDetailDataWrapper.Datas == null || sheetDetailDataWrapper.Datas.Count() == 0)
                    { 
                        continue;
                    }
    
                    Type type = sheetDetailDataWrapper.Datas.GetType().GetGenericArguments()[0];
    
                    if (sheetDetailDataWrapper.HaveTitle)
                    { 
                        sheet = SetTitle(sheet, sheetDetailDataWrapper, type);
                    }
    
                    sheet = AddValue(sheet, sheetDetailDataWrapper, type);
    
                    StartRow = StartRow + sheetDetailDataWrapper.EmptyIntervalRow;
                }
    
                return sheet;
            }
    
            #endregion
    
            #region 设置值
    
            private void SetCellValue(ICell cell, object obj)
            { 
                if (obj == null)
                { 
                    cell.SetCellValue(" "); return;
                }
      
                if (obj is String)
                { 
                    cell.SetCellValue(obj.ToString()); return;
                }
    
                if (obj is Int32 || obj is Double)
                { 
                    cell.SetCellValue(Math.Round(Double.Parse(obj.ToString()), 2)); return;
                }
    
                if (obj.GetType().IsEnum)
                { 
                    cell.SetCellValue(EnumService.GetDescription((Enum)obj)); return;
                }
    
                if (obj is DateTime)
                { 
                    cell.SetCellValue(((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss")); return;
                }
    
                if (obj is Boolean)
                { 
                    cell.SetCellValue((Boolean)obj ? "√" : "×"); return;
                }     
            }
    
            #endregion
    
            #region SetTitle
            private ISheet SetTitle(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
            { 
                IRow titleRow = null;
    
                ICell titleCell = null;
    
                if (!String.IsNullOrEmpty(sheetDetailDataWrapper.DataName))
                { 
                    titleRow = sheet.CreateRow(StartRow);
    
                    buildContext.Row = titleRow;
     
                    StartRow++;
    
                    titleCell = SetCell(titleRow, 0, sheetDetailDataWrapper.DataName);
    
                    if (OnHeadCellSetAfter != null)
                    { 
                        OnHeadCellSetAfter(buildContext);
                    }
                }
    
                IRow row = sheet.CreateRow(StartRow);
    
                buildContext.Row = row;
    
                IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
    
                int i = 0;
    
                foreach (PropertyInfo property in checkPropertyInfos)
                { 
                    DisplayNameAttribute dn = property.GetCustomAttributes(typeof(DisplayNameAttribute), false).SingleOrDefault() as DisplayNameAttribute;
    
                    if (dn != null)
                    { 
                        SetCell(row, i++, dn.DisplayName);
                        continue;
                    }
    
                    Type t = property.PropertyType;
    
                    if (t.IsGenericType)
                    { 
                        if (sheetDetailDataWrapper.Titles == null || sheetDetailDataWrapper.Titles.Count() == 0)
                        { 
                            continue;
                        }
    
                        foreach (var item in sheetDetailDataWrapper.Titles)
                        { 
                            SetCell(row, i++, item.TypeName);
                        }
                    }
                }
            
                if (titleCell != null && i > 0)
                { 
                    titleCell.MergeTo(titleRow.CreateCell(i - 1));
    
                    titleCell.CellStyle = this.CenterStyle;
                }
    
                StartRow++;
    
                return sheet;
            }
            #endregion
    
            #region AddValue
            private ISheet AddValue(ISheet sheet, ISheetDataWrapper sheetDetailDataWrapper, Type type)
            { 
                IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
    
                Int32 cellCount = 0;
    
                foreach (var item in sheetDetailDataWrapper.Datas)
                { 
                    if (item == null)
                    { 
                        StartRow++;
                        continue;
                    }
    
                    IRow newRow = sheet.CreateRow(StartRow);
    
                    buildContext.Row = newRow;
    
                    foreach (PropertyInfo property in checkPropertyInfos)
                    { 
                        Object obj = property.GetValue(item, null);
    
                        Type t = property.PropertyType;
    
                        if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>))
                        { 
                            var ssd = ((IEnumerable)obj).Cast<IExtendedBase>();
    
                            if (ssd == null)
                            { 
                                continue;
                            }
    
                            foreach (var v in sheetDetailDataWrapper.Titles)
                            { 
                                IExtendedBase sv = ssd.Where(s => s.TypeId == v.TypeId).SingleOrDefault();
    
                                SetCell(newRow, cellCount++, sv.TypeValue);
                            }
    
                            continue;
                        }
     
                        SetCell(newRow, cellCount++, obj);
                    }
    
                    StartRow++;
                    cellCount = 0;
                }
    
                return sheet;
            }
    
            #endregion
    
            #region 设置单元格
            /// <summary>
            /// 设置单元格
            /// </summary>
            /// <param name="row"></param>
            /// <param name="index"></param>
            /// <param name="value"></param>
            /// <returns></returns>
            private ICell SetCell(IRow row, int index, object value)
            { 
                ICell cell = row.CreateCell(index);
    
                SetCellValue(cell, value);
    
                buildContext.Cell = cell;
    
                if (OnContentCellSetAfter != null)
                { 
                    OnContentCellSetAfter(buildContext);
                }
    
                return cell;
            } 
            #endregion
    
            #region ExcelToDataTable
    
            /// <summary>
            /// 导入
            /// </summary>
            /// <typeparam name="T">具体对象</typeparam>
            /// <param name="fs"></param>
            /// <param name="fileName"></param>
            /// <param name="isFirstRowColumn"></param>
            /// <returns></returns>
            public static IEnumerable<T> ExcelToDataTable<T>(Stream fs, bool isFirstRowColumn = false) where T : new()
            { 
                List<T> ts = new List<T>();
    
                Type type = typeof(T);
    
                IList<PropertyInfo> checkPropertyInfos = ExcelModelsPropertyManage.CreatePropertyInfos(type);
    
                try
                { 
                    IWorkbook workbook = WorkbookFactory.Create(fs);
    
                    fs.Dispose();
    
                    ISheet sheet = workbook.GetSheetAt(0);
    
                    if (sheet != null)
                    { 
                        IRow firstRow = sheet.GetRow(0);
    
                        int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
    
                        Int32 startRow = isFirstRowColumn ? 1 : 0;
    
                        int rowCount = sheet.LastRowNum; //行数
    
                        int length = checkPropertyInfos.Count;
    
                        length = length > cellCount + 1 ? cellCount + 1 : length;
    
                        Boolean haveValue = false;
    
                        for (int i = startRow; i <= rowCount; ++i)
                        { 
                            IRow row = sheet.GetRow(i);
    
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　
    
                            T t = new T();
    
                            for (int f = 0; f < length; f++)
                            { 
                                ICell cell = row.GetCell(f);
    
                                if (cell == null || String.IsNullOrEmpty(cell.ToString()))
                                { 
                                    continue;
                                }
    
                                object b = cell.ToString();
    
                                if (cell.CellType == CellType.Numeric)
                                { 
                                    //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    { 
                                        b = cell.DateCellValue;
                                    }
                                    else
                                    { 
                                        b = cell.NumericCellValue;
                                    }
                                }
    
                                PropertyInfo pinfo = checkPropertyInfos[f];
    
                                if (pinfo.PropertyType.Name != b.GetType().Name) //类型不一样的时候，强转
                                { 
                                    b = System.ComponentModel.TypeDescriptor.GetConverter(pinfo.PropertyType).ConvertFrom(b.ToString());
                                }
    
                                type.GetProperty(pinfo.Name).SetValue(t, b, null);
    
                                if (!haveValue)
                                { 
                                    haveValue = true;
                                }
                            }
                            if (haveValue)
                            { 
                                ts.Add(t); haveValue = false;
                            }
                        }
                    }
    
                    return ts;
                }
                catch (Exception ex)
                { 
                    return null;
                }
            }
    
            #endregion
        }
    
        public class BuildContext
        { 
            public WorkbookBuilder WorkbookBuilder {  get; set; }
            
            public IWorkbook Workbook {  get; set; }
    
            public ISheet Sheet {  get; set; }
    
            public IRow Row {  get; set; }
    
            public ICell Cell {  get; set; }
    
        }
    }

WorkbookTest.cs

using System;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using System.Collections.Generic;
    using UnitTestProject.Model;
    using ExcelHelper.Operating;
    using System.Linq;
    using System.IO;
    using ExcelHelper.Operating.Model;
    
    namespace UnitTestProject
    { 
        [TestClass]
        public class WorkbookTest
        { 
            [TestMethod]
            public void WorkbookInsert()
            { 
                List<SumAnalysisX> sumAnalysisXs = new List<SumAnalysisX>();
    
                for (int i = 0; i < 8; i++)
                { 
                    SumAnalysisX sumAnalysisX1 = new SumAnalysisX() {  ClassName = null, MaxScore = 100 + i, Total = 1000 + i, TotalAverage = 10 + i };
    
                    List<SubjectScoreDetailX> ssd = new List<SubjectScoreDetailX>();
    
                    for (int j = 0; j < 4; j++)
                    { 
                        ssd.Add(new SubjectScoreDetailX() {  Score = null, TypeName = "kemu " + j.ToString(), TypeId = j });
                    }
    
                    sumAnalysisX1.SubjectScoreDetails = ssd;
    
                    sumAnalysisXs.Add(sumAnalysisX1);
    
                }
    
                WorkbookWrapper w = new WorkbookWrapper();
    
                SheetDetail sd = new SheetDetail("sxf");
    
                SheetDetailDataWrapper sheetDetailDataWrapper = new SheetDetailDataWrapper("No.1", sumAnalysisXs.Cast<IExcelModelBase>());
    
                sd.SheetDetailDataWrappers.Add(sheetDetailDataWrapper);
    
                w.AddSheetDetail(sd);
    
                w.Save(@"C:\sxf.xls");
            }
            [TestMethod]
            public void WorkbookInsert2()
            { 
                String path = AppDomain.CurrentDomain.BaseDirectory + "/D.xls";
    
                IEnumerable<PointsCoupon> pcs;
    
                using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
                { 
                     pcs = WorkbookBuilder.ExcelToDataTable<PointsCoupon>(fs, false);             
                }
    
                foreach (var item in pcs)
                { 
                    Console.WriteLine(item.ParValue);
                }
            }
        }
    }

## 六、总结 ##

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

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

项目地址：[https://github.com/aa317016589/ExcelHelper/][https_github.com_aa317016589_ExcelHelper]

唯一不变的就是变。

[20181106133614632.png]: /images/20210626/3e248f29cb774e628dfbd03fa0671d33.png
[20181106133649247.png]: /images/20210626/1ee20c60bb2049fe85e373b7834ac531.png
[https_github.com_aa317016589_ExcelHelper]: https://github.com/aa317016589/ExcelHelper/