使用PHPExcel将数据图片导出到Excel

PHPExcel（现在已迁移到PhpSpreadsheet）是一个强大的PHP库，可以用于创建和操作Excel文件。以下是使用PHPExcel/PhpSpreadsheet将数据和图片导出到Excel的详细方法：

基本步骤

1. 安装PHPExcel/PhpSpreadsheet

composer require phpoffice/phpspreadsheet

2. 基本数据导出代码

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// 创建新的Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 设置单元格数据
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('A2', 1);
$sheet->setCellValue('B2', 'John Doe');

// 保存Excel文件
$writer = new Xlsx($spreadsheet);
$writer->save('export_data.xlsx');

添加图片到Excel

方法1：从文件添加图片

// 创建绘图对象
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath('path/to/your/image.jpg'); // 图片路径
$drawing->setHeight(100); // 设置高度
$drawing->setCoordinates('D2'); // 设置图片插入位置
$drawing->setWorksheet($sheet);

方法2：从GD资源添加图片

// 创建GD图像资源
$gdImage = imagecreate(200, 100);
$bgColor = imagecolorallocate($gdImage, 255, 255, 255);
$textColor = imagecolorallocate($gdImage, 0, 0, 0);
imagestring($gdImage, 5, 50, 40, 'Sample Image', $textColor);

// 创建内存绘图对象
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing();
$drawing->setName('Sample image');
$drawing->setDescription('Sample image');
$drawing->setImageResource($gdImage);
$drawing->setRenderingFunction(MemoryDrawing::RENDERING_JPEG);
$drawing->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
$drawing->setHeight(100);
$drawing->setCoordinates('F2');
$drawing->setWorksheet($sheet);

完整示例

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

// 创建新的Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 设置表头
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Photo');

// 设置数据行
$sheet->setCellValue('A2', 1);
$sheet->setCellValue('B2', 'John Doe');

// 从文件添加图片
$drawing = new Drawing();
$drawing->setName('User Photo');
$drawing->setDescription('User Photo');
$drawing->setPath('path/to/user_photo.jpg');
$drawing->setHeight(100);
$drawing->setCoordinates('C2');
$drawing->setWorksheet($sheet);

// 创建动态图片
$gdImage = imagecreate(200, 100);
$bgColor = imagecolorallocate($gdImage, 200, 200, 255);
$textColor = imagecolorallocate($gdImage, 0, 0, 0);
imagestring($gdImage, 5, 50, 40, 'Dynamic Image', $textColor);

$memoryDrawing = new MemoryDrawing();
$memoryDrawing->setName('Dynamic Image');
$memoryDrawing->setDescription('Dynamic Image');
$memoryDrawing->setImageResource($gdImage);
$memoryDrawing->setRenderingFunction(MemoryDrawing::RENDERING_JPEG);
$memoryDrawing->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
$memoryDrawing->setHeight(100);
$memoryDrawing->setCoordinates('E2');
$memoryDrawing->setWorksheet($sheet);

// 自动调整列宽
foreach(range('A','E') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

// 保存Excel文件
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export_with_images.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');

注意事项

1. 图片格式支持：PHPExcel支持JPEG、PNG、GIF等常见图片格式
2. 图片大小控制：可以通过setHeight()和setWidth()方法控制图片大小
3. 内存限制：处理大量图片时可能需要增加PHP内存限制
4. 性能优化：对于大量数据导出，考虑分批次处理
5. PhpSpreadsheet迁移：如果使用PhpSpreadsheet，命名空间需要相应调整

替代方案

如果遇到性能问题，也可以考虑： - 使用CSV格式导出纯数据 - 使用TCPDF或mPDF生成PDF报告 - 使用专门的报表工具如JasperReports

希望这个指南能帮助你成功将数据和图片导出到Excel文件！