C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&使用excel表达式收集数据&单元格映射&标题映射&模板文件的参数数据替换（第二版-增加深度读取和更新功能）

　　前言：新的的封装类，增加了单元格映射深度更新和读取的功能，预留了标题映射的深度更新接口待扩展。。。（以后有时间和精力再完善吧）

　　【深度更新】：我这里定义的深度更新策略，指的是：假如我们需要读取一组单元格的映射数据为一个对象，但是有不止一组这样的单元格数据对象，且这些对象的单元格位置排列是有规律的！

如：我要收集一个对象，在A1,A2，B1,B2的位置组成的一个数据对象，下一个对象位置在：  A5,C6,B5,B6的位置，同理。。。

　　前面的文章介绍了使用单元格映射关系，我可以顺利收集到其中一个对象，但是我不可能把所有的单元格都建立对象关联起来，且数据又不符合标题行数据映射；那么就提出了一个新的策略，我这里叫：深度更新表达式读取策略。

 

　　下面放置完整代码，这版本做了深度更新的接口的抽象和封装，类有点多：

 

　　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>
    /// 根据单元格表达式和单元格数据集获取数据
    /// </summary>
    /// <param name="cellExpress">单元格表达式</param>
    /// <param name="workbook">excel工作文件</param>
    /// <param name="currentSheet">当前sheet</param>
    /// <returns></returns>
    public static object GetVByExpress(string cellExpress, IWorkbook workbook, ISheet currentSheet)
    {
        object value = null;

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

            //创建临时行，单元格，执行表达式运算；
            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 创建工作表

    /// <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)
    {
        CellModelColl cellModelColl = new CellModelColl(0);

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

        //补全标题行映射数据的标题和下标位置映射关系
        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页，则手动初始化下标关系
            for (int i = 0; i < titleMapperList.Count; i++)
            {
                titleMapperList[i].ExcelTitleIndex = i;
            }
        }

        int currentRowIndex = defaultBeginTitleIndex;
        //添加标题单元格数据
        foreach (var titleMapper in titleMapperList)
        {
            cellModelColl.Add(new CellModel
            {
                RowIndex = defaultBeginTitleIndex,
                ColumnIndex = titleMapper.ExcelTitleIndex,
                CellValue = titleMapper.ExcelTitle,
                IsCellFormula = false
            });
        }
        currentRowIndex++;

        //将标题行数据转出单元格数据
        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>
    /// 将单元格数据列表生成工作表
    /// </summary>
    /// <param name="commonCellList">所有的单元格数据列表</param>
    /// <param name="workbook">更新时添加：要更新的工作表</param>
    /// <param name="sheetName">指定要创建的sheet名称时添加</param>
    /// <returns></returns>
    public static IWorkbook CreateOrUpdateWorkbook(CellModelColl 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 = ExcelHelper.GetExcelWorkbookSheet(workbook, sheetName);

        if (worksheet != null && commonCellList != null && commonCellList.Count > 0)
        {
            //设置首列显示
            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);

            //设置表达式重算（如果不添加该代码，表达式更新不出结果值）
            worksheet.ForceFormulaRecalculation = true;
        }

        return workbook;
    }

    /// <summary>
    /// 更新模板文件数据：将使用单元格映射的数据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)
    {
        //该方法默认替换模板数据在首个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数据为文件
    /// </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.Substring(0, filePath.LastIndexOf("\\") + 1);
        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到字节流中(提供给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数据

    /// <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);
        if (!File.Exists(filePath))
        {
            return tList;
        }
        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);

                //标题下标属性字典
                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;

                if (currentSheetRowTitleIndex >= 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;
                                if (propertyInfo != null && propertyInfo.CanWrite)
                                {
                                    try
                                    {
                                        if (excelTitleFieldMapper.IsCheckContentEmpty)
                                        {
                                            if (cell.CellType == CellType.Formula)
                                            {
                                                cellValue = formulaEvaluator.Evaluate(cell).StringValue;
                                            }
                                            if (string.IsNullOrEmpty(cellValue))
                                            {
                                                t = default(T);
                                                break;
                                            }
                                        }

                                        if (excelTitleFieldMapper.IsCoordinateExpress || (cell != null && 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);
        }
        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行数据开始获取
            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>
    /// 获取文件单元格数据对象
    /// </summary>
    /// <typeparam name="T">T的属性必须标记了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>
    /// 读取单元格数据对象列表-支持深度读取
    /// </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 获取深度表达式更新列表

        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 通过表达式映射列表读取对象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 执行初始表达式数据收集

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

        #endregion

        #region 执行深度更新策略收集数据

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

            //执行单元格表达式深度更新

            bool isContinute = false;

            do
            {
                //通过深度更新策略更新初始表达式数据
                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>
    /// 获取文件首个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))
        {
            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>
    /// 获取文件首个sheet的标题位置
    /// </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>
    /// 根据标题名称获取标题行下标位置
    /// </summary>
    /// <param name="sheet">要查找的sheet</param>
    /// <param name="titleNames">标题名称</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行数据开始获取
            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

}

　　2-ExcelCellExpressReadAttribute  单元格表达式读取特性：

/// <summary>
    /// Excel单元格-表达式读取-标记特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelCellExpressReadAttribute : System.Attribute
    {
        /// <summary>
        /// 读取数据使用：该参数使用表达式生成数据（Excel文件中支持的表达式均可以，可以是单元格位置也可以是表达式（如：A1,B2,C1+C2...））
        /// </summary>
        public string CellCoordinateExpress { get; set; }

        /// <summary>
        /// 字符输出格式（数字和日期类型需要）
        /// </summary>
        public string OutputFormat { get; set; }

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

　　3-ExcelCellFieldMapper  单元格字段映射类

/// <summary>
    /// 单元格字段映射类
    /// </summary>
    internal class ExcelCellFieldMapper
    {
        /// <summary>
        /// 属性信息(一个属性可以添加一个表达式读取，多个变量替换和多个坐标写入)
        /// </summary>
        public PropertyInfo PropertyInfo { get; set; }

        /// <summary>
        /// 单元格—表达式读取（单元格坐标表达式（如：A1,B2,C1+C2...横坐标使用26进制字母，纵坐标使用十进制数字））
        /// </summary>
        public ExcelCellExpressReadAttribute CellExpressRead { get; set; }

        /// <summary>
        /// 单元格—模板文件的预定义变量写入（{A} {B}）
        /// </summary>
        public List<ExcelCellParamWriteAttribute> CellParamWriteList { get; set; }

        /// <summary>
        /// 单元格—坐标位置写入（(0,0),(1,1)）
        /// </summary>
        public List<ExcelCellPointWriteAttribute> CellPointWriteList { get; set; }

        /// <summary>
        /// 获取对应关系_T属性添加了单元格映射关系
        /// </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;
        }
    }

　　4-ExcelCellParamWriteAttribute  Excel单元格-模板参数写入-标记特性

/// <summary>
    /// Excel单元格-模板参数写入-标记特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = true)]
    public class ExcelCellParamWriteAttribute : System.Attribute
    {
        /// <summary>
        /// 模板文件的预定义变量使用（{A} {B}）
        /// </summary>
        public string CellParamName { get; set; }

        /// <summary>
        /// 字符输出格式（数字和日期类型需要）
        /// </summary>
        public string OutputFormat { get; set; }

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


    }

　　5-ExcelCellPointWriteAttribute  Excel单元格-表达式读取-标记特性

/// <summary>
    /// Excel单元格-表达式读取-标记特性
    /// </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>
        /// 字符输出格式（数字和日期类型需要）
        /// </summary>
        public string OutputFormat { get; set; }


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

　　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>
        /// 标题所在行位置（默认为0，没有标题填-1）
        /// </summary>
        public int TitleRowIndex { get; set; }

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

    }

　　7-ExcelTitleAttribute  Excel标题标记特性

/// <summary>
    /// Excel标题标记特性
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)]
    public class ExcelTitleAttribute : System.Attribute
    {
        /// <summary>
        /// Excel行标题(标题和下标选择一个即可)
        /// </summary>
        public string RowTitle { get; set; }
        /// <summary>
        /// Excel行下标(标题和下标选择一个即可,默认值-1)
        /// </summary>
        public int RowTitleIndex { get; set; }

        /// <summary>
        /// 单元格是否要检查空数据（true为检查，为空的行数据不添加）
        /// </summary>
        public bool IsCheckContentEmpty { get; set; }

        /// <summary>
        /// 字符输出格式（数字和日期类型需要）
        /// </summary>
        public string OutputFormat { get; set; }

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

        /// <summary>
        /// 标题特性构造方法
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="isCheckEmpty">单元格是否要检查空数据</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;
        }
    }

　　8-ExcelTitleFieldMapper  标题字段映射类

/// <summary>
    /// 标题字段映射类
    /// </summary>
    internal class ExcelTitleFieldMapper
    {
        /// <summary>
        /// 属性信息
        /// </summary>
        public PropertyInfo PropertyInfo { get; set; }
        /// <summary>
        /// 行标题
        /// </summary>
        public string ExcelTitle { get; set; }
        /// <summary>
        /// 行标题下标位置
        /// </summary>
        public int ExcelTitleIndex { get; set; }
        /// <summary>
        /// 是否要做行内容空检查
        /// </summary>
        public bool IsCheckContentEmpty { get; set; }

        /// <summary>
        /// 字符输出格式（数字和日期类型需要）
        /// </summary>
        public string OutputFormat { get; set; }

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

        /// <summary>
        /// 获取对应关系_T属性添加了标题映射关系
        /// </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>
        /// 获取对应关系_手动提供映射关系
        /// </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>
        /// 获取对应关系_未提供（默认属性名和标题名一致）
        /// </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;
        }

    }

