将EXCEL中的数据导入SQL SERVER数据库 - 黑马程序员技术交流社区

protected void btnUpload_Click(object sender, EventArgs e)
{
if (this.fuOpen.PostedFile.FileName != "")
{
//这里是确保文件是excel格式
//Response.Write(this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.')));
if (this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.') + 1) == "xls")
{
Random rd = new Random(1);
string filename = DateTime.Now.Date.ToString("yyyymmdd") + DateTime.Now.ToLongTimeString().Replace(":", "") + rd.Next(9999).ToString() + ".xls";
try
{
this.fuOpen.PostedFile.SaveAs(@Server.MapPath("fileupload/") + filename);
}
catch (HttpException he)
{
Response.Write("文件上传不成功，请检查文件是否过大，是否有写权限！");
return;
}
#region --------读取文件内容到服务器内存----------
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("fileupload") + "/" + filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
//这是从EXCEL中读取内容，要保证字段名和excel表中的字段名相同
string Sql = "select taotiName, Que_type, timu_neirong, OptionA, OptionB, OptionC, OptionD,Que_answer from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close();
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
#endregion
#region --------插入到数据库中---------
string conn1 = "Data Source=.\\SQLEXPRESS;Initial Catalog=ExamOnline;Integrated Security=SSPI;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1 = new OleDbConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string taotiName, Que_type, timu_neirong, OptionA, OptionB, OptionC, OptionD, Que_answer;
taotiName = ds.Tables["[Sheet1$]"].Rows[i]["taotiName"].ToString();
timu_neirong = ds.Tables["[Sheet1$]"].Rows[i]["timu_neirong"].ToString();
Que_type = ds.Tables["[Sheet1$]"].Rows[i]["Que_type"].ToString();
OptionA = ds.Tables["[Sheet1$]"].Rows[i]["OptionA"].ToString();
OptionB = ds.Tables["[Sheet1$]"].Rows[i]["OptionB"].ToString();
OptionC = ds.Tables["[Sheet1$]"].Rows[i]["OptionC"].ToString();
OptionD = ds.Tables["[Sheet1$]"].Rows[i]["OptionD"].ToString();
Que_answer = ds.Tables["[Sheet1$]"].Rows[i]["Que_answer"].ToString();
string excelsql = "insert into Questions_info(taotiName, Que_type, timu_neirong, OptionA, OptionB, OptionC, OptionD,Que_answer) values('" + taotiName + "','" + Que_type + "','" + timu_neirong + "','" + OptionA + "','" + OptionB + "','" + OptionC + "','" + OptionD + "','" + Que_answer + "') ";
OleDbCommand mycommand1 = new OleDbCommand(excelsql, thisconnection1);
try
{
mycommand1.ExecuteNonQuery();
}
catch (OleDbException ode)
{
Response.Write("<b>导入不成功，请重试！</b>");
return;
}
}
Response.Write("更新成功");
thisconnection1.Close();
#endregion
}
else
{
Response.Write("导入文件的格式不正确！");
}
}
else
{
Response.Write("您还没有选择要导入的文件！");
}
}

复制代码