需求:easyswoole实现的服务端要实现execl表格的导入以及导出,从而实现人员以及成绩的变更
composer require phpoffice/phpspreadsheet
来进行安装
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());
}
}
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());
}
}
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);