C#_Excel數據讀取與寫入_自定義解析封裝類_支持設置標題行位置&使用excel表達式收集數據&單元格映射&標題映射&模板文件的參數數據替換

　　本篇博客園是被任務所逼，而已有的使用nopi技術的文檔技術經驗又不支持我需要的應對各種複雜需求的苛刻要求，只能自己造輪子封裝了，由於需要應對很多總類型的數據採集需求，因此有了本篇博客的代碼封裝，下麵一點點介紹吧：

　　收集excel你有沒有遇到過一下痛點：

　　1-需要收集指定行標題位置的數據，我的標題行不一定在第一行。  這個和我的csv的文檔需求是一致的

　　2-需要採集指定單元格位置的數據生成一個對象，而不是一個列表。   這裡我的方案是制定一個單元格映射類解決問題。  單元格映射類，支持表達式數據採集（我可能需要一個單元格的數據+另一個單元格的數據作為一個屬性等等）

　　3-應對不規範標題無法轉出字元串進行映射時，能不能通過制定標題的列下標建立對應關係，進行列表數據採集呢？   本博客同時支持標題字元串數據採集和標題下標數據採集，這個就牛逼了。

　　4-存儲含有表達式的數據，這個並不是難點，由於很重要，就在這裡列一下

　　5-應對Excel模板文件的數據指定位置填入數據，該位置可能會變動的解決方案。本文為了應對該情況，藉助了單元格映射關係，添加了模板參數名的屬性處理，可以應對模板文件調整時的位置變動問題。

　　6-一個能同時處理excel新老版本（.xls和.xlsx），一個指定excel位置保存數據，保存含有表達式的數據，一個可以將多個不同的數據組合存放到一個excel中的需求都可以滿足。   

　　痛點大概就是上面這些了，下麵寫主要代碼吧，供大家參考，不過封裝的類方法有點多：

　　本文藉助了NPOI程式包做了業務封裝：　　

　　1-主要封裝類-ExcelHelper：

　　該類包含很多輔助功能：比如自動幫助尋找含有指定標題名所在的位置、表達式元素A1,B2對應單元格位置的解析等等：

using NLog;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;

namespace PPPayReportTools.Excel
{
    /// <summary>
    /// EXCEL幫助類
    /// </summary>
    /// <typeparam name="T">泛型類</typeparam>
    /// <typeparam name="TCollection">泛型類集合</typeparam>
    public class ExcelHelper
    {
        private static Logger _Logger = LogManager.GetCurrentClassLogger();

        #region 創建工作表

        /// <summary>
        /// 將列表數據生成工作表
        /// </summary>
        /// <param name="tList">要導出的數據集</param>
        /// <param name="fieldNameAndShowNameDic">鍵值對集合（鍵：欄位名，值：顯示名稱）</param>
        /// <param name="workbook">更新時添加：要更新的工作表</param>
        /// <param name="sheetName">指定要創建的sheet名稱時添加</param>
        /// <param name="excelFileDescription">讀取或插入定製需求時添加</param>
        /// <returns></returns>
        public static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, Dictionary<string, string> fieldNameAndShowNameDic, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new()
        {
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);

