C#_Excel數(shù)據(jù)讀取與寫入_自定義解析封裝類_支持設(shè)置標(biāo)題行位置&使用excel表達(dá)式收集數(shù)據(jù)&單元格映射&標(biāo)題映射&模板文件的參數(shù)數(shù)據(jù)替換（第二版-增加深度讀取和更新功能）

行者花雕 2021-04-04

展開全文

　　前言：新的的封裝類，增加了單元格映射深度更新和讀取的功能，預(yù)留了標(biāo)題映射的深度更新接口待擴(kuò)展。。。（以后有時(shí)間和精力再完善吧）

　　【深度更新】：我這里定義的深度更新策略，指的是：假如我們需要讀取一組單元格的映射數(shù)據(jù)為一個(gè)對象，但是有不止一組這樣的單元格數(shù)據(jù)對象，且這些對象的單元格位置排列是有規(guī)律的！

如：我要收集一個(gè)對象，在A1,A2，B1,B2的位置組成的一個(gè)數(shù)據(jù)對象，下一個(gè)對象位置在： A5,C6,B5,B6的位置，同理。。。

　　前面的文章介紹了使用單元格映射關(guān)系，我可以順利收集到其中一個(gè)對象，但是我不可能把所有的單元格都建立對象關(guān)聯(lián)起來，且數(shù)據(jù)又不符合標(biāo)題行數(shù)據(jù)映射；那么就提出了一個(gè)新的策略，我這里叫：深度更新表達(dá)式讀取策略。

　　下面放置完整代碼，這版本做了深度更新的接口的抽象和封裝，類有點(diǎn)多：

