PHPExcel导出纵向合并单元格
程序员文章站
2022-06-14 09:08:24
...
TP5框架,使用PHPExcel导出可纵向合并单元格的excel表格,PHPExcel插件放在根目录下vendor文件夹内。
1、controller目录中控制器,Order.php
<?php
namespace app\index\controller;
use app\index\model\ExcelDown; //excel导出
class Order{
public function download(){
//获取数据,并重组
$w = [
['users']=>[
['nickname'=>'小新','member_id'=>9,'order_num'=>49,'list_num'=>3],
['nickname'=>'小白','member_id'=>10,'order_num'=>6,'list_num'=>1],
['nickname'=>'大熊','member_id'=>11,'order_num'=>17,'list_num'=>1],
['nickname'=>'胖虎','member_id'=>17,'order_num'=>9,'list_num'=>2]
],
['goods']=>[
['goods_name'=>'红提','nickname'=>'小新','goods_num'=>1,'amount'=>'26.80','sort'=>1],
['goods_name'=>'海南绿橙','nickname'=>'小新','goods_num'=>3,'amount'=>'108.00','sort'=>2],
['goods_name'=>'甘蔗','nickname'=>'小新','goods_num'=>1,'amount'=>'25.60','sort'=>3],
['goods_name'=>'海南绿橙','nickname'=>'小白','goods_num'=>1,'amount'=>'36.00','sort'=>4],
['goods_name'=>'红枣','nickname'=>'大熊','goods_num'=>2,'amount'=>'18.80','sort'=>5],
['goods_name'=>'芒果','nickname'=>'胖虎','goods_num'=>3,'amount'=>'45.90','sort'=>6],
['goods_name'=>'山竹','nickname'=>'胖虎','goods_num'=>2,'amount'=>'28.00','sort'=>7]
]
];
// print_r($w);exit;
//excel表参数
$data = array(
'letter'=>array('A','B','C','D','E','F'),
'width'=>array('6','20','14','20','15','10'),
'header'=>array('序号','昵称','累计下单数','商品名称','数量','支付金额'),
'sheetname'=>'购买记录表',
'filename'=>'购买记录表(' . date('Ymd') . ').xls',
'field'=>array('sort','nickname','order_num','goods_name','goods_num','amount'), //数据字段名
);
//导出数据
$excelc = new ExcelDown();
$res = $excelc->push($w,$data);
}
}
?>
2、model中模型,ExcelDown.php
<?php
namespace app\index\model;
use think\Model;
use think\Db;
class ExcelDown extends Model{
public function __construct(){
vendor("PHPExcel.PHPExcel"); //导入PHPExcel类库(文件路径:/excel/vendor/)
}
/**
* 导出excel表格
* @param array $res 要导出的数据
* @param array $data 表格参数
* @param string $imgPath 图片路径
* @param array $imgLetter 图片要插入的表格列
*/
function push($res,$data,$imgPath='',$imgLetter=''){
$objPHPExcel = new \PHPExcel();
//设置excel文件右键属性所显示的信息
$objPHPExcel->getProperties()->setCreator("xxx") //作者
->setLastModifiedBy("xxx") //最后一次保存者
->setTitle("Office 2003 XLS Test Document") //标题
->setSubject("Office 2003 XLS Test Document") //主题
->setDescription("Test document for Office 2003 XLS, generated using PHP classes.") //描述
->setKeywords("office 2003 openxml php") //标注
->setCategory("Test result file"); //类别
//设置列宽
for($i=0;$i<count($data['letter']);$i++){
$objPHPExcel->getActiveSheet()->getColumnDimension($data['letter'][$i])->setWidth($data['width'][$i]);
}
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(18);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(18);
$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(18);
//设置字体大小
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
//设置字体粗体
$objPHPExcel->getActiveSheet()->getStyle('A3:'.end($data['letter']).'3')->getFont()->setBold(true);
//设置文字垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A3:'.end($data['letter']).'3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置表格框线
$objPHPExcel->getActiveSheet()->getStyle('A1:'.end($data['letter']).'3')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//设置标题字体粗体
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//设置标题字体大小
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
//设置标题文字水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置标题文字垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置副标题水平居右
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//设置水平居中
for($i=0;$i<count($data['letter']);$i++){
$objPHPExcel->getActiveSheet()->getStyle($data['letter'][$i].'3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($data['letter'][$i])->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
//横向合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:'.end($data['letter']).'1');
$objPHPExcel->getActiveSheet()->mergeCells('A2:'.end($data['letter']).'2');
//设置表头内容
for($i=0;$i<count($data['letter']);$i++){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', $data['sheetname'])
->setCellValue('A2', '导出时间:' . date('Y-m-d H:i',time()))
->setCellValue($data['letter'][$i].'3', $data['header'][$i]);
}
for($i=0;$i<count($res['goods']);$i++){
for($j=0;$j<count($data['letter']);$j++){
$objPHPExcel->getActiveSheet(0)->setCellValue($data['letter'][$j].($i+4),$res['goods'][$i][$data['field'][$j]]);
}
//设置文字垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+4).':'.end($data['letter']).($i+4))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置表格框线
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+4).':'.end($data['letter']).($i+4))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
//设置行高
$objPHPExcel->getActiveSheet()->getRowDimension($i+4)->setRowHeight(18);
//自动换行
$objPHPExcel->getActiveSheet()->getStyle('A'.($i+4).':'.end($data['letter']).($i+4))->getAlignment()->setWrapText(true);
}
//纵向合并单元格
$start = 4;
for($m=0;$m<count($res['users']);$m++){
$end = $start + (int)$res['users'][$m]['list_num'] - 1;
$objPHPExcel->getActiveSheet()->mergeCells('B'.$start.':B'.$end);
$objPHPExcel->getActiveSheet()->mergeCells('C'.$start.':C'.$end);
$start += (int)$res['users'][$m]['list_num'];
}
//设置工作表名称
$objPHPExcel->getActiveSheet()->setTitle($data['sheetname']);
//将活动表索引设置为第一个表,因此Excel将作为第一个表打开此表
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean(); //清除缓冲区,避免乱码
// 将输出重定向到一个客户端web浏览器(Excel2007)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$data['filename']);
header('Cache-Control: max-age=0');
//要是输出为Excel2007,使用 Excel2007对应的类,生成的文件名为.xlsx。
//如果是Excel2003及以下,使用Excel5,对应生成.xls文件
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//支持浏览器下载生成的文档
$objWriter->save('php://output');
//支持保存生成的文件在当前目录下,直接文件名做为参数
// $objWriter->save('test.xlsx');
}
}
?>
推荐阅读
-
easypoi合并单元格导入(EasyPoi导入导出最全案例)
-
easypoi合并单元格导入(EasyPoi导入导出最全案例)
-
java POI导出excel,合并单元格边框消失
-
Java使用POI导出Excel时合并单元格
-
php导出excel图片格式,PHPExcel API接口用法大全,按模板导入excel,美化excel,导出图片,设置单元格字体颜色背景色边框,合并单元格,设置行高列宽...
-
POI导出Excel(合并单元格),获取excel内容
-
easypoi实现表格导出(实现一对多,合并单元格)
-
phpexcel 怎么向合并的单元格写内容
-
JXL导出EXCEL合并单元格
-
导出Excel合并单元格