            workbook = ExcelHelper.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription);
            return workbook;
        }
        /// <summary>
        /// 將列表數據生成工作表（T的屬性需要添加：屬性名列名映射關係）
        /// </summary>
        /// <param name="tList">要導出的數據集</param>
        /// <param name="workbook">更新時添加：要更新的工作表</param>
        /// <param name="sheetName">指定要創建的sheet名稱時添加</param>
        /// <param name="excelFileDescription">讀取或插入定製需求時添加</param>
        /// <returns></returns>
        public static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, IWorkbook workbook = null, string sheetName = "sheet1", ExcelFileDescription excelFileDescription = null) where T : new()
        {
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>();

            workbook = ExcelHelper.CreateOrUpdateWorkbook<T>(tList, titleMapperList, workbook, sheetName, excelFileDescription);
            return workbook;
        }

        private static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, List<ExcelTitleFieldMapper> titleMapperList, IWorkbook workbook, string sheetName, ExcelFileDescription excelFileDescription = null)
        {
            //xls文件格式屬於老版本文件，一個sheet最多保存65536行；而xlsx屬於新版文件類型；
            //Excel 07 - 2003一個工作表最多可有65536行，行用數字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一個工作簿中最多含有255個工作表，預設情況下是三個工作表；
            //Excel 2007及以後版本，一個工作表最多可有1048576行，16384列；
            if (workbook == null)
            {
                workbook = new XSSFWorkbook();
                //workbook = new HSSFWorkbook();
            }
            ISheet worksheet = null;
            if (workbook.GetSheetIndex(sheetName) >= 0)
            {
                worksheet = workbook.GetSheet(sheetName);
            }
            else
            {
                worksheet = workbook.CreateSheet(sheetName);
            }

            IRow row1 = null;
            ICell cell = null;

            int defaultBeginTitleIndex = 0;
            if (excelFileDescription != null)
            {
                defaultBeginTitleIndex = excelFileDescription.TitleRowIndex;
            }

            PropertyInfo propertyInfo = null;
            T t = default(T);

            int tCount = tList.Count;
            int currentRowIndex = 0;
            int dataIndex = 0;
            do
            {
                row1 = worksheet.GetRow(currentRowIndex);
                if (row1 == null)
                {
                    row1 = worksheet.CreateRow(currentRowIndex);
                }

                if (currentRowIndex >= defaultBeginTitleIndex)
                {
                    //到達標題行
                    if (currentRowIndex == defaultBeginTitleIndex)
                    {
                        int cellIndex = 0;
                        foreach (var titleMapper in titleMapperList)
                        {
                            cell = row1.GetCell(cellIndex);

                            if (cell == null)
                            {
                                cell = row1.CreateCell(cellIndex);
                            }
                            ExcelHelper.SetCellValue(cell, titleMapper.ExcelTitle, outputFormat: null);
                            cellIndex++;
                        }
                    }
                    //到達內容行
                    else
                    {
                        dataIndex = currentRowIndex - defaultBeginTitleIndex - 1;
                        if (dataIndex <= tCount - 1)
                        {
                            t = tList[dataIndex];

                            int cellIndex = 0;
                            foreach (var titleMapper in titleMapperList)
                            {
                                propertyInfo = titleMapper.PropertyInfo;

                                cell = row1.GetCell(cellIndex);
                                if (cell == null)
                                {
                                    cell = row1.CreateCell(cellIndex);
                                }

                                ExcelHelper.SetCellValue<T>(cell, t, titleMapper);

                                cellIndex++;
                            }

                            //重要：設置行寬度自適應(大批量添加數據時，該行代碼需要註釋，否則會極大減緩Excel添加行的速度！)
                            //worksheet.AutoSizeColumn(i, true);
                        }
                    }
                }

                currentRowIndex++;

            } while (dataIndex < tCount - 1);

            //設置表達式重算（如果不添加該代碼，表達式更新不出結果值）
            worksheet.ForceFormulaRecalculation = true;

            return workbook;
        }

        /// <summary>
        /// 將單元格數據列表生成工作表
        /// </summary>
        /// <param name="commonCellList">所有的單元格數據列表</param>
        /// <param name="workbook">更新時添加：要更新的工作表</param>
        /// <param name="sheetName">指定要創建的sheet名稱時添加</param>
        /// <returns></returns>
        public static IWorkbook CreateOrUpdateWorkbook(CommonCellModelColl commonCellList, IWorkbook workbook = null, string sheetName = "sheet1")
        {
            //xls文件格式屬於老版本文件，一個sheet最多保存65536行；而xlsx屬於新版文件類型；
            //Excel 07 - 2003一個工作表最多可有65536行，行用數字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一個工作簿中最多含有255個工作表，預設情況下是三個工作表；
            //Excel 2007及以後版本，一個工作表最多可有1048576行，16384列；
            if (workbook == null)
            {
                workbook = new XSSFWorkbook();
                //workbook = new HSSFWorkbook();
            }
            ISheet worksheet = null;
            if (workbook.GetSheetIndex(sheetName) >= 0)
            {
                worksheet = workbook.GetSheet(sheetName);
            }
            else
            {
                worksheet = workbook.CreateSheet(sheetName);
            }

            //設置首列顯示
            IRow row1 = null;
            int rowIndex = 0;
            int columnIndex = 0;
            int maxColumnIndex = 0;
            Dictionary<int, CommonCellModel> rowColumnIndexCellDIC = null;
            ICell cell = null;
            object cellValue = null;

            do
            {
                rowColumnIndexCellDIC = commonCellList.GetRawCellList(rowIndex).ToDictionary(m => m.ColumnIndex);
                maxColumnIndex = rowColumnIndexCellDIC.Count > 0 ? rowColumnIndexCellDIC.Keys.Max() : 0;

                if (rowColumnIndexCellDIC != null && rowColumnIndexCellDIC.Count > 0)
                {
                    row1 = worksheet.GetRow(rowIndex);
                    if (row1 == null)
                    {
                        row1 = worksheet.CreateRow(rowIndex);
                    }
                    columnIndex = 0;
                    do
                    {
                        cell = row1.GetCell(columnIndex);
                        if (cell == null)
                        {
                            cell = row1.CreateCell(columnIndex);
                        }

                        if (rowColumnIndexCellDIC.ContainsKey(columnIndex))
                        {
                            cellValue = rowColumnIndexCellDIC[columnIndex].CellValue;

                            ExcelHelper.SetCellValue(cell, cellValue, outputFormat: null, rowColumnIndexCellDIC[columnIndex].IsCellFormula);
                        }
                        columnIndex++;
                    } while (columnIndex <= maxColumnIndex);
                }
                rowIndex++;
            } while (rowColumnIndexCellDIC != null && rowColumnIndexCellDIC.Count > 0);

            //設置表達式重算（如果不添加該代碼，表達式更新不出結果值）
            worksheet.ForceFormulaRecalculation = true;

            return workbook;
        }

        /// <summary>
        /// 更新模板文件數據：將使用單元格映射的數據T存入模板文件中
        /// </summary>
        /// <param name="filePath">所有的單元格數據列表</param>
        /// <param name="t">添加了單元格參數映射的數據對象</param>
        /// <returns></returns>
        public static IWorkbook CreateOrUpdateWorkbook<T>(string filePath, T t)
        {
            //該方法預設替換模板數據在首個sheet里

            IWorkbook workbook = null;
            CommonCellModelColl commonCellColl = ExcelHelper._ReadCellList(filePath, out workbook);

            ISheet worksheet = workbook.GetSheetAt(0);

            //獲取t的單元格映射列表
            Dictionary<string, ExcelCellFieldMapper> tParamMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.CellParamName);

            var rows = worksheet.GetRowEnumerator();
            IRow row;
            ICell cell;
            string cellValue;
            ExcelCellFieldMapper cellMapper;
            while (rows.MoveNext())
            {
                row = (XSSFRow)rows.Current;
                int cellCount = row.Cells.Count;

                for (int i = 0; i < cellCount; i++)
                {
                    cell = row.Cells[i];
                    cellValue = cell.ToString();
                    if (tParamMapperDic.ContainsKey(cellValue))
                    {
                        cellMapper = tParamMapperDic[cellValue];
                        ExcelHelper.SetCellValue<T>(cell, t, cellMapper);
                    }
                }

            }

            if (tParamMapperDic.Count > 0)
            {
                //迴圈所有單元格數據替換指定變數數據
                foreach (var cellItem in commonCellColl)
                {
                    cellValue = cellItem.CellValue.ToString();

                    if (tParamMapperDic.ContainsKey(cellValue))
                    {
                        cellItem.CellValue = tParamMapperDic[cellValue].PropertyInfo.GetValue(t);
                    }
                }
            }

            //設置表達式重算（如果不添加該代碼，表達式更新不出結果值）
            worksheet.ForceFormulaRecalculation = true;

            return workbook;
        }

        #endregion

        #region 保存工作表到文件

        /// <summary>
        /// 保存Workbook數據為文件
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fileDirectoryPath"></param>
        /// <param name="fileName"></param>
        public static void SaveWorkbookToFile(IWorkbook workbook, string filePath)
        {
            //xls文件格式屬於老版本文件，一個sheet最多保存65536行；而xlsx屬於新版文件類型；
            //Excel 07 - 2003一個工作表最多可有65536行，行用數字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一個工作簿中最多含有255個工作表，預設情況下是三個工作表；
            //Excel 2007及以後版本，一個工作表最多可有1048576行，16384列；

            MemoryStream ms = new MemoryStream();
            //這句代碼非常重要，如果不加，會報：打開的EXCEL格式與擴展名指定的格式不一致
            ms.Seek(0, SeekOrigin.Begin);
            workbook.Write(ms);
            byte[] myByteArray = ms.GetBuffer();

            string fileDirectoryPath = filePath.Split('\\')[0];
            if (!Directory.Exists(fileDirectoryPath))
            {
                Directory.CreateDirectory(fileDirectoryPath);
            }
            string fileName = filePath.Replace(fileDirectoryPath, "");

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            File.WriteAllBytes(filePath, myByteArray);
        }

        #endregion

        #region 讀取Excel數據

        /// <summary>
        /// 讀取Excel數據1_手動提供屬性信息和標題對應關係
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="fieldNameAndShowNameDic"></param>
        /// <param name="excelFileDescription"></param>
        /// <returns></returns>
        public static List<T> ReadTitleDataList<T>(string filePath, Dictionary<string, string> fieldNameAndShowNameDic, ExcelFileDescription excelFileDescription) where T : new()
        {
            //標題屬性字典列表
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);

            List<T> tList = ExcelHelper._GetTList<T>(filePath, titleMapperList, excelFileDescription);
            return tList ?? new List<T>(0);
        }

        /// <summary>
        /// 讀取Excel數據2_使用Excel標記特性和文件描述自動創建關係
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="excelFileDescription"></param>
        /// <returns></returns>
        public static List<T> ReadTitleDataList<T>(string filePath, ExcelFileDescription excelFileDescription) where T : new()
        {
            //標題屬性字典列表
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>();

            List<T> tList = ExcelHelper._GetTList<T>(filePath, titleMapperList, excelFileDescription);
            return tList ?? new List<T>(0);
        }

        private static List<T> _GetTList<T>(string filePath, List<ExcelTitleFieldMapper> titleMapperList, ExcelFileDescription excelFileDescription) where T : new()
        {
            List<T> tList = new List<T>(500 * 10000);
            T t = default(T);

            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                IFormulaEvaluator formulaEvaluator = null;

                try
                {
                    workbook = new XSSFWorkbook(fileStream);
                    formulaEvaluator = new XSSFFormulaEvaluator(workbook);
                }
                catch (Exception)
                {
                    workbook = new HSSFWorkbook(fileStream);
                    formulaEvaluator = new HSSFFormulaEvaluator(workbook);
                }

                int sheetCount = workbook.NumberOfSheets;

                int currentSheetIndex = 0;
                int currentSheetRowTitleIndex = -1;
                do
                {
                    var sheet = workbook.GetSheetAt(currentSheetIndex);

                    //標題下標屬性字典
                    Dictionary<int, ExcelTitleFieldMapper> sheetTitleIndexPropertyDic = new Dictionary<int, ExcelTitleFieldMapper>(0);

                    //如果沒有設置標題行，則通過自動查找方法獲取
                    if (excelFileDescription.TitleRowIndex < 0)
                    {
                        string[] titleArray = titleMapperList.Select(m => m.ExcelTitle).ToArray();
                        currentSheetRowTitleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleArray);
                    }
                    else
                    {
                        currentSheetRowTitleIndex = excelFileDescription.TitleRowIndex;
                    }

                    var rows = sheet.GetRowEnumerator();

                    bool isHaveTitleIndex = false;
                    //含有Excel行下標
                    if (titleMapperList.Count > 0 && titleMapperList[0].ExcelTitleIndex >= 0)
                    {
                        isHaveTitleIndex = true;

                        foreach (var titleMapper in titleMapperList)
                        {
                            sheetTitleIndexPropertyDic.Add(titleMapper.ExcelTitleIndex, titleMapper);
                        }
                    }

                    PropertyInfo propertyInfo = null;
                    int currentRowIndex = 0;

                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;
                        currentRowIndex = row.RowNum;

                        //到達標題行
                        if (isHaveTitleIndex == false && currentRowIndex == currentSheetRowTitleIndex)
                        {
                            ICell cell = null;
                            string cellValue = null;
                            Dictionary<string, ExcelTitleFieldMapper> titleMapperDic = titleMapperList.ToDictionary(m => m.ExcelTitle);
                            for (int i = 0; i < row.Cells.Count; i++)
                            {
                                cell = row.Cells[i];
                                cellValue = cell.StringCellValue;
                                if (titleMapperDic.ContainsKey(cellValue))
                                {
                                    sheetTitleIndexPropertyDic.Add(i, titleMapperDic[cellValue]);
                                }
                            }
                        }

                        //到達內容行
                        if (currentRowIndex > currentSheetRowTitleIndex)
                        {
                            t = new T();
                            ExcelTitleFieldMapper excelTitleFieldMapper = null;
                            foreach (var titleIndexItem in sheetTitleIndexPropertyDic)
                            {
                                ICell cell = row.GetCell(titleIndexItem.Key);

                                excelTitleFieldMapper = titleIndexItem.Value;

                                //沒有數據的單元格預設為null
                                string cellValue = cell?.ToString() ?? "";
                                propertyInfo = excelTitleFieldMapper.PropertyInfo;
                                try
                                {
                                    if (excelTitleFieldMapper.IsCheckContentEmpty)
                                    {
                                        if (string.IsNullOrEmpty(cellValue))
                                        {
                                            t = default(T);
                                            break;
                                        }
                                    }

                                    if (excelTitleFieldMapper.IsCoordinateExpress || cell.CellType == CellType.Formula)
                                    {
                                        //讀取含有表達式的單元格值
                                        cellValue = formulaEvaluator.Evaluate(cell).StringValue;
                                        propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType));
                                    }
                                    else if (propertyInfo.PropertyType.IsEnum)
                                    {
                                        object enumObj = propertyInfo.PropertyType.InvokeMember(cellValue, BindingFlags.GetField, null, null, null);
                                        propertyInfo.SetValue(t, Convert.ChangeType(enumObj, propertyInfo.PropertyType));
                                    }
                                    else
                                    {
                                        propertyInfo.SetValue(t, Convert.ChangeType(cellValue, propertyInfo.PropertyType));
                                    }
                                }
                                catch (Exception e)
                                {
                                    ExcelHelper._Logger.Debug($"文件_{filePath}讀取{currentRowIndex + 1}行內容失敗！");
                                    t = default(T);
                                    break;
                                }
                            }
                            if (t != null)
                            {
                                tList.Add(t);
                            }
                        }
                    }

                    currentSheetIndex++;

                } while (currentSheetIndex + 1 <= sheetCount);
            }
            return tList ?? new List<T>(0);
        }

        /// <summary>
        /// 讀取文件的所有單元格數據
        /// </summary>
        /// <param name="filePath">文件路徑</param>
        /// <returns></returns>
        public static CommonCellModelColl ReadCellList(string filePath)
        {
            IWorkbook workbook = null;

            CommonCellModelColl commonCellColl = ExcelHelper._ReadCellList(filePath, out workbook);
            return commonCellColl;
        }

        /// <summary>
        /// 讀取文件的所有單元格數據
        /// </summary>
        /// <param name="filePath">文件路徑</param>
        /// <returns></returns>
        public static CommonCellModelColl ReadCellList(string filePath, out IWorkbook workbook)
        {
            CommonCellModelColl commonCellColl = ExcelHelper._ReadCellList(filePath, out workbook);
            return commonCellColl;
        }

        private static CommonCellModelColl _ReadCellList(string filePath, out IWorkbook workbook)
        {
            CommonCellModelColl commonCellColl = new CommonCellModelColl(10000);
            CommonCellModel cellModel = null;
            workbook = null;

            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                try
                {
                    workbook = new XSSFWorkbook(fileStream);
                }
                catch (Exception)
                {
                    workbook = new HSSFWorkbook(fileStream);
                }
                var sheet = workbook.GetSheetAt(0);

                var rows = sheet.GetRowEnumerator();
                List<ICell> cellList = null;
                ICell cell = null;

                //從第1行數據開始獲取
                while (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;

                    cellList = row.Cells;
                    int cellCount = cellList.Count;

                    for (int i = 0; i < cellCount; i++)
                    {
                        cell = cellList[i];
                        cellModel = new CommonCellModel
                        {
                            RowIndex = row.RowNum,
                            ColumnIndex = i,
                            CellValue = cell.ToString(),
                            IsCellFormula = cell.CellType == CellType.Formula ? true : false
                        };
                        commonCellColl.Add(cellModel);
                    }
                }
            }
            return commonCellColl;
        }

        /// <summary>
        /// 獲取文件單元格數據對象
        /// </summary>
        /// <typeparam name="T">T的屬性必須標記了ExcelCellAttribute</typeparam>
        /// <param name="filePath">文建路徑</param>
        /// <returns></returns>
        public static T ReadCellData<T>(string filePath) where T : new()
        {
            T t = new T();

            ExcelHelper._Logger.Info($"開始讀取{filePath}的數據...");

            CommonCellModelColl commonCellColl = ExcelHelper.ReadCellList(filePath);

            Dictionary<PropertyInfo, ExcelCellFieldMapper> propertyMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.PropertyInfo);
            string cellExpress = null;
            string pValue = null;
            PropertyInfo propertyInfo = null;
            foreach (var item in propertyMapperDic)
            {
                cellExpress = item.Value.CellCoordinateExpress;
                propertyInfo = item.Key;
                pValue = ExcelHelper.GetVByExpress(cellExpress, propertyInfo, commonCellColl).ToString();
                if (!string.IsNullOrEmpty(pValue))
                {
                    propertyInfo.SetValue(t, Convert.ChangeType(pValue, propertyInfo.PropertyType));
                }
            }
            return t;
        }

        /// <summary>
        /// 獲取文件首個sheet的標題位置
        /// </summary>
        /// <typeparam name="T">T必須做了標題映射</typeparam>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static int FileFirstSheetTitleIndex<T>(string filePath)
        {
            int titleIndex = 0;

            if (File.Exists(filePath))
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;
                    try
                    {
                        workbook = new XSSFWorkbook(fileStream);
                    }
                    catch (Exception)
                    {
                        workbook = new HSSFWorkbook(fileStream);
                    }

                    string[] titleArray = ExcelTitleFieldMapper.GetModelFieldMapper<T>().Select(m => m.ExcelTitle).ToArray();

                    ISheet sheet = workbook.GetSheetAt(0);
                    titleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleArray);
                }
            }

            return titleIndex;
        }

        /// <