- A+
所属分类：.NET技术
前言
本文对应的场景是导入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; }
View Code
导入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模板
