c#读取excel文件

读取Excel文件到DataSet中

1.         /// <summary> 
2.         /// 读取Excel文档 
3.         /// </summary> 
4.         /// <param name="Path">文件名称</param> 
5.         /// <returns>返回一个数据集</returns> 
6.         public DataSet ExcelToDS(string Path)
7.         {
8.             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
9.             OleDbConnection conn = new OleDbConnection(strConn);
10.             conn.Open();
11.             string strExcel = "";
12.             OleDbDataAdapter myCommand = null;
13.             DataSet ds = null;
14.             strExcel = "select * from [Recovered_Sheet1$]";
15.             myCommand = new OleDbDataAdapter(strExcel, strConn);
16.             ds = new DataSet();
17.             myCommand.Fill(ds);
18.             return ds;
19.         }

写入Excel文件

1.     // <summary> 
2.         // 写入Excel文档 
3.         // <//summary> 
4.         // <param name="Path">文件名称<//param> 
5.         public bool SaveFP2toExcel(string Path)
6.         {
7.             try
8.             {
9.                 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
10.                 OleDbConnection conn = new OleDbConnection(strConn);
11.                 conn.Open();
12.                 System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
13.                 cmd.Connection = conn;
14.                 cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'"; 
15.                 cmd.ExecuteNonQuery (); 
16.                 for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
17.                 {
18.                     if (fp2.Sheets[0].Cells[i, 0].Text != "")
19.                     {
20.                         cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
21.                         fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
22.                         "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
23.                         cmd.ExecuteNonQuery();
24.                     }
25.                 }
26.                 conn.Close();
27.                 return true;
28.             }
29.             catch (System.Data.OleDb.OleDbException ex)
30.             {
31.                 System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
32.             }
33.             return false;
34.         }