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>