PHP导出excel文件带文字图片
首先需在框架安装phpExcel
复制粘贴即可使用
1.导出不带图片的Excel
function exportText($param, $title, $putArr, $fieldArr) { $phpexcel = new \PHPExcel(); //获取当前工作表 $phpexcel->setActiveSheetIndex(0); //设置工作表名称 $phpexcel->getActiveSheet()->setTitle($title); //设置列宽 $charactors = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
$charactors_filp = array_flip($charactors);
//计算导出列数 $count = count($putArr); $count != count($fieldArr) ? exit('导出字段有误') : ''; $copy = []; for ($i = 0; $i < $count; $i++)
{
if (isset($charactors[$i])) {
//列宽小26，循环a-z
$l = $charactors[$i];
} else {
//a-z结束，动态拼接列
$excel_l = str_split($copy[$i-1]);
if (count($excel_l) == 1)
{
//数量为1说明上个列为Z，新的列从AA开始
$l = 'AA';
}else{
//上一个列已经循环到Z结束，重新开始
if ($excel_l[1] == 'Z')
{
$start_l = $charactors_filp[$excel_l[0]] + 1;
$l = $charactors[$start_l].'A';
}else{
$start_l = $charactors_filp[$excel_l[1]] + 1;
$l = $excel_l[0] . $charactors[$start_l];
}
}
}
$copy[] = $l; //设置列宽 $phpexcel->getActiveSheet()->getColumnDimension($l)->setWidth(16); //设置表头 $tl = $l . '1'; $tltitle = $putArr[$i]; $phpexcel->getActiveSheet()->setCellValue($tl, $tltitle); } //将需要导出的数组循环放入excel foreach ($param as $key => $value) { $key += 2; for ($i = 0; $i < $count; $i++) { $phpexcel->getActiveSheet()->setCellValue($copy[$i] . $key, $value[$fieldArr[$i]]); } } //下载到服务器 $sheet = $title . '.xlsx'; (new \PHPExcel_Writer_Excel2007($phpexcel))->save(SAVE_PATH . $sheet); return '/upload/' . $sheet; //本地浏览器下载 $write = new PHPExcel_Writer_Excel2007($phpexcel); header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type:application/vnd.ms-execl"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download");; header('Content-Disposition:attachment;filename='.$title.'.xls'); header("Content-Transfer-Encoding:binary"); $write->save('php://output');
//激活当前表
$phpexcel->setActiveSheetIndex(0);
ob_end_clean();//清除缓冲区,避免乱码
$expfilename = date('Ymd') . rand(1000, 9999) . '.xls';
//弹出提示下载文件
header('pragma:public');
header("Content-Disposition:attachment;filename=$expfilename");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory:: createWriter($phpexcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
2.调用此方法即可
$array = [ [ 'id' => 1, 'name' => 'xxx', 'cate' => '老人', 'username' => '张三', ], [ 'id' => 2, 'name' => 'xxxx', 'cate' => '老年人', 'username' => '李四', ] ]; $putArr = ['id','名称','分类','用户名']; $fieldArr = ['id','name','cate','username'];
run($array, '导出文件名称', $putArr, $fieldArr);
3.导出带有图片的Excel
function exportTextImg($param, $title, $putArr, $fieldArr) { $phpexcel = new \PHPExcel(); //获取当前工作表 $phpexcel->setActiveSheetIndex(0); //设置工作表名称 $phpexcel->getActiveSheet()->setTitle($title); //设置列宽 $charactors = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'); $count = count($putArr); $count != count($fieldArr) ? die('数据对不上') : ''; $copy = []; for ($i = 0; $i < $count; $i++) { if (isset($charactors[$i])) { $l = $charactors[$i]; } else { $k = intval($i / $count) - 1; $last = $i % $count; $l = $charactors[$k] . $charactors[$last]; } $copy[] = $l; //设置列宽 $phpexcel->getActiveSheet()->getColumnDimension($l)->setWidth(16); //设置表头 $tl = $l . '1'; $tltitle = $putArr[$i]; $phpexcel->getActiveSheet()->setCellValue($tl, $tltitle); } $copy_img = []; foreach ($param as $key => $value) { $key += 2; for ($i = 0; $i < $count; $i++) { if (strpos($fieldArr[$i],'img') !== false) { $objDrawing = new PHPExcel_Worksheet_Drawing(); //实例化导出的图片类 if(!$value[$fieldArr[$i]]){ continue; } /** 导出的图片地址必须是本地，否则excel会报错。 * 因为我导出的文件是直接下载到本地了，但是图片地址是阿里云oss地址，这样导出Excel文件会报错。 * 所以我把图片下载到了本地，然后将图片地址保存到数组内，excel下载成功后，再删除掉图片 */ //将远程图片下载到本地 $file = file_get_contents($value[$fieldArr[$i]]); $fileArr = explode('/', $value[$fieldArr[$i]]); $filename = dirname(__FILE__)."/img/".end($fileArr); file_put_contents($filename, $file); //处理图片 ，设置图片宽高 $phpexcel->getActiveSheet()->getRowDimension($key)->setRowHeight(60); //获取图片 $objDrawing->setPath($filename); $objDrawing->setHeight(50);//照片高度 $objDrawing->setWidth(50); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing->setCoordinates($copy[$i] . $key); // 图片偏移距离 $objDrawing->setOffsetX(12); $objDrawing->setOffsetY(12); $objDrawing->setWorksheet($phpexcel->getActiveSheet()); //这一步将图片地址保存 $copy_img[] = $filename; }else{ $phpexcel->getActiveSheet()->setCellValue($copy[$i] . $key, $value[$fieldArr[$i]]); } } } // //下载到服务器，返回文件地址 // { // $sheet = $title . '.xlsx'; // $url = 'img/'.$sheet; // (new \PHPExcel_Writer_Excel2007($phpexcel))->save($url); // //这一步删除远程文件在本地或者服务器上的地址。 // //注意，必须等到文件下载成功后在执行删除，否则文件会找不到当前这个图片地址报错 // if(!empty($copy_img)) // { // foreach ($copy_img as $k => $v) // { // unlink($v); // } // } // return '/upload/' . $sheet; // } //下载到本地，浏览器下载 { $write = new PHPExcel_Writer_Excel2007($phpexcel); header("Pragma: public"); header("Expires: 0"); header("Cache-Control:must-revalidate, post-check=0, pre-check=0"); header("Content-Type:application/force-download"); header("Content-Type:application/vnd.ms-execl"); header("Content-Type:application/octet-stream"); header("Content-Type:application/download");; header('Content-Disposition:attachment;filename='.$title.'xls'); header("Content-Transfer-Encoding:binary"); //这一步删除远程文件在本地或者服务器上的地址。 //注意，必须等到文件下载成功后在执行删除，否则文件会找不到当前这个图片地址报错 if(!empty($copy_img)) { foreach ($copy_img as $k => $v) { unlink($v); } } $write->save('php://output'); } }
根据类似的参数调用此方法，文件将保存到服务器，下载即可，或者直接调用方法输出到浏览器下载
