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

}