Thinkphp使用phpexcel导入文件并写入数据库

现实中，我们往往很多地方都需要这样批量导入数据，除了phpexcel还有csv等方法可以解决

下面详细介绍一下使用方法

首先在官方下载安装包解压到本地，然后复制保存到tp框架下的vendor目录下

html页面如下

下面是后端代码：

public function column(){
        $grade=D('school')->where(array('parent_id'=>1))->select();
        $school = D('schooll')->select();
        $this->assign('school',$school);
        $this->assign('grade',$grade);
        if(IS_POST){
          $data_p = I('post.');
        if ($data_P['sno']==' ') {
                 $this->ajaxReturn(array('nr' => '学号已存在!', 'sf' => 0));
          }elseif($data_P['sname']==' '){
                 $this->ajaxReturn(array('nr' => '姓名不为空!', 'sf' => 0));
            }elseif($data_P['grade']==' '){
                  $this->ajaxReturn(array('nr'=> '不为空','sf'=>'0'));
            }elseif($data_P['major']==' '){
                  $this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
            }elseif($data_P['classs']==' '){
                  $this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
            }elseif($data_P['school']==' '){
                  $this->ajaxReturn(array('nr'=>'不为空','sf'=>'0'));
            }else{
          $student = M('student')->add(array('sno'=>$data_p['sno'],'passwd'=>md5($data_p['passwd']),'sname'=>$data_p['sname'],'zt'=>$data_p['checkresult'],'majorname'=>$data_p['major'],'classname'=>$data_p['classs'],'gradename'=>$data_p['school'],'schoolname'=>$data_p['grade']));
              }
              if ($student) {
                        $this->ajaxReturn(array('nr' => '添加成功'.$data_P['major'], 'sf' => 0));
                    } else {
                        $this->ajaxReturn(array('nr' => '添加失敗!', 'sf' => 0));
                      }
                  }
         $this->display('index/column');
        }

        public function upload() {
                if (!empty($_FILES)) {
                  $config = array(
                'exts' => array('xlsx','xls'),
                'maxSize' => 3145728,
                'rootPath' =>"./Excel/",
                'subName' => array('date','Ymd'),
           );

           $upload = new \Think\Upload($config);

            if (!$info = $upload->upload()){

               $this->error($upload->getError());

              }else{

           //vendor('PHPExcel.PHPExcel');/////引入类库

           //vendor('PHPExcel.Reader.Excel5');
           import("vendor.PHPExcel");
           import("vendor.PHPExcel.Reader.Excel5");
           $objPHPExcel = new \PHPExcel();/////初始化引入的方法

           $PHPReader=new \PHPExcel_Reader_Excel5();

          $file_name=$upload->rootPath.$info['excel']['savepath'].$info['excel']['savename'];
            }
            $objReader = $PHPReader->load($file_name);

         $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
              //判断导入表格后缀格式
                if ($extension == 'xlsx') {

                    $objReader =\PHPExcel_IOFactory::createReader('Excel2007');

                    $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');

                } elseif($extension == 'xls'){

                    $objReader =\PHPExcel_IOFactory::createReader('Excel5');

                    $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');

               }

          $sheet =$objPHPExcel->getSheet(0);//获取表中第一个工作表

          $highestRow = $sheet->getHighestRow();//取得总行数

          $highestColumn =$sheet->getHighestColumn(); //取得总列数

          for($currentRow=1;$currentRow<=$highestRow;$currentRow++){

            for($currentColumn='A';$currentColumn<=$highestColumn;$currentColumn++){
              $address=$currentColumn.$currentRow;
              $arr[$currentRow][$currentColumn]=$sheet->getCell($address)->getValue();
            }
      for ($i = 2; $i <= $highestRow; $i++) {

          $data_p['sno'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();

         $passwd =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();

            $data_p['passwd']=md5($passwd);

         $data_p['sname'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();

         $data_p['zt'] = '0';

         $data_p['majorname'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();

         $data_p['classname'] =$objPHPExcel->getActiveSheet()->getCell("E" .$i)->getValue();

         $data_p['gradename'] =$objPHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();

         $data_p['schoolname'] =$objPHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();

         $ex=M('Student')->add($data_p);
              }
      if($ex){
                $this->success('导入成功!');
         }else{
                $this->error("请选择上传的文件");

                  }

　　　　}
}

好的具体的就是这些。哪里不懂可以留言1119702968@qq.com咨询。

巴特西

Thinkphp使用phpexcel导入文件并写入数据库

最新文章

热门文章