Laravel使用PhpOffice\PhpSpreadsheet多sheet数据导出（包含图片导出）
导出的示例
处理下载
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
public function exportPost($id)
{
//处理数据
$exportData = $this->dealExposrData($id);
$name = '申请表' . date("Y-m-d", time());
$spreadsheet = new Spreadsheet();
foreach ($exportData as $key => $data) {
$this->opSheet($spreadsheet,$key,$data);
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空：
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
excel样式
public function opSheet($spreadsheet,$n, $data)
{
$spreadsheet->createSheet();//创建sheet
$objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
// 学习经历
$keys = $data['college'][0];//这是你的数据键名
$count = count($keys);//计算你所占的列数
$infoNum = ceil(count($data['info']) / 3);//求k-v值的所占行数
$infoStart = $infoNum + 2 ;//下面的详细信息的开始行数
// 社团同学习经历
// 工作经历同学习经历
// 获奖同学习经历
// 自我介绍
$infoStart_introduction = $infoStart_prize + $count_prize;
// 证书
$infoStart_certificate = $infoStart_introduction + 2;
$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
$sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['info']['姓名']);//设置sheet的名称
// 标题
$objActSheet->setCellValue('A1', $data['title']);
$spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格
$spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小
$spreadsheet->getActiveSheet($n)->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//固定行居中
$spreadsheet->getActiveSheet($n)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//循环数据居中
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart_introduction")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart_certificate")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//标题栏加粗
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true);
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart_introduction")->getFont()->setBold(true);
$spreadsheet->getActiveSheet($n)->getStyle("$infoStart_certificate")->getFont()->setBold(true);
//设置每个sheet中的名称title
$objActSheet->setCellValue(strtoupper(chr(65)) . ("$infoStart"), '大学经历');
$objActSheet->setCellValue(strtoupper(chr(65)) . ("$infoStart_introduction"), '自我介绍');
$objActSheet->setCellValue(strtoupper(chr(65)) . ("$infoStart_certificate"), '证书');
// 合并单元格
$spreadsheet->getActiveSheet($n)->mergeCells(strtoupper(chr(65)) . $infoStart.':' . $cellName[$count - 1] . $infoStart);
$spreadsheet->getActiveSheet($n)->mergeCells(strtoupper(chr(65)) . $infoStart_introduction.':' . $cellName[$count - 1] . $infoStart_introduction);
$spreadsheet->getActiveSheet($n)->mergeCells(strtoupper(chr(65)) . $infoStart_certificate.':' . $cellName[$count - 1] . $infoStart_certificate);
// 设置字体大小
$spreadsheet->getActiveSheet($n)->getStyle(strtoupper(chr(65)) . $infoStart)->getFont()->setSize(15);
$spreadsheet->getActiveSheet($n)->getStyle(strtoupper(chr(65)) . $infoStart_introduction)->getFont()->setSize(15);
$spreadsheet->getActiveSheet($n)->getStyle(strtoupper(chr(65)) . $infoStart_certificate)->getFont()->setSize(15);
// 学习经历数据循环
foreach ($data['college'] as $key => $item)
{
//循环设置单元格：$key+$infoStart,因为第一行是表头，所以写到表格时 从第数据行开始写
for ($i = 65; $i < $count + 65; $i++) {
//数字转字母从65开始：
$sheet->setCellValue(strtoupper(chr($i)) . ($key+1+"$infoStart"), $item[$i - 65]);
$spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20);
}
}
// 自我介绍
$sheet->setCellValue(strtoupper(chr(65)) . ("$infoStart_introduction"+1), $data['introduction']);
$spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr(65)))->setWidth(20); //固定列宽
$spreadsheet->getActiveSheet($n)->mergeCells(strtoupper(chr(65)) . ("$infoStart_introduction" +1).':' . $cellName[$count - 1] . ("$infoStart_introduction"+1));
// 证书-多图片下载
$certificateNum = 0;
foreach ($data['certificate'] as $k=>$img) {
if ($img != '') {
$drawing[$k] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing[$k]->setName('图片');
$drawing[$k]->setDescription('图片');
$drawing[$k]->setPath(public_path($img));
$drawing[$k]->setWidth(80);
$drawing[$k]->setHeight(80);
$drawing[$k]->setCoordinates(strtoupper(chr(65+$certificateNum)) .(1+"$infoStart_certificate"));
$drawing[$k]->setOffsetX(12);
$drawing[$k]->setOffsetY(12);
$drawing[$k]->setWorksheet($spreadsheet->getActiveSheet());
$certificateNum++;
}
}
// 处理基本信息之类的
$rowNumber = 1;
$infoIndex = 0;
foreach ($data['info'] as $key => $value) {
if ($infoIndex % 3 == 0) {
$rowNumber++;
$infoCellName1 = 'A' . $rowNumber;
$infoCellName2 = 'B' . $rowNumber;
} elseif ($infoIndex % 3 == 1) {
$infoCellName1 = 'C' . $rowNumber;
$infoCellName2 = 'D' . $rowNumber;
} else {
$infoCellName1 = 'E' . $rowNumber;
$infoCellName2 = 'F' . $rowNumber;
}
$spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key);
$spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName2, $value);
$infoIndex++;
}
}
数据格式
public function dealExposrData($id) {
$exportData =[
0=>[
"title" => "XX公司招聘申请表"
"introduction" => "诚实正直、。。。。。。。。。。。。。。。。。。。。"
"info" => array:22 [
"姓名" => "XX"
"性别" => "X"
"年龄" => "22"
"民族" => "汉族"
"籍贯" => "XX省XX县"
"生源地" => "XX省XX市"
"政治面貌" => "共青团员"
"身高体重" => "182/75"
"身份证号" => "111111111111111"
"健康状态" => "健康"
"婚育状态" => "未婚"
"总和排名" => ""
"外语证书" => "四级"
"计算机证" => "计算机一级 "
"资格资质" => ""
"电话" => "13000000000"
"紧急电话" => "13900000000"
"电子邮箱" => "xxxx@163.com"
"应聘岗位" => "xxx工程师"
"现居地" => "XX省XX市"
"学籍验证码" => "xxxxxxxx"
"兴趣爱好" => "跑步、打篮球"
]
"college" => array:2 [
0 => array:6 [
0 => "起止时间"
1 => "毕业院校"
2 => "专业"
3 => "学历"
4 => "学位"
5 => "备注"
]
1 => array:6 [
0 => "XX年X月X日"
1 => "XX大学"
2 => "XXX"
3 => "本科"
4 => ""
5 => ""
]
]
"clubLeader" => array:3 [ ]
"jobs" => array:2 [ ]
"family" => array:3 [ ]
"prize" => array:3 [ ]
"certificate" => array:15 [
0 => "/uploads/file/2020-07-17/XXXXX.jpg"
1 => "/uploads/file/2020-07-17/XXXXX.jpg"
]
]
1=>[...]
2=>[...]
]
return $exportData;
}
完成！