　　1-ExcelHelper 幫助類：

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


        public static IWorkbook GetExcelWorkbook(string filePath)
        {
            IWorkbook workbook = null;

            try
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    try
                    {
                        workbook = new XSSFWorkbook(fileStream);
                    }
                    catch (Exception)
                    {
                        workbook = new HSSFWorkbook(fileStream);
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception($"文件：{filePath}被占用！", e);
            }
            return workbook;
        }

        public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, int sheetIndex = 0)
        {
            ISheet sheet = null;

            if (workbook != null)
            {
                if (sheetIndex >= 0)
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
            }
            return sheet;
        }

        public static ISheet GetExcelWorkbookSheet(IWorkbook workbook, string sheetName = "sheet1")
        {
            ISheet sheet = null;

            if (workbook != null && !string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
            }
            return sheet;
        }

        public static IRow GetOrCreateRow(ISheet sheet, int rowIndex)
        {
            IRow row = null;
            if (sheet != null)
            {
                row = sheet.GetRow(rowIndex);
                if (row == null)
                {
                    row = sheet.CreateRow(rowIndex);
                }
            }
            return row;
        }

        public static ICell GetOrCreateCell(ISheet sheet, int rowIndex, int columnIndex)
        {
            ICell cell = null;

            IRow row = ExcelHelper.GetOrCreateRow(sheet, rowIndex);
            if (row != null)
            {
                cell = row.GetCell(columnIndex);
                if (cell == null)
                {
                    cell = row.CreateCell(columnIndex);
                }
            }

            return cell;
        }

        /// <summary>
        /// 根據(jù)單元格表達(dá)式和單元格數(shù)據(jù)集獲取數(shù)據(jù)
        /// </summary>
        /// <param name="cellExpress">單元格表達(dá)式</param>
        /// <param name="workbook">excel工作文件</param>
        /// <param name="currentSheet">當(dāng)前sheet</param>
        /// <returns></returns>
        public static object GetVByExpress(string cellExpress, IWorkbook workbook, ISheet currentSheet)
        {
            object value = null;

            //含有單元格表達(dá)式的取表達(dá)式值，沒有表達(dá)式的取單元格字符串
            if (!string.IsNullOrEmpty(cellExpress) && workbook != null && currentSheet != null)
            {
                IFormulaEvaluator formulaEvaluator = null;
                if (workbook is HSSFWorkbook)
                {
                    formulaEvaluator = new HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    formulaEvaluator = new XSSFFormulaEvaluator(workbook);
                }

                //創(chuàng)建臨時(shí)行，單元格，執(zhí)行表達(dá)式運(yùn)算；
                IRow newRow = currentSheet.CreateRow(currentSheet.LastRowNum + 1);
                ICell cell = newRow.CreateCell(0);
                cell.SetCellFormula(cellExpress);
                cell = formulaEvaluator.EvaluateInCell(cell);
                value = cell.ToString();

                currentSheet.RemoveRow(newRow);
            }

            return value ?? "";

        }

        #region 創(chuàng)建工作表

        /// <summary>
        /// 將列表數(shù)據(jù)生成工作表
        /// </summary>
        /// <param name="tList">要導(dǎo)出的數(shù)據(jù)集</param>
        /// <param name="fieldNameAndShowNameDic">鍵值對集合（鍵：字段名，值：顯示名稱）</param>
        /// <param name="workbook">更新時(shí)添加：要更新的工作表</param>
        /// <param name="sheetName">指定要?jiǎng)?chuàng)建的sheet名稱時(shí)添加</param>
        /// <param name="excelFileDescription">讀取或插入定制需求時(shí)添加</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>
        /// 將列表數(shù)據(jù)生成工作表（T的屬性需要添加：屬性名列名映射關(guān)系）
        /// </summary>
        /// <param name="tList">要導(dǎo)出的數(shù)據(jù)集</param>
        /// <param name="workbook">更新時(shí)添加：要更新的工作表</param>
        /// <param name="sheetName">指定要?jiǎng)?chuàng)建的sheet名稱時(shí)添加</param>
        /// <param name="excelFileDescription">讀取或插入定制需求時(shí)添加</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)
        {
            CellModelColl cellModelColl = new CellModelColl(0);

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

            //補(bǔ)全標(biāo)題行映射數(shù)據(jù)的標(biāo)題和下標(biāo)位置映射關(guān)系
            ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: sheetName);
            IRow titleRow = null;
            if (sheet != null)
            {
                titleRow = sheet.GetRow(defaultBeginTitleIndex);
            }

            if (titleRow != null)
            {
                List<ICell> titleCellList = titleRow.Cells;
                foreach (var titleMapper in titleMapperList)
                {
                    if (titleMapper.ExcelTitleIndex < 0)
                    {
                        foreach (var cellItem in titleCellList)
                        {
                            if (cellItem.ToString().Equals(titleMapper.ExcelTitle, StringComparison.OrdinalIgnoreCase))
                            {
                                titleMapper.ExcelTitleIndex = cellItem.ColumnIndex;
                                break;
                            }
                        }
                    }
                    else if (string.IsNullOrEmpty(titleMapper.ExcelTitle))
                    {
                        ICell cell = titleRow.GetCell(titleMapper.ExcelTitleIndex);
                        if (cell != null)
                        {
                            titleMapper.ExcelTitle = cell.ToString();
                        }
                    }
                }
            }
            else
            {
                //如果是新建Sheet頁，則手動(dòng)初始化下標(biāo)關(guān)系
                for (int i = 0; i < titleMapperList.Count; i++)
                {
                    titleMapperList[i].ExcelTitleIndex = i;
                }
            }

            int currentRowIndex = defaultBeginTitleIndex;
            //添加標(biāo)題單元格數(shù)據(jù)
            foreach (var titleMapper in titleMapperList)
            {
                cellModelColl.Add(new CellModel
                {
                    RowIndex = defaultBeginTitleIndex,
                    ColumnIndex = titleMapper.ExcelTitleIndex,
                    CellValue = titleMapper.ExcelTitle,
                    IsCellFormula = false
                });
            }
            currentRowIndex++;

            //將標(biāo)題行數(shù)據(jù)轉(zhuǎn)出單元格數(shù)據(jù)
            foreach (var item in tList)
            {
                foreach (var titleMapper in titleMapperList)
                {
                    cellModelColl.Add(new CellModel
                    {
                        RowIndex = currentRowIndex,
                        ColumnIndex = titleMapper.ExcelTitleIndex,
                        CellValue = titleMapper.PropertyInfo.GetValue(item),
                        IsCellFormula = titleMapper.IsCoordinateExpress
                    });
                }
                currentRowIndex++;
            }

            workbook = ExcelHelper.CreateOrUpdateWorkbook(cellModelColl, workbook, sheetName);

            return workbook;
        }

        /// <summary>
        /// 將單元格數(shù)據(jù)列表生成工作表
        /// </summary>
        /// <param name="commonCellList">所有的單元格數(shù)據(jù)列表</param>
        /// <param name="workbook">更新時(shí)添加：要更新的工作表</param>
        /// <param name="sheetName">指定要?jiǎng)?chuàng)建的sheet名稱時(shí)添加</param>
        /// <returns></returns>
        public static IWorkbook CreateOrUpdateWorkbook(CellModelColl commonCellList, IWorkbook workbook = null, string sheetName = "sheet1")
        {
            //xls文件格式屬于老版本文件，一個(gè)sheet最多保存65536行；而xlsx屬于新版文件類型；
            //Excel 07 - 2003一個(gè)工作表最多可有65536行，行用數(shù)字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一個(gè)工作簿中最多含有255個(gè)工作表，默認(rèn)情況下是三個(gè)工作表；
            //Excel 2007及以后版本，一個(gè)工作表最多可有1048576行，16384列；
            if (workbook == null)
            {
                workbook = new XSSFWorkbook();
                //workbook = new HSSFWorkbook();
            }
            ISheet worksheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName);

            if (worksheet != null && commonCellList != null && commonCellList.Count > 0)
            {
                //設(shè)置首列顯示
                IRow row1 = null;
                int rowIndex = 0;
                int maxRowIndex = commonCellList.Max(m => m.RowIndex);
                Dictionary<int, CellModel> rowColumnIndexCellDIC = null;
                ICell cell = null;
                object cellValue = null;

                do
                {
                    rowColumnIndexCellDIC = commonCellList.GetRawCellList(rowIndex).ToDictionary(m => m.ColumnIndex);
                    int 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);
                        }
                        int columnIndex = 0;
                        do
                        {
                            cell = row1.GetCell(columnIndex);
                            if (cell == null)
                            {
                                cell = row1.CreateCell(columnIndex);
                            }

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

                                CellFactory.SetCellValue(cell, cellValue, outputFormat: null, rowColumnIndexCellDIC[columnIndex].IsCellFormula);
                            }
                            columnIndex++;
                        } while (columnIndex <= maxColumnIndex);
                    }
                    rowIndex++;
                } while (rowIndex <= maxRowIndex);

                //設(shè)置表達(dá)式重算（如果不添加該代碼，表達(dá)式更新不出結(jié)果值）
                worksheet.ForceFormulaRecalculation = true;
            }

            return workbook;
        }

        /// <summary>
        /// 更新模板文件數(shù)據(jù)：將使用單元格映射的數(shù)據(jù)T存入模板文件中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="t"></param>
        /// <param name="excelFileDescription"></param>
        /// <returns></returns>
        public static IWorkbook UpdateTemplateWorkbook<T>(IWorkbook workbook, ISheet sheet, T t, ExcelFileDescription excelFileDescription = null)
        {
            //該方法默認(rèn)替換模板數(shù)據(jù)在首個(gè)sheet里

            CellModelColl commonCellColl = ExcelHelper.ReadCellList(workbook, sheet, false);

            List<IExcelCellPointDeepUpdate> excelCellPointDeepList = new List<IExcelCellPointDeepUpdate>(0);
            if (excelFileDescription != null)
            {
                excelCellPointDeepList.Add((IExcelCellPointDeepUpdate)excelFileDescription.ExcelDeepUpdateList);
            }

            //獲取t的單元格映射列表
            List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
            foreach (var cellMapper in cellMapperList)
            {
                if (cellMapper.CellParamWriteList.Count > 0)
                {
                    foreach (var cellParamWriteAttribute in cellMapper.CellParamWriteList)
                    {
                        CellModel cellModel = commonCellColl.GetCell(cellParamWriteAttribute.CellParamName);
                        if (cellModel != null)
                        {
                            cellModel.CellValue = cellMapper.PropertyInfo.GetValue(t);
                        }
                    }
                }
                if (cellMapper.CellPointWriteList.Count > 0)
                {
                    object cellValue = cellMapper.PropertyInfo.GetValue(t);
                    ICellModel firstCellPosition = null;
                    foreach (var cellPointWriteAttribute in cellMapper.CellPointWriteList)
                    {
                        firstCellPosition = CellFactory.GetCellByExcelPosition(cellPointWriteAttribute.CellPosition);
                        CellFactory.SetDeepUpdateCellValue(sheet, firstCellPosition.RowIndex, firstCellPosition.ColumnIndex, cellValue, cellPointWriteAttribute.OutputFormat, false, excelCellPointDeepList);
                    }
                }
            }

            workbook = ExcelHelper.CreateOrUpdateWorkbook(commonCellColl, workbook, sheet.SheetName);

            return workbook;
        }

        #endregion

        #region 保存工作表到文件

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

            MemoryStream ms = new MemoryStream();
            //這句代碼非常重要，如果不加，會(huì)報(bào)：打開的EXCEL格式與擴(kuò)展名指定的格式不一致
            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);
        }

        /// <summary>
        /// 保存workbook到字節(jié)流中(提供給API接口使用)
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public static byte[] SaveWorkbookToByte(IWorkbook workbook)
        {
            MemoryStream stream = new MemoryStream();
            stream.Seek(0, SeekOrigin.Begin);
            workbook.Write(stream);

            byte[] byteArray = stream.GetBuffer();
            return byteArray;
        }

        #endregion

        #region 讀取Excel數(shù)據(jù)

        /// <summary>
        /// 讀取Excel數(shù)據(jù)1_手動(dòng)提供屬性信息和標(biāo)題對應(yīng)關(guān)系
        /// </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()
        {
            //標(biāo)題屬性字典列表
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);

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

        /// <summary>
        /// 讀取Excel數(shù)據(jù)2_使用Excel標(biāo)記特性和文件描述自動(dòng)創(chuàng)建關(guān)系
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="excelFileDescription"></param>
        /// <returns></returns>
        public static List<T> ReadTitleDataList<T>(string filePath, ExcelFileDescription excelFileDescription) where T : new()
        {
            //標(biāo)題屬性字典列表
            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);

            try
            {
                IWorkbook workbook = ExcelHelper.GetExcelWorkbook(filePath);
                IFormulaEvaluator formulaEvaluator = null;

                if (workbook is XSSFWorkbook)
                {
                    formulaEvaluator = new XSSFFormulaEvaluator(workbook);
                }
                else if (workbook is HSSFWorkbook)
                {
                    formulaEvaluator = new HSSFFormulaEvaluator(workbook);
                }

                int sheetCount = workbook.NumberOfSheets;

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

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

                    //如果沒有設(shè)置標(biāo)題行，則通過自動(dòng)查找方法獲取
                    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行下標(biāo)
                    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;

                    if (currentSheetRowTitleIndex >= 0)
                    {
                        while (rows.MoveNext())
                        {
                            IRow row = (IRow)rows.Current;
                            currentRowIndex = row.RowNum;

                            //到達(dá)標(biāo)題行（尋找標(biāo)題行映射）
                            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]);
                                    }
                                }
                            }

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

                                    excelTitleFieldMapper = titleIndexItem.Value;

                                    //沒有數(shù)據(jù)的單元格默認(rèn)為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)
                                        {
                                            //讀取含有表達(dá)式的單元格值
                                            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}行內(nèi)容失敗！");
                                        t = default(T);
                                        break;
                                    }
                                }
                                if (t != null)
                                {
                                    tList.Add(t);
                                }
                            }
                        }
                    }

                    currentSheetIndex++;

                } while (currentSheetIndex + 1 <= sheetCount);
            }
            catch (Exception e)
            {
                throw new Exception($"文件：{filePath}被占用！", e);
            }
            return tList ?? new List<T>(0);
        }

        public static CellModelColl ReadCellList(IWorkbook workbook, ISheet sheet, bool isRunFormula = false)
        {
            CellModelColl commonCells = new CellModelColl(10000);

            IFormulaEvaluator formulaEvaluator = null;
            if (workbook != null)
            {
                if (workbook is HSSFWorkbook)
                {
                    formulaEvaluator = new HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    formulaEvaluator = new XSSFFormulaEvaluator(workbook);
                }
            }
            if (sheet != null)
            {
                CellModel cellModel = null;

                var rows = sheet.GetRowEnumerator();

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

                    List<ICell> cellList = row.Cells;

                    ICell cell = null;
                    foreach (var cellItem in cellList)
                    {
                        cell = cellItem;
                        if (isRunFormula && cell.CellType == CellType.Formula)
                        {
                            cell = formulaEvaluator.EvaluateInCell(cell);
                        }

                        cellModel = new CellModel
                        {
                            RowIndex = cell.RowIndex,
                            ColumnIndex = cell.ColumnIndex,
                            CellValue = cell.ToString(),
                            IsCellFormula = cell.CellType == CellType.Formula
                        };

                        commonCells.Add(cellModel);
                    }
                }
            }
            return commonCells;
        }

        /// <summary>
        /// 獲取文件單元格數(shù)據(jù)對象
        /// </summary>
        /// <typeparam name="T">T的屬性必須標(biāo)記了ExcelCellAttribute</typeparam>
        /// <param name="filePath">文建路徑</param>
        /// <param name="sheetIndex">（可選）sheet所在位置</param>
        /// <param name="sheetName">（可選）sheet名稱</param>
        /// <returns></returns>
        public static T ReadCellData<T>(IWorkbook workbook, ISheet sheet) where T : new()
        {
            T t = new T();

            if (workbook != null)
            {

                if (sheet != null)
                {
                    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.CellExpressRead.CellCoordinateExpress;
                        propertyInfo = item.Key;
                        pValue = ExcelHelper.GetVByExpress(cellExpress, workbook, sheet).ToString();
                        if (!string.IsNullOrEmpty(pValue))
                        {
                            try
                            {
                                propertyInfo.SetValue(t, Convert.ChangeType(pValue, propertyInfo.PropertyType));
                            }
                            catch (Exception)
                            {

                                throw;
                            }

                        }
                    }
                }
            }

            return t;
        }

        /// <summary>
        /// 讀取單元格數(shù)據(jù)對象列表-支持深度讀取
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="excelFileDescription"></param>
        /// <returns></returns>
        public static List<T> ReadCellData<T>(IWorkbook workbook, ISheet sheet, ExcelFileDescription excelFileDescription) where T : new()
        {
            List<T> tList = new List<T>(0);
            T t = default(T);

            #region 獲取深度表達(dá)式更新列表

            List<IExcelCellExpressDeepUpdate<T>> excelCellExpressDeepUpdateList = new List<IExcelCellExpressDeepUpdate<T>>(0);
            if (excelFileDescription != null)
            {
                foreach (var item in excelFileDescription.ExcelDeepUpdateList)
                {
                    if (item is IExcelCellExpressDeepUpdate<T>)
                    {
                        excelCellExpressDeepUpdateList.Add((IExcelCellExpressDeepUpdate<T>)item);
                    }
                }
            }

            #endregion

            #region 通過表達(dá)式映射列表讀取對象T

            Func<List<ExcelCellFieldMapper>, T> expressMapperFunc = (excelCellFieldMapperList) =>
            {
                t = new T();
                foreach (var cellMapper in excelCellFieldMapperList)
                {
                    string currentCellExpress = cellMapper.CellExpressRead.CellCoordinateExpress;

                    object pValue = ExcelHelper.GetVByExpress(currentCellExpress, workbook, sheet);

                    try
                    {
                        cellMapper.PropertyInfo.SetValue(t, Convert.ChangeType(pValue, cellMapper.PropertyInfo.PropertyType));
                    }
                    catch (Exception)
                    {
                    }
                }
                return t;
            };

            #endregion

            #region 執(zhí)行初始表達(dá)式數(shù)據(jù)收集

            //獲取t的單元格映射列表
            List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
            t = expressMapperFunc(cellMapperList);

            #endregion

            #region 執(zhí)行深度更新策略收集數(shù)據(jù)

            Action<IExcelCellExpressDeepUpdate<T>> actionDeepReadAction = (excelCellExpressDeepUpdate) =>
            {
                //獲取初始表達(dá)式映射列表
                cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();

                //執(zhí)行單元格表達(dá)式深度更新

                bool isContinute = false;

                do
                {
                    //通過深度更新策略更新初始表達(dá)式數(shù)據(jù)
                    foreach (var cellMapper in cellMapperList)
                    {
                        if (cellMapper.CellExpressRead != null)
                        {
                            string currentCellExpress = cellMapper.CellExpressRead.CellCoordinateExpress;
                            currentCellExpress = excelCellExpressDeepUpdate.GetNextCellExpress(currentCellExpress);
                            cellMapper.CellExpressRead.CellCoordinateExpress = currentCellExpress;
                        }
                    }
                    t = expressMapperFunc(cellMapperList);
                    isContinute = excelCellExpressDeepUpdate.IsContinute(t);
                    if (isContinute)
                    {
                        tList.Add(t);
                    }

                } while (isContinute);
            };

            foreach (var item in excelCellExpressDeepUpdateList)
            {
                actionDeepReadAction(item);
            }

            #endregion

            return tList;
        }

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

            if (File.Exists(filePath))
            {
                try
                {
                    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);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception($"文件：{filePath}被占用！", e);
                }
            }

            return titleIndex;
        }

        /// <summary>
        /// 獲取文件首個(gè)sheet的標(biāo)題位置
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="titleNames"></param>
        /// <returns></returns>
        public static int FileFirstSheetTitleIndex(string filePath, params string[] titleNames)
        {
            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);
                    }
                    ISheet sheet = workbook.GetSheetAt(0);
                    titleIndex = ExcelHelper.GetSheetTitleIndex(sheet, titleNames);
                }
            }

            return titleIndex;
        }

        #endregion

        #region 輔助方法

        /// <summary>
        /// 根據(jù)標(biāo)題名稱獲取標(biāo)題行下標(biāo)位置
        /// </summary>
        /// <param name="sheet">要查找的sheet</param>
        /// <param name="titleNames">標(biāo)題名稱</param>
        /// <returns></returns>
        private static int GetSheetTitleIndex(ISheet sheet, params string[] titleNames)
        {
            int titleIndex = -1;

            if (sheet != null && titleNames != null && titleNames.Length > 0)
            {
                var rows = sheet.GetRowEnumerator();
                List<ICell> cellList = null;
                List<string> rowValueList = null;

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

                    cellList = row.Cells;
                    rowValueList = new List<string>(cellList.Count);
                    foreach (var cell in cellList)
                    {
                        rowValueList.Add(cell.ToString());
                    }

                    bool isTitle = true;
                    foreach (var title in titleNames)
                    {
                        if (!rowValueList.Contains(title))
                        {
                            isTitle = false;
                            break;
                        }
                    }
                    if (isTitle)
                    {
                        titleIndex = row.RowNum;
                        break;
                    }
                }
            }
            return titleIndex;
        }

        #endregion

    }

