调用例子

$firstLine = [
'query' => 'Matched product ',
'keywordText' => 'Product targets',
'addAs' => 'Added as',
'impressions' => 'Impressions',
'clicks' => 'Clicks',
'ctr' => 'CTR',
'cost' => 'Spend(USD)',
'cpc' => 'CPC(USD)',
'attributedUnitsOrdered1d' => 'Orders',
'attributedSales1d' => 'Sales(USD)',
'acos' => 'ACOS',
'roas' => 'ROAS',
'cvr' => 'Conversion rate',
];
$user = user::getLoginUser();
//调用xls下载
comModel::xlsDownload($firstLine, $data['data'], 'searchTermExport-' . $user . '.xls');

代码

/**
* 描述 : xls 下载封装
* 参数 ：
* $firstLine array 首行及数据键值 例：['name' => '中文名','user' => '用户名']
* $data array 下载数据 二维数组
* $fileName str 下载文件名
* $imgKey array 要下载图片的字段
* $downType int 0 浏览器下载，1下载到本地
* 作者 : Smart.Chen
*/
public static function xlsDownload($firstLine = [], $data = [], $fileName = 'download.xls', $imgKey = [], $downType = 0)
{
L::open('excel');
$objPHPExcel = new PHPExcel();
$objActSheet = $objPHPExcel->getActiveSheet();
//统一设置单元格样式
$lineArr = [];
$firstLineArr = array_keys($firstLine);
for ($i = 0; $i < count($firstLine); $i++) {
$y = ($i / 26);
if ($y >= 1) {
$y = intval($y);
$lineArr[$firstLineArr[$i]] = chr($y + 64) . chr($i - $y * 26 + 65);
$str = chr($y + 64) . chr($i - $y * 26 + 65);
} else {
$lineArr[$firstLineArr[$i]] = chr($i + 65);
$str = chr($i + 65);
}
$objPHPExcel->setActiveSheetIndex(0)->getStyle($str)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimension($str)->setWidth(20);
}
//自动换行 日志列
// $objActSheet->getStyle('S2:S' . (count($data) + 1))->getAlignment()->setWrapText(true);
//第一行
$first = 1;
foreach ($firstLine as $f => $val) {
$objActSheet->setCellValue($lineArr[$f] . $first, $val);
}
foreach ($data as $k => $v) {
$k += 2;
foreach ($v as $ke => $item) {
if (!empty($lineArr[$ke])) {
if (!empty($imgKey) && in_array($ke, $imgKey)) {
if (is_file(stripslashes($item))) {
// 图片生成
$objDrawing[$k] = new PHPExcel_Worksheet_Drawing();
$objDrawing[$k]->setPath(stripslashes($item));
// 设置宽度高度
$objDrawing[$k]->setHeight(130);//照片高度
$objDrawing[$k]->setWidth(130); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing[$k]->setCoordinates($lineArr[$ke] . $k, $item);
// 图片偏移距离
$objDrawing[$k]->setOffsetX(6);
$objDrawing[$k]->setOffsetY(6);
$objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
} else {
$url = $item;
$filename = pathinfo($url, PATHINFO_BASENAME);
if (empty($filename)) {
continue;
}
if (!is_dir(ROOT_DIR . '/data/downImage')) {
mkdir(ROOT_DIR . '/data/downImage', 0755, true);
}
$path = ROOT_DIR . '/data/downImage/' . $filename;
$pathNew = ROOT_DIR . '/data/downImage/new-' . $filename;
if (!file_exists($pathNew)) {
$file = file_get_contents($url);
$resource = fopen($path, 'a');
if ($resource !== false && $file) {
fwrite($resource, $file);
fclose($resource);
if (file_exists($path) && is_file($path)) {
$pathNew = self::imageCompression($path, $pathNew);
}
}
}
if (file_exists($pathNew)) {
$objDrawing[$k] = new PHPExcel_Worksheet_Drawing();
$objDrawing[$k]->setPath($pathNew);
// 设置宽度高度
$objDrawing[$k]->setHeight(130);//照片高度
$objDrawing[$k]->setWidth(130); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing[$k]->setCoordinates($lineArr[$ke] . $k, $item);
// 图片偏移距离
$objDrawing[$k]->setOffsetX(6);
$objDrawing[$k]->setOffsetY(6);
$objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
// unlink($path);
} else {
$objActSheet->setCellValue($lineArr[$ke] . $k, '');
}
}
} else {
$objActSheet->setCellValue($lineArr[$ke] . $k, $item);
}
}
}
// 表格高度
$objActSheet->getRowDimension($k)->setRowHeight(100);
}
$fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
// $objPHPExcel->getActiveSheet()->setTitle('test');
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
//保存到服务器
if ($downType === 1) {
//文件夹是否存在
if (!is_dir(ROOT_DIR . '/data/uploads')) {
mkdir(ROOT_DIR . '/data/uploads', 0777, true);
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //xls 标准
$objWriter->save(ROOT_DIR . '/data/uploads/' . $fileName);
$downPath = '/data/uploads/' . $fileName;
$imsHost = of::config('env.ims');
return $imsHost . $downPath;
} else {
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$fileName");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
}
}
/**
* 描述 : 图片压缩按比例
* 参数 ：
* $oldDir 图片原始路径
* $newDir 压缩图片保存路径
* $size 压缩后尺寸 默认 200
* $del 是否删除原始文件
* 作者 : Smart.Chen
*/
public static function imageCompression($oldDir, $newDir = '', $size = 100, $del = false)
{
if ($oldDir == '') {
return false;
}
if ($newDir == '') {
$pathInfo = pathinfo($oldDir);
$newName = explode('.', $pathInfo['basename']);
$newPath = $pathInfo['dirname'];
$newName = $newName[0];
$newDir = $newPath . '/' . $newName . '_' . $size . '.' . $pathInfo['extension'];
}
if (file_exists($newDir)) {
return $newDir;
}
//获取原始图片长宽及类型
list($width, $height, $type) = getimagesize($oldDir);
switch ($type) {
case 1:
$image = imagecreatefromgif($oldDir);
break;
case 2:
$image = imagecreatefromjpeg($oldDir);
break;
case 3:
$image = imagecreatefrompng($oldDir);
break;
}
//按照尺寸等比计算长宽
if ($width >= $size) {
$per = $size / $width;//计算比例
$new_width = $width * $per;
$new_height = $height * $per;
} else {
$new_width = $width;
$new_height = $height;
}
$image_wp = imagecreatetruecolor($new_width, $new_height);
imagecopyresampled($image_wp, $image, 0, 0, 0, 0, $new_width, $new_height, $width, $height);
//保存压缩后的图片
imagejpeg($image_wp, $newDir, 90);
if ($del) {
//删除原始图片
unlink($oldDir);
}
return $newDir;
}