　　接口封装类：

　　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单元格坐标位置初始化对象
        /// </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>
        /// 将数据放入单元格中
        /// </summary>
        /// <param name="cell">单元格对象</param>
        /// <param name="cellValue">数据</param>
        /// <param name="outputFormat">格式化字符串</param>
        /// <param name="isCoordinateExpress">是否是表达式数据</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
                        {
                            string cellValueStr = cellValue.ToString();
                            if (cellValueStr.Length > 32767)
                            {
                                cellValueStr = cellValueStr.Substring(0, 32764) + "...";
                            }
                            cell.SetCellValue(cellValueStr);
                        }
                    }
                }
            }

        }

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

                #region 执行单元格深度更新策略

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

                ICellModel nextCellPosition = null;
                Action<IExcelCellPointDeepUpdate> actionDeepUpdateAction = (excelDeepUpdate) =>
                {
                    //获取起始执行单元格位置
                    nextCellPosition = excelDeepUpdate.GetNextCellPoint(startCellPosition);

                    //执行深度更新，一直到找不到下个单元格为止
                    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>
        /// 数字转字母
        /// </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>
        /// 字母转数字
        /// </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;
        }

    }

　　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>
        /// 默认初始化对象
        /// </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>
        /// 默认初始化对象
        /// </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>
        /// 根据行下标，列下标获取单元格数据
        /// </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>
        /// 所有一行所有的单元格数据
        /// </summary>
        /// <param name="rowIndex">行下标</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>
        /// 所有一列所有的单元格数据
        /// </summary>
        /// <param name="columnIndex">列下标</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);
        }

    }