View Code

　　2-ExcelCellExpressReadAttribute 單元格表達(dá)式讀取特性：

/// <summary>
    /// Excel單元格-表達(dá)式讀取-標(biāo)記特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelCellExpressReadAttribute : System.Attribute
    {
        /// <summary>
        /// 讀取數(shù)據(jù)使用：該參數(shù)使用表達(dá)式生成數(shù)據(jù)（Excel文件中支持的表達(dá)式均可以，可以是單元格位置也可以是表達(dá)式（如：A1,B2,C1+C2...））
        /// </summary>
        public string CellCoordinateExpress { get; set; }

        /// <summary>
        /// 字符輸出格式（數(shù)字和日期類型需要）
        /// </summary>
        public string OutputFormat { get; set; }

        /// <summary>
        /// 生成單元格表達(dá)式讀取特性
        /// </summary>
        /// <param name="cellCoordinateExpress">初始單元格表達(dá)式</param>
        /// <param name="outputFormat">（可選）格式化字符串</param>
        public ExcelCellExpressReadAttribute(string cellCoordinateExpress, string outputFormat = "")
        {
            this.CellCoordinateExpress = cellCoordinateExpress;
            this.OutputFormat = outputFormat;
        }
    }

View Code

　　3-ExcelCellFieldMapper 單元格字段映射類

