欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

webapi 导入excel处理数据

程序员文章站 2023-10-16 19:29:45
参考资料 https://blog.csdn.net/pan_junbiao/article/details/82935992 https://www.cnblogs.com/dansediao/p/5482467.html https://www.cnblogs.com/shiyh/p/74782 ......

参考资料

        

     

     

excel转成datatable工具类(excelhelp)

 

using system;
using system.collections.generic;
using system.data;
using system.io;
using system.reflection;
using npoi.hssf.usermodel;
using npoi.ss.usermodel;
using npoi.ss.util;
using npoi.xssf.usermodel;

namespace elearning.common.extensions
{
    public static class excelhelp
    {
        /// <summary>
        /// excel文件流转化成datatable
        /// </summary>
        public static datatable exceltotableforxlsx(stream filestream, bool havenote = false)
        {
            var dt = new datatable();
            using (var fs = filestream)
            {
                var xssfworkbook = new xssfworkbook(fs);
                var sheet = xssfworkbook.getsheetat(0);
                //表头  判断是否包含备注
                var firstrownum = sheet.firstrownum;
                if (havenote)
                {
                    firstrownum += 1;
                }
                var header = sheet.getrow(firstrownum);
                var columns = new list<int>();
                for (var i = 0; i < header.lastcellnum; i++)
                {
                    var obj = getvaluetypeforxlsx(header.getcell(i) as xssfcell);
                    if (obj == null || obj.tostring() == string.empty)
                    {
                        dt.columns.add(new datacolumn("columns" + i.tostring()));
                        //continue;
                    }
                    else
                        dt.columns.add(new datacolumn(obj.tostring()));
                    columns.add(i);
                }
                //数据
                for (var i = firstrownum + 1; i <= sheet.lastrownum; i++)
                {
                    var dr = dt.newrow();
                    var hasvalue = false;
                    if (sheet.getrow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.getrow(i).getcell(j);
                        if (cell != null && cell.celltype == celltype.numeric)
                        {
                            //npoi中数字和日期都是numeric类型的,这里对其进行判断是否是日期类型
                            if (dateutil.iscelldateformatted(cell)) //日期类型
                            {
                                dr[j] = cell.datecellvalue;
                            }
                            else //其他数字类型
                            {
                                dr[j] = cell.numericcellvalue;
                            }
                        }
                        else
                        {
                            dr[j] = getvaluetypeforxlsx(sheet.getrow(i).getcell(j) as xssfcell);
                        }
                        if (dr[j] != null && dr[j].tostring() != string.empty)
                        {
                            hasvalue = true;
                        }
                    }
                    if (hasvalue)
                    {
                        dt.rows.add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object getvaluetypeforxlsx(xssfcell cell)
        {
            if (cell == null)
                return null;
            switch (cell.celltype)
            {

                case celltype.blank: //blank:
                    return null;
                case celltype.boolean: //boolean:
                    return cell.booleancellvalue;
                case celltype.numeric: //numeric:
                    return cell.numericcellvalue;
                case celltype.string: //string:
                    return cell.stringcellvalue;
                case celltype.error: //error:
                    return cell.errorcellvalue;
                case celltype.formula: //formula:
                default:
                    return "=" + cell.cellformula;
            }
        }

  #region  转化实体为datatable

        /// <summary>
        /// convert a list{t} to a datatable.
        /// </summary>
        public static datatable todatatable<t>(this list<t> items)
        {
            var tb = new datatable(typeof(t).name);

            propertyinfo[] props = typeof(t).getproperties(bindingflags.public | bindingflags.instance);

            foreach (propertyinfo prop in props)
            {
                type t = getcoretype(prop.propertytype);
                tb.columns.add(prop.name, t);
            }

            foreach (t item in items)
            {
                var values = new object[props.length];

                for (int i = 0; i < props.length; i++)
                {
                    values[i] = props[i].getvalue(item, null);
                }

                tb.rows.add(values);
            }

            return tb;
        }

        /// <summary>
        /// return underlying type if type is nullable otherwise return the type
        /// </summary>
        public static type getcoretype(type t)
        {
            if (t != null && isnullable(t))
            {
                if (!t.isvaluetype)
                {
                    return t;
                }
                else
                {
                    return nullable.getunderlyingtype(t);
                }
            }
            else
            {
                return t;
            }
        }

        /// <summary>
        /// determine of specified type is nullable
        /// </summary>
        public static bool isnullable(type t)
        {
            return !t.isvaluetype || (t.isgenerictype && t.getgenerictypedefinition() == typeof(nullable<>));
        }

        #endregion

        #region datatable to list

        /// <summary>
        /// datatable转成list
        /// </summary>
        public static list<t> todatalist<t>(this datatable dt)
        {
            var list = new list<t>();
            var plist = new list<propertyinfo>(typeof(t).getproperties());
            foreach (datarow item in dt.rows)
            {
                var s = activator.createinstance<t>();
                for (var i = 0; i < dt.columns.count; i++)
                {
                    var info = plist.find(p => p.name == dt.columns[i].columnname);
                    if (info != null)
                    {
                        try
                        {
                            if (!convert.isdbnull(item[i]))
                            {
                                object v = null;
                                if (info.propertytype.tostring().contains("system.nullable"))
                                {
                                    v = convert.changetype(item[i], nullable.getunderlyingtype(info.propertytype));
                                }
                                else
                                {
                                    v = convert.changetype(item[i], info.propertytype);
                                }
                                info.setvalue(s, v, null);
                            }
                        }
                        catch (exception ex)
                        {
                            throw new exception("字段[" + info.name + "]转换出错," + ex.message);
                        }
                    }
                }
                list.add(s);
            }
            return list;
        }
        #endregion
     }
}

 

sqlbulkcopyhelper工具类

using system;
using system.configuration;
using system.data;
using system.data.sqlclient;

namespace elearning.common.helpers
{
    public class sqlbulkcopyhelper
    {
        public static void savetable(datatable dttable)
        {
            var connectionstring = configurationmanager.connectionstrings["connectionstring"].tostring();
            var sbc = new sqlbulkcopy(connectionstring, sqlbulkcopyoptions.useinternaltransaction) {bulkcopytimeout = 5000};
            try
            {
                sbc.destinationtablename = dttable.tablename;
                sbc.writetoserver(dttable);
            }
            catch (exception ex)
            {
                //处理异常
            }
            finally
            {
                //sqlcmd.clone();
                //srcconnection.close();
                //desconnection.close();
            }
        }
    }
}

对应excel实体类:

    /// <summary>
    /// 导入用户视图模型
    /// </summary>
    public class insertadminusersviewmodel
    {
        public string 性别 { set; get; }
        public string 出生日期 { set; get; }
        public string 身份证号 { set; get; }
        public string 经销商名称 { set; get; }
        public string 经销商岗位 { set; get; }
        public string 更新时间 { set; get; }
    }

导入信息接口(insertadminusers):

        /// <summary>
        /// 导入用户
        /// </summary>
        [httppost, route("api/user/insertadminusers")]
        [allowanonymous]
        public object insertadminusers()
        {
            var filelist = httpcontext.current.request.files;
            var users = new list<insertadminusersviewmodel>();
            if (filelist.count > 0)
            {
                for (var i = 0; i < filelist.count; i++)
                {
                    var file = filelist[i];
                    var datatable = excelhelp.exceltotableforxlsx(file.inputstream);//excel转成datatable
                    users = datatable.todatalist<insertadminusersviewmodel>();//datatable转成list
                }
            }
            var succe = new list<esysuser>();
            var faile = new list<esysuser>();
            var names = userservice.findlist(u => !u.isdelete).select(u => u.loginname).tolist();
    //数据list转成数据库实体对应的list
            foreach (var u in users)
            {
                if (string.isnullorempty(u.状态) || !u.状态.equals("1"))
                    continue;
                var s = new esysuser
                {
                    createtime = datetime.now,
                    birthday = datetime.parseexact(u.出生日期, "yyyymmdd", cultureinfo.currentculture),
                    email = string.empty,
                    isdelete = false,
                    modifytime = datetime.parseexact(u.更新时间, "yyyymmddhhmmssfff", cultureinfo.currentculture),
                    userid = guidutil.newsequentialid(),
                    username = u.职员名称,
                    usertype = "jxs",
                    unumber = u.职员代码,
                    agentjobname = u.经销商岗位,
                    agentname = u.经销商名称.
                    cardno = u.身份证号
                };
                if (!string.isnullorempty(s.cardno) && s.cardno.length > 14)
                {
                    var str = s.cardno.substring(6, 8);
                    try
                    {
                        s.birthday = datetime.parseexact(str, "yyyymmdd", cultureinfo.currentculture);
                    }
                    catch (exception e)
                    {
                        console.writeline(e);
                    }

                }
                var t = names.where(f => f == s.loginname);
                var p1 = succe.where(o => o.loginname == s.loginname);
                if (t.any() || p1.any())
                {
                    s.remark = "登录名重复";
                    faile.add(s);
                }
                else
                {
                    succe.add(s);
                }
            }
            var dt = succe.todatatable();//转成 sqlbulkcopy所需要的类型:datatable
            if (string.isnullorempty(dt.tablename))
                dt.tablename = "esysuser";
            var r = succe.count;
            sqlbulkcopyhelper.savetable(dt);//批量插入
            var list = new { succeed = succe.take(100).tolist(), failed = faile.take(100).tolist() }; //数据太多的话,浏览器会崩溃
            return ok(list);
        }

基本上就是这些了。