.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
}