/// <summary>
    /// 單元格字段映射類
    /// </summary>
    internal class ExcelCellFieldMapper
    {
        /// <summary>
        /// 屬性信息(一個(gè)屬性可以添加一個(gè)表達(dá)式讀取，多個(gè)變量替換和多個(gè)坐標(biāo)寫入)
        /// </summary>
        public PropertyInfo PropertyInfo { get; set; }

        /// <summary>
        /// 單元格—表達(dá)式讀取（單元格坐標(biāo)表達(dá)式（如：A1,B2,C1+C2...橫坐標(biāo)使用26進(jìn)制字母，縱坐標(biāo)使用十進(jìn)制數(shù)字））
        /// </summary>
        public ExcelCellExpressReadAttribute CellExpressRead { get; set; }

        /// <summary>
        /// 單元格—模板文件的預(yù)定義變量寫入（{A} {B}）
        /// </summary>
        public List<ExcelCellParamWriteAttribute> CellParamWriteList { get; set; }

        /// <summary>
        /// 單元格—坐標(biāo)位置寫入（(0,0),(1,1)）
        /// </summary>
        public List<ExcelCellPointWriteAttribute> CellPointWriteList { get; set; }

        /// <summary>
        /// 獲取對應(yīng)關(guān)系_T屬性添加了單元格映射關(guān)系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<ExcelCellFieldMapper> GetModelFieldMapper<T>()
        {
            List<ExcelCellFieldMapper> fieldMapperList = new List<ExcelCellFieldMapper>(100);

            List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
            ExcelCellExpressReadAttribute cellExpress = null;
            List<ExcelCellParamWriteAttribute> cellParamWriteList = null;
            List<ExcelCellPointWriteAttribute> cellPointWriteList = null;
            foreach (var item in tPropertyInfoList)
            {
                cellExpress = item.GetCustomAttribute<ExcelCellExpressReadAttribute>();
                cellParamWriteList = item.GetCustomAttributes<ExcelCellParamWriteAttribute>().ToList();
                cellPointWriteList = item.GetCustomAttributes<ExcelCellPointWriteAttribute>().ToList();
                if (cellExpress != null || cellParamWriteList.Count > 0 || cellPointWriteList.Count > 0)
                {
                    fieldMapperList.Add(new ExcelCellFieldMapper
                    {
                        CellExpressRead = cellExpress,
                        CellParamWriteList = cellParamWriteList,
                        CellPointWriteList = cellPointWriteList,
                        PropertyInfo = item
                    });
                }
            }

            return fieldMapperList;
        }
    }

