phpexcel导入数据到数据库

蔚落 2022-05-29 06:10 244阅读 0赞

页面代码

<form action="{:U('upload')}" enctype="multipart/form-data" method="post">
    
    
    
            <div class="row">
              
               <div class="col-lg-12">
                <h1>上传<small>开题成绩</small> <button type="button" class="btn btn-info" οnclick="window.open('__ROOT__/Public/上传答辩成绩模板.xlsx')"> 下载模板</button>
    </h1>
                <ol class="breadcrumb">
                  <li class="active"><input class="btn btn-info" type="file" name="file_stu" />
    
                </ol>
                <input type="submit" class="btn btn-info" value="导入数据"></li>
              </div>
            </div> 
    
              
            </div>
    
            
    
            
              
              <div class="col-lg-12">
                <div class="panel panel-primary">
                  <div class="panel-heading">
                    <h3 class="panel-title"><i class="fa fa-money"></i> 开题成绩</h3>
                  </div>
                  <div class="panel-body">
    
                    <div class="table-responsive">
                      <table class="table table-bordered table-hover table-striped tablesorter">
                        <thead>
                          <tr>
                            <th>学号 </th>
                            <th>姓名 </th>
                            <th>指导老师成绩 </th>
                            <th>答辩委员会成绩 </th>
                             
                          </tr>
                          
                        </thead>
                        <tbody>
                          <tbody>
                             <volist name="list" id="vo">
                          <tr>
                            <td>{$vo.id}</td>
                            <td>{$vo.name}</td>
                           <td><if condition="$vo['score1'] eq -1">未上传<else />{$vo.score1}</if></td>
                            <td><if condition="$vo['score2'] eq -1">未上传<else />{$vo.score2}</if></td>
                          </tr>
                          
                          
                          
                        </volist>
                        </tbody>
                        </tbody>
                      </table>
                    </div>
                    
                  </div>
                </div>
              </div>
            </div>
    
          </div>

界面为

![Image 1][]

路径为

![Image 1][]

后台代码为

public function upload() {
        if(IS_POST){
        $fileInfo=$_FILES['file_stu'];
        //var_dump($fileInfo);die;
        $newName=uploadFile($fileInfo);
        //var_dump(class_exists('PHPExcel_IOFactory'));die;
        //var_dump($newName);die;
    
        vendor("PHPExcel.PHPExcel");
            $file_name=$newName;
            //echo($file_name);die;
            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
            //echo($extension);die;
            if ($extension == 'xlsx') { //判断是什么后缀
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
            } else if ($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(); //取得总列数
            //echo($highestColumn);die;
            //D('pro_info')->execute('truncate table pro_info');
            for ($i = 2; $i <= $highestRow; $i++) {
            //看这里看这里,前面小写的a是表中的字段名，后面的大写A是excel中位置
            $data['id'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
            $data['name'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
            $data['score1'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
            $data['score2'] =$objPHPExcel->getActiveSheet()->getCell("D" .$i)->getValue();
            //echo($data);die;
            $data['id']=strval($data['id']);
           //var_dump($data);die;
            //$arr[]=$data; //把所有数据存入数组中 前台显示
            $data1 = array('score1'=>$data['score1'],'score2'=>$data['score2']);
            //$User-> where('id=5')->setField($data);
            $value=D('paper_student')->where('id='.$data['id'])->setField($data1);
           }//for end
           $paper_student=D('paper_student');
            $a=explode(',',session('member'),6);
            //var_dump($a);
    
            //$m=array();
            $k=0;
    
            foreach ($a as $value) {
                //echo "$value";
    
                 $lists[$k]=$paper_student->where("id = '".$value."'")->find();
                 $k++;
            }
            //$m=array($lists[0],$lists[1],$lists[2],$lists[3],$lists[4]);
            //var_dump($lists);
            $this->assign('list',$lists);
            //$this->assign('list',$m);
            //$this->display();
           //$this->assign('paper_student',$value);
           $this->display('lst');
    
      }else{
        $this->display('lst');
       }
      }

<?php
    header('content-type:text/html;charset=utf-8');
    function uploadFile($fileInfo, $uploadPath='./Public/upload',$allowExt=array('xlsx','xls')){//fileInfo获取到的上传文件信息
    	if($fileInfo['error']>0){
                switch ($fileInfo) {
                    case 1:
                        $mes='文件上传过大';
                        break;
                    case 4:
                        $mes='没有选择上传文件';
                        break;
                    default:
                        $mes='文件上传失败';
                        break;
                }
                exit($mes);
            }
            //检测文件上传类型
            $ext=pathinfo($fileInfo['name'],PATHINFO_EXTENSION);
            /*$allowExt=array('xlsx','xls');*/
            if(!in_array($ext, $allowExt)){
               exit('不是Excel文件，重新上传');
            }
            //检测文件是否是通过HTTP传来的
            if (!is_uploaded_file($fileInfo['tmp_name'])) {
               exit('文件不是通过HTTP POST传过来的');
            }
           /* $uploadPath='./Public/upload/';*/
            if(!file_exists($uploadPath)){//如果没有这个文件夹则创建一个
                mkdir($uploadPath,0777,true);
                chmod($uploadPath, 0777);
            }
            $uniName=md5(uniqid(microtime(true),true)).'.'.$ext;//随机生成一个文件名 防止同名替换
            //$uniName='1630090136'.'.'.$ext;
            $destination=$uploadPath.'/'.$uniName;
            if(!@move_uploaded_file($fileInfo['tmp_name'], $destination)){
                exit('文件移动失败');
            }
            //$this->success('文件上传成功');
            return $destination;//上传成功返回路径和文件名
    }//uploadFile end

[Image 1]: