EasySwoole导入导出Execl表格
需求：easyswoole实现的服务端要实现execl表格的导入以及导出，从而实现人员以及成绩的变更
安装 execl 组件
- 这里有两种选择，一种就是经常使用的phpoffice/phpspreadsheet，而另一种则是xlswriter，本文章使用的则是phpoffice/phpspreadsheet
- 根据自己的php版本安装对应版本的 phpoffice/phpspreadsheet，执行命令
composer require phpoffice/phpspreadsheet来进行安装
基本使用
execl导入
代码语言：javascript复制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导出
代码语言：javascript复制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配置来解决内存问题，但完成工作的时间可能会更长
注意
代码语言：javascript复制$spreadsheet->disconnectWorksheets();
unset($spreadsheet);- 有些使用者会在swoole导出execl表格后调用以上方法来销毁spreadsheet连接，在这里说明一下，此连接和mysql连接一样，调用完成之后直接销毁和不销毁，只是存在的生命周期不同，并不会引起内存泄漏
- 数据量大的时候不建议使用xlsx或xls导出表格，建议使用csv导出（数据量小随意）
- 如导出大文件时，尽量注意内存使用
