ASP.NET 上传文件导入Excel

前言

　　本文对应的场景是导入excel数据，excel对应的字段都配置在xml文件中。截图如下：

 

代码实战

　　工具类

　　实体类：xmlreadmodel.cs

    public class xmlreadmodel
    {
        /// <summary>
        /// 导入所需键值对
        /// </summary>
        public hashtable importhashtable { set; get; } = new hashtable();

        /// <summary>
        /// 导出所需键值对
        /// </summary>
        public hashtable exporthashtable { set; get; } = new hashtable();
    }

　　工具方法：读取xml文件内容到实体中。

        /// <summary>
        /// 读取xml文件到hashtable
        /// </summary>
        public static xmlreadmodel readtohashtable(string path)
        {
            var xr = new xmlreadmodel();
            var xmldoc = new xmldocument();
            xmldoc.load(path);
            //获取节点列表 
            var topm = xmldoc.selectnodes("//columnname");
            foreach (xmlelement element in topm)
            {
                var enabled = element.attributes[0].value;
                if (enabled == "true") //字段启用
                {
                    var dbproperty = element.getelementsbytagname("dbproperty")[0].innertext;
                    var excelproperty = element.getelementsbytagname("excelproperty")[0].innertext;
                    if (!xr.importhashtable.containskey(excelproperty))
                    {
                        xr.importhashtable.add(excelproperty, dbproperty);
                    }
                    if (!xr.exporthashtable.containskey(dbproperty))
                    {
                        xr.exporthashtable.add(dbproperty, excelproperty);
                    }
                }
            }
            return xr;
        }

　　excel文件内容转成datatable方法

        /// <summary>
        /// excel文件流转化成datatable
        /// </summary>
        public static datatable exceltotableforxlsx(stream filestream, hashtable ht = null, 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
                    {
                        if (ht != null)
                        {
                            var o = ht[obj.tostring()].tostring();//这里就是根据xml中读取的字段对应关系进行字段赋值的。
                            dt.columns.add(new datacolumn(o));
                        }
                        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;
        }

　　获取excel单元格值类型，转成c#对应的值类型。

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

　　datatable转成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
                                {
                                    if (info.propertytype.equals(typeof(bool)))
                                    {
                                        var value = item[i].tostring();
                                        if (value.equals("true", stringcomparison.currentcultureignorecase) || value.equals("false", stringcomparison.currentcultureignorecase))
                                            v = convert.changetype(item[i], info.propertytype);
                                        else if (value.equals("1", stringcomparison.currentcultureignorecase) || value.equals("0", stringcomparison.currentcultureignorecase))
                                        {
                                            if (value.equals("1", stringcomparison.currentcultureignorecase))
                                                v = true;
                                            else
                                                v = false;
                                        }
                                    }
                                    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;
        }

　　导入excel方法

        [httppost, route("api/workstage/importfile")]
        public object importfile()
        {
            var filelist = httpcontext.current.request.files;
            var models = new list<dmodel>();
            var path = httpcontext.current.server.mappath("/importconfig/modelconfig.xml");
            var xr = xmlhelper.readtohashtable(path);//读取excel的字段对应关系，代码的实体字段和excel中的字段对应，在后面的excel的值读取还有数据库实体赋值用得到。

            try
            {
                if (filelist.count > 0)
                {
                    for (var i = 0; i < filelist.count; i++)
                    {
                        var file = filelist[i];
                        var filename = file.filename;
                        var fn = filename.split('\\');
                        if (fn.length > 1)
                        {
                            filename = fn[fn.length - 1];
                        }
                        datatable datatable = null;
                        var fs = filename.split('.');
                        if (fs.length > 1)
                        {
                            datatable = excelhelp.exceltotableforxlsx(file.inputstream, xr.importhashtable); //excel转成datatable
                        }
                        models = datatable.todatalist<dworkstage>(); //datatable转成list
                    }
                }
                var succe = new list<dmodel>();//需要插入的数据列表
                var exportlist = new list<dmodel>();//需要导出给用户的失败数据列表
                // 做一些数据逻辑处理，把处理好的数据加到succe列表中
                if (succe.any())
                {
                    sqlbulkcopyhelper.bulkinsertdata(succe, "dmodel");
                }

                var url = string.empty;
                if (exportlist.any())
                {
                    var extdt = exportlist.todatatable(xr.exporthashtable);//把数据库中的字段转成excel中需要展示的字段，并保存到datatable中。
                    url = savefile(extdt, "失败信息.xlsx");//把datatable保存到本地服务器或者文件服务器中，然后把文件下载地址返回给前端。
                }

                var list = new { failed = faile.take(100).tolist(), failedcount = faile.count }; //数据太多的话，浏览器会崩溃
                 var json = new { list, msg = "添加成功", url };
                 return  json;

            }
            catch (exception ex)
            {
                var json = new { msg = "添加失败", ex.message, ex };
                return  json;
            }
        }

　　具体的xml文件

　　　　具体的节点可以自己命名。

<?xml version="1.0" encoding="utf-8" ?>
<tableconfig>
  <!--商品名称-->
  <columnname enabled="true" >
    <dbproperty>productname</dbproperty>
    <excelproperty>商品名称</excelproperty>
  </columnname>
  <!--原因,导出失败列表时用到的字段，导入时用不到-->
  <columnname enabled="true" >
    <dbproperty>sourcecode</dbproperty>
    <excelproperty>原因</excelproperty>
  </columnname>
  <!--创建时间-->
  <columnname enabled="true" >
    <dbproperty>createtime</dbproperty>
    <excelproperty>创建时间</excelproperty>
  </columnname>
  <!--更新时间-->
  <columnname enabled="true" >
    <dbproperty>updatetime</dbproperty>
    <excelproperty>更新时间</excelproperty>
  </columnname>
</tableconfig>

　　具体的excel模板