phpexcel导出excel文件以及图片问题解决方案

phpexcel导出excel文件以及图片问题解决方案

下载phpexcel插件

composer安装命令

composer require phpoffice/phpexcel

git地址

目录结构

简单的将数据导出excel

<?php
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Writer/Excel2007.php";$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,],['id' => 2,'name' => 'fj','nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,]
];
//实例化
$objExcel = new PHPExcel();
//设置文档属性
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F");
//设置表头
$arrHeader = array('用户ID', '用户名', '昵称','用户密码','手机号码' ,'注册时间');
$lenth = count($arrHeader);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
for ($i = 0; $i < $lenth; $i++) {$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};//填充表格信息
foreach ($arr as $k => $v) {//从第二行开始$k += 2;//表格内容$objActSheet->setCellValue('A' . $k, $v['id']);$objActSheet->setCellValue('B' . $k, $v['username']);$objActSheet->setCellValue('C' . $k, $v['nickname']);$objActSheet->setCellValue('D' . $k, $v['password']);$objActSheet->setCellValue('E' . $k, $v['phone']);$objActSheet->setCellValue('F' . $k, date('Y-m-d H:i:s', $v['init_time']));$width = array(20, 20, 15, 10, 10, 30, 10, 15);//设置表格的宽度$objActSheet->getColumnDimension('A')->setWidth($width[5]);$objActSheet->getColumnDimension('B')->setWidth($width[1]);$objActSheet->getColumnDimension('C')->setWidth($width[0]);$objActSheet->getColumnDimension('D')->setWidth($width[5]);$objActSheet->getColumnDimension('E')->setWidth($width[5]);$objActSheet->getColumnDimension('F')->setWidth($width[5]);$outfile = "人员表" . time() . ".xlsx";
}//清空输出缓冲区ob_end_clean();//告诉浏览器强制下载header("Content-Type: application/force-download");//二进制文件类型header("Content-Type: application/octet-stream");header("Content-Type: application/download");//设置表名header('Content-Disposition:inline;filename="' . $outfile . '"');header("Content-Transfer-Encoding: binary");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');

效果展示：

当导出数据中存在图片

