EasySwoole文档：EasySwoole导入导出Execl表格

EasySwoole导入导出Execl表格

需求：easyswoole实现的服务端要实现execl表格的导入以及导出，从而实现人员以及成绩的变更

---

安装 execl 组件

1. 这里有两种选择，一种就是经常使用的phpoffice/phpspreadsheet，而另一种则是xlswriter，本文章使用的则是phpoffice/phpspreadsheet

2. 根据自己的php版本安装对应版本的 phpoffice/phpspreadsheet

基本使用

execl导入

public function upload()
{
           try {
           $file = $this->request()->getUploadedFile('file'); 
           $path = EASYSWOOLE_ROOT.'/Static/Uploads'; 
           if(!is_dir($path)){
               File::createDirectory($path);
           } 
           $path = $path.'/'.$file->getClientFilename(); 
           $file->moveTo($path); 
           $spreadsheet = IOFactory::load($path); 
           //读取默认工作表 
           $worksheet = $spreadsheet->getSheet(0); 
           //取得一共有多少行 
           $allRow = $worksheet->getHighestRow(); 
           $data = []; 
           //清空用户表
           UserModel::create()->destroy(null,true); 
           for($i = 2; $i <= $allRow; $i++)
           { 
                $data['user_name'] = $spreadsheet->getActiveSheet()->getCell('B'.$i)->getValue();//姓名 
                $data['user_num'] = $spreadsheet->getActiveSheet()->getCell('C'.$i)->getValue();//编号 
                $data['user_unit'] = $spreadsheet->getActiveSheet()->getCell('D'.$i)->getValue();//单位 
                $data['add_time'] = time();
                UserModel::create($data)->save();
           }
           //清空统计记录 
           ScoreModel::create()->destroy(null,true); 
           $this->writeJson(Status::CODE_OK,null,'导入成功');
      }catch (\Throwable $throwable){ 
           $this->writeJson(Status::CODE_BAD_REQUEST,null,$throwable->getMessage()); 
      } 
   }

execl导出

public function download()
{ 
           try { 
                $spreadsheet = new Spreadsheet(); 
                //设置表格 
                $spreadsheet->setActiveSheetIndex(0);
                //设置表头
                $spreadsheet->setActiveSheetIndex(0)
                       ->setCellValue('A1','序号') 
                       ->setCellValue('B1','姓名')
                       ->setCellValue('C1','编号')
                       ->setCellValue('D1','单位') 
                       ->setCellValue('E1','靶位') 
                       ->setCellValue('F1','弹数')
                       ->setCellValue('G1','总成绩')
                       ->setCellValue('H1','靶型') 
                       ->setCellValue('I1','射击时间'); 
                //设置表头居中 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('H1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                $spreadsheet->setActiveSheetIndex(0)->getStyle('I1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); 
                //设置表格宽度 
                $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10); 
                $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20); 
                $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30); 
                $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10); 
                $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10); 
                $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10); 
                $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10); 
                $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30); 
                //查询数据 
                $rows = ScoreModel::create() 
                       ->join('target_type as type','type.target_type_id = score.score_target_type') 
                       ->field(['score.*','type.target_type_name']) 
                       ->all(); 
                //遍历数据 
                foreach ($rows as $i => $row) 
                { 
                       $spreadsheet->getActiveSheet()->setCellValue('A'.($i+2),($i+1)); 
                       $spreadsheet->getActiveSheet()->setCellValue('B'.($i+2),$row->score_user_name);
                       $spreadsheet->getActiveSheet()->setCellValue('C'.($i+2),$row->score_user_num); 
                       $spreadsheet->getActiveSheet()->setCellValue('D'.($i+2),$row->score_user_unit); 
                       $spreadsheet->getActiveSheet()->setCellValue('E'.($i+2),$row->score_target_name); 
                       $spreadsheet->getActiveSheet()->setCellValue('F'.($i+2),$row->score_count); 
                       $spreadsheet->getActiveSheet()->setCellValue('G'.($i+2),$row->score_sum); 
                       $spreadsheet->getActiveSheet()->setCellValue('H'.($i+2),$row->target_type_name); 
                       $spreadsheet->getActiveSheet()->setCellValue('I'.($i+2),date('Y-m-d H:i:s',$row->start_time)); 
                } 
                $writer = IOFactory::createWriter($spreadsheet,'Xls'); 
                //设置filename 
                $filename = '成绩名单-'.date('Ymd').'.xls';
                //保存
                $writer->save($filename); 
                //swoole下载文件，使用response输出 
                $this->response()->write(file_get_contents($filename)); 
                $this->response()->withHeader('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); 
                $this->response()->withHeader('Content-Disposition', 'attachment;filename='.$filename); 
                $this->response()->withHeader('Cache-Control','max-age=0'); $this->response()->end(); 
                $this->writeJson(Status::CODE_OK, null, '导出成功'); 
          }catch (\Throwable $throwable){ 
                $this->writeJson(Status::CODE_BAD_REQUEST,null,$throwable->getMessage()); 
          } 
       }

phpexecl和xlswriter的区别

• xlswriter是一个 PHP C 扩展，而PHPExecl是用PHP实现的扩展

• 由于内存原因，PHPExcel数据量相对较大的情况下无法正常工作，虽然可以通过修改memory_limit配置来解决内存问题，但完成工作的时间可能会更长

注意

$spreadsheet->disconnectWorksheets();

unset($spreadsheet);

• 有些使用者会在swoole导出execl表格后调用以上方法来销毁spreadsheet连接，在这里说明一下，此连接和mysql连接一样，调用完成之后直接销毁和不销毁，只是存在的生命周期不同，并不会引起内存泄漏

• 数据量大的时候不建议使用xlsx或xls导出表格，建议使用csv导出（数据量小随意）

• 如导出大文件时，尽量注意内存使用

这篇关于EasySwoole如何导入导出Excel表格的文章就讲到这里，更多相关技术咨询欢迎前往并持续关注编程学习网。