View Code

　　4-ExcelCellParamWriteAttribute Excel單元格-模板參數(shù)寫入-標(biāo)記特性

/// <summary>
    /// Excel單元格-模板參數(shù)寫入-標(biāo)記特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = true)]
    public class ExcelCellParamWriteAttribute : System.Attribute
    {
        /// <summary>
        /// 模板文件的預(yù)定義變量使用（{A} {B}）
        /// </summary>
        public string CellParamName { get; set; }

        /// <summary>
        /// 字符輸出格式（數(shù)字和日期類型需要）
        /// </summary>
        public string OutputFormat { get; set; }

        public ExcelCellParamWriteAttribute(string cellParamName, string outputFormat = "")
        {
            CellParamName = cellParamName;
            OutputFormat = outputFormat;
        }


    }

View Code

　　5-ExcelCellPointWriteAttribute Excel單元格-表達(dá)式讀取-標(biāo)記特性

/// <summary>
    /// Excel單元格-表達(dá)式讀取-標(biāo)記特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = true)]
    public class ExcelCellPointWriteAttribute : System.Attribute
    {
        /// <summary>
        /// 單元格位置（A3,B4...）
        /// </summary>
        public string CellPosition { get; set; }

        /// <summary>
        /// 字符輸出格式（數(shù)字和日期類型需要）
        /// </summary>
        public string OutputFormat { get; set; }


        public ExcelCellPointWriteAttribute(string cellPosition, string outputFormat = null)
        {
            CellPosition = cellPosition;
            OutputFormat = outputFormat;
        }
    }

View Code

　　6-ExcelFileDescription Excel文件描述類，含有深度更新策略

public class ExcelFileDescription
    {
        public ExcelFileDescription(int titleRowIndex)
        {
            this.TitleRowIndex = titleRowIndex;
        }

        public ExcelFileDescription(IExcelDeepUpdate excelDeepUpdate)
        {
            this.ExcelDeepUpdateList = new List<IExcelDeepUpdate> { excelDeepUpdate };
        }
        public ExcelFileDescription(List<IExcelDeepUpdate> excelDeepUpdateList)
        {
            this.ExcelDeepUpdateList = excelDeepUpdateList;
        }

        /// <summary>
        /// 標(biāo)題所在行位置（默認(rèn)為0，沒有標(biāo)題填-1）
        /// </summary>
        public int TitleRowIndex { get; set; }

        /// <summary>
        /// Excel深度更新策略
        /// </summary>
        public List<IExcelDeepUpdate> ExcelDeepUpdateList { get; set; }

    }

View Code

　　7-ExcelTitleAttribute Excel標(biāo)題標(biāo)記特性

/// <summary>
    /// Excel標(biāo)題標(biāo)記特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelTitleAttribute : System.Attribute
    {
        /// <summary>
        /// Excel行標(biāo)題(標(biāo)題和下標(biāo)選擇一個(gè)即可)
        /// </summary>
        public string RowTitle { get; set; }
        /// <summary>
        /// Excel行下標(biāo)(標(biāo)題和下標(biāo)選擇一個(gè)即可,默認(rèn)值-1)
        /// </summary>
        public int RowTitleIndex { get; set; }

        /// <summary>
        /// 單元格是否要檢查空數(shù)據(jù)（true為檢查，為空的行數(shù)據(jù)不添加）
        /// </summary>
        public bool IsCheckContentEmpty { get; set; }

        /// <summary>
        /// 字符輸出格式（數(shù)字和日期類型需要）
        /// </summary>
        public string OutputFormat { get; set; }

        /// <summary>
        /// 是否是公式列
        /// </summary>
        public bool IsCoordinateExpress { get; set; }

        /// <summary>
        /// 標(biāo)題特性構(gòu)造方法
        /// </summary>
        /// <param name="title">標(biāo)題</param>
        /// <param name="isCheckEmpty">單元格是否要檢查空數(shù)據(jù)</param>
        /// <param name="isCoordinateExpress">是否是公式列</param>
        /// <param name="outputFormat">是否有格式化輸出要求</param>
        public ExcelTitleAttribute(string title, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "")
        {
            RowTitle = title;
            IsCheckContentEmpty = isCheckEmpty;
            IsCoordinateExpress = isCoordinateExpress;
            OutputFormat = outputFormat;
            RowTitleIndex = -1;
        }

        public ExcelTitleAttribute(int titleIndex, bool isCheckEmpty = false, bool isCoordinateExpress = false, string outputFormat = "")
        {
            RowTitleIndex = titleIndex;
            IsCheckContentEmpty = isCheckEmpty;
            IsCoordinateExpress = isCoordinateExpress;
            OutputFormat = outputFormat;
        }
    }

