.net读取Excel

.net读取Excel

1. 定义连接字符串:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CC.excel;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

2. 使用的 select command : SELECT * FROM [sheet1$]
工作表名+$ (例如，[Sheet1$] )。
使用特殊位址的 (例如，[Sheet1$A1:B1]): "Select * from [Sheet1$A1:B1]"

3. 说明: "HDR=Yes;" 包含第一行, "IMEX=1;"  将excel cell 內含值视为 text

示例代码:

 1
 2        public void ExcelTest()
 3        {
 4            DataSet myDS = new DataSet();
 5
 6            //数据库连接字符串
 7            string myConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"E:\\CSharpTest\\2005\\ReadExcelTest\\ReadExcelTest\\test.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
 8            //查询字符串 
 9            string mySQLstr = "SELECT * FROM [XXX$]";
10            //连接数据库操作 
11            OleDbConnection myConnection = new OleDbConnection(myConn);
12
13            try
14            {               
15                //执行SQL语句操作 
16                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(mySQLstr, myConnection);
17                //打开Excel
18                myConnection.Open();
19                //向DataSet填充数据
20                myDataAdapter.Fill(myDS);
21                if (myDS.Tables[0].Rows.Count > 0)
22                    Console.Write(myDS.Tables[0].Rows[0]["XX"].ToString());
23                else
24                    Console.Write("查询结果为空!");
25                Console.Read();
26            }
27            catch (Exception ex)
28            {
29                throw new Exception(ex.Message);
30            }
31            finally {
32                if (myConnection.State == ConnectionState.Open)
33                    myConnection.Close();
34            }
35        }

参考: http://www.connectionstrings.com/?carrier=excel