C#_Excel数据读取与写入_自定义解析封装类_支持设置标题行位置&a

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


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

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

        static ISheet GetExcelWorkbookSheet(IWorkbook workbook,int sheetIndex = 0)
        {
            ISheet sheet = if (workbook != )
            {
                if (sheetIndex >= )
                {
                    sheet = workbook.GetSheetAt(sheetIndex);
                }
            }
             sheet;
        }

        string sheetName = sheet1null && !.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet ==  workbook.CreateSheet(sheetName);
                }
            }
            static IRow GetOrCreateRow(ISheet sheet,1)">int rowIndex)
        {
            IRow row = ;
            if (sheet != )
            {
                row = sheet.GetRow(rowIndex);
                if (row == )
                {
                    row = sheet.CreateRow(rowIndex);
                }
            }
             row;
        }

        static ICell GetOrCreateCell(ISheet sheet,1)">int rowIndex,1)"> columnIndex)
        {
            ICell cell = ;

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

             cell;
        }

        <summary>
         根据单元格表达式和单元格数据集获取数据
        </summary>
        <param name="cellExpress">单元格表达式</param>
        <param name="workbook">excel工作文件<param name="currentSheet">当前sheet<returns></returns>
        static object GetVByExpress( cellExpress,IWorkbook workbook,ISheet currentSheet)
        {
            object value = //含有单元格表达式的取表达式值，没有表达式的取单元格字符串
            if (!string.IsNullOrEmpty(cellExpress) && workbook != null && currentSheet != )
            {
                IFormulaEvaluator formulaEvaluator = ;
                if (workbook is HSSFWorkbook)
                {
                    formulaEvaluator =  HSSFFormulaEvaluator(workbook);
                }
                else
                {
                    formulaEvaluator =  XSSFFormulaEvaluator(workbook);
                }

                创建临时行，单元格，执行表达式运算；
                IRow newRow = currentSheet.CreateRow(currentSheet.LastRowNum + 1);
                ICell cell = newRow.CreateCell();
                cell.SetCellFormula(cellExpress);
                cell = formulaEvaluator.EvaluateInCell(cell);
                value = cell.ToString();

                currentSheet.RemoveRow(newRow);
            }

            return value ?? "";

        }

        #region 创建工作表

         将列表数据生成工作表
        <param name="tList">要导出的数据集<param name="fieldNameAndShowNameDic">键值对集合（键：字段名，值：显示名称）更新时添加：要更新的工作表<param name="sheetName">指定要创建的sheet名称时添加<param name="excelFileDescription">读取或插入定制需求时添加static IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList,Dictionary<string,1)">string> fieldNameAndShowNameDic,IWorkbook workbook = null,1)">",ExcelFileDescription excelFileDescription = null) where T : ()
        {
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);

            workbook = ExcelHelper.CreateOrUpdateWorkbook<T>(tList,titleMapperList,workbook,sheetName,excelFileDescription);
             workbook;
        }
         将列表数据生成工作表（T的属性需要添加：属性名列名映射关系）
        ();

            workbook = ExcelHelper.CreateOrUpdateWorkbook<T>string sheetName,1)">)
        {
            CellModelColl cellModelColl = new CellModelColl();

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

            补全标题行映射数据的标题和下标位置映射关系
            ISheet sheet = ExcelHelper.GetExcelWorkbookSheet(workbook,sheetName: sheetName);
            IRow titleRow = )
            {
                titleRow = sheet.GetRow(defaultBeginTitleIndex);
            }

            if (titleRow != )
            {
                List<ICell> titleCellList = titleRow.Cells;
                foreach (var titleMapper in titleMapperList)
                {
                    if (titleMapper.ExcelTitleIndex < )
                    {
                        var cellItem  titleCellList)
                        {
                            if (cellItem.ToString().Equals(titleMapper.ExcelTitle,StringComparison.OrdinalIgnoreCase))
                            {
                                titleMapper.ExcelTitleIndex = cellItem.ColumnIndex;
                                break;
                            }
                        }
                    }
                    else if (.IsNullOrEmpty(titleMapper.ExcelTitle))
                    {
                        ICell cell = titleRow.GetCell(titleMapper.ExcelTitleIndex);
                        if (cell != )
                        {
                            titleMapper.ExcelTitle = cell.ToString();
                        }
                    }
                }
            }
            如果是新建Sheet页，则手动初始化下标关系
                for (int i = 0; i < titleMapperList.Count; i++)
                {
                    titleMapperList[i].ExcelTitleIndex = i;
                }
            }

            int currentRowIndex = defaultBeginTitleIndex;
            添加标题单元格数据
             titleMapperList)
            {
                cellModelColl.Add( CellModel
                {
                    RowIndex = defaultBeginTitleIndex,ColumnIndex = titleMapper.ExcelTitleIndex,CellValue = titleMapper.ExcelTitle,IsCellFormula = false
                });
            }
            currentRowIndex++将标题行数据转出单元格数据
            var item  tList)
            {
                 titleMapperList)
                {
                    cellModelColl.Add( CellModel
                    {
                        RowIndex = currentRowIndex,1)"> titleMapper.PropertyInfo.GetValue(item),IsCellFormula = titleMapper.IsCoordinateExpress
                    });
                }
                currentRowIndex++;
            }

            workbook = ExcelHelper.CreateOrUpdateWorkbook(cellModelColl,sheetName);

             workbook;
        }

         将单元格数据列表生成工作表
        <param name="commonCellList">所有的单元格数据列表static IWorkbook CreateOrUpdateWorkbook(CellModelColl commonCellList,1)">)
        {
            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 == )
            {
                workbook =  XSSFWorkbook();
                workbook = new HSSFWorkbook();
            }
            ISheet worksheet =if (worksheet != null && commonCellList != null && commonCellList.Count > 设置首列显示
                IRow row1 = int rowIndex = int maxRowIndex = commonCellList.Max(m => m.RowIndex);
                Dictionary<int,CellModel> rowColumnIndexCellDIC = ;
                ICell cell = object cellValue = ;

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

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

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

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

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

             更新模板文件数据：将使用单元格映射的数据T存入模板文件中
        <typeparam name="T"></typeparam>
        <param name="workbook"></param>
        <param name="sheet"></param>
        <param name="t"></param>
        <param name="excelFileDescription"></param>
        static IWorkbook UpdateTemplateWorkbook<T>(IWorkbook workbook,ISheet sheet,T t,1)">该方法默认替换模板数据在首个sheet里

            CellModelColl commonCellColl = ExcelHelper.ReadCellList(workbook,sheet,1)">);

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

            获取t的单元格映射列表
            List<ExcelCellFieldMapper> cellMapperList = ExcelCellFieldMapper.GetModelFieldMapper<T>();
            var cellMapper  cellMapperList)
            {
                if (cellMapper.CellParamWriteList.Count > )
                {
                    var cellParamWriteAttribute  cellMapper.CellParamWriteList)
                    {
                        CellModel cellModel = commonCellColl.GetCell(cellParamWriteAttribute.CellParamName);
                        if (cellModel != )
                        {
                            cellModel.CellValue = cellMapper.PropertyInfo.GetValue(t);
                        }
                    }
                }
                if (cellMapper.CellPointWriteList.Count > object cellValue = cellMapper.PropertyInfo.GetValue(t);
                    ICellModel firstCellPosition = ;
                    var cellPointWriteAttribute  cellMapper.CellPointWriteList)
                    {
                        firstCellPosition = CellFactory.GetCellByExcelPosition(cellPointWriteAttribute.CellPosition);
                        CellFactory.SetDeepUpdateCellValue(sheet,firstCellPosition.RowIndex,firstCellPosition.ColumnIndex,cellPointWriteAttribute.OutputFormat, ExcelHelper.CreateOrUpdateWorkbook(commonCellColl,sheet.SheetName);

            #endregion

        #region 保存工作表到文件

         保存Workbook数据为文件
        <param name="fileDirectoryPath"></param>
        <param name="fileName"></param>
        void SaveWorkbookToFile(IWorkbook workbook,1)"> filePath)
        {
            Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；

            MemoryStream ms =  MemoryStream();
            这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致
            ms.Seek(byte[] myByteArray = ms.GetBuffer();

            string fileDirectoryPath = filePath.Split('')[];
            Directory.Exists(fileDirectoryPath))
            {
                Directory.CreateDirectory(fileDirectoryPath);
            }
            string fileName = filePath.Replace(fileDirectoryPath, (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            File.WriteAllBytes(filePath,myByteArray);
        }

         保存workbook到字节流中(提供给API接口使用)
        byte[] SaveWorkbookToByte(IWorkbook workbook)
        {
            MemoryStream stream =  MemoryStream();
            stream.Seek(byte[] byteArray = stream.GetBuffer();
             byteArray;
        }

        #region 读取Excel数据

         读取Excel数据1_手动提供属性信息和标题对应关系
        <param name="filePath"></param>
        <param name="fieldNameAndShowNameDic"></param>
        static List<T> ReadTitleDataList<T>(string filePath,ExcelFileDescription excelFileDescription) ()
        {
            标题属性字典列表
            List<ExcelTitleFieldMapper> titleMapperList = ExcelTitleFieldMapper.GetModelFieldMapper<T>(fieldNameAndShowNameDic);

            List<T> tList = ExcelHelper._GetTList<T>(filePath,1)">return tList ?? new List<T>();
        }

         读取Excel数据2_使用Excel标记特性和文件描述自动创建关系
        ();

            List<T> tList = ExcelHelper._GetTList<T>);
        }

        static List<T> _GetTList<T>(()
        {
            List<T> tList = 500 * 10000);
            T t = default(T);

            
            {
                IWorkbook workbook = ExcelHelper.GetExcelWorkbook(filePath);
                IFormulaEvaluator formulaEvaluator =  XSSFWorkbook)
                {
                    formulaEvaluator =  XSSFFormulaEvaluator(workbook);
                }
                 HSSFFormulaEvaluator(workbook);
                }

                int sheetCount = workbook.NumberOfSheets;

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

                    标题下标属性字典
                    Dictionary<new Dictionary<);

                    如果没有设置标题行，则通过自动查找方法获取
                    if (excelFileDescription.TitleRowIndex < string[] titleArray = titleMapperList.Select(m => m.ExcelTitle).ToArray();
                        currentSheetRowTitleIndex = ExcelHelper.GetSheetTitleIndex(sheet,titleArray);
                    }
                    
                    {
                        currentSheetRowTitleIndex = excelFileDescription.TitleRowIndex;
                    }

                    var rows = sheet.GetRowEnumerator();

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

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

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

                            到达标题行（寻找标题行映射）
                            if (isHaveTitleIndex == false && currentRowIndex == currentSheetRowTitleIndex)
                            {
                                ICell cell = ;
                                string cellValue = ;
                                Dictionary< m.ExcelTitle);
                                0; i < row.Cells.Count; i++)
                                {
                                    cell = row.Cells[i];
                                    cellValue = cell.StringCellValue;
                                     (titleMapperDic.ContainsKey(cellValue))
                                    {
                                        sheetTitleIndexPropertyDic.Add(i,titleMapperDic[cellValue]);
                                    }
                                }
                            }

                            到达内容行
                            if (currentRowIndex > currentSheetRowTitleIndex)
                            {
                                t =  T();
                                ExcelTitleFieldMapper excelTitleFieldMapper = var titleIndexItem  sheetTitleIndexPropertyDic)
                                {
                                    ICell cell = row.GetCell(titleIndexItem.Key);

                                    excelTitleFieldMapper = titleIndexItem.Value;

                                    没有数据的单元格默认为null
                                    string cellValue = cell?.ToString() ?? ;
                                    propertyInfo = excelTitleFieldMapper.PropertyInfo;
                                    
                                    {
                                         (excelTitleFieldMapper.IsCheckContentEmpty)
                                        {
                                            .IsNullOrEmpty(cellValue))
                                            {
                                                t = (T);
                                                ;
                                            }
                                        }

                                        if (excelTitleFieldMapper.IsCoordinateExpress || cell.CellType == CellType.Formula)
                                        {
                                            读取含有表达式的单元格值
                                            cellValue = formulaEvaluator.Evaluate(cell).StringValue;
                                            propertyInfo.SetValue(t,Convert.ChangeType(cellValue,propertyInfo.PropertyType));
                                        }
                                         (propertyInfo.PropertyType.IsEnum)
                                        {
                                            object enumObj = propertyInfo.PropertyType.InvokeMember(cellValue,BindingFlags.GetField,1)">);
                                            propertyInfo.SetValue(t,Convert.ChangeType(enumObj,1)">
                                        {
                                            propertyInfo.SetValue(t,propertyInfo.PropertyType));
                                        }
                                    }
                                     (Exception e)
                                    {
                                        ExcelHelper._Logger.Debug($文件_{filePath}读取{currentRowIndex + 1}行内容失败！);
                                        t = (T);
                                        ;
                                    }
                                }
                                if (t != )
                                {
                                    tList.Add(t);
                                }
                            }
                        }
                    }

                    currentSheetIndex++;

                } while (currentSheetIndex + 1 <= sheetCount);
            }
            static CellModelColl ReadCellList(IWorkbook workbook,1)">bool isRunFormula = )
        {
            CellModelColl commonCells = );

            IFormulaEvaluator formulaEvaluator =  XSSFFormulaEvaluator(workbook);
                }
            }
            )
            {
                CellModel cellModel =  sheet.GetRowEnumerator();

                从第1行数据开始获取
                 (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;

                    List<ICell> cellList = row.Cells;

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

                        cellModel =  CellModel
                        {
                            RowIndex = cell.RowIndex,1)"> cell.ColumnIndex,1)"> cell.ToString(),IsCellFormula = cell.CellType == CellType.Formula
                        };

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

         获取文件单元格数据对象
        T的属性必须标记了ExcelCellAttribute</typeparam>
        <param name="filePath">文建路径<param name="sheetIndex">（可选）sheet所在位置（可选）sheet名称static T ReadCellData<T>(IWorkbook workbook,ISheet sheet) ()
        {
            T t =  T();

            )
            {

                )
                {
                    Dictionary<PropertyInfo,ExcelCellFieldMapper> propertyMapperDic = ExcelCellFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.PropertyInfo);
                    string cellExpress = string pValue = ;
                    PropertyInfo propertyInfo =  propertyMapperDic)
                    {
                        cellExpress = item.Value.CellExpressRead.CellCoordinateExpress;
                        propertyInfo = item.Key;
                        pValue = ExcelHelper.GetVByExpress(cellExpress,sheet).ToString();
                        .IsNullOrEmpty(pValue))
                        {
                            
                            {
                                propertyInfo.SetValue(t,Convert.ChangeType(pValue,propertyInfo.PropertyType));
                            }
                             (Exception)
                            {

                                throw;
                            }

                        }
                    }
                }
            }

             t;
        }

         读取单元格数据对象列表-支持深度读取
        static List<T> ReadCellData<T>(IWorkbook workbook,1)">#region 获取深度表达式更新列表

            List<IExcelCellExpressDeepUpdate<T>> excelCellExpressDeepUpdateList = new List<IExcelCellExpressDeepUpdate<T>>( excelFileDescription.ExcelDeepUpdateList)
                {
                    if (item is IExcelCellExpressDeepUpdate<T>)
                    {
                        excelCellExpressDeepUpdateList.Add((IExcelCellExpressDeepUpdate<T>)item);
                    }
                }
            }

            #endregion

            #region 通过表达式映射列表读取对象T

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

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

                    
                    {
                        cellMapper.PropertyInfo.SetValue(t,cellMapper.PropertyInfo.PropertyType));
                    }
                     (Exception)
                    {
                    }
                }
                 t;
            };

            #region 执行初始表达式数据收集

            ();
            t = expressMapperFunc(cellMapperList);

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

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

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

                bool isContinute = 通过深度更新策略更新初始表达式数据
                     cellMapperList)
                    {
                        if (cellMapper.CellExpressRead != )
                        {
                             cellMapper.CellExpressRead.CellCoordinateExpress;
                            currentCellExpress = excelCellExpressDeepUpdate.GetNextCellExpress(currentCellExpress);
                            cellMapper.CellExpressRead.CellCoordinateExpress = currentCellExpress;
                        }
                    }
                    t = expressMapperFunc(cellMapperList);
                    isContinute = excelCellExpressDeepUpdate.IsContinute(t);
                     (isContinute)
                    {
                        tList.Add(t);
                    }

                }  (isContinute);
            };

             excelCellExpressDeepUpdateList)
            {
                actionDeepReadAction(item);
            }

             tList;
        }

         获取文件首个sheet的标题位置
        T必须做了标题映射int FileFirstSheetTitleIndex<T>(int titleIndex =  (File.Exists(filePath))
            {
                
                        {
                            workbook =  XSSFWorkbook(fileStream);
                        }
                         (Exception)
                        {
                            workbook =  HSSFWorkbook(fileStream);
                        }

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

                        ISheet sheet = workbook.GetSheetAt();
                        titleIndex = (Exception e)
                {
                     titleIndex;
        }

        <param name="titleNames"></param>
        int FileFirstSheetTitleIndex(params [] titleNames)
        {
             HSSFWorkbook(fileStream);
                    }
                    ISheet sheet = workbook.GetSheetAt();
                    titleIndex = titleIndex;
        }

        #region 辅助方法

         根据标题名称获取标题行下标位置
        <param name="sheet">要查找的sheet<param name="titleNames">标题名称int GetSheetTitleIndex(ISheet sheet,1)">int titleIndex = -null && titleNames != null && titleNames.Length >  sheet.GetRowEnumerator();
                List<ICell> cellList = ;
                List<string> rowValueList =  (IRow)rows.Current;

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

                    bool isTitle = var title  titleNames)
                    {
                        rowValueList.Contains(title))
                        {
                            isTitle = ;
                            ;
                        }
                    }
                     (isTitle)
                    {
                        titleIndex = row.RowNum;
                        ;
                    }
                }
            }
            #endregion

    }

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

         字符输出格式（数字和日期类型需要）
        string OutputFormat {  生成单元格表达式读取特性
        <param name="cellCoordinateExpress">初始单元格表达式<param name="outputFormat">（可选）格式化字符串</param>
        public ExcelCellExpressReadAttribute(string cellCoordinateExpress,1)">string outputFormat = this.CellCoordinateExpress = cellCoordinateExpress;
            this.OutputFormat = outputFormat;
        }
    }

 单元格字段映射类
    </summary>
    internal  ExcelCellFieldMapper
    {
         属性信息(一个属性可以添加一个表达式读取，多个变量替换和多个坐标写入)
        public PropertyInfo PropertyInfo {  单元格—表达式读取（单元格坐标表达式（如：A1,C1+C2...横坐标使用26进制字母，纵坐标使用十进制数字））
        public ExcelCellExpressReadAttribute CellExpressRead {  单元格—模板文件的预定义变量写入（{A} {B}）
        public List<ExcelCellParamWriteAttribute> CellParamWriteList {  单元格—坐标位置写入（(0,0),(1,1)）
        public List<ExcelCellPointWriteAttribute> CellPointWriteList {  获取对应关系_T属性添加了单元格映射关系
        static List<ExcelCellFieldMapper> GetModelFieldMapper<T>()
        {
            List<ExcelCellFieldMapper> fieldMapperList = new List<ExcelCellFieldMapper>(100);

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

             fieldMapperList;
        }
    }

 Excel单元格-模板参数写入-标记特性
     ExcelCellParamWriteAttribute : System.Attribute
    {
         模板文件的预定义变量使用（{A} {B}）
        string CellParamName { ; }

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


    }

 ExcelCellPointWriteAttribute : System.Attribute
    {
         单元格位置（A3,B4...）
        string CellPosition { ; }


        public ExcelCellPointWriteAttribute(string cellPosition,1)">string outputFormat = )
        {
            CellPosition = cellPosition;
            OutputFormat = outputFormat;
        }
    }

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

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

         标题所在行位置（默认为0，没有标题填-1）
        int TitleRowIndex {  Excel深度更新策略
        public List<IExcelDeepUpdate> ExcelDeepUpdateList { ; }

    }

 Excel标题标记特性
     ExcelTitleAttribute : System.Attribute
    {
         Excel行标题(标题和下标选择一个即可)
        string RowTitle { ; }
         Excel行下标(标题和下标选择一个即可,默认值-1)
        int RowTitleIndex {  单元格是否要检查空数据（true为检查，为空的行数据不添加）
        bool IsCheckContentEmpty {  是否是公式列
        bool IsCoordinateExpress {  标题特性构造方法
        <param name="title">标题<param name="isCheckEmpty">单元格是否要检查空数据<param name="isCoordinateExpress">是否是公式列是否有格式化输出要求public ExcelTitleAttribute(string title,1)">bool isCheckEmpty = false,1)">bool isCoordinateExpress = )
        {
            RowTitle = title;
            IsCheckContentEmpty = isCheckEmpty;
            IsCoordinateExpress = isCoordinateExpress;
            OutputFormat = outputFormat;
            RowTitleIndex = -;
        }

        int titleIndex,1)">)
        {
            RowTitleIndex = titleIndex;
            IsCheckContentEmpty = outputFormat;
        }
    }

 标题字段映射类
     ExcelTitleFieldMapper
    {
         属性信息
         行标题
        string ExcelTitle {  行标题下标位置
        int ExcelTitleIndex {  是否要做行内容空检查
         获取对应关系_T属性添加了标题映射关系
        static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>()
        {
            List<ExcelTitleFieldMapper> fieldMapperList = new List<ExcelTitleFieldMapper>((T).GetProperties().ToList();
            ExcelTitleAttribute excelTitleAttribute = var tPropertyInfo  tPropertyInfoList)
            {
                excelTitleAttribute = (ExcelTitleAttribute)tPropertyInfo.GetCustomAttribute((ExcelTitleAttribute));
                
                if (excelTitleAttribute !=  ExcelTitleFieldMapper
                    {
                        PropertyInfo = tPropertyInfo,ExcelTitle = excelTitleAttribute.RowTitle,ExcelTitleIndex = excelTitleAttribute.RowTitleIndex,IsCheckContentEmpty = excelTitleAttribute.IsCheckContentEmpty,OutputFormat = excelTitleAttribute.OutputFormat,IsCoordinateExpress = excelTitleAttribute.IsCoordinateExpress
                    });
                }
            }
             fieldMapperList;
        }

         获取对应关系_手动提供映射关系
        static List<ExcelTitleFieldMapper> GetModelFieldMapper<T>(Dictionary< fieldNameAndShowNameDic)
        {
            List<ExcelTitleFieldMapper> fieldMapperList = (T).GetProperties().ToList();
            PropertyInfo propertyInfo =  fieldNameAndShowNameDic)
            {
                propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key,StringComparison.OrdinalIgnoreCase));

                fieldMapperList.Add( ExcelTitleFieldMapper
                {
                    PropertyInfo = propertyInfo,1)"> item.Value,ExcelTitleIndex = -= 
                });
            }
             获取对应关系_未提供（默认属性名和标题名一致）
        static List<ExcelTitleFieldMapper> GetModelDefaultFieldMapper<T>(T).GetProperties().ToList();

             tPropertyInfoList)
            {
                fieldMapperList.Add( item,1)"> item.Name,1)"> fieldMapperList;
        }

    }

 单元格工厂类
     CellFactory
    {
        static Regex _CellPostionRegex = new Regex([A-Z]+d+);
        static Regex _RowRegex = d+);

         通过Excel单元格坐标位置初始化对象
        <param name="excelCellPosition">A1,B2等等static ICellModel GetCellByExcelPosition( excelCellPosition)
        {
            CellModel cellModel = bool isMatch = CellFactory._CellPostionRegex.IsMatch(excelCellPosition);
             (isMatch)
            {
                Match rowMath = CellFactory._RowRegex.Match(excelCellPosition);
                int rowPositon = Convert.ToInt32(rowMath.Value);
                int rowIndex = rowPositon - int columnIndex = CellFactory.GetExcelColumnIndex(excelCellPosition.Replace(rowPositon.ToString(),1)">));

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

         将数据放入单元格中
        <param name="cell">单元格对象<param name="cellValue">数据格式化字符串是否是表达式数据void SetCellValue(ICell cell,1)">object cellValue,1)">string outputFormat,1)">bool isCoordinateExpress)
        {
             (isCoordinateExpress)
                {
                    cell.SetCellFormula(cellValue.ToString());
                }
                .IsNullOrEmpty(outputFormat))
                    {
                        string formatValue = ;
                        IFormatProvider formatProvider = if (cellValue  DateTime)
                        {
                            formatProvider =  DateTimeFormatInfo();
                            ((DateTimeFormatInfo)formatProvider).ShortDatePattern = outputFormat;
                        }
                        formatValue = ((IFormattable)cellValue).ToString(outputFormat,formatProvider);

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

        }

        void SetDeepUpdateCellValue(ISheet sheet,1)">int columnIndex,1)">bool isCoordinateExpress,List<IExcelCellPointDeepUpdate>更新起始单元格数据
                ICell nextCell = ExcelHelper.GetOrCreateCell(sheet,rowIndex,columnIndex);
                CellFactory.SetCellValue(nextCell,outputFormat,isCoordinateExpress);

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

                ICellModel startCellPosition =  rowIndex,1)"> columnIndex
                };

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

                    执行深度更新，一直到找不到下个单元格为止
                    
                    {
                        nextCell =if (nextCell != )
                        {
                            CellFactory.SetCellValue(nextCell,isCoordinateExpress);
                            nextCellPosition = excelDeepUpdate.GetNextCellPoint(nextCellPosition);
                        }
                    } while (nextCell != );
                };

                var excelDeepUpdate  excelDeepUpdateList)
                {
                    actionDeepUpdateAction(excelDeepUpdate);
                }

                

            }

        }


         数字转字母
        <param name="columnIndex"></param>
        string GetExcelColumnPosition( number)
        {
            var a = number / 26var b = number % if (a > return CellFactory.GetExcelColumnPosition(a - 1) + (char)(b + 65);
            }
            return (()).ToString();
            }
        }

         字母转数字
        <param name="columnPosition"></param>
        int GetExcelColumnIndex( zm)
        {
            int index = char[] chars = zm.ToUpper().ToCharArray();
            0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)A' + 1) * (int)Math.Pow(26,chars.Length - i - return index - ;
        }

    }

 CellModel : ICellModel
    {
        int RowIndex { ; }
        int ColumnIndex { object CellValue { bool IsCellFormula {  CellModel() { }

         默认初始化对象
        <param name="rowIndex"></param>
        <param name="cellValue"></param>
        public CellModel(default(object)) : this(rowIndex,columnIndex,1)">)
        {
        }

        <param name="cellValue"></param>
        <param name="isCellFormula"></param>
         isCellFormula)
        {
            this.RowIndex = rowIndex;
            this.ColumnIndex = columnIndex;
            this.CellValue = cellValue;
            this.IsCellFormula = isCellFormula;
        }

         获取单元格位置
         GetCellPosition()
        {
            return CellFactory.GetExcelColumnPosition(this.ColumnIndex) + (this.RowIndex + ).ToString();
        }
    }

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

        }

         根据行下标，列下标获取单元格数据
        public CellModel this[ columnIndex]
        {
            get
            {
                CellModel cell = this.FirstOrDefault(m => m.RowIndex == rowIndex && m.ColumnIndex == columnIndex);
                 cell;
            }
            )
                {
                    cell.CellValue = value.CellValue;
                }
            }
        }

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

        public CellModel GetCell( cellStringValue)
        {
            CellModel cellModel = ;

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

             所有一行所有的单元格数据
        <param name="rowIndex">行下标public List<CellModel> GetRawCellList( rowIndex)
        {
            List<CellModel> cellList = ;
            cellList = this.FindAll(m => m.RowIndex == rowIndex);

            return cellList ?? new List<CellModel>( 所有一列所有的单元格数据
        <param name="columnIndex">列下标public List<CellModel> GetColumnCellList( columnIndex)
        {
            List<CellModel> cellList = this.FindAll(m => m.ColumnIndex == columnIndex);

            );
        }

    }

class ExcelCellExpressDeepUpdate<T> : IExcelCellExpressDeepUpdate<T>
        {
            private Regex cellPointRegex = [A-Z]+[0-9]+private Action<ICellModel> updateCellPointFunc { ; }
            public Func<T,1)">bool> CheckContinuteFunc { ; }

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

             IsContinute(T t)
            {
                return .CheckContinuteFunc(t);
            }

            string GetNextCellExpress( currentExpress)
            {
                string nextCellExpress = currentExpress;

                List<ICellModel> cellModelList = .GetCellModelList(currentExpress);
                string oldPointStr = string newPointStr =  cellModelList)
                {
                    oldPointStr = item.GetCellPosition();
                    .updateCellPointFunc(item);
                    newPointStr = item.GetCellPosition();

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


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

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

        }

 ExcelCellPointDeepUpdate : IExcelCellPointDeepUpdate
    {
        public ExcelCellPointDeepUpdate(Action<ICellModel> updateCellPointFunc)
        {
             updateCellPointFunc;
        }

         ICellModel GetNextCellPoint(ICellModel cellModel)
        {
            ICellModel nextCell = ;

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

             nextCell;
        }

    }

interface ICellModel
    {
         GetCellPosition();

    }

 单元格深度更新接口
     IExcelCellDeepUpdate : IExcelDeepUpdate
    {
    }

interface IExcelCellExpressDeepUpdate<T> : IExcelCellDeepUpdate
    {
         currentExpress);
         IsContinute(T t);

    }

 单元格坐标深度更新接口
     IExcelCellPointDeepUpdate : IExcelCellDeepUpdate
    {
        ICellModel GetNextCellPoint(ICellModel cellModel);
    }

 Excel深度更新策略接口
     IExcelDeepUpdate
    {
    }

 Excel标题深度更新策略
     IExcelTitleDeepUpdate : IExcelDeepUpdate
    {
    }