读取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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
1
private void OnLoad(object sender, System.EventArgs e)
2
{
3
// 在此处放置用户代码以初始化页面
4
Excel.Application xApp=new Excel.ApplicationClass();
5![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
16
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
17![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
//读取数据,通过Range对象
19
Excel.Range rng1=xSheet.get_Range("A1",Type.Missing);
20
Console.WriteLine(rng1.Value2);
21![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
//读取,通过Range对象,但使用不同的接口得到Range
23
Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
24
Console.WriteLine(rng2.Value2);
25![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
//写入数据
27
Excel.Range rng3=xSheet.get_Range("C6",Missing.Value);
28
rng3.Value2="Hello";
29
rng3.Interior.ColorIndex=6; //设置Range的背景色
30![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
//保存方式三
44
xBook.Save();
45![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
xSheet=null;
47
xBook=null;
48
xApp.Quit(); //这一句是非常重要的,否则Excel对象不能从内存中退出
49
xApp=null;
50
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)