一、效果
二、代码
<?php
namespace app\index\controller;
use think\facade\Env;
class Test
{
public function test()
{
self::excelAction();
}
public function excelAction()
{
$objPHPExcel = new \PHPExcel();
$titleArray = [['头像', '姓名', '性别', '爱好', '时间', 'ID']];
$data = $this->getData();
$lastData = array_merge($titleArray, $data);
$cols = self::getCols(count($titleArray[0]));
$lastCol = end($cols);
$k = 0;
foreach ($lastData as $val) {
$k++;
foreach ($cols as $_k => $_v) {
if ($k != 1) {
$objPHPExcel->getActiveSheet()->getColumnDimension($_v)->setWidth(20);
$objPHPExcel->getActiveSheet()->getRowDimension($k)->setRowHeight(80);
if ($_k == 0) {
$imageObj[$k] = new \PHPExcel_Worksheet_Drawing();
$url = $this->download($val[$_k]);
$imageObj[$k]->setPath($url);
$imageObj[$k]->setHeight(80);
$imageObj[$k]->setWidth(80);
$imageObj[$k]->setCoordinates($_v . $k);
$imageObj[$k]->setOffsetX(12);
$imageObj[$k]->setOffsetY(12);
$imageObj[$k]->setWorksheet($objPHPExcel->getActiveSheet());
} else {
$objPHPExcel->getActiveSheet()->setcellvalue($_v . $k, $val[$_k]);
$objPHPExcel->getActiveSheet()->setCellValueExplicit($_v . $k, $val[$_k], \PHPExcel_Cell_DataType::TYPE_STRING);
}
} else {
$objPHPExcel->getActiveSheet()->setcellvalue($_v . $k, $val[$_k]);
$objPHPExcel->getActiveSheet()->getStyle($_v . $k)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValueExplicit($_v . $k, $val[$_k], \PHPExcel_Cell_DataType::TYPE_STRING);
}
}
}
$objPHPExcel->getActiveSheet()->getStyle("A1:{$lastCol}{$k}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$fn = "订单列表(" . date("Y.m.d.H.i.s") . ").xls";
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header("Content-Disposition: attachment;filename=$fn");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
public function getData()
{
$photo = 'http://www.pptbz.com/pptpic/UploadFiles_6909/201203/2012031220134655.jpg';
$data = array(
array($photo, '张三', '男', '吃', '2019-03-20', '123456789876543'),
array($photo, '李四', '男', '喝', '2019-03-21', '123456789034455'),
array($photo, '王五', '男', '玩', '2019-03-22', '987654321234567'),
);
return $data;
}
public static function getCols($count)
{
$columnFlag = [
0 => 'Z', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H',
9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q',
18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z'
];
if ($count == 0) {
return [];
}
$column = [];
for ($index = 1; $index <= $count; $index++) {
if ($index <= 26) {
$column[] = $columnFlag[$index];
} else {
$value = floor($index / 26);
if ($index % 26 == 0) {
$value -= 1;
}
$column[] = $columnFlag[$value] . $columnFlag[floor($index % 26)];
}
}
return $column;
}
function download($url, $path = '/data/excel/')
{
$path = Env::get('root_path') . '/public' . $path;
$this->createDirectory($path);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
$file = curl_exec($ch);
curl_close($ch);
$filename = pathinfo($url, PATHINFO_BASENAME);
$savePath = $path . $filename;
if (!file_exists($savePath)) {
$resource = fopen($savePath, 'a');
fwrite($resource, $file);
fclose($resource);
}
return $savePath;
}
function createDirectory($dir)
{
if (!file_exists($dir)) {
mkdir($dir, 0777, true);
chmod($dir, 0777);
}
}
}