View Code

　　8-ExcelTitleFieldMapper 標(biāo)題字段映射類

/// <summary>
    /// 標(biāo)題字段映射類
    /// </summary>
    internal class ExcelTitleFieldMapper
    {
        /// <summary>
        /// 屬性信息
        /// </summary>
        public PropertyInfo PropertyInfo { get; set; }
        /// <summary>
        /// 行標(biāo)題
        /// </summary>
        public string ExcelTitle { get; set; }
        /// <summary>
        /// 行標(biāo)題下標(biāo)位置
        /// </summary>
        public int ExcelTitleIndex { get; set; }
        /// <summary>
        /// 是否要做行內(nèi)容空檢查
        /// </summary>
        public bool IsCheckContentEmpty { get; set; }

        /// <summary>
        /// 字符輸出格式（數(shù)字和日期類型需要）
        /// </summary>
        public string OutputFormat { get; set; }

        /// <summary>
        /// 是否是公式列
        /// </summary>
        public bool IsCoordinateExpress { get; set; }

        /// <summary>
        /// 獲取對應(yīng)關(guān)系_T屬性添加了標(biāo)題映射關(guān)系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>()
        {
            List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100);

            List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
            ExcelTitleAttribute excelTitleAttribute = null;
            foreach (var tPropertyInfo in tPropertyInfoList)
            {
                excelTitleAttribute = (ExcelTitleAttribute)tPropertyInfo.GetCustomAttribute(typeof(ExcelTitleAttribute));
                
                if (excelTitleAttribute != null)
                {
                    fieldMapperList.Add(new ExcelTitleFieldMapper
                    {
                        PropertyInfo = tPropertyInfo,
                        ExcelTitle = excelTitleAttribute.RowTitle,
                        ExcelTitleIndex = excelTitleAttribute.RowTitleIndex,
                        IsCheckContentEmpty = excelTitleAttribute.IsCheckContentEmpty,
                        OutputFormat = excelTitleAttribute.OutputFormat,
                        IsCoordinateExpress = excelTitleAttribute.IsCoordinateExpress
                    });
                }
            }
            return fieldMapperList;
        }

        /// <summary>
        /// 獲取對應(yīng)關(guān)系_手動(dòng)提供映射關(guān)系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fieldNameAndShowNameDic"></param>
        /// <returns></returns>
        public static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>(Dictionary<string, string> fieldNameAndShowNameDic)
        {
            List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100);

            List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
            PropertyInfo propertyInfo = null;

            foreach (var item in fieldNameAndShowNameDic)
            {
                propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key, StringComparison.OrdinalIgnoreCase));

                fieldMapperList.Add(new ExcelTitleFieldMapper
                {
                    PropertyInfo = propertyInfo,
                    ExcelTitle = item.Value,
                    ExcelTitleIndex = -1,
                    OutputFormat = null,
                    IsCheckContentEmpty = false,
                    IsCoordinateExpress = false
                });
            }
            return fieldMapperList;
        }

        /// <summary>
        /// 獲取對應(yīng)關(guān)系_未提供（默認(rèn)屬性名和標(biāo)題名一致）
        /// </summary>
        /// <returns></returns>
        public static List<ExcelTitleFieldMapper> GetModelDefaultFieldMapper<T>()
        {
            List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>(100);

            List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();

            foreach (var item in tPropertyInfoList)
            {
                fieldMapperList.Add(new ExcelTitleFieldMapper
                {
                    PropertyInfo = item,
                    ExcelTitle = item.Name,
                    ExcelTitleIndex = -1,
                    OutputFormat = null,
                    IsCheckContentEmpty = false,
                    IsCoordinateExpress = false
                });
            }
            return fieldMapperList;
        }

    }

View Code

　　接口封裝類：

　　a-CellFactory 單元格工廠類

