在现代 Web 开发中，经常需要生成包含数据和图片的 Excel 报表。本文将介绍如何使用 PHP 和 PhpOfficePhpSpreadsheet 库创建一个包含图片的 Excel 文件。
准备工作
首先需要安装 PhpSpreadsheet 库：
composer require phpoffice/phpspreadsheet
核心实现
1. 创建图片插入函数
function addImageToCell($worksheet, $imagePath, $cell, $name)
{
$drawing = new Drawing();
$drawing->setName($name);
$drawing->setDescription($name);
$drawing->setPath($imagePath);
$drawing->setHeight(80);
$drawing->setWidth(100);
$drawing->setCoordinates($cell);
$drawing->setOffsetX(5);
$drawing->setOffsetY(5);
$drawing->setWorksheet($worksheet);
}
这个函数使用 PhpOfficePhpSpreadsheetWorksheetDrawing 类来处理图片插入，设置了图片的尺寸、位置和偏移量。
2. 准备数据
$header = [\'课程名称\', \'图片\'];
$list = [
[
\'name\' => \'thinkphp\',
\'image\' => \'images/thinkphp.png\'
],
[
\'name\' => \'npm\',
\'image\' => \'images/npm.png\'
],
[
\'name\' => \'uni-app\',
\'image\' => \'images/uni-app.jpg\'
]
];
3. 创建 Excel 并填充数据
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// 填充标题行
$worksheet->setCellValue(\'A1\', $header[0]);
$worksheet->setCellValue(\'B1\', $header[1]);
$worksheet->getColumnDimension(\'B\')->setWidth(15);
$line = 1;
foreach ($list as $k => $v) {
$line++;
$worksheet->getRowDimension($line)->setRowHeight(100);
$worksheet->getCell(\'A\' . $line)->setValue($v[\'name\']);
addImageToCell($worksheet, $v[\'image\'], \'B\' . $line, $v[\'name\']);
}
4. 保存文件
通过命令行保存文件：php index.php
$writer = new Xlsx($spreadsheet);
$filename = \'course_list.xlsx\';
$writer->save($filename);
通过站点保存文件：php -S 127.0.0.1:8888，访问127.0.0.1:8888可直接触发下载。也可以通过Ngnix等部署成站点，访问下载
header(\'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\');
header(\'Content-Disposition: attachment;filename=\"course_list.xlsx\"\');
header(\'Cache-Control: max-age=0\');
$writer = new Xlsx($spreadsheet);
$writer->save(\'php://output\');
关键要点
- 使用
Drawing类处理图片插入 - 设置合适的行高和列宽以适应图片
- 使用
setOffsetX和setOffsetY调整图片在单元格中的位置 - 确保图片路径正确且文件存在
实际效果
生成的 Excel 文件将包含两列：课程名称和对应的图片，每张图片都会整齐地显示在单元格中，具有良好的视觉效果。
这种方法非常适合生成产品目录、员工信息表、课程列表等需要同时展示文本信息和图片的报表。
完整代码
<?php
require __DIR__ . \'/vendor/autoload.php\';
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWorksheetDrawing;
use PhpOfficePhpSpreadsheetWriterXlsx;
function addImageToCell($worksheet, $imagePath, $cell, $name)
{
$drawing = new Drawing();
$drawing->setName($name);
$drawing->setDescription($name);
$drawing->setPath($imagePath);
$drawing->setHeight(80);
$drawing->setWidth(100);
$drawing->setCoordinates($cell);
$drawing->setOffsetX(5);
$drawing->setOffsetY(5);
$drawing->setWorksheet($worksheet);
}
// 示例数据
$header = [\'课程名称\', \'图片\'];
$list = [
[
\'name\' => \'thinkphp\',
\'image\' => \'images/thinkphp.png\'
],
[
\'name\' => \'npm\',
\'image\' => \'images/npm.png\'
],
[
\'name\' => \'uni-app\',
\'image\' => \'images/uni-app.jpg\'
]
];
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// 填充标题行
$worksheet->setCellValue(\'A1\', $header[0]);
$worksheet->setCellValue(\'B1\', $header[1]);
$worksheet->getColumnDimension(\'B\')->setWidth(15);
$line = 1;
foreach ($list as $k => $v) {
$line++;
$worksheet->getRowDimension($line)->setRowHeight(100);
$worksheet->getCell(\'A\' . $line)->setValue($v[\'name\']);
addImageToCell($worksheet, $v[\'image\'], \'B\' . $line, $v[\'name\']);
}
// 保存为 Excel 文件
$writer = new Xlsx($spreadsheet);
$filename = \'course_list.xlsx\';
$writer->save($filename);
// 下载 Excel 文件
// header(\'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\');
// header(\'Content-Disposition: attachment;filename=\"course_list.xlsx\"\');
// header(\'Cache-Control: max-age=0\');
// $writer = new Xlsx($spreadsheet);
// $writer->save(\'php://output\');
