PhpSpreadsheet导出图片

PhpSpreadsheet导出图片

    //导出public function pdf($ids){$jzInfo = $this->model->where('id',$ids)->find();try {//巡检人员$staff_ids = \app\admin\model\inspection\Plan::where('id',$jzInfo['plan_id'])->value('staff_id');$staff_names = \app\admin\model\inspection\Staff::whereIn('id',$staff_ids)->column('staff_name');$staff_names = implode(',',$staff_names);$salf_ids = \app\admin\model\inspection\Plan::where('id',$jzInfo['plan_id'])->value('salf_id');$salf_names = \app\admin\model\inspection\Staff::where('id',$salf_ids)->value('staff_name');$spreadsheet = new Spreadsheet();$worksheet = $spreadsheet->getActiveSheet();//设置工作表标题名称$worksheet->setTitle('电梯托管日常巡检');//表头//设置单元格内容setCellValueByColumnAndRow(列，行)$worksheet->setCellValueByColumnAndRow(1, 1, '海康智慧城市科技（广东）有限公司电梯托管日常巡检表');//合并单元格$worksheet->mergeCells('A1:B1');$worksheet->setCellValueByColumnAndRow(1, 5, '巡检内容');$worksheet->setCellValueByColumnAndRow(2, 5, ' 巡检结果');$styleBorderArray = ['borders' => ['outline' => ['borderStyle' => Border::BORDER_THIN, // 边框样式'color' => ['argb' => '0000000'], // 边框颜色],],];$styleArray = ['font' => ['bold' => true],'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,],'borders' => ['outline' => ['borderStyle' => Border::BORDER_THIN, // 边框样式'color' => ['argb' => '0000000'], // 边框颜色],],];//设置单元格样式$worksheet->getStyle('A1:B1')->applyFromArray($styleArray)->getFont()->setSize(16);$worksheet->getStyle('A5')->applyFromArray($styleBorderArray);$worksheet->getStyle('B5')->applyFromArray($styleBorderArray);$list = \app\admin\model\inspection\ProjectSite::alias("project_site")->field("project_site.*,inspection_area.area_name,inspection_area_site.site_name")->join("inspection_area","project_site.area_id=inspection_area.id")->join("inspection_area_site","project_site.area_site_id=inspection_area_site.id")->where('project_site.id',$ids)->select();$list_num = count($list);$change_num = 5+$list_num;$j = 6;for ($i=0; $i < $list_num; $i++) {//$j = $i + 4; //从表格第4行开始$val = $list[$i];$list_item = \app\admin\model\inspection\Projectitem::where('project_site_id','=',$val->id)->select();$item_count = count($list_item);if(empty($item_count)){continue;}//foreach($list_item as $v_item){$coummunit_id = $val['area_id'];for ($it=0; $it < $item_count; $it++) {$num = $j+$it;$worksheet->setCellValueByColumnAndRow(1, $num, $list_item[$it]['item_name']);$worksheet->setCellValueByColumnAndRow(2, $num, $list_item[$it]['value']==2?"正常":($list_item[$it]['value']==1?"异常":$list_item[$it]['value']));$worksheet->getStyle('A'.$num)->applyFromArray($styleBorderArray);$worksheet->getStyle('B'.$num)->applyFromArray($styleBorderArray);}$change_num +=$item_count;}$coumunity_name = \app\admin\model\Community::where('id',$list[0]['area_id'])->find();$worksheet->setCellValueByColumnAndRow(1, 2, "项目地点 : ".$coumunity_name['name']);$worksheet->mergeCells('A2:B2');$worksheet->getStyle('A2:B2')->applyFromArray($styleBorderArray);$worksheet->setCellValueByColumnAndRow(1, 3, '巡检时间 : '.date("Y-m-d H:i:s",$list[0]['checktime']));$worksheet->mergeCells('A3:B3');$worksheet->getStyle('A3:B3')->applyFromArray($styleBorderArray);$worksheet->setCellValueByColumnAndRow(1, 4, '设备注册代码 : '.$coumunity_name['device_code']);$worksheet->mergeCells('A4:B4');$worksheet->getStyle('A4:B4')->applyFromArray($styleBorderArray);$worksheet->getColumnDimension('A')->setWidth(60);$worksheet->getColumnDimension('B')->setWidth(20);$worksheet->setCellValueByColumnAndRow(1, $change_num, "安全管理员 : ".(isset($staff_names)? $staff_names:''));$worksheet->setCellValueByColumnAndRow(2, $change_num, "安全总监 : ".(isset($salf_names)? $salf_names:''));$worksheet->getStyle('A'.$change_num)->applyFromArray($styleBorderArray);$worksheet->getStyle('B'.$change_num)->applyFromArray($styleBorderArray);//清洁项目$worksheet->setCellValueByColumnAndRow(1, 96, '海康智慧城市科技（广东）有限公司电梯托管清洁记录表');$worksheet->getStyle('A96:B96')->applyFromArray($styleArray)->getFont()->setSize(16);//合并单元格$worksheet->mergeCells('A96:B96');$worksheet->getStyle('A2:B2')->applyFromArray($styleBorderArray);$worksheet->setCellValueByColumnAndRow(1, 97, "项目地点 : ".$coumunity_name['name']);$worksheet->mergeCells('A97:B97');$worksheet->getStyle('A97:B97')->applyFromArray($styleBorderArray);//$completetime = \app\admin\model\inspection\Project::where('id',$jzInfo['project_id'])->value('completetime');$worksheet->setCellValueByColumnAndRow(1, 98, '清洁时间 : '.date("Y-m-d H:i:s",$list[0]['checktime']));$worksheet->mergeCells('A98:B98');$worksheet->getStyle('A98:B98')->applyFromArray($styleBorderArray);$worksheet->setCellValueByColumnAndRow(1, 99, '设备注册代码 : '.$coumunity_name['device_code']);$worksheet->mergeCells('A99:B99');$worksheet->getStyle('A99:B99')->applyFromArray($styleBorderArray);$worksheet->setCellValueByColumnAndRow(1, 100, '清洁内容');$worksheet->setCellValueByColumnAndRow(2, 100, ' 清洁结果');$worksheet->getStyle('A100')->applyFromArray($styleBorderArray);$worksheet->getStyle('B100')->applyFromArray($styleBorderArray);$change_nums = 100+$list_num;$j = 101;for ($i=0; $i < $list_num; $i++) {//$j = $i + 4; //从表格第4行开始$val = $list[$i];$list_item = \app\admin\model\inspection\Projectclear::where('project_site_id','=',$val->id)->select();$item_count = count($list_item);if(empty($item_count)){continue;}$coummunit_id = $val['area_id'];for ($it=0; $it < $item_count; $it++) {$num = $j+$it;$worksheet->setCellValueByColumnAndRow(1, $num, $list_item[$it]['item_name']);$worksheet->setCellValueByColumnAndRow(2, $num, $list_item[$it]['value']==2?"正常":($list_item[$it]['value']==1?"异常":$list_item[$it]['value']));$worksheet->getStyle('A'.$num)->applyFromArray($styleBorderArray);$worksheet->getStyle('B'.$num)->applyFromArray($styleBorderArray);}$change_nums +=$item_count;}//盖章$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();$drawing->setPath("./excel/xun.png");$drawing->setWidth(80);$drawing->setCoordinates('B'.$change_num/2);$drawing->setWorksheet($worksheet);//七牛图片下载$config = get_addon_config('qiniu');$bucket = $config['bucket'];$auth = new Auth($config['accessKey'], $config['secretKey']);$images = explode(',',$jzInfo['images']);$change_num = $change_num +1;$worksheet->setCellValueByColumnAndRow(1, $change_num, '巡检图片');$worksheet->mergeCells('A'.$change_num.':B'.$change_num);$styleArray = ['font' => ['bold' => true],'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,],];$worksheet->getStyle('A'.$change_num.':B'.$change_num)->applyFromArray($styleArray)->getFont()->setSize(16);//巡检图片导出到excelforeach($images as $key => $imageUrl){++$change_num;$worksheet->getRowDimension($change_num)->setRowHeight(70);//合并单元格// $worksheet->mergeCells('A'.$change_num.':B'.$change_num);// $worksheet->setHeight(300);$imageSavePath = './excel/image'.$key.'.jpg';// 下载图片到本地$imageUrl = $auth->privateDownloadUrl($imageUrl);file_put_contents($imageSavePath, file_get_contents($imageUrl));$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();$drawing->setResizeProportional(false);$drawing->setPath($imageSavePath);$drawing->setHeight(70);$drawing->setWidth(60);// $drawing->setOffsetX(12);// $drawing->setOffsetY(12);$drawing->setCoordinates('A'.$change_num);$drawing->setWorksheet($worksheet);//$change_num += 40;// 最后删除本地图片//unlink($imageSavePath);}$filename = $coumunity_name['name'].'电梯托管日常巡检表.xlsx';header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="'.$filename.'"');header('Cache-Control: max-age=0');// 保存Excel文件$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');$writer->save('php://output');} catch (Exception $e) {dump($e->getMessage());}}