public class XMLReadModel { /// <summary>
        /// 导入所需键值对 /// </summary>
        public Hashtable ImportHashtable { set; get; } = new Hashtable(); /// <summary>
        /// 导出所需键值对 /// </summary>
        public Hashtable ExportHashtable { set; get; } = new Hashtable(); }

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

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

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

        [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 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>