thinkphp5 使用PHPExcel导入导出简单案例

2018-12-25 阅读：3290 分类：后端标签： TP5

thinkphp5 使用PHPExcel导入简单案例:

扩展包链接：https://pan.baidu.com/s/19SpsMJ4WIjm7jUqdFaMVRw

提取码：w0t0

导入功能：

 public function ten_daoru(){
    //引入PHPExcel类
    vendor('PHPExcel');
    vendor("PHPExcel/PHPExcel.IOFactory");  //导入phpexcel插件
    vendor('PHPExcel/PHPExcel.Reader.Excel5');
  
    //文件路径
    $filePath =ROOT_PATH.'/public/static/uploadExcel/exam.xlsx';
    //实例化PHPExcel类
    $PHPExcel = new \PHPExcel();
    //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
    $PHPReader = new \PHPExcel_Reader_Excel2007();
    if (!$PHPReader->canRead($filePath)) {
      $PHPReader = new \PHPExcel_Reader_Excel5();
      if (!$PHPReader->canRead($filePath)) {
        echo 'no Excel';
        return;
      }
    }
    //读取Excel文件
    $PHPExcel = $PHPReader->load($filePath);
    //读取excel文件中的第一个工作表
    $sheet = $PHPExcel->getSheet(0);
    //取得最大的列号
    $allColumn = $sheet->getHighestColumn();
    //取得最大的行号
    $allRow = $sheet->getHighestRow();
    //从第二行开始插入,第一行是列名
    for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
      //获取B列的值
      $a = $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue();
      //获取C列的值
      $b = $PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue();
  
      $result=db('tenth')->insert(array('username'=>$a,'tel'=>$b));
    }
       if($result){
          return $this->success('导入成功！','admin/Tenth/ten');
        }else{
          return $this->success('导入失败！');
        }
   }

导出功能

  // excel导出
     public function excel_daochu(){
       vendor("PHPExcel.PHPExcel");  //导入phpexcel插件
       $objPHPExcel = new \PHPExcel();
       $objPHPExcel->createSheet(); //创建新的内置表
       $objPHPExcel->setActiveSheetIndex(); //吧新创建的sheet设定为当前活动sheet
       $objSheet=$objPHPExcel->getActiveSheet(); //获取当前活动sheet
       // $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//导出居中显示
       // $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_LEFT);//导出居中显示
       $data=\think\Db::name('prg')->select();

       $objSheet->setTitle("财务数据表");//给当前活动sheet设置名称
       $objSheet->setCellValue('A1','姓名')->setCellValue('B1','所属机构')->setCellValue('C1','职称')->setCellValue('D1','联系电话')->setCellValue('E1','提交时间')->setCellValue('F1','商户订单号')->setCellValue('G1','金额')->setCellValue('H1','签到状态')->setCellValue('I1','支付状态')->setCellValue('J1','晚宴')->setCellValue('K1','座位号');
       $j=2;
        foreach($data as $key=>$val){
            $objSheet->setCellValue('A'.$j,$val['username'])->setCellValue('B'.$j,$val['hospital'])->setCellValue('C'.$j,$val['hos_title'])->setCellValue('D'.$j,$val['tel'])->setCellValue('E'.$j,date("Y/m/d H:i:s",$val['addtime']))->setCellValue('F'.$j,$val['z_num'])->setCellValue('G'.$j,$val['mony'])->setCellValue('H'.$j,$val['descs'])->setCellValue('I'.$j,$val['paym'])->setCellValue('J'.$j,$val['dinner'])->setCellValue('K'.$j,$val['seat_num']);
            $j++;
        }
       $objWriter=\PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel5");
       //$objWriter->save($dir."/export_1.xls");
       $time=date("Ymd");
       $this->brower_export('Excel5',$time.'_财务数据表.xls');//输出到浏览器
       $objWriter->save("php://output");
    }
    public function brower_export($type,$filename){
            if($type=='Excel5'){
                header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输入excel2003文件
            }else{
                header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输入excel2007文件
            }
            
            header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器输出文件的名称
            header('Cache-Control: max-age=0');//禁止缓存
    }