三层架构Excel导入数据库

来源：互联网发布：玩游戏学编程app 编辑：程序博客网时间：2024/06/06 13:03

            using (Stream stream = File.Open("D:\\10级net3班信息表.xls", FileMode.Open, FileAccess.Read))//建立流，指向要读取的文件
            {
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                HSSFSheet sheet = workbook.GetSheet("10级net3班联系表");

Class.BLL.T_student bstudent = new Class.BLL.T_student();

                //便利所有行，LastRowNum是最后一行的索引，而不是总的行数，总的行数应该是LastRowNum+1，所以下面使用i<=sheet.LastRowNum或者i<LastRowNum+1
                List<Class.MODEL.T_student> list = new List<Class.MODEL.T_student>();
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                        HSSFRow row = sheet.GetRow(i);
                        Class.MODEL.T_student mstudent = new Class.MODEL.T_student();
                        mstudent.classid = row.GetCell(0).StringCellValue;
                        mstudent.studentid = row.GetCell(1).StringCellValue;
                        mstudent.studnetname = row.GetCell(2).StringCellValue;
                        mstudent.tell = row.GetCell(3).StringCellValue;
                        mstudent.QQ = row.GetCell(4).StringCellValue;
                        list.Add(mstudent);
                }
                bstudent.AddTran(list);
                MessageBox.Show("操作成功");
            }

//bll

public void AddTran(List<Class.MODEL.T_student> models)
        {
            dal.AddTran(models);
        }

//dal

public void AddTran(List<Class.MODEL.T_student> models)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Student(");
            strSql.Append("classid,studentid,studentname,tell,QQ)");
            strSql.Append(" values (");
            strSql.Append("@classid,@studentnum,@studentname,@mobile,@qq)");
            List<DbHelperSQL.SqlAndParameter> list = new List<DbHelperSQL.SqlAndParameter>();
            foreach (Class.MODEL.T_student model in models)
            {

                    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)};
                    parameters[0].Value = model.classid;
                    parameters[1].Value = model.studentid;
                    parameters[2].Value = model.studnetname;
                    parameters[3].Value = model.tell;
                    parameters[4].Value = model.QQ;
                    DbHelperSQL.SqlAndParameter sqladnparameter = new DbHelperSQL.SqlAndParameter(strSql.ToString(), parameters);
                    list.Add(sqladnparameter);

            }
            DbHelperSQL.ExecuteSqlTran(list);
        }