ThinkPHP中PHPExcel的使用（包含日期格式）

1.实现excel导入

 

 /**实现导入excel
     **/
   function impUser($tid){
        if (!empty($_FILES)) {
        
            $config=array(
                'exts'=>array('xlsx','xls'),
                'rootPath'=>"./Public/",
                'savePath'=>'Excel/',
                'autoSub'    =>    true,
                'subName'    =>    array('date','Ymd'),
            );
            $upload = new \Think\Upload($config);
            if (!$info=$upload->upload()) {
                $this->error($upload->getError());
            }
              vendor("PHPExcel.Classes.PHPExcel");
            $file_name=$upload->rootPath.$info['import']['savepath'].$info['import']['savename'];
            //var_dump($file_name);exit;
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            for($i=3;$i<=$highestRow;$i++)
            {
                $data['tid']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
                $data['content']=$objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
                $data['aa']= $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
                $data['ab'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
                $data['ac'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
                $data['ad']= $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
                $data['answer']= $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
                $data['addtime']= gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue()));   
                $data['addtime']=strtotime( $data['addtime']);
                $data['quescore']= $objPHPExcel->getActiveSheet()->getCell("j".$i)->getValue();
            
                M('Quest')->add($data);
                    
            }
            $this->success('导入成功！');
        }else
        {
            $this->error("请选择上传的文件");
        }

 

2.上传文件的格式

3.文件导出

//导出excel
    function expUser($tid){
         $xlsName = M('Test') -> where(array('id'=>$tid))->getField('title');
        //$xlsName  = "我是导出的excel";
        $xlsCell  = array(
        array('id','题号id'),
        array('tid','题试卷id'),
        array('content','问题名称'),
        array('aa','选项A'),
        array('ab','选项B'),
        array('ac','选项C'),
        array('ad','选项D'),
        array('answer','选项'),
        array('addtime','时间'),
        array('quescore','分数')
        
        );
        $xlsModel = M('Quest');

        $xlsData  = $xlsModel-> where(array('tid'=>$tid))->Field('id,tid,content,aa,ab,ac,ad,answer,addtime,quescore')->select();
        
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

4.注意引入的phpexcel的路径

把phpexcel的包放入 ThinkPHP/Library/Vendor 下面

 $objReader = \PHPExcel_IOFactory::createReader//这里要注意‘\’ 要有这个