C#读取Excel到DataTable



1. 原文地址：http://developer.51cto.com/art/201302/380622.htm
2. 亦可参考：http://blog.youkuaiyun.com/jpday/article/details/9915445
   
3. /// <summary>  
4.         /// 读取Excel文件到DataSet中  
5.         /// </summary>  
6.         /// <param name="filePath">文件路径</param> 
7.         /// <returns></returns> 
8.         public static DataSet ToDataTable(string filePath)  
9.         {  
10.             string connStr = "";             
11.             string fileType = System.IO.Path.GetExtension(fileName);  
12.             if (string.IsNullOrEmpty(fileType)) return null;  
13.    
14.             if (fileType == ".xls")  
15.                 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";  
16.             else 
17.                 connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";  
18.             string sql_F = "Select * FROM [{0}]";  
19.    
20.             OleDbConnection conn = null;  
21.             OleDbDataAdapter da = null;  
22.             DataTable dtSheetName= null;  
23.    
24.             DataSet ds = new DataSet();  
25.             try 
26.             {  
27.                 // 初始化连接，并打开  
28.                 conn = new OleDbConnection(connStr);  
29.                 conn.Open();  
30.    
31.                 // 获取数据源的表定义元数据                        
32.                 string SheetName = "";  
33.                 dtSheetName= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });  
34.    
35.                 // 初始化适配器  
36.                 da = new OleDbDataAdapter();  
37.                 for (int i = 0; i < dtSheetName.Rows.Count; i++)  
38.                 {  
39.                     SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];  
40.    
41.                     if (SheetName .Contains("$") && !SheetName .Replace("'", "").EndsWith("$"))  
42.                     {  
43.                         continue;  
44.                     }  
45.    
46.                     da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName ), conn);  
47.                     DataSet dsItem = new DataSet();  
48.                     da.Fill(dsItem, tblName);  
49.    
50.                     ds.Tables.Add(dsItem.Tables[0].Copy());  
51.                 }  
52.             }  
53.             catch (Exception ex)  
54.             {  
55.             }  
56.             finally 
57.             {  
58.                 // 关闭连接  
59.                 if (conn.State == ConnectionState.Open)  
60.                 {  
61.                     conn.Close();  
62.                     da.Dispose();  
63.                     conn.Dispose();  
64.                 }  
65.             }  
66.             return ds;  
67.         }