分享：一个基于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 特性标记来实现的。

五、主要实现类

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/

唯一不变的就是变。