.NET操作Excel笔记

如果你新建一个项目的话，首先要添加Microsoft.Office.Core 与Microsoft.Office.Interop.Exce这两个应用，然后就能很方便的操作了，示例代码（只实现了简单的读写）：

 

1 privateExcel._Application excelApp; 
2 privateWorkbook wbclass; 
3    
4 excelApp = newExcel.Application(); 
5    
6 objectobjOpt = System.Reflection.Missing.Value; 
7                
8 wbclass = (Workbook)excelApp.Workbooks.Open("E:\Book6.xlsx", objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);


上面声明，引用，并把要操作的 excel 的路径传给他

 

得到所有的表名：

 

 

1 List<string> list = new List<string>();
2            Excel.Sheets sheets = wbclass.Worksheets;
3            string sheetNams = string.Empty;
4            foreach (Excel.Worksheet sheet in sheets)
5            {
6                list.Add(sheet.Name);
7            }

 

 

 获取某个表中的数据，这里获取的是sheet 表中的：

 

01 publicExcel.Worksheet GetWorksheetByName(string name) 
02         { 
03             Excel.Worksheet sheet = null; 
04             Excel.Sheets sheets = wbclass.Worksheets; 
05             foreach (Excel.Worksheet s in sheets) 
06             { 
07                 if(s.Name == name) 
08                 { 
09                     sheet = s; 
10                     break; 
11                 } 
12             } 
13             returnsheet; 
14         } 
15    
16         publicSystem.Data.DataTable GetDateTable(string name) 
17         { 
18             System.Data.DataTable dt = newSystem.Data.DataTable(); 
19    
20             var worksheet = GetWorksheetByName(name);      //调用上面的方法，利用表名得到这张表 
21    
22             string cellContent; 
23    
24             intiRowCount = worksheet.UsedRange.Rows.Count; 
25             intiColCount = worksheet.UsedRange.Columns.Count; 
26             Excel.Range range; 
27             for (intiRow = 1; iRow <= iRowCount; iRow++) 
28             { 
29                 DataRow dr = dt.NewRow(); 
30    
31                 for (intiCol = 1; iCol <= iColCount; iCol++) 
32                 { 
33                     range = (Excel.Range)worksheet.Cells[iRow, iCol]; 
34    
35                     cellContent = (range.Value2 == null) ? "": range.Text.ToString(); 
36    
37                     if(iRow == 1) 
38                     { 
39                         dt.Columns.Add(cellContent); 
40                     } 
41                     else 
42                     { 
43                         dr[iCol - 1] = cellContent; 
44                     } 
45                 } 
46    
47                 if(iRow != 1) 
48                     dt.Rows.Add(dr); 
49             } 
50    
51             returndt; 
52    
53         }



上面得到的只是 

 
1 System.Data.DataTable

如何把数据取出来，请看下面：

 

1 var dataTable = GetDateTable("Sheet1");   //调用上面的方法 
2            
3            foreach (DataRow row indataTable.Rows) 
4            {      
5                stringa = (string)row[2]; 
6                stringb = (string)row[4]; 
7  
8 }
posted @ 2014-03-27 22:02  super1234  阅读(513)  评论(0)    收藏  举报