<?php
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Writer/Excel2007.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Worksheet/Drawing.php";$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,'img'   => '1.jpg'],['id' => 2,'name' => 'fj','nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,'img' => 'unnamed.jpg'],['id' => 3,'name' => 'szy','nickname' => 'szy','password' => '123456','phone' => '13451163641','init_time' => 1622632002]
];
//实例化
$objExcel = new PHPExcel();
//设置文档属性
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F,G");
//设置表头
$arrHeader = array('用户ID', '用户名', '昵称','用户密码','手机号码' ,'注册时间','图片');
$lenth = count($arrHeader);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
for ($i = 0; $i < $lenth; $i++) {$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};//填充表格信息
foreach ($arr as $k => $v) {//从第二行开始$k += 2;//表格内容$objActSheet->setCellValue('A' . $k, $v['id']);$objActSheet->setCellValue('B' . $k, $v['username']);$objActSheet->setCellValue('C' . $k, $v['nickname']);$objActSheet->setCellValue('D' . $k, $v['password']);$objActSheet->setCellValue('E' . $k, $v['phone']);$objActSheet->setCellValue('F' . $k, date('Y-m-d H:i:s', $v['init_time']));if ($v['img']){//实例化图片操作类$objDrawing  = new PHPExcel_Worksheet_Drawing();//设置图片地址$objDrawing -> setPath($v['img']);//设置图片高$objDrawing ->setHeight(80);//设置图片宽$objDrawing ->setWidth(80);//设置图片存放在表格的位置$objDrawing ->setCoordinates('G' . $k);//设置X方向偏移量$objDrawing ->setOffsetX(12);//设置Y方向偏移量$objDrawing ->setOffsetY(12);$objDrawing ->setWorksheet($objActSheet);//设置表格的高度$objActSheet->getRowDimension($k)->setRowHeight(100);}$width = array(20, 20, 15, 10, 10, 30, 10, 15);//设置表格的宽度$objActSheet->getColumnDimension('A')->setWidth($width[5]);$objActSheet->getColumnDimension('B')->setWidth($width[1]);$objActSheet->getColumnDimension('C')->setWidth($width[0]);$objActSheet->getColumnDimension('D')->setWidth($width[5]);$objActSheet->getColumnDimension('E')->setWidth($width[5]);$objActSheet->getColumnDimension('F')->setWidth($width[5]);$objActSheet->getColumnDimension('G')->setWidth(80);}$outfile = "人员表" . time() . ".xlsx";//清空输出缓冲区ob_end_clean();//告诉浏览器强制下载header("Content-Type: application/force-download");//二进制文件类型header("Content-Type: application/octet-stream");header("Content-Type: application/download");//设置表名header('Content-Disposition:inline;filename="' . $outfile . '"');header("Content-Transfer-Encoding: binary");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');

效果展示

当一个单元格需要导出多张图片

<?php
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Writer/Excel2007.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Worksheet/Drawing.php";$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,'img'   => '1.jpg'],['id' => 2,'name' => 'fj','nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,'img' => ['unnamed.jpg','1.jpg']],['id' => 3,'name' => 'szy','nickname' => 'szy','password' => '123456','phone' => '13451163641','init_time' => 1622632002,'img'       => ['unnamed.jpg','1.jpg','unnamed.jpg']],['id' => 4,'name' => 'zw','nickname' => 'zw','password' => '123456','phone' => '13451163631','init_time' => 1622632002,]
];
//实例化
$objExcel = new PHPExcel();
//设置文档属性
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F,G");
//设置表头
$arrHeader = array('用户ID', '用户名', '昵称','用户密码','手机号码' ,'注册时间','图片');
$lenth = count($arrHeader);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
for ($i = 0; $i < $lenth; $i++) {$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};//填充表格信息
foreach ($arr as $k => $v) {//从第二行开始$k += 2;//表格内容$objActSheet->setCellValue('A' . $k, $v['id']);$objActSheet->setCellValue('B' . $k, $v['username']);$objActSheet->setCellValue('C' . $k, $v['nickname']);$objActSheet->setCellValue('D' . $k, $v['password']);$objActSheet->setCellValue('E' . $k, $v['phone']);$objActSheet->setCellValue('F' . $k, date('Y-m-d H:i:s', $v['init_time']));if ($v['img']){foreach ($v['img'] as $k1=>$v1){//实例化图片操作类$objDrawing  = new PHPExcel_Worksheet_Drawing();//设置图片地址$objDrawing -> setPath($v1);//设置图片高$objDrawing ->setHeight(80);//设置图片宽$objDrawing ->setWidth(80);//设置图片存放在表格的位置$objDrawing ->setCoordinates('G' . $k);//设置X方向偏移量每一张图片的后面追加一个偏移量$objDrawing ->setOffsetX(80*($k1+1));//设置Y方向偏移量$objDrawing ->setOffsetY(0);$objDrawing ->setWorksheet($objActSheet);//设置表格的高度$objActSheet->getRowDimension($k)->setRowHeight(100);}}$width = array(20, 20, 15, 10, 10, 30, 10, 15);//设置表格的宽度$objActSheet->getColumnDimension('A')->setWidth($width[5]);$objActSheet->getColumnDimension('B')->setWidth($width[1]);$objActSheet->getColumnDimension('C')->setWidth($width[0]);$objActSheet->getColumnDimension('D')->setWidth($width[5]);$objActSheet->getColumnDimension('E')->setWidth($width[5]);$objActSheet->getColumnDimension('F')->setWidth($width[5]);$objActSheet->getColumnDimension('G')->setWidth(80);}$outfile = "人员表" . time() . ".xlsx";//清空输出缓冲区ob_end_clean();//告诉浏览器强制下载header("Content-Type: application/force-download");//二进制文件类型header("Content-Type: application/octet-stream");header("Content-Type: application/download");//设置表名header('Content-Disposition:inline;filename="' . $outfile . '"');header("Content-Transfer-Encoding: binary");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');

效果展示：

excle导入图片时遇到的问题

$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,'img'   => '8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg'],['id' => 2,'name' => 'fj','nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,'img' => ['8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg','1.jpg']],['id' => 3,'name' => 'szy','nickname' => 'szy','password' => '123456','phone' => '13451163641','init_time' => 1622632002,'img'       => ['unnamed.jpg','1.jpg','unnamed.jpg']],['id' => 4,'name' => 'zw','nickname' => 'zw','password' => '123456','phone' => '13451163631','init_time' => 1622632002,]
];

