1 /// <summary> 2 /// Excel导入/导出通用类 3 /// </summary> 4 public class ExcelHelper 5 { 6 /// <summary> 7 /// 是否是版本号为12的Excel文件 8 /// </summary> 9 private static string FileExt = ".xls"; 10 /// <summary> 11 /// 构造Excel的连接字符串 12 /// </summary> 13 /// <param name="excelPath"></param> 14 /// <returns></returns> 15 private static string ExcelConnectionString(string excelPath) 16 { 17 if (!excelPath.ToLower().Contains(".xlsx")) 18 { 19 return string.Format( 20 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", 21 excelPath); 22 } 23 return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", 24 excelPath); 25 26 } 27 28 /// <summary> 29 /// 构造Excel的连接字符串 30 /// </summary> 31 /// <param name="excelPath"></param> 32 /// <returns></returns> 33 private static string ExcelConnectionString(string excelPath, string fileExt) 34 { 35 FileExt = fileExt; 36 if (Regex.IsMatch(FileExt, @"^\.?xls$", RegexOptions.IgnoreCase)) 37 return string.Format( 38 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", 39 excelPath); 40 else 41 return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", 42 excelPath); 43 } 44 45 /// <summary> 46 /// 由EXCEL转换成DataTable(Excel绝对路径) 47 /// </summary> 48 /// <param name="excelPath"></param> 49 /// <returns></returns> 50 public static DataTable GetDataTable(string excelPath) 51 { 52 string selectCommandText = "select * from [sheet1$]"; 53 54 DataSet dataSet = null; 55 dataSet = new DataSet(); 56 new OleDbDataAdapter(selectCommandText, ExcelConnectionString(excelPath)).Fill(dataSet); 57 if (dataSet.Tables.Count > 0) 58 { 59 return dataSet.Tables[0]; 60 } 61 62 return null; 63 } 64 65 /// <summary> 66 /// 由EXCEL转换成DataTable(Excel绝对路径,Sheet名称) 67 /// </summary> 68 /// <param name="strpath"> 文件路径及文件名 </param> 69 /// <param name="SheetName">工作表名称</param> 70 /// <returns> </returns> 71 public static DataTable GetDataTable(String strpath, string SheetName) 72 { 73 OleDbDataAdapter myCommand = new OleDbDataAdapter( 74 "SELECT * FROM [" + SheetName + "$A1:Z1002]", ExcelConnectionString(strpath)); 75 76 DataTable dt = new DataTable(); 77 myCommand.Fill(dt); 78 return dt; 79 } 80 81 82 83 /// <summary> 84 /// 由EXCEL转换成DataTable(Excel绝对路径)
85 /// </summary> 86 /// <param name="excelPath"></param> 87 /// <returns></returns> 88 public static DataTable GetDataTable1(string excelPath) 89 { 90 string selectCommandText = "select * from [sheet1$A3:F35] "; 91 92 DataSet dataSet = null; 93 dataSet = new DataSet(); 94 new OleDbDataAdapter(selectCommandText, ExcelConnectionString(excelPath)).Fill(dataSet); 95 if (dataSet.Tables.Count > 0) 96 { 97 return dataSet.Tables[0]; 98 } 99 100 return null; 101 } 102 103 }