东西类
实体类：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></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要领