/// <summary>
    /// 單元格工廠類
    /// </summary>
    public class CellFactory
    {
        private static Regex _CellPostionRegex = new Regex("[A-Z]+\\d+");
        private static Regex _RowRegex = new Regex("\\d+");

        /// <summary>
        /// 通過Excel單元格坐標(biāo)位置初始化對象
        /// </summary>
        /// <param name="excelCellPosition">A1,B2等等</param>
        /// <returns></returns>
        public static ICellModel GetCellByExcelPosition(string excelCellPosition)
        {
            CellModel cellModel = null;

            bool isMatch = CellFactory._CellPostionRegex.IsMatch(excelCellPosition);
            if (isMatch)
            {
                Match rowMath = CellFactory._RowRegex.Match(excelCellPosition);
                int rowPositon = Convert.ToInt32(rowMath.Value);
                int rowIndex = rowPositon - 1;
                int columnIndex = CellFactory.GetExcelColumnIndex(excelCellPosition.Replace(rowPositon.ToString(), ""));

                cellModel = new CellModel(rowIndex, columnIndex);
            }
            return cellModel;
        }

        /// <summary>
        /// 將數(shù)據(jù)放入單元格中
        /// </summary>
        /// <param name="cell">單元格對象</param>
        /// <param name="cellValue">數(shù)據(jù)</param>
        /// <param name="outputFormat">格式化字符串</param>
        /// <param name="isCoordinateExpress">是否是表達(dá)式數(shù)據(jù)</param>
        public static void SetCellValue(ICell cell, object cellValue, string outputFormat, bool isCoordinateExpress)
        {
            if (cell != null)
            {
                if (isCoordinateExpress)
                {
                    cell.SetCellFormula(cellValue.ToString());
                }
                else
                {
                    if (!string.IsNullOrEmpty(outputFormat))
                    {
                        string formatValue = null;
                        IFormatProvider formatProvider = null;
                        if (cellValue is DateTime)
                        {
                            formatProvider = new DateTimeFormatInfo();
                            ((DateTimeFormatInfo)formatProvider).ShortDatePattern = outputFormat;
                        }
                        formatValue = ((IFormattable)cellValue).ToString(outputFormat, formatProvider);

                        cell.SetCellValue(formatValue);
                    }
                    else
                    {
                        if (cellValue is decimal || cellValue is double || cellValue is int)
                        {
                            cell.SetCellValue(Convert.ToDouble(cellValue));
                        }
                        else if (cellValue is DateTime)
                        {
                            cell.SetCellValue((DateTime)cellValue);
                        }
                        else if (cellValue is bool)
                        {
                            cell.SetCellValue((bool)cellValue);
                        }
                        else
                        {
                            cell.SetCellValue(cellValue.ToString());
                        }
                    }
                }
            }

        }

        public static void SetDeepUpdateCellValue(ISheet sheet, int rowIndex, int columnIndex, object cellValue, string outputFormat, bool isCoordinateExpress, List<IExcelCellPointDeepUpdate> excelDeepUpdateList)
        {
            if (sheet != null)
            {
                //更新起始單元格數(shù)據(jù)
                ICell nextCell = ExcelHelper.GetOrCreateCell(sheet, rowIndex, columnIndex);
                CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress);

                #region 執(zhí)行單元格深度更新策略

                ICellModel startCellPosition = new CellModel
                {
                    RowIndex = rowIndex,
                    ColumnIndex = columnIndex
                };

                ICellModel nextCellPosition = null;
                Action<IExcelCellPointDeepUpdate> actionDeepUpdateAction = (excelDeepUpdate) =>
                {
                    //獲取起始執(zhí)行單元格位置
                    nextCellPosition = excelDeepUpdate.GetNextCellPoint(startCellPosition);

                    //執(zhí)行深度更新，一直到找不到下個(gè)單元格為止
                    do
                    {
                        nextCell = ExcelHelper.GetOrCreateCell(sheet, nextCellPosition.RowIndex, nextCellPosition.ColumnIndex);
                        if (nextCell != null)
                        {
                            CellFactory.SetCellValue(nextCell, cellValue, outputFormat, isCoordinateExpress);
                            nextCellPosition = excelDeepUpdate.GetNextCellPoint(nextCellPosition);
                        }
                    } while (nextCell != null);
                };

                foreach (var excelDeepUpdate in excelDeepUpdateList)
                {
                    actionDeepUpdateAction(excelDeepUpdate);
                }

                #endregion

            }

        }


        /// <summary>
        /// 數(shù)字轉(zhuǎn)字母
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public static string GetExcelColumnPosition(int number)
        {
            var a = number / 26;
            var b = number % 26;

            if (a > 0)
            {
                return CellFactory.GetExcelColumnPosition(a - 1) + (char)(b + 65);
            }
            else
            {
                return ((char)(b + 65)).ToString();
            }
        }

        /// <summary>
        /// 字母轉(zhuǎn)數(shù)字
        /// </summary>
        /// <param name="columnPosition"></param>
        /// <returns></returns>
        public static int GetExcelColumnIndex(string zm)
        {
            int index = 0;
            char[] chars = zm.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1;
        }

    }

View Code

　　b-CellModel 單元格定義類

public class CellModel : ICellModel
    {
        public int RowIndex { get; set; }
        public int ColumnIndex { get; set; }
        public object CellValue { get; set; }

        public bool IsCellFormula { get; set; }

        public CellModel() { }

        /// <summary>
        /// 默認(rèn)初始化對象
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="cellValue"></param>
        public CellModel(int rowIndex, int columnIndex, object cellValue = default(object)) : this(rowIndex, columnIndex, cellValue, false)
        {
        }

        /// <summary>
        /// 默認(rèn)初始化對象
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <param name="cellValue"></param>
        /// <param name="isCellFormula"></param>
        public CellModel(int rowIndex, int columnIndex, object cellValue, bool isCellFormula)
        {
            this.RowIndex = rowIndex;
            this.ColumnIndex = columnIndex;
            this.CellValue = cellValue;
            this.IsCellFormula = isCellFormula;
        }

        /// <summary>
        /// 獲取單元格位置
        /// </summary>
        /// <returns></returns>
        public string GetCellPosition()
        {
            return CellFactory.GetExcelColumnPosition(this.ColumnIndex) + (this.RowIndex + 1).ToString();
        }
    }

    public class CellModelColl : List<CellModel>, IList<CellModel>
    {
        public CellModelColl() { }
        public CellModelColl(int capacity) : base(capacity)
        {

        }

        /// <summary>
        /// 根據(jù)行下標(biāo)，列下標(biāo)獲取單元格數(shù)據(jù)
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public CellModel this[int rowIndex, int columnIndex]
        {
            get
            {
                CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
                return cell;
            }
            set
            {
                CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
                if (cell != null)
                {
                    cell.CellValue = value.CellValue;
                }
            }
        }

        public CellModel CreateOrGetCell(int rowIndex, int columnIndex)
        {
            CellModel cellModel = this[rowIndex, columnIndex];
            if (cellModel == null)
            {
                cellModel = new CellModel()
                {
                    RowIndex = rowIndex,
                    ColumnIndex = columnIndex
                };
                this.Add(cellModel);
            }
            return cellModel;
        }

        public CellModel GetCell(string cellStringValue)
        {
            CellModel cellModel = null;

            cellModel = this.FirstOrDefault(m => m.CellValue.ToString().Equals(cellStringValue, System.StringComparison.OrdinalIgnoreCase));

            return cellModel;
        }

        /// <summary>
        /// 所有一行所有的單元格數(shù)據(jù)
        /// </summary>
        /// <param name="rowIndex">行下標(biāo)</param>
        /// <returns></returns>
        public List<CellModel> GetRawCellList(int rowIndex)
        {
            List<CellModel> cellList = null;
            cellList = this.FindAll(m => m.RowIndex == rowIndex);

            return cellList ?? new List<CellModel>(0);
        }

        /// <summary>
        /// 所有一列所有的單元格數(shù)據(jù)
        /// </summary>
        /// <param name="columnIndex">列下標(biāo)</param>
        /// <returns></returns>
        public List<CellModel> GetColumnCellList(int columnIndex)
        {
            List<CellModel> cellList = null;
            cellList = this.FindAll(m => m.ColumnIndex == columnIndex);

            return cellList ?? new List<CellModel>(0);
        }

    }

View Code

　　c-ExcelCellExpressDeepUpdate<T> 單元格表達(dá)式深度更新類

