webapi 导入excel处理数据

参考资料

　　　　    

　　　　　

　　　　　

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

基本上就是这些了。