如果你直接把Excel当成一个数据源,那么操作就会方便的多,如下代码:
try
{
OleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=aa.xls;Extended Properties=Excel 8.0;");
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM [Sheet1$]",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet,"ExcelInfo");
MessageBox.Show(thisDataSet.Tables["ExcelInfo"].Rows[1][1].ToString());
myConn.Close();
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Message);
}
try
{
OleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=aa.xls;Extended Properties=Excel 8.0;");
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM [Sheet1$]",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet,"ExcelInfo");
MessageBox.Show(thisDataSet.Tables["ExcelInfo"].Rows[1][1].ToString());
myConn.Close();
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Message);
}
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Inetpub\\wwwroot\\contacts.xls;"+
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [ContactList$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
然后从数据库中得到DataSet实例sqlDs,使用Merge函数融合myDataSet到sqlDs中
DataSet sqlDs=new DataSet();
sqlDs.Merge(myDataSet);
象数据库提交sqlDs
myDa.Update(sqlDs);//myDa产生sqlDs的OleDbDataAdapter
string conn="Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source="+this.textBox1.Text+";"+
"Extended Properties=Excel 8.0";
string cmstr="SELECT * FROM [Sheet1$]";
DataSet ds=new DataSet();
objconn=new OleDbConnection(conn);
objconn.Open();
objcommand=new OleDbCommand(cmstr,objconn);
objadapter=new OleDbDataAdapter(objcommand);
objadapter.Fill(ds);
dataSet11.Merge(ds);
sqlDataAdapter1.Fill(dataSet11);
sqlDataAdapter1.Update(dataSet11);
this.dataGrid1.DataSource=dataSet11;
objconn.Close();dataSet11,sqlDataAdapter1是通过.net环境的sqlDataAdapter组件自动生成的连接数据库中的一个表,以上的语句更新数据库但数据库毫无反映.