public class ExcelCellExpressDeepUpdate<T> : IExcelCellExpressDeepUpdate<T>
        {
            private Regex cellPointRegex = new Regex("[A-Z]+[0-9]+");

            private Action<ICellModel> updateCellPointFunc { get; set; }
            public Func<T, bool> CheckContinuteFunc { get; set; }

            public ExcelCellExpressDeepUpdate(Action<ICellModel> updateCellPointFunc, Func<T, bool> checkIsContinuteFunc)
            {
                this.updateCellPointFunc = updateCellPointFunc;
                this.CheckContinuteFunc = checkIsContinuteFunc;
            }

            public bool IsContinute(T t)
            {
                return this.CheckContinuteFunc(t);
            }

            public string GetNextCellExpress(string currentExpress)
            {
                string nextCellExpress = currentExpress;

                List<ICellModel> cellModelList = this.GetCellModelList(currentExpress);
                string oldPointStr = null;
                string newPointStr = null;
                foreach (var item in cellModelList)
                {
                    oldPointStr = item.GetCellPosition();
                    this.updateCellPointFunc(item);
                    newPointStr = item.GetCellPosition();

                    nextCellExpress = nextCellExpress.Replace(oldPointStr, newPointStr);
                }
                return nextCellExpress;
            }


            private List<ICellModel> GetCellModelList(string cellExpress)
            {
                List<ICellModel> cellModelList = new List<ICellModel>(100);
                MatchCollection matchCollection = this.cellPointRegex.Matches(cellExpress);

                foreach (Match matchItem in matchCollection)
                {
                    cellModelList.Add(CellFactory.GetCellByExcelPosition(matchItem.Value));
                }
                return cellModelList;
            }

        }

View Code

　　d-ExcelCellPointDeepUpdate 單元格坐標(biāo)深度更新類

public class ExcelCellPointDeepUpdate : IExcelCellPointDeepUpdate
    {
        private Action<ICellModel> updateCellPointFunc { get; set; }


        public ExcelCellPointDeepUpdate(Action<ICellModel> updateCellPointFunc)
        {
            this.updateCellPointFunc = updateCellPointFunc;
        }

        public ICellModel GetNextCellPoint(ICellModel cellModel)
        {
            ICellModel nextCell = null;

            ICellModel cell = new CellModel(cellModel.RowIndex, cellModel.ColumnIndex);
            if (cellModel != null && this.updateCellPointFunc != null)
            {
                this.updateCellPointFunc(cell);
                if (cell.RowIndex != cellModel.RowIndex || cell.ColumnIndex != cellModel.ColumnIndex)
                {
                    nextCell = cell;
                }
            }

            return nextCell;
        }

    }

View Code

　　e-ICellModel 單元格抽象接口

public interface ICellModel
    {
        int RowIndex { get; set; }
        int ColumnIndex { get; set; }
        object CellValue { get; set; }

        bool IsCellFormula { get; set; }

        string GetCellPosition();

    }

View Code

　　f-IExcelCellDeepUpdate 單元格深度更新接口

/// <summary>
    /// 單元格深度更新接口
    /// </summary>
    public interface IExcelCellDeepUpdate : IExcelDeepUpdate
    {
    }

View Code

　　g-IExcelCellExpressDeepUpdate<T> 單元格表達(dá)式深度更新接口

public interface IExcelCellExpressDeepUpdate<T> : IExcelCellDeepUpdate
    {
        string GetNextCellExpress(string currentExpress);
        bool IsContinute(T t);

    }

View Code

　　h-IExcelCellPointDeepUpdate 單元格坐標(biāo)深度更新接口

/// <summary>
    /// 單元格坐標(biāo)深度更新接口
    /// </summary>
    public interface IExcelCellPointDeepUpdate : IExcelCellDeepUpdate
    {
        ICellModel GetNextCellPoint(ICellModel cellModel);
    }

View Code

　　i-IExcelDeepUpdate Excel深度更新大抽象接口

/// <summary>
    /// Excel深度更新策略接口
    /// </summary>
    public interface IExcelDeepUpdate
    {
    }

View Code

　　j-IExcelTitleDeepUpdate Excel標(biāo)題深度更新接口

/// <summary>
    /// Excel標(biāo)題深度更新策略
    /// </summary>
    public interface IExcelTitleDeepUpdate : IExcelDeepUpdate
    {
    }

View Code

　　深度更新使用示例一：

string path = @"C:\Users\Administrator\Desktop\控制臺測試\Test\WebApplication1\WebApplication1\2020年2月 paypal憑證.xlsx";
            ExcelFileDescription excelFileDescription = new ExcelFileDescription(new ExcelCellExpressDeepUpdate<AccountMultiCurrencyTransactionSource_Summary>(m => m.RowIndex += 15, m => m.BeginingBalance > 0));
            IWorkbook workbook = ExcelHelper.GetExcelWorkbook(path);
            ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName: "chictoo+7");
            List<AccountMultiCurrencyTransactionSource_Summary> dataList = ExcelHelper.ReadCellData<AccountMultiCurrencyTransactionSource_Summary>(workbook, sheet, excelFileDescription);

/// <summary>
    /// 賬戶_多幣種交易報(bào)表_數(shù)據(jù)源
    /// </summary>
    public class AccountMultiCurrencyTransactionSource_Summary
    {
        [ExcelCellExpressRead("A2")]
        public string AccountName { get; set; }

        /// <summary>
        /// 期初
        /// </summary>
        [ExcelCellExpressReadAttribute("B3")]
        public double BeginingBalance { get; set; }
/// <summary>
        /// 收款
        /// </summary>
        [ExcelCellExpressReadAttribute("B4")]
        [ExcelTitle(3)]
        public double TotalTransactionPrice { get; set; }
}

　　總結(jié)：時(shí)間有限，沒有來得及進(jìn)行深度的抽象和優(yōu)化，優(yōu)化有機(jī)會(huì)再繼續(xù)吧?！?/p>

/// <summary>
    /// 賬戶_多幣種交易報(bào)表_數(shù)據(jù)源
    /// </summary>
    public class AccountMultiCurrencyTransactionSource_Summary
    {
        [ExcelCellExpressRead("A2")]
        public string AccountName { get; set; }

        /// <summary>
        /// 期初
        /// </summary>
        [ExcelCellExpressReadAttribute("B3")]
        public double BeginingBalance { get; set; }
/// <summary>
        /// 收款
        /// </summary>
        [ExcelCellExpressReadAttribute("B4")]
        [ExcelTitle(3)]
        public double TotalTransactionPrice { get; set; }
}

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：行者花雕 > 《待分類》

舉報(bào)/認(rèn)領(lǐng)