如何将EXCEL文件数据导入SQLSERVER中？
小弟现又碰到了一个难题，即不知如何将EXCEL文件导入至SQLSERVER中。
本人思路是： 在网络页面添加一个Upload控件和Button控件，通过点击Upload控件选择要导入的EXCEL文件，然后点击Button
按钮实现将文件数据导入SQLSERVER已建立的表中的功能，并删除刚才上传的EXCEL文件。
由于本人初学不久，对编程不是很熟悉，希望有过这方面经验的高手积极帮助一下小弟！最好能够提供c＃的代码参考一下，谢谢！！
[解决办法]
protected void Button2_Click(object sender, EventArgs e)
{
try
{
//开始导入
//建立EXCEL的连接
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
string sqlStr = "select * from [Sheet1$]";
OleDbCommand myCmd = new OleDbCommand(sqlStr, objConn);
OleDbDataAdapter myda = new OleDbDataAdapter(myCmd);
DataSet myDs = new DataSet();
myda.Fill(myDs, "Temp");
DataGrid1.DataSource = myDs;
try
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection mySqlConn = new SqlConnection(strConn);
mySqlConn.Open();
string strCmd = "";
int rows = myDs.Tables[0].Rows.Count;
int cols = myDs.Tables[0].Columns.Count;
int k = 0;
string a = "";
string chongfu = "";
for (int i = 0; i < rows; i++)
{
a = myDs.Tables[0].Rows[i].ItemArray[0].ToString();
string strSql = "select * from studys where idkey= "" + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + " "";
SqlDataAdapter myAda = new SqlDataAdapter(strSql, mySqlConn);
myAda.Fill(myDs, "Temp");
if (myAda.Fill(myDs, "Temp") != 0)
{
k += 1;
chongfu += "," + myDs.Tables[0].Rows[i].ItemArray[0].ToString();
}
else
{
strCmd = "insert into Studys (idkey,title,who,describe,place,begindate,enddate) values( "";
for (int j = 0; j < cols; j++)
{
if (j == 0)
{
strCmd = strCmd + myDs.Tables[0].Rows[i].ItemArray[j].ToString();
}
else
{
strCmd += " ", "" + myDs.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
strCmd += " ")";
//strCmd = " select * into studys from opendatasource ( "Microsoft.Jet.OLEDB.4.0 ", "Data Source=" + File1.PostedFile.FileName.ToString() + ";User ID=sa;Password=;HDR=YES;Extended Properties=Excel 8.0;Persist Security Info=false ")...sheet1$";
//try
// {
SqlCommand mySqlCmd = new SqlCommand(strCmd, mySqlConn);
mySqlCmd.ExecuteNonQuery();
// }
// catch (System.Data.SqlClient.SqlException ex)
// {
// Label1.Text = "导入出错，可能字段类型不匹配" + "出错在第" + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "行";
// }
}
}
mySqlConn.Close();
objConn.Close();
Label1.Text = "当前Excel文件中有" + k + "条记录与Sql数据库中的记录重复";
Label2.Text = "重复的记录为" + chongfu;
BindGrid();//在页面显示出来
}
catch (System.Data.SqlClient.SqlException ex)
{
Label1.Text =ex.Message ;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
Label1.Text = "请确保excel文件版本及路径正确，并现未被访问!操作未被进行";
return;
}
}
