Excel导入类ExcelHelper 

  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  }

 

posted @ 2013-04-25 10:20  loong618  阅读(342)  评论(0)    收藏  举报