使用PHP和PhpSpreadsheet在Excel中插入图片的完整代码
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">准备工作</a></li><li><a href="#_label1">核心实现</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1. 创建图片插入函数</a></li><li><a href="#_lab2_1_1">2. 准备数据</a></li><li><a href="#_lab2_1_2">3. 创建 Excel 并填充数据</a></li><li><a href="#_lab2_1_3">4. 保存文件</a></li></ul><li><a href="#_label2">关键要点</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">实际效果</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">完整代码</a></li><ul class="second_class_ul"></ul></ul></div><p>在现代 Web 开发中，经常需要生成包含数据和图片的 Excel 报表。本文将介绍如何使用 PHP 和 PhpOffice\PhpSpreadsheet 库创建一个包含图片的 Excel 文件。</p><p class="maodian"><a name="_label0"></a></p><h2>准备工作</h2>
<p>首先需要安装 PhpSpreadsheet 库：</p>
<div class="jb51code"><pre class="brush:bash;">composer require phpoffice/phpspreadsheet
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>核心实现</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>1. 创建图片插入函数</h3>
<div class="jb51code"><pre class="brush:php;">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);
}
</pre></div>
<p>这个函数使用 <code>PhpOffice\PhpSpreadsheet\Worksheet\Drawing</code> 类来处理图片插入，设置了图片的尺寸、位置和偏移量。</p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2. 准备数据</h3>
<div class="jb51code"><pre class="brush:php;">$header = ['课程名称', '图片'];
$list = [
[
'name' => 'thinkphp',
'image' => 'images/thinkphp.png'
],
[
'name' => 'npm',
'image' => 'images/npm.png'
],
[
'name' => 'uni-app',
'image' => 'images/uni-app.jpg'
]
];
</pre></div>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>3. 创建 Excel 并填充数据</h3>
<div class="jb51code"><pre class="brush:php;">$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// 填充标题行
$worksheet->setCellValue('A1', $header);
$worksheet->setCellValue('B1', $header);
$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']);
}
</pre></div>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>4. 保存文件</h3>
<p>通过命令行保存文件：<code>php index.php</code></p>
<div class="jb51code"><pre class="brush:php;">$writer = new Xlsx($spreadsheet);
$filename = 'course_list.xlsx';
$writer->save($filename);
</pre></div>
<p>通过站点保存文件：<code>php -S 127.0.0.1:8888</code>，访问127.0.0.1:8888可直接触发下载。也可以通过Ngnix等部署成站点，访问下载</p>
<div class="jb51code"><pre class="brush:php;">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');
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>关键要点</h2>
<ul><li>使用 <code>Drawing</code> 类处理图片插入</li><li>设置合适的行高和列宽以适应图片</li><li>使用 <code>setOffsetX</code> 和 <code>setOffsetY</code> 调整图片在单元格中的位置</li><li>确保图片路径正确且文件存在</li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>实际效果</h2>
<p>生成的 Excel 文件将包含两列：课程名称和对应的图片，每张图片都会整齐地显示在单元格中，具有良好的视觉效果。</p>
<p>这种方法非常适合生成产品目录、员工信息表、课程列表等需要同时展示文本信息和图片的报表。</p>
<p class="maodian"><a name="_label4"></a></p><h2>完整代码</h2>
<div class="jb51code"><pre class="brush:php;"><?php
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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);
$worksheet->setCellValue('B1', $header);
$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');
</pre></div>
頁:
[1]