C#从Excel中读取数据

引入命名空间using System.Data.OleDb;

 1         /// <summary>
 2         /// 从Excel文件中读取数据
 3         /// </summary>
 4         /// <param name="path">Excel路径</param>
 5         /// <param name="sheet">表单名称</param>
 6         /// <returns></returns>
 7         private DataTable GetExcelData(string path, string sheet)
 8         {
 9             DataSet ds = new DataSet();
10             try
11             {
12                 string strType = Path.GetExtension(path);
13                 if (File.Exists(path))
14                 {
15                     string strCon = string.Empty;
16                     if (strType == ".xlsx")
17                     {
18                         strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;IMEX=1'";
19                     }
20                     else if (strType == ".xls")
21                     {
22                         strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;IMEX=1'";
23                     }
24 
25                     OleDbConnection conn = new OleDbConnection(strCon);
26                     conn.Open();
27                     string tblname = "[" + sheet + "$]";
28 
29                     try
30                     {
31                         string sql = "select * from " + tblname;
32                         OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
33                         adapter.Fill(ds, tblname);
34                     }
35                     catch (Exception ex)
36                     {
37                         //可以写点日志
38                     }
39                     finally
40                     {
41                         conn.Close();
42                     }
43                 }
44             }
45             catch (Exception ex)
46             {
47                 ////可以写点日志
48             }
49             return ds.Tables[0];
50 
51         }

如果程序报错：未在本地计算机上注册"microsoft.ACE.oledb.12.0"，去这里下载

http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

posted @ 2016-12-12 10:32  枫林余晖  阅读(1440)  评论(0)  编辑  收藏  举报