excel数据导入sql

显示全部楼层 · 2015-1-22 09:12:21

public partial class Default2 : System.Web.UI.Page
{
string strConn = "Data Source=.;DataBase=Database;Uid=sa

wd=sa"; //链接SQL数据库
protected void Page_Load(object sender, EventArgs e)
{
      SqlConnection cn = new SqlConnection(strConn);
      cn.Open();
      SqlDataAdapter sda = new SqlDataAdapter("select * from suserss", cn);
      DataSet ds = new DataSet();
      sda.Fill(ds, "suserss");
      this.GridView1.DataSource = ds.Tables["suserss"];
      this.GridView1.DataKeyNames = new string[] { "gasId" };
      this.GridView1.DataBind();

}
/// <summary>
/// 查询EXCEL电子表格添加到DATASET
/// </summary>

public DataSet ExecleDs(string filenameurl, string table)
{
      string strConn = "

rovider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
      OleDbConnection conn = new OleDbConnection(strConn);
      conn.Open();
      DataSet ds = new DataSet();
      OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
      odda.Fill(ds, table);
      return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{
      if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
   {
         Response.Write("<script>alert('请您选择Excel文件')</script> ");
         return;//当无文件时,返回
   }
   string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
   if (IsXls != ".xls")
   {
         Response.Write("<script>alert('只可以选择Excel文件')</script>");
         return;//当选择的不是Excel文件时,返回
   }
      SqlConnection cn = new SqlConnection(strConn);
      cn.Open();
      string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;             //获取Execle文件名  DateTime日期函数
   // string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
      string savePath = Server.MapPath(("upfiles/") + filename);
      FileUpload1.SaveAs(savePath);                      //SaveAs 将上传的文件内容保存在服务器上
      DataSet ds = ExecleDs(savePath, filename);          //调用自定义方法
      DataRow[] dr = ds.Tables[filename].Select();          //定义一个DataRow数组
      int rowsnum = ds.Tables[filename].Rows.Count;
      if (rowsnum == 0)
      {
         Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
      }
      else
   {
      for (int i = 0; i < dr.Length; i++)
         {
            string gasId = dr["车辆ID"].ToString();
            string carName = dr["车辆"].ToString();//日期 excel列名【名称不能变,否则就会出错】
            string gasBeginTime = dr["起始加油时间"].ToString();//编号列名以下类似
            decimal beginLc = Convert.ToDecimal(dr["起始里程"].ToString());
            string gasEndTime = dr["最后加油时间"].ToString();
            decimal endLc = Convert.ToDecimal(dr["最后里程"].ToString());
            decimal gLs = Convert.ToDecimal(dr["公里数"].ToString());
            decimal gasNum = Convert.ToDecimal(dr["加油量"].ToString());
            decimal gasHao = Convert.ToDecimal(dr["油耗量"].ToString());
            string yearMonth = dr["年月"].ToString();
            string sqlcheck = "select count(*) from suserss where carName='" + carName + "'And yearMonth='" + yearMonth + "'";  //检查车辆是否存在
            SqlCommand sqlcmd = new SqlCommand(sqlcheck,cn);
            int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
            if (count < 1)
            {
               string insertstr = "insert into suserss (gasId,carName, gasBeginTime, beginLc, gasEndTime, endLc, gLs, gasNum, gasHao, yearMonth) values('" + gasId + "','" + carName + "','" + gasBeginTime + "'," + beginLc + ",'" + gasEndTime + "'," + endLc + "," + gLs + "," + gasNum + "," + gasHao + ",'" + yearMonth + "')";

               SqlCommand cmd = new SqlCommand(insertstr, cn);
               try
               {
                     cmd.ExecuteNonQuery();
               }
               catch (MembershipCreateUserException ex)    //捕捉异常
               {
                     Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
               }
            }
            else
            {

               Response.Write("<script>alert('内容重复！禁止导入');location='default.aspx'</script></script> ");
               continue;
            }
         }
         Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
   }

   cn.Close();

  }
}
请大家帮忙一下，这个代码导入增加了的数据的excel，数据可以导入到excel中，但是跳出的对话框是'内容重复！禁止导入'。请问这个能不能帮忙改一下。
刚刚发了一个，手贱的点了已解决，现在在发一个。

吻之樱花 · 2015-1-22 09:15:54

修改一下：这个代码导入增加了的数据的excel，数据可以导入到sql中，但是跳出的对话框是'内容重复！禁止导入'。请问这个能不能帮忙改一下。