　　c-ExcelCellExpressDeepUpdate<T> 单元格表达式深度更新类

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;
            }

        }

　　d-ExcelCellPointDeepUpdate 单元格坐标深度更新类

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;
        }

    }

　　e-ICellModel 单元格抽象接口

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

        bool IsCellFormula { get; set; }

        string GetCellPosition();

    }

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

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

　　g-IExcelCellExpressDeepUpdate<T> 单元格表达式深度更新接口

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

    }

　　h-IExcelCellPointDeepUpdate  单元格坐标深度更新接口

/// <summary>
    /// 单元格坐标深度更新接口
    /// </summary>
    public interface IExcelCellPointDeepUpdate : IExcelCellDeepUpdate
    {
        ICellModel GetNextCellPoint(ICellModel cellModel);
    }

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

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

　　j-IExcelTitleDeepUpdate Excel标题深度更新接口

/// <summary>
    /// Excel标题深度更新策略
    /// </summary>
    public interface IExcelTitleDeepUpdate : IExcelDeepUpdate
    {
    }

 

　　深度更新使用示例一：

　　

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>
    /// 账户_多币种交易报表_数据源
    /// </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; }
}

 

　　总结：时间有限，没有来得及进行深度的抽象和优化，优化有机会再继续吧。　

/// <summary>
    /// 账户_多币种交易报表_数据源
    /// </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; }
}