ThinkPHP 3.12 导入导出excel数据

导入 导出的exced如上：

git osc:  https://git.oschina.net/sanpie/Think.git

以下是thinkphp IndexAction类
<?php
 class IndexAction extends Action {
    public function index(){
    $this->display();
   }
   
    /**
     *
     *上传excel文件
     */
    public function upload(){
        //引入Thinkphp 上传文件类
        import('ORG.Net.UploadFile');
        //实例化上传类
        $upload = new UploadFile();// 实例化上传类
        //设置附件上传文件大小
        $upload->maxSize = 2000000;
        //设置附件上传类型
        $upload->allowExts = array('xls','xlsx','csv');
        //设置附件上传目录 /HOME/temp/
        $upload->savePath = './Home/temp/';
        //保持附件文件名不变
        $upload ->saveRule = '';
        //存在同名文件是否覆盖
        $upload->uploadReplace = true;

        if(!$upload -> upload()){//上传失败
            $this->error($upload->getErrorMsg());
        }else{ //成功
            //获取上传成功文件信息
            $info = $upload->getUploadFileInfo();
            //获取上传保存文件名
            $fileName = $info[0]['savename'];
            //重定向 把filename 文件名传给importExcel()方法
            $this->redirect('Index/importExcel',array('fileName'=>$fileName),1,'上传成功！');
        }
        $this->display();
    }

    /**
     * 导入excel
     */
    public function importExcel(){
        header("content-type:text/html;charset=utf-8");
        //引入PHPExcel类
       // vendor('PHPExcel');
        vendor('PHPExcel.PHPExcel.IOFactory');
        vendor('PHPExcel.Reader.Excel2007');
        $fileName = $_GET['fileName'];
        $filePath = './Home/temp/'.$fileName.'.xls';

        $PHPReader = new PHPExcel_Reader_Excel2007();
        if(!$PHPReader->canRead($filePath)){
            $PHPReader = new PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($filePath)){
                echo 'no excel';
                return;
            }
        }

        $PHPExcel = $PHPReader->load($filePath);
        $sheet = $PHPExcel->getSheet(0);
        $allColumn = $sheet->getHighestColumn();
        $allRow = $sheet->getHighestRow();
        for($currentRow = 2; $currentRow <= $allRow; $currentRow++){
            //获取B列的值
            $name = $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue();
            //获取C列的值
            $sex = $PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue();
            $m = new Model('User');
            $data['name']= $name;
            $data['sex']= $sex;
            $num = $m->add($data);
        }
        if($num > 0){
            echo "添加成功";
        }
    }


    /**
     *
     * 导出excel
     * @param $expTitle
     * @param $expCellName
     * @param $expTableData
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     */
    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");

        $objPHPExcel = new PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    /**
     *
     * 导出Excel
     */
    function expUser(){//导出Excel
        $xlsName  = "User";
        $xlsCell  = array(
            array('id','id'),
            array('name','名字'),
            array('sex','性别'),
        );
        $xlsModel = M('User');

        $xlsData  = $xlsModel->Field('id,name,sex')->select();
        foreach ($xlsData as $k => $v)
        {
            $xlsData[$k]['sex']=$v['sex']==1?'男':'女';
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);

    }

}

转载于:https://my.oschina.net/u/2437864/blog/736520