Excel读写操作
本文介绍了一个使用Microsoft Jet OleDb实现的简单方法来读取和写入Excel文件。通过提供的代码示例，您可以了解到如何将Excel文件转换为DataSet以及如何将数据写回Excel文件。
1
/**//// <summary>
2
/// 读取Excel文档
3/// </summary>
4/// <param name="Path">文件名称</param>
5
/// <returns>返回一个数据集</returns>
6
public DataSet ExcelToDS(string Path)
7
{
8string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
9OleDbConnection conn = new OleDbConnection(strConn);
10conn.Open();
11string strExcel = "";
12OleDbDataAdapter myCommand = null;
13DataSet ds = null;
14strExcel="select * from [sheet1$]";
15myCommand = new OleDbDataAdapter(strExcel, strConn);
16ds = new DataSet();
17myCommand.Fill(ds,"table1");
18return ds;
19}
20
21
22/**//// <summary>
23/// 写入Excel文档
24/// </summary>
25/// <param name="Path">文件名称</param>
26public bool SaveFP2toExcel(string Path)
27{
28try
29
{
30string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
31OleDbConnection conn = new OleDbConnection(strConn);
32conn.Open();
33System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
34cmd.Connection =conn;
35//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
36//cmd.ExecuteNonQuery ();
37for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
38{
39if(fp2.Sheets [0].Cells[i,0].Text!="")
40{
41cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
42fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
43"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
44cmd.ExecuteNonQuery ();
45
}
46}
47conn.Close ();
48return true;
49}
50catch(System.Data.OleDb.OleDbException ex)
51{
52System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
53}
54return false;
55}
/**//// <summary>2
/// 读取Excel文档3
/// </summary>4
/// <param name="Path">文件名称</param>5
/// <returns>返回一个数据集</returns>6
public DataSet ExcelToDS(string Path)7
{8
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";9
OleDbConnection conn = new OleDbConnection(strConn);10
conn.Open();11
string strExcel = "";12
OleDbDataAdapter myCommand = null;13
DataSet ds = null;14
strExcel="select * from [sheet1$]";15
myCommand = new OleDbDataAdapter(strExcel, strConn);16
ds = new DataSet();17
myCommand.Fill(ds,"table1");18
return ds;19
}20
21
22
/**//// <summary>23
/// 写入Excel文档24
/// </summary>25
/// <param name="Path">文件名称</param>26
public bool SaveFP2toExcel(string Path)27
{28
try29
{30
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";31
OleDbConnection conn = new OleDbConnection(strConn);32
conn.Open();33
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();34
cmd.Connection =conn;35
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";36
//cmd.ExecuteNonQuery ();37
for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)38
{39
if(fp2.Sheets [0].Cells[i,0].Text!="")40
{41
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+42
fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+43
"','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";44
cmd.ExecuteNonQuery ();45
}46
}47
conn.Close ();48
return true;49
}50
catch(System.Data.OleDb.OleDbException ex)51
{52
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );53
}54
return false;55
}
扫一扫
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
