将Excel表中的数据批量导入到数据库

毕业设计需要，做了一个批量导入　　

期间的错误还是调试了蛮久的，特别是　 excelConnString 要特别注意不能有任何错误　

注意：我这里用的excel2007

 

　　　　　string path = fpImport.PostedFile.FileName;//fpImport 是我用的控件名

            //string filename = fpImport.PostedFile.FileName.Substring(fpImport.PostedFile.FileName.LastIndexOf("\\"));

            string filename = System.IO.Path.GetFileName(path);
            fpImport.PostedFile.SaveAs(Server.MapPath("fileupload\\") + filename);//上传文件，这个也很重要

            //SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

            //连接Excel 数据源
            string excelConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("fileupload\\") + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

            //excelConnString += @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";

            OleDbConnection excelConn = new OleDbConnection(excelConnString);
            excelConn.Open();
            //DataTable dtExcelSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            //string SheetName = dtExcelSchema.Rows[1]["TABLE_NAME"].ToString();
            //OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + SheetName + "]", excelConn);

            string sql = "select *from[sheet1$]";

            OleDbDataAdapter mycomm = new OleDbDataAdapter(sql, excelConn);

            DataSet myds = new DataSet();
            mycomm.Fill(myds);

            //SqlCommand cm = new SqlCommand();
            //cm.Connection = conn;
            //conn.Open();
            StudentService stuService = new StudentService();
            StudentInfo model = new StudentInfo();
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                //string Sql = "insert into Student(StudentId,CollegeId,MedicalId,StudentName,Sex,Age,Grade,Password)values(" + myds.Tables[0].Rows[i]["学号"] + ","
                //    + myds.Tables[0].Rows[i]["学院编号"] + "," + myds.Tables[0].Rows[i]["医疗卡号"] + "," + myds.Tables[0].Rows[i]["姓名"] + ","
                //    + myds.Tables[0].Rows[i]["性别"] + "," + myds.Tables[0].Rows[i]["年龄"] + "," + myds.Tables[0].Rows[i]["年级"] + "," +myds.Tables[0].Rows[i]["密码"] + ")";

                //cm.CommandText = Sql;
                if (!stuService.Exists(myds.Tables[0].Rows[i]["学号"].ToString()))//这里对重复数据导入也做了处理！！！
                {
                    model.StudentId = myds.Tables[0].Rows[i]["学号"].ToString();
                    model.CollegeId = Convert.ToInt32(myds.Tables[0].Rows[i]["学院编号"].ToString());
                    model.MedicalId = myds.Tables[0].Rows[i]["医疗卡号"].ToString();
                    model.StudentName = myds.Tables[0].Rows[i]["姓名"].ToString();
                    model.Sex = myds.Tables[0].Rows[i]["性别"].ToString();
                    model.Age = Convert.ToInt32(myds.Tables[0].Rows[i]["年龄"].ToString());
                    model.Grade = myds.Tables[0].Rows[i]["年级"].ToString();
                    model.Password = myds.Tables[0].Rows[i]["密码"].ToString();
                    stuService.Add(model);
                }
                //else
                //{

                //}

                //try
                //{
                //    //cm.ExecuteNonQuery();
                   
                //}
                //catch (Exception ex)

                //{
                //    //throw new System.Exception(ex.ToString());
                //    Response.Write("<script>alert('注意：您导入的数据中有已经有该学生学号！')</script>");

                //}
            }
            Response.Write("<script>alert('导入成功！')</script>");
            //cm.Dispose();
            //conn.Close();