当前位置： 移动技术网 > IT编程>开发语言>c# > 创建execl导入工具类的步骤

创建execl导入工具类的步骤

2019年07月18日  | 移动技术网IT编程  | 我要评论

1、创建实体属性标记

复制代码 代码如下:

public class cellattribute : attribute
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="displayname">显示名称</param>
        /// <param name="hander"></param>
        public cellattribute(string displayname, type hander = null)
        {
            displayname = displayname;

            hander = hander;
        }

        /// <summary>
        /// 显示名称
        /// </summary>
        public string displayname { get; set; }

        /// <summary>
        /// 类型
        /// </summary>
        public type hander { get; set; }
    }

2、创建通用处理方法

复制代码 代码如下:

public class xlsfilehandler<t> where t : new()
    {
        private readonly string _path;
        private readonly dictionary<string, cellattribute> _cellattributes;
        readonly dictionary<string, string> _propdictionary;

        public xlsfilehandler(string path)
        {
            _path = path;
            _cellattributes = new dictionary<string, cellattribute>();
            _propdictionary = new dictionary<string, string>();
            createmappers();
        }

        /// <summary>
        /// 创建映射
        /// </summary>
        private void createmappers()
        {
            foreach (var prop in typeof(t).getproperties())
            {
                foreach (cellattribute cellmapper in prop.getcustomattributes(false).oftype<cellattribute>())
                {
                    _propdictionary.add(cellmapper.displayname, prop.name);
                    _cellattributes.add(cellmapper.displayname, cellmapper);
                }
            }
        }

        /// <summary>
        /// 获取整个xls文件对应行的t对象
        /// </summary>
        /// <returns></returns>
        public list<t> todata()
        {
            list<t> datalist = new list<t>();
            using (filestream stream = getstream())
            {
                iworkbook workbook = new hssfworkbook(stream);
                isheet sheet = workbook.getsheetat(0);
                var rows = sheet.getrowenumerator();
                int lastcell = 0;
                int i = 0;
                irow headrow = null;
                while (rows.movenext())
                {
                    var row = sheet.getrow(i);
                    if (i == 0)
                    {
                        headrow = sheet.getrow(0);
                        lastcell = row.lastcellnum;
                    }
                    else
                    {
                        t t = getdata(workbook, headrow, row, lastcell);
                        datalist.add(t);
                    }
                    i++;
                }
                stream.close();
            }
            return datalist;
        }

        /// <summary>
        /// 获取t对象
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="headrow"></param>
        /// <param name="currentrow"></param>
        /// <param name="lastcell"></param>
        /// <returns></returns>
        private t getdata(iworkbook workbook, irow headrow, irow currentrow, int lastcell)
        {
            t t = new t();
            for (int j = 0; j < lastcell; j++)
            {
                var displayname = headrow.cells[j].stringcellvalue;
                if (!_cellattributes.containskey(displayname) || !_propdictionary.containskey(displayname))
                {
                    continue;
                }
                var currentattr = _cellattributes[displayname];
                var propname = _propdictionary[displayname];

                icell currentcell = currentrow.getcell(j);
                string value = currentcell != null ? getcellvalue(workbook, currentcell) : "";
                if (currentattr.hander != null)
                {
                    setvalue(ref t, propname, invokehandler(currentattr.hander, value));
                }
                else
                {
                    setvalue(ref t, propname, value);
                }
            }
            return t;
        }

        /// <summary>
        /// 动态执行处理方法
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static object invokehandler(type type, object value)
        {
            system.reflection.constructorinfo constructor = type.getconstructor(type.emptytypes);
            if (constructor == null) throw new argumentnullexception("type");
            object mgconstructor = constructor.invoke(null);
            system.reflection.methodinfo method = type.getmethod("getresults");
            return method.invoke(mgconstructor, new[] { value });
        }

        /// <summary>
        /// 获取文件流
        /// </summary>
        /// <returns></returns>
        private filestream getstream()
        {
            if (!file.exists(_path)) throw new filenotfoundexception("path");
            return new filestream(_path, filemode.open, fileaccess.read, fileshare.read);
        }

        /// <summary>
        /// 获取xls文件单元格的值
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string getcellvalue(iworkbook workbook, icell cell)
        {
            string value;
            switch (cell.celltype)
            {
                case celltype.formula:
                    hssfformulaevaluator evaluator = new hssfformulaevaluator(workbook);
                    value = evaluator.evaluate(cell).formatasstring();
                    break;
                default:
                    value = cell.tostring();
                    break;
            }
            return value;
        }

        /// <summary>
        /// 设置t属性值
        /// </summary>
        /// <param name="t"></param>
        /// <param name="propname"></param>
        /// <param name="value"></param>
        private static void setvalue(ref t t, string propname, object value)
        {
            var typename = t.gettype().getproperty(propname).propertytype.name;
            var property = t.gettype().getproperty(propname);
            switch (typename)
            {
                case "int32":
                    property.setvalue(t, convert.toint32(value), null);
                    break;
                case "datetime":
                    property.setvalue(t, convert.todatetime(value), null);
                    break;
                case "decimal":
                    property.setvalue(t, convert.todecimal(value), null);
                    break;
                default:
                    property.setvalue(t, value, null);
                    break;
            }
        }
    }

3、创建execl文件映射类

复制代码 代码如下:

public class readmapper
    {
        [cellattribute("测试1")]
        public decimal code { get; set; }

        [cellattribute("测试2")]
        public int name { get; set; }

        [cellattribute("测试3", typeof(classcellhander))]
        public string group { get; set; }

        [cellattribute("测试4")]
        public datetime addtime { get; set; }
    }

4、指定execl文件路径，通过通用处理方法导出映射实体

复制代码 代码如下:

[test]
        public void read1()
        {
            const string filepath = @"c:\users\zk\desktop\1.xls";
            xlsfilehandler<readmapper> handler = new xlsfilehandler<readmapper>(filepath);
            list<readmapper> readmappers = handler.todata();
            assert.areequal(readmappers.count, 3);
        }

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网