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.split('\\')[0];
            if (!directory.exists(filedirectorypath))
            {
                directory.createdirectory(filedirectorypath);
            }
            string filename = filepath.replace(filedirectorypath, "");

            if (file.exists(filepath))
            {
                file.delete(filepath);
            }
            file.writeallbytes(filepath, mybytearray);
        }

        /// <summary>
        /// 保存workbook到字节流中(提供给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);
            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;
                                    try
                                    {
                                        if (exceltitlefieldmapper.ischeckcontentempty)
                                        {
                                            if (string.isnullorempty(cellvalue))
                                            {
                                                t = default(t);
                                                break;
                                            }
                                        }

                                        if (exceltitlefieldmapper.iscoordinateexpress || cell.celltype == celltype.formula)
                                        {
                                            //读取含有表达式的单元格值
                                            cellvalue = formulaevaluator.evaluate(cell).stringvalue;
                                            propertyinfo.setvalue(t, convert.changetype(cellvalue, propertyinfo.propertytype));
                                        }
                                        else if (propertyinfo.propertytype.isenum)
                                        {
                                            object enumobj = propertyinfo.propertytype.invokemember(cellvalue, bindingflags.getfield, null, null, null);
                                            propertyinfo.setvalue(t, convert.changetype(enumobj, propertyinfo.propertytype));
                                        }
                                        else
                                        {
                                            propertyinfo.setvalue(t, convert.changetype(cellvalue, propertyinfo.propertytype));
                                        }
                                    }
                                    catch (exception e)
                                    {
                                        excelhelper._logger.debug($"文件_{filepath}读取{currentrowindex + 1}行内容失败！");
                                        t = default(t);
                                        break;
                                    }
                                }
                                if (t != null)
                                {
                                    tlist.add(t);
                                }
                            }
                        }
                    }

                    currentsheetindex++;

                } while (currentsheetindex + 1 <= sheetcount);
            }
            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
                        {
                            cell.setcellvalue(cellvalue.tostring());
                        }
                    }
                }
            }

        }

        public static void setdeepupdatecellvalue(isheet sheet, int rowindex, int columnindex, object cellvalue, string outputformat, bool iscoordinateexpress, list<iexcelcellpointdeepupdate> exceldeepupdatelist)
        {
            if (sheet != null)
            {
                //更新起始单元格数据
                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; }
}