注：对于实体类对象最好新建一个并且继承原有实体类，这样可以将类型进行修改；
方法一：此种方法是用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 ExcelLib11 {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 ICtripPolicyExcelImport10 {11 /// 打开文件 12 bool Open(); 13 //ExcelVersion Version { get; }14 /// 文件路径 15 string FilePath { get; set; }16 /// 文件是否已经打开 17 bool IfOpen { get; }18 /// 文件包含工作表的数量 19 int SheetCount { get; }20 /// 当前工作表序号 21 int CurrentSheetIndex { get; set; }22 /// 获取当前工作表中行数 23 int GetRowCount();24 /// 获取当前工作表中列数 25 int GetColumnCount();26 /// 获取当前工作表中某一行中单元格的数量 27 /// 行序号 28 int GetCellCountInRow(int Row);29 /// 获取当前工作表中某一单元格的值（按字符串返回） 30 /// 行序号 31 /// 列序号 32 string GetCellValue(int Row, int Col);33 /// 关闭文件 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 (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.GetExcelToDataTable12 {13 public class EPPlusHelperAppService:ApplicationService,IEPPlusHelperAppService14 {15 private static string GetString(object obj)16 {17 try18 {19 return obj.ToString();20 }21 catch (Exception ex)22 {23 return "";24 }25 }26 27 /// 28 ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）29 /// 30 /// 文件的绝对路径31 /// 50 /// 将worksheet转成datatable51 /// 52 /// 待处理的worksheet53 /// 返回处理后的datatable 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 //剩下的写入datatable74 else75 dr[j - 1] = GetString(worksheet.Cells[i, j].Value);76 }77 }78 return dt;79 }80 }81 } 之前我有一个程序用的是方法一进行Excel导入的，速度不是很快，后来我又用了第二种方法但是速度更慢了，到底这两种方法哪种快，请大虾指导，还是我用第二种方法的时候业务判断有问题，不得而知，
就请明白人指导我到底这两种方法哪种比较好些；
3：实体类与DataTable之间的互转：
1 ///2 /// DataTable与实体类互相转换 3 /// 4 ///实体类 5 public class ModelHandlerwhere T : new() 6 { 7 #region DataTable转换成实体类 8 9 /// 10 /// 填充对象列表：用DataSet的第一个表填充实体类 11 /// 12 /// DataSet 13 ///14 public List 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 /// 27 /// 填充对象列表：用DataSet的第index个表填充实体类 28 /// 29 public ListFillModel(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 /// 42 /// 填充对象列表：用DataTable填充实体类 43 /// 44 public ListFillModel(DataTable dt) 45 { 46 if (dt == null || dt.Rows.Count == 0) 47 { 48 return null; 49 } 50 List modelList = new List (); 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 /// 68 /// 填充对象：用DataRow填充实体类 69 /// 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 ///94 /// 实体类转换成DataSet 95 /// 96 /// 实体类列表 97 ///98 public DataSet FillDataSet(List modelList) 99 {100 if (modelList == null || modelList.Count == 0)101 {102 return null;103 }104 else105 {106 DataSet ds = new DataSet();107 ds.Tables.Add(FillDataTable(modelList));108 return ds;109 }110 }111 112 /// 113 /// 实体类转换成DataTable114 /// 115 /// 实体类列表116 ///117 public DataTable FillDataTable(List 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 /// 138 /// 根据实体类得到表结构139 /// 140 /// 实体类141 ///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 #endregion153 }
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 }