分享：一個基於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/

唯一不變的就是變。