问题描述：

      最近用thinkphp5做项目，要用excel表格导出数据。tp5还是第一次使用，在网上找了好多方法，现在把成功后的使用步骤分享一下。
      按照步骤来，很简单的。

解决办法：
第一步：从https://github.com/PHPOffice/PHPExcel下载PHPExcel，并将解压的文件中的Classes文件夹重命名为PHPExcel，然后复制到根目录下‘vendor’文件夹中。





第二步：在控制器中创建excel.php文件，或者直接复制 public function export方法放在导出按钮的控制器里面。

<?php
namespace app\index\controller;
use think\Controller;
use think\Db;
class Excel extends Controller
{
  //导出excel
 public function export(){

        $xlsData = Db::name('company')->select();
        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Worksheet.Drawing');
        Vendor('PHPExcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        //set document Property
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F");
        $arrHeader = array('公司名称','联系人','手机号','QQ','公司描述','备注');
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };

        //填充表格信息
        foreach($xlsData as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$v['company_name']);
            $objActSheet->setCellValue('B'.$k, $v['linkman']);
            $objActSheet->setCellValue('C'.$k, $v['telphone']);
            $objActSheet->setCellValue('D'.$k, $v['qq']);
            $objActSheet->setCellValue('E'.$k, $v['description']);
            $objActSheet->setCellValue('F'.$k, $v['notes']);

           // 对图片进行判断
            if(!empty($v['imgsrc'])){
                $image = $v['imgsrc'];
                if( @fopen($image , 'r' ) ) {
                    $objDrawing = new \PHPExcel_Worksheet_Drawing();
                    $objDrawing->setPath($image);
                    // 设置图片的宽度
                    $objDrawing->setHeight(50);
                    $objDrawing->setWidth(50);
                    $objDrawing->setCoordinates('F' . $k);
                    $objDrawing->setWorksheet($objExcel ->getActiveSheet());
                }
            }

            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }

        $width = array(10,15,20,25,30,20);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[0]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[2]);
        $objActSheet->getColumnDimension('D')->setWidth($width[3]);
        $objActSheet->getColumnDimension('E')->setWidth($width[4]);
        $objActSheet->getColumnDimension('F')->setWidth($width[5]);       

        $outfile = "公司信息列表.xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }


//导入excel文件
    public function import(){

        Vendor('PHPExcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.IOFactory.PHPExcel_IOFactory');//调用类库,路径是基于vendor文件夹的
        Vendor('PHPExcel.PHPExcel.Reader.Excel5');//调用类库,路径是基于vendor文件夹的
       
        $file = request()->file('excel');
      //$file = request()->file('file');//如果前端框架是layui
      if(is_null($file)){         
           // return json_encode(["code" => 1001, "msg" => "请先选择文件再点击上传"]);//如果前端框架是layui
                     
                      $this->error('请先选择文件再点击上传');
        }
        $save_path = ROOT_PATH . 'public/excel/';

        $info = $file->move($save_path); //print_r($info);exit;
        $filename=$save_path . DIRECTORY_SEPARATOR . $info->getSaveName();
        if(file_exists($filename)) {
        //如果文件存在
            if( strstr($filename,'.xlsx')) {
                $PHPReader = new \PHPExcel_Reader_Excel2007();
            } else {
                $PHPReader = new \PHPExcel_Reader_Excel5();

            } //载入excel文件

            $PHPExcel = $PHPReader->load($filename);

            $sheet = $PHPExcel->getActiveSheet(0);//获得sheet
            $highestRow = $sheet->getHighestRow(); // 取得共有数据数
            $data=$sheet->toArray();//转换为数组格式
                       array_shift($excel_array);  //删除第一个数组(标题);
            //从数据库获取数据
            for($i=0;$i<$highestRow-1;$i++){
                $user['company_name']=trim($data[$i][0]);
                $user['linkman']=trim($data[$i][1]);
                $user['telphone']=trim($data[$i][2]);
                $user['qq']=trim($data[$i][3]);
                $user['address']=trim($data[$i][4]);
                $user['description']=trim($data[$i][5]);
                $map['company_name']=trim($data[$i][0]);
                $map['linkman']=trim($data[$i][1]);
            //第二栏 检测数据库中是否存在相同数据
                $data_list=Db::name("company")->where($map)->find();
                if(!$data_list){ 
                   $new_datas[] = $user;
                } else {
                   $new_datas =array();
                }
            }

            if(!empty($new_datas)){
                db('company')->insertAll($new_datas, true);
                $this->success('成功上传' . count($new_datas) . '条数据。有'.($highestRow-count($new_datas)).'无效数据。','collect/index');
                            //return json_encode(["code" => 1000, "msg" => '成功上传' . count($new_datas) . '条数据。有'.($highestRow-count($new_datas)).'无效数据。']);//layui


            }else{
                $this->error('上传'.($highestRow-count($new_datas)).'条无效数据。');
                //return json_encode(["code" => 1001, "msg" =>'上传'.($highestRow-count($new_datas)).'条无效数据。']);//layui


            }
        }else{
            return array("resultcode" => -5, "resultmsg" => "文件不存在", "data" => null);
            //return json_encode(["code" => 1001, "msg" => "文件不存在"]);//layui


        }
    }
}

?>

//导出页面代码
<form action="__MODULE__/excel/export" enctype="multipart/form-data" method="post">

   <input type="submit" value="导出">

</form>

//导入页面代码
<form method="post" action="{:url('student/savestudentImport')}" class="form-signin" enctype="multipart/form-data" >

    <input name="excel" type="file" class="form-control">

    <button class="btn btn-lg btn-primary btn-block" type="submit">导入</button>

</form>
//如果前端框架使用的是layui
//html
<button type="button" class="layui-btn" id="test1">
   <i class="layui-icon">&#xe67c;</i>上传excel
</button>
//js
        layui.use(['table','upload'], function(){
            var table = layui.table;
            var tableIn=null;
             var upload = layui.upload;
   
              //执行实例
            var uploadInst = upload.render({
                elem: '#test1' //绑定元素
                ,url: '/admin/wash/xng_import' //上传接口
                ,method:'post'
                ,accept:'file'
                ,exts:'xls|xlsx'
                ,done: function(res){
                  //上传完毕回调
                  console.log(res);
                     if(res.code==1){
                     layer.msg(res.msg,{icon:6});
                                                 tableIn.reload({
                            page:{curr:1}
                            ,where:{}
                        })
                    }else{
                     layer.msg(res.msg,{icon:5});
                    }
                }
                ,error: function(){
                  //请求异常回调
                }
            });
     });