黑马程序员之C#学习笔记:将EXCEL表中的数据导入到后台数据库

--------------------------------------------------- 2345王牌技术员联盟、2345王牌技术员联盟、期待与您交流！---------------------------------------------------------

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("您还没有选择要导入的文件！");
  }

}

--------------------------------------------------- 2345王牌技术员联盟、2345王牌技术员联盟、期待与您交流！---------------------------------------------------------