从excal倒入到数据库

     //使用简单方式执行数据插入操作

            SqlConnection conn = new SqlConnection(strcon);
            SqlCommand cmd = conn.CreateCommand();
            conn.Open();
            //一定要在先打开数据库连接，然后在使用conn.BeginTransaction();开始事务
            //为数据库连接指定事物，表明在这个练级中执行的所有数据库的操作都使用事务机制，即要成功全成功，要失败全失败
            SqlTransaction tran = conn.BeginTransaction();
            //为命令对象指定事务，使用此命令对象执行的所有数据库操作都使用事务机制
            cmd.Transaction = tran;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Student(");
            strSql.Append("classid,studentnum,studentname,mobile,qq)");
            strSql.Append(" values (");
            strSql.Append("@classid,@studentnum,@studentname,@mobile,@qq)");
            cmd.CommandText = strSql.ToString();
            SqlParameter[] parameters = {
     new SqlParameter("@classid", SqlDbType.VarChar,50),
     new SqlParameter("@studentnum", SqlDbType.VarChar,50),
     new SqlParameter("@studentname", SqlDbType.VarChar,50),
     new SqlParameter("@mobile", SqlDbType.VarChar,50),
     new SqlParameter("@qq", SqlDbType.VarChar,50)};
            try
            {
                using (Stream stream = File.Open(@"E:\优快云\学生相关\2010级\10级net3班信息表.xls", FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(stream);
                    HSSFSheet sheet = workbook.GetSheet("10级net3班联系表");
                    StringBuilder sb1 = new StringBuilder();
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = sheet.GetRow(i);
                        //cmd.Parameters.Clear();

                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            parameters[j].Value = row.GetCell(j).StringCellValue;
                        }
                        //将上面声明和实例化的SqlParameter的数组添加到cmd.SqlParameter集合中，其相当于便利SqlParameter数组中的每一个元素，然后使用cmd.SqlParameter.Add(parameters[index]),其实AddRange方式内部还是调用了cmd.SqlParameter.Add方法
                        cmd.Parameters.AddRange(parameters);
                        cmd.ExecuteNonQuery();
                    }
                    //如果上面的代码执行没有发生错误，就提交事务
                    tran.Commit();
                }
                conn.Close();
                conn.Dispose();
                MessageBox.Show("操作成功");
            }
            catch
            {
                //如果前面try{}中的任何一行代码出现错误，代码都会停止执行下面的代码，而跳到catch中
                //回滚事物，在try{}中出错之前执行的所有针对数据库的操作都会取消
                tran.Rollback();
                MessageBox.Show("操作失败");
            }

 

 

 //使用三层执行插入数据操作
            using (Stream stream = File.Open(@"E:\优快云\学生相关\2010级\10级net3班信息表.xls", FileMode.Open, FileAccess.Read))//建立流，指向要读取的文件
            {
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                HSSFSheet sheet = workbook.GetSheet("10级net3班联系表");
                Excel.BLL.T_Student bstudent = new Excel.BLL.T_Student();
                int result = 0;
                //便利所有行，LastRowNum是最后一行的索引，而不是总的行数，总的行数应该是LastRowNum+1，所以下面使用i<=sheet.LastRowNum或者i<LastRowNum+1

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i);
                    Excel.Model.T_Student mstudent = new Excel.Model.T_Student();
                    mstudent.classid = row.GetCell(0).StringCellValue;
                    mstudent.studentnum = row.GetCell(1).StringCellValue;
                    mstudent.studentname = row.GetCell(2).StringCellValue;
                    mstudent.mobile = row.GetCell(3).StringCellValue;
                    mstudent.qq = row.GetCell(4).StringCellValue;
                    result = bstudent.Add(mstudent);
                }
                if (result > 0)
                {
                    MessageBox.Show("操作成功");
                }
            }