[phpoffice]导出excel时带上图片，通用方法-开发文档-DZ插件网

[phpoffice]导出excel时带上图片，通用方法

饾暦饾枎饾枒饾枏饾枂饾枅饾枑

2023/04/21 15:51:29

最近运营的一些产品需求，总是要求有商品的地方要把商品图片一块导出到excel。所以写了一个功能相对通用的方法。

首先先将phpoffice必须要用到的命名空间引入进来：

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
use PhpOffice\PhpSpreadsheet\style\Border;
use PhpOffice\PhpSpreadsheet\style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

调用方法：

exportNew方法核心代码：

exportNew(array $title, array $body, string $filename='', array $ext=[], string $export_type='xlsx')
{
if(empty($title)){
exit('表头数据为空');
}
if(empty($body)){
exit('导出数据为空');
}
$title1 = array_keys($title);//字段名称
$title2 = array_values($title);//列表头
//生成列对应的数据字段名称
foreach($title1 as $k => $v){
$letter = intToChr($k);
$title1_new[$letter] = $v;
}
unset($title1);
//生成excel的表头
foreach($title2 as $k => $v){
$letter = intToChr($k);
$title2_new[$letter] = $v;
}
unset($title2);
//超大数据导出
set_time_limit(0);
//使用模板
$spreadsheet = new Spreadsheet();
//表格对象
$worksheet = $spreadsheet->getActiveSheet();
//画表头
$startRow = 1;
foreach($title2_new as $k => $v){
$width = 12;
if(!empty($ext['column_width']) && is_array($ext['column_width']) ){
foreach($ext['column_width'] as $k2 => $v2){
$key = array_search($k2, $title1_new);
if($k == $key){
$width = $v2;
}
}
}
$worksheet->getColumnDimension($k)->setWidth($width);//列宽
$styleArray = [
'borders' => [
'outline' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '00000000'],
],
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startColor' => [
'argb' => '76933C',
],
'endColor' => [
'argb' => '76933C',
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
];
$worksheet->getStyle($k.$startRow)->applyFromArray($styleArray);
$worksheet->setCellValue($k.$startRow, $v);
$worksheet->getRowDimension($startRow)->setRowHeight(25);
}
//画数据体
foreach($body['list'] as $k => $v){//行
$row = $startRow + $k + 1;
foreach($title1_new as $k2 => $v2){//列
if(empty($ext['image']['column'][$v2])){
$worksheet->setCellValue($k2.$row, $v[$v2]);
}else{
$drawing = new Drawing();
//$drawing->setName($val['name']);
//$drawing->setDescription($val['name']);
$imgval = $v[$v2];
$imgurl = explode('?', $imgval)[0];
$fullfilename = $ext['image']['dir'] . basename($imgurl);
$tmp_img_arr[] = $fullfilename;
if(!is_file($fullfilename)){
continue;
}
$drawing->setPath($fullfilename);//注意，这里的$fullfilename必须为本地图片，如果图片在网上需要先下载下来
$drawing->setHeight(80);
//$drawing->setWidth(50);
$drawing->setCoordinates($ext['image']['column'][$v2] . $row);
$drawing->setWorksheet($worksheet);
//设置行高
$worksheet->getRowDimension($row)->setRowHeight(60);
unset($drawing);
}
}
unset($body['list'][$k]);
}
//创建文件
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//下载表格
$filename = empty($filename) ? date('Y-m-d_H-i', time()) : $filename;
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');//禁止缓存
$writer->save('php://output');
//释放资源防止内存溢出
$spreadsheet->disconnectWorksheets();
unset($spreadsheet, $writer);
exit();
}

注意：$fullfilename必须为本地图片，如果你的图片是在阿里云或者七牛，需要先将这些图片下载到本地，不能实时取网上图片，否则程序运行相当慢，还可能导致图片取不到程序报错。

导出的excel效果：

如果还不清楚如何使用，请到群里问群主。

进入原文参与互动