当导入的图片的路径不是本地服务器的时setPath时会报错

这时需要对图片进行处理，需要将图片转化为gd类

<?php
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Writer/Excel2007.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Worksheet/Drawing.php";ini_set('display_errors',1);            //错误信息
ini_set('display_startup_errors',1);    //php启动错误信息
error_reporting(-1);                    //打印出所有的 错误信息$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,'img'   => '8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg'],['id' => 2,'name' => 'fj','nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,'img' => ['8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg','1.jpg']],['id' => 3,'name' => 'szy','nickname' => 'szy','password' => '123456','phone' => '13451163641','init_time' => 1622632002,'img'       => ['unnamed.jpg','1.jpg','unnamed.jpg']],['id' => 4,'name' => 'zw','nickname' => 'zw','password' => '123456','phone' => '13451163631','init_time' => 1622632002,]
];
//实例化
$objExcel = new PHPExcel();
//设置文档属性
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F,G");
//设置表头
$arrHeader = array('用户ID', '用户名', '昵称','用户密码','手机号码' ,'注册时间','图片');
$lenth = count($arrHeader);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
for ($i = 0; $i < $lenth; $i++) {$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};//填充表格信息
foreach ($arr as $k => $v) {//从第二行开始$k += 2;//表格内容$objActSheet->setCellValue('A' . $k, $v['id']);$objActSheet->setCellValue('B' . $k, $v['username']);$objActSheet->setCellValue('C' . $k, $v['nickname']);$objActSheet->setCellValue('D' . $k, $v['password']);$objActSheet->setCellValue('E' . $k, $v['phone']);$objActSheet->setCellValue('F' . $k, date('Y-m-d H:i:s', $v['init_time']));if ($v['img']){foreach ($v['img'] as $k1=>$v1){$img = substr($v1,'-3','3');if ($img == 'jpg'){$img1 = @imagecreatefromjpeg($v1);}if ($img == 'gif'){$img1 =  @imagecreatefromjpeg($v1);}if ($img = 'png'){$img1 = @imagecreatefromjpeg($v1);}//实例化图片操作类$objDrawing  = new PHPExcel_Worksheet_MemoryDrawing();//设置图片存放在表格的位置$objDrawing ->setCoordinates('G' . $k);$objDrawing -> setImageResource($img1);//设置X方向偏移量每一张图片的后面追加一个偏移量$objDrawing ->setOffsetX(80*($k1+1));//设置Y方向偏移量$objDrawing ->setOffsetY(0);$objDrawing ->setWorksheet($objActSheet);//渲染方法$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);////$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);//设置图片高$objDrawing ->setHeight(80);//设置图片宽$objDrawing ->setWidth(80);//设置表格的高度$objActSheet->getRowDimension($k)->setRowHeight(100);}}$width = array(20, 20, 15, 10, 10, 30, 10, 15);//设置表格的宽度$objActSheet->getColumnDimension('A')->setWidth($width[5]);$objActSheet->getColumnDimension('B')->setWidth($width[1]);$objActSheet->getColumnDimension('C')->setWidth($width[0]);$objActSheet->getColumnDimension('D')->setWidth($width[5]);$objActSheet->getColumnDimension('E')->setWidth($width[5]);$objActSheet->getColumnDimension('F')->setWidth($width[5]);$objActSheet->getColumnDimension('G')->setWidth(80);}$outfile = "人员表" . time() . ".xlsx";//清空输出缓冲区ob_end_clean();//告诉浏览器强制下载header("Content-Type: application/force-download");//二进制文件类型header("Content-Type: application/octet-stream");header("Content-Type: application/download");//设置表名header('Content-Disposition:inline;filename="' . $outfile . '"');header("Content-Transfer-Encoding: binary");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');

效果展示

这样可以实现图片的导出，但是在实际开发过程中导出多张图片，不仅消耗php的内存空间，并且导出的文件也比较大，故生成一个图片的链接地址传入excle中导出，是一个比较好的解决方法。

具体实现代码

<?php
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Writer/Excel2007.php";
require "./vendor/phpoffice/phpexcel/PHPExcel/PHPExcel/Worksheet/Drawing.php";ini_set('display_errors',1);            //错误信息
ini_set('display_startup_errors',1);    //php启动错误信息
error_reporting(-1);                    //打印出所有的 错误信息$arr = [['id' => 1,'name' => 'wyq','nickname' => '心如止水','password' => '123456','phone' => '13451167651','init_time' => 1622632002,'img'   => '8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg'],['id' => 2,'name' => 'fj',
//        👌😄'nickname' => 'xj','password' => '123456','phone' => '13451163651','init_time' => 1622632002,'img' => ['8.131.95.189/uploads/goods/20210412/thumb_thumb_thumb_thumb_054960a3349bd19ff089a48f2c1dda7a.jpg','1.jpg']],['id' => 3,'name' => 'szy','nickname' => 'szy','password' => '123456','phone' => '13451163641','init_time' => 1622632002,'img'       => ['unnamed.jpg','1.jpg','unnamed.jpg']],['id' => 4,'name' => 'zw','nickname' => 'zw','password' => '123456','phone' => '13451163631','init_time' => 1622632002,]
];
//实例化
$objExcel = new PHPExcel();
//设置文档属性
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
//设置内容；
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter = explode(',', "A,B,C,D,E,F,G,H,I,J");
//设置表头
$arrHeader = array('用户ID', '用户名', '昵称','用户密码','手机号码' ,'注册时间','图片','图片','图片');
$lenth = count($arrHeader);
//填充表头信息 A1:用户ID、B1:用户名、C1:昵称
for ($i = 0; $i < $lenth; $i++) {$objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
};//填充表格信息
foreach ($arr as $k => $v) {//从第二行开始$k += 2;//表格内容$objActSheet->setCellValue('A' . $k, $v['id']);$objActSheet->setCellValue('B' . $k, $v['name']);$objActSheet->setCellValue('C' . $k, $v['nickname']);$objActSheet->setCellValue('D' . $k, $v['password']);$objActSheet->setCellValue('E' . $k, $v['phone']);$objActSheet->setCellValue('F' . $k, date('Y-m-d H:i:s', $v['init_time']));if ($v['img']){$s = 'G';foreach ($v['img'] as $k1=>$v1){$objActSheet->setCellValue($s.$k , "图片" . ($k1+1));$objActSheet->getCell($s.$k)->getHyperlink()->setUrl($v1);$objActSheet->getStyle($s.$k)->getFont()->getColor()->setRGB('#0000FF');$s++;}}$width = array(20, 20, 15, 10, 10, 30, 10, 15);//设置表格的宽度$objActSheet->getColumnDimension('A')->setWidth($width[5]);$objActSheet->getColumnDimension('B')->setWidth($width[1]);$objActSheet->getColumnDimension('C')->setWidth($width[0]);$objActSheet->getColumnDimension('D')->setWidth($width[5]);$objActSheet->getColumnDimension('E')->setWidth($width[5]);$objActSheet->getColumnDimension('F')->setWidth($width[5]);$objActSheet->getColumnDimension('G')->setWidth($width[5]);$objActSheet->getColumnDimension('H')->setWidth($width[5]);$objActSheet->getColumnDimension('I')->setWidth($width[5]);$objActSheet->getColumnDimension('J')->setWidth($width[5]);
}$outfile = "人员表" . time() . ".xlsx";//清空输出缓冲区ob_end_clean();//告诉浏览器强制下载header("Content-Type: application/force-download");//二进制文件类型header("Content-Type: application/octet-stream");header("Content-Type: application/download");//设置表名header('Content-Disposition:inline;filename="' . $outfile . '"');header("Content-Transfer-Encoding: binary");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');

效果展示：