读取Excel数据
1
/// <summary>
2
/// 将指定Excel文件中的数据转换成DataTable对象,供应用程序进一步处理
3
/// </summary>
4
/// <param name="filePath"></param>
5
/// <returns></returns>
6
public static System.Data.DataTable Import(string filePath)
7
{
8
System.Data.DataTable rs = new System.Data.DataTable();
9
bool canOpen=false;
10
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source=" +
11
12
filePath + ";" + "Extended Properties=\"Excel 8.0;\"");
13
try//尝试数据连接是否可用
14
{
15
conn.Open();
16
conn.Close();
17
canOpen=true;
18
}
19
catch{}
20
//文件可以打开
21
if(canOpen)
22
{
23
try//如果数据连接可以打开则尝试读入数据
24
{
25
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",conn);
26
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
27
myData.Fill(rs);
28
conn.Close();
29
}
30
catch//如果数据连接可以打开但是读入数据失败,则从文件中提取出工作表的名称,再读入数据
31
{
32
string sheetName=GetSheetName(filePath);
33
if(sheetName.Length>0)
34
{
35
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM
36
37
["+sheetName+"$]",conn);
38
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
39
myData.Fill(rs);
40
conn.Close();
41
}
42
}
43
}
44
else
45
{
46
System.IO.StreamReader tmpStream=File.OpenText(filePath);
47
string tmpStr=tmpStream.ReadToEnd();
48
tmpStream.Close();
49
rs=GetDataTableFromString(tmpStr);
50
tmpStr="";
51
}
52
return rs;
53
}

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

1
private void OnLoad(object sender, System.EventArgs e)
2
{
3
// 在此处放置用户代码以初始化页面
4
Excel.Application xApp=new Excel.ApplicationClass();
5
6
xApp.Visible=true;
7
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
8
Excel.Workbook xBook=xApp.Workbooks._Open(@"D:\Sample.xls",
9
Missing.Value,Missing.Value,Missing.Value,Missing.Value
10
,Missing.Value,Missing.Value,Missing.Value,Missing.Value
11
,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
12
//xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
13
//指定要操作的Sheet,两种方式:
14
15
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
16
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
17
18
//读取数据,通过Range对象
19
Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
20
Console.WriteLine(rng1.Value2);
21
22
//读取,通过Range对象,但使用不同的接口得到Range
23
Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
24
Console.WriteLine(rng2.Value2);
25
26
//写入数据
27
Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
28
rng3.Value2="Hello";
29
rng3.Interior.ColorIndex=6; //设置Range的背景色
30
31
//保存方式一:保存WorkBook
32
xBook.SaveAs(@"D:\CData.xls",
33
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
34
Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
35
Missing.Value,Missing.Value);
36
37
//保存方式二:保存WorkSheet
38
xSheet.SaveAs(@"D:\CData2.xls",
39
Missing.Value,Missing.Value,Missing.Value,Missing.Value,
40
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
41
42
43
//保存方式三
44
xBook.Save();
45
46
xSheet=null;
47
xBook=null;
48
xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
49
xApp=null;
50
}

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50
