注：对于实体类对象最好新建一个并且继承原有实体类，这样可以将类型进行修改；
方法一：此种方法是用EPPLUS中的FileInfo流进行读取的（是不是流我还真不太了解，若有懂得请留言，非常感谢了）
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using Abp.Extensions; 7 8 namespace HYZT.Ltxy.International.Ctrip.Exporting 9 { 10 public class ExcelLib 11 { 12 public ICtripPolicyExcelImport GetExcel(string filePath) 13 { 14 if (filePath.Trim() .IsNullOrEmpty()) 15 throw new Exception("文件名不能为空"); 16 //因为这儿用得是EPPLUS对Excel进行的操作，所以只能操作 17 //2007以后的版本以后的（即扩展名为.xlsx） 18 if (!filePath.Trim().EndsWith("xlsx")) 19 throw new Exception("请使用office Excel 2007版本或2010版本"); 20 21 else if (filePath.Trim().EndsWith("xlsx")) 22 { 23 ICtripPolicyExcelImport res = new CtripPolicyExcelImport(filePath.Trim()); 24 return res; 25 } 26 else return null; 27 } 28 } 29 }
方法接口：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace HYZT.Ltxy.International.Ctrip.Exporting 8 { 9 public interface ICtripPolicyExcelImport 10 { 11 /// <summary> 打开文件 </summary> 12 bool Open(); 13 //ExcelVersion Version { get; } 14 /// <summary> 文件路径 </summary> 15 string FilePath { get; set; } 16 /// <summary> 文件是否已经打开 </summary> 17 bool IfOpen { get; } 18 /// <summary> 文件包含工作表的数量 </summary> 19 int SheetCount { get; } 20 /// <summary> 当前工作表序号 </summary> 21 int CurrentSheetIndex { get; set; } 22 /// <summary> 获取当前工作表中行数 </summary> 23 int GetRowCount(); 24 /// <summary> 获取当前工作表中列数 </summary> 25 int GetColumnCount(); 26 /// <summary> 获取当前工作表中某一行中单元格的数量 </summary> 27 /// <param name="Row">行序号</param> 28 int GetCellCountInRow(int Row); 29 /// <summary> 获取当前工作表中某一单元格的值（按字符串返回） </summary> 30 /// <param name="Row">行序号</param> 31 /// <param name="Col">列序号</param> 32 string GetCellValue(int Row, int Col); 33 /// <summary> 关闭文件 </summary> 34 void Close(); 35 } 36 }
方法实现：
1 using OfficeOpenXml; 2 using System; 3 using System.Collections.Generic; 4 using System.IO; 5 using System.Linq; 6 using System.Text; 7 using System.Threading.Tasks; 8 9 namespace HYZT.Ltxy.International.Ctrip.Exporting 10 { 11 public class CtripPolicyExcelImport:ICtripPolicyExcelImport 12 { 13 14 public CtripPolicyExcelImport() 15 { } 16 17 public CtripPolicyExcelImport(string path) 18 { filePath = path; } 19 20 21 private string filePath = ""; 22 private ExcelWorkbook book = null; 23 private int sheetCount = 0; 24 private bool ifOpen = false; 25 private int currentSheetIndex = 0; 26 private ExcelWorksheet currentSheet = null; 27 private ExcelPackage ep = null; 28 29 public bool Open() 30 { 31 try 32 { 33 ep = new ExcelPackage(new FileInfo(filePath)); 34 35 if (ep == null) return false; 36 book =ep.Workbook; 37 sheetCount = book.Worksheets.Count; 38 currentSheetIndex = 0; 39 currentSheet = book.Worksheets[1]; 40 ifOpen = true; 41 } 42 catch (Exception ex) 43 { 44 throw new Exception(ex.Message); 45 } 46 return true; 47 } 48 49 public void Close() 50 { 51 if (!ifOpen || ep == null) return; 52 ep.Dispose(); 53 } 54 55 //public ExcelVersion Version 56 //{ get { return ExcelVersion.Excel07; } } 57 58 public string FilePath 59 { 60 get { return filePath; } 61 set { filePath = value; } 62 } 63 64 public bool IfOpen 65 { get { return ifOpen; } } 66 67 public int SheetCount 68 { get { return sheetCount; } } 69 70 public int CurrentSheetIndex 71 { 72 get { return currentSheetIndex; } 73 set 74 { 75 if (value != currentSheetIndex) 76 { 77 if (value >= sheetCount) 78 throw new Exception("工作表序号超出范围"); 79 currentSheetIndex = value; 80 currentSheet =book.Worksheets[currentSheetIndex+1]; 81 } 82 } 83 } 84 85 public int GetRowCount() 86 { 87 if (currentSheet == null) return 0; 88 return currentSheet.Dimension.End.Row; 89 } 90 91 public int GetColumnCount() 92 { 93 if (currentSheet == null) return 0; 94 return currentSheet.Dimension.End.Column; 95 } 96 97 public int GetCellCountInRow(int Row) 98 { 99 if (currentSheet == null) return 0; 100 if (Row >= currentSheet.Dimension.End.Row) return 0; 101 return currentSheet.Dimension.End.Column; 102 } 103 //根据行号和列号获取指定单元格的数据 104 public string GetCellValue(int Row, int Col) 105 { 106 if (currentSheet == null) return ""; 107 if (Row >= currentSheet.Dimension.End.Row || Col >= currentSheet.Dimension.End.Column) return ""; 108 object tmpO =currentSheet.GetValue(Row+1, Col+1); 109 if (tmpO == null) return ""; 110 return tmpO.ToString(); 111 } 112 } 113 }
方法调用实现功能：
1 //用于程序是在本地，所以此时的路径是本地电脑的绝对路劲； 2 //当程序发布后此路径应该是服务器上的绝对路径，所以在此之前还要有 3 //一项功能是将本地文件上传到服务器上的指定位置，此时在获取路径即可 4 public string GetExcelToCtripPolicy(string filePath) 5 { 6 ExcelLib lib = new ExcelLib(); 7 if (filePath == null) 8 return new ReturnResult<bool>(false, "未找到相应文件"); 9 string str= tmp.GetCellValue(i, j); 10 return str; 11 }
方法二：将Excel表格转化成DataTable表，然后在对DataTable进行业务操作
1 using Abp.Application.Services; 2 using OfficeOpenXml; 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using System.IO; 7 using System.Linq; 8 using System.Text; 9 using System.Threading.Tasks; 10 11 namespace HYZT.Ltxy.International.Ctrip.GetExcelToDataTable 12 { 13 public class EPPlusHelperAppService:ApplicationService,IEPPlusHelperAppService 14 { 15 private static string GetString(object obj) 16 { 17 try 18 { 19 return obj.ToString(); 20 } 21 catch (Exception ex) 22 { 23 return ""; 24 } 25 } 26 27 /// <summary> 28 ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet） 29 /// </summary> 30 /// <param name="fullFielPath">文件的绝对路径</param> 31 /// <returns></returns> 32 public DataTable WorksheetToTable(string filePath) 33 { 34 try 35 { 36 FileInfo existingFile = new FileInfo(filePath); 37 38 ExcelPackage package = new ExcelPackage(existingFile); 39 ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//选定 指定页 40 41 return WorksheetToTable(worksheet); 42 } 43 catch (Exception) 44 { 45 throw; 46 } 47 } 48 49 /// <summary> 50 /// 将worksheet转成datatable 51 /// </summary> 52 /// <param name="worksheet">待处理的worksheet</param> 53 /// <returns>返回处理后的datatable</returns> 54 public static DataTable WorksheetToTable(ExcelWorksheet worksheet) 55 { 56 //获取worksheet的行数 57 int rows = worksheet.Dimension.End.Row; 58 //获取worksheet的列数 59 int cols = worksheet.Dimension.End.Column; 60 61 DataTable dt = new DataTable(worksheet.Name); 62 DataRow dr = null; 63 for (int i = 1; i <= rows; i++) 64 { 65 if (i > 1) 66 dr = dt.Rows.Add(); 67 68 for (int j = 1; j <= cols; j++) 69 { 70 //默认将第一行设置为datatable的标题 71 if (i == 1) 72 dt.Columns.Add(GetString(worksheet.Cells[i, j].Value)); 73 //剩下的写入datatable 74 else 75 dr[j - 1] = GetString(worksheet.Cells[i, j].Value); 76 } 77 } 78 return dt; 79 } 80 } 81 }
之前我有一个程序用的是方法一进行Excel导入的，速度不是很快，后来我又用了第二种方法但是速度更慢了，到底这两种方法哪种快，请大虾指导，还是我用第二种方法的时候业务判断有问题，不得而知，
就请明白人指导我到底这两种方法哪种比较好些；
3：实体类与DataTable之间的互转：
1 /// <summary> 2 /// DataTable与实体类互相转换 3 /// </summary> 4 /// <typeparam name="T">实体类</typeparam> 5 public class ModelHandler<T> where T : new() 6 { 7 #region DataTable转换成实体类 8 9 /// <summary> 10 /// 填充对象列表：用DataSet的第一个表填充实体类 11 /// </summary> 12 /// <param name="ds">DataSet</param> 13 /// <returns></returns> 14 public List<T> FillModel(DataSet ds) 15 { 16 if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0) 17 { 18 return null; 19 } 20 else 21 { 22 return FillModel(ds.Tables[0]); 23 } 24 } 25 26 /// <summary> 27 /// 填充对象列表：用DataSet的第index个表填充实体类 28 /// </summary> 29 public List<T> FillModel(DataSet ds, int index) 30 { 31 if (ds == null || ds.Tables.Count <= index || ds.Tables[index].Rows.Count == 0) 32 { 33 return null; 34 } 35 else 36 { 37 return FillModel(ds.Tables[index]); 38 } 39 } 40 41 /// <summary> 42 /// 填充对象列表：用DataTable填充实体类 43 /// </summary> 44 public List<T> FillModel(DataTable dt) 45 { 46 if (dt == null || dt.Rows.Count == 0) 47 { 48 return null; 49 } 50 List<T> modelList = new List<T>(); 51 foreach (DataRow dr in dt.Rows) 52 { 53 //T model = (T)Activator.CreateInstance(typeof(T)); 54 T model = new T(); 55 for (int i = 0; i < dr.Table.Columns.Count; i++) 56 { 57 PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName); 58 if (propertyInfo != null && dr[i] != DBNull.Value) 59 propertyInfo.SetValue(model, dr[i], null); 60 } 61 62 modelList.Add(model); 63 } 64 return modelList; 65 } 66 67 /// <summary> 68 /// 填充对象：用DataRow填充实体类 69 /// </summary> 70 public T FillModel(DataRow dr) 71 { 72 if (dr == null) 73 { 74 return default(T); 75 } 76 77 //T model = (T)Activator.CreateInstance(typeof(T)); 78 T model = new T(); 79 80 for (int i = 0; i < dr.Table.Columns.Count; i++) 81 { 82 PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName); 83 if (propertyInfo != null && dr[i] != DBNull.Value) 84 propertyInfo.SetValue(model,dr[i],null); 85 } 86 return model; 87 } 88 89 #endregion 90 91 #region 实体类转换成DataTable 92 93 /// <summary> 94 /// 实体类转换成DataSet 95 /// </summary> 96 /// <param name="modelList">实体类列表</param> 97 /// <returns></returns> 98 public DataSet FillDataSet(List<T> modelList) 99 { 100 if (modelList == null || modelList.Count == 0) 101 { 102 return null; 103 } 104 else 105 { 106 DataSet ds = new DataSet(); 107 ds.Tables.Add(FillDataTable(modelList)); 108 return ds; 109 } 110 } 111 112 /// <summary> 113 /// 实体类转换成DataTable 114 /// </summary> 115 /// <param name="modelList">实体类列表</param> 116 /// <returns></returns> 117 public DataTable FillDataTable(List<T> modelList) 118 { 119 if (modelList == null || modelList.Count == 0) 120 { 121 return null; 122 } 123 DataTable dt = CreateData(modelList[0]); 124 125 foreach(T model in modelList) 126 { 127 DataRow dataRow = dt.NewRow(); 128 foreach (PropertyInfo propertyInfo in typeof(T).GetProperties()) 129 { 130 dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null); 131 } 132 dt.Rows.Add(dataRow); 133 } 134 return dt; 135 } 136 137 /// <summary> 138 /// 根据实体类得到表结构 139 /// </summary> 140 /// <param name="model">实体类</param> 141 /// <returns></returns> 142 private DataTable CreateData(T model) 143 { 144 DataTable dataTable = new DataTable(typeof (T).Name); 145 foreach (PropertyInfo propertyInfo in typeof(T).GetProperties()) 146 { 147 dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType)); 148 } 149 return dataTable; 150 } 151 152 #endregion 153 }
3.1：将实体类转化成DataTable之后对DataTable进行操作
//首先将数据库中查出的数据变成实体类集合，然后将实体类集合转变成DataTable表格 //dataPercent,然后在对此表格进行操作，表头转化和表格信息
//设置新表的表头：即字段名，有英文改为中文
1 for (int i = 0; i < dataPercent.Columns.Count; i++) 2 { 3 DataColumn column = dataPercent.Columns[i]; 4 string name = column.ColumnName; 5 switch (name) 6 { 7 case "IsDomestic": 8 dataPercent.Columns[i].ColumnName = "国内/国际"; 9 break; 10 case "TripType": 11 dataPercent.Columns[i].ColumnName = "行程类型"; 12 break; 13 case "GoFlightCode": 14 dataPercent.Columns[i].ColumnName = "去程航班号"; 15 break; 16 case "GoCabin": 17 dataPercent.Columns[i].ColumnName = "去程舱位"; 18 break; 19 case "GoSeatNum": 20 dataPercent.Columns[i].ColumnName = "去程座位数"; 21 break; 22 case "Line": 23 dataPercent.Columns[i].ColumnName = "去程行程"; 24 break; 25 case "DepartDate": 26 dataPercent.Columns[i].ColumnName = "去程航班日期"; 27 break; 28 case "BackFlightCode": 29 dataPercent.Columns[i].ColumnName = "回程航班号"; 30 break; 31 case "BackCabin": 32 dataPercent.Columns[i].ColumnName = "回程舱位"; 33 break; 34 case "ReturnDate": 35 dataPercent.Columns[i].ColumnName = "回程航班日期"; 36 break; 37 case "BackSeatNum": 38 dataPercent.Columns[i].ColumnName = "回程座位数"; 39 break; 40 case "AvCmd": 41 dataPercent.Columns[i].ColumnName = "黑屏的AV查询指令"; 42 break; 43 case "State": 44 dataPercent.Columns[i].ColumnName = "状态"; 45 break; 46 case "Interval": 47 dataPercent.Columns[i].ColumnName = "间隔时间(分钟)"; 48 break; 49 case "Telphone": 50 dataPercent.Columns[i].ColumnName = "联系电话"; 51 break; 52 case "Remark": 53 dataPercent.Columns[i].ColumnName = "备注"; 54 break; 55 } 56 } 57 DataTable dtResult = new DataTable(); 58 //克隆表结构 59 dtResult = dataPercent.Clone();
//将克隆的表格进行字段类型的重置，有利于改变表格数据 60 foreach (DataColumn col in dtResult.Columns) 61 { 62 if (col.ColumnName == "行程类型" || col.ColumnName == "国内/国际" ||col.ColumnName =="状态") 63 { 64 //修改列类型 65 col.DataType = typeof(String); 66 } 67 } 68 foreach (DataRow row in dataPercent.Rows) 69 { 70 DataRow rowNew = dtResult.NewRow(); 71 //rowNew["Id"] = row["Id"]; 72 rowNew["国内/国际"] = row["国内/国际"] == "true" ? "是" : "否"; 73 rowNew["行程类型"] = row["行程类型"] == "1" ? "单程" : "往返"; 74 rowNew["去程航班号"] = row["去程航班号"]; 75 rowNew["去程舱位"] = row["去程舱位"]; 76 rowNew["去程座位数"] = row["去程座位数"]; 77 rowNew["去程行程"] = row["去程行程"]; 78 rowNew["去程航班日期"] = row["去程航班日期"]; 79 rowNew["回程航班号"] = row["回程航班号"]; 80 rowNew["回程舱位"] = row["回程舱位"]; 81 rowNew["回程航班日期"] = row["回程航班日期"]; 82 rowNew["回程座位数"] = row["回程座位数"]; 83 rowNew["黑屏的AV查询指令"] = row["黑屏的AV查询指令"]; 84 //rowNew["创建人Id"] = row["创建人Id"]; 85 rowNew["状态"] = row["状态"] == "1" ? "有效" : "挂起"; 86 rowNew["间隔时间(分钟)"] = row["间隔时间(分钟)"]; 87 rowNew["联系电话"] = row["联系电话"]; 88 rowNew["备注"] = row["备注"]; 89 dtResult.Rows.Add(rowNew); 90 }