C#操作Excel文件三种方式_彭世瑜_新浪博客

 1 //加载Excel   
 2 public static DataSet LoadDataFromExcel(string filePath)  
 3 {  
 4     try  
 5     {  
 6         string strConn;  
 7         strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";  
 8         OleDbConnection OleConn = new OleDbConnection(strConn);  
 9         OleConn.Open();  
10         String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等   
11   
12         OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);  
13         DataSet OleDsExcle = new DataSet();  
14         OleDaExcel.Fill(OleDsExcle, "Sheet1");  
15         OleConn.Close();  
16         return OleDsExcle;  
17     }  
18     catch (Exception err)  
19     {  
20         MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",  
21             MessageBoxButtons.OK, MessageBoxIcon.Information);  
22         return null;  
23     }  
24 }  

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

1. Start Microsoft Visual Studio .NET.
2. On the File menu, click New, and then click Project. Select Windows Application from the Visual C# Project types. Form1 is created by default.
3. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate Microsoft Excel Object Library, and click Select.
   3. Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click Yes.
   4. using Excel = Microsoft.Office.Interop.Excel;

 1 private void button1_Click(object sender, EventArgs e)
 2 {
 3     Excel.Application xlApp ;
 4     Excel.Workbook xlWorkBook ;
 5     Excel.Worksheet xlWorkSheet ;
 6     Excel.Range range ;
 7 
 8     string str;
 9     int rCnt = 0;
10     int cCnt = 0;
11 
12     xlApp = new Excel.Application();
13     xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls");
14     xlWorkSheet = xlWorkBook.Sheets["Sheet1"];
15 
16     range = xlWorkSheet.UsedRange;
17 
18     for (rCnt = 1; rCnt