下载phpexcel插件
composer安装命令
composer require phpoffice/phpexcel
git地址
https://github.com/PHPOffice/PHPExcel
目录结构
简单的将数据导出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' => 'http://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' => ['http://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' => 'http://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' => ['http://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' => 'http://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' => ['http://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');
效果展示: