PHPExcel导出纵向合并单元格——第二种

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 = [
			['goods_name'=>'红提','member_id'=>9,'nickname'=>'小新','goods_num'=>1,'amount'=>'26.80','sort'=>1],
			['goods_name'=>'海南绿橙','member_id'=>9,'nickname'=>'小新','goods_num'=>3,'amount'=>'108.00','sort'=>2],
			['goods_name'=>'甘蔗','member_id'=>9,'nickname'=>'小新','goods_num'=>1,'amount'=>'25.60','sort'=>3],
			['goods_name'=>'海南绿橙','member_id'=>10,'nickname'=>'小白','goods_num'=>1,'amount'=>'36.00','sort'=>4],
			['goods_name'=>'红枣','member_id'=>11,'nickname'=>'大熊','goods_num'=>2,'amount'=>'18.80','sort'=>5],
			['goods_name'=>'芒果','member_id'=>17,'nickname'=>'胖虎','goods_num'=>3,'amount'=>'45.90','sort'=>6],
			['goods_name'=>'山竹','member_id'=>17,'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);
			
			//纵向合并同一用户
			if($i>=1 &&($res[$i]['member_id']==$res[$i-1]['member_id'])){
				$objPHPExcel->getActiveSheet()->mergeCells('B'.($i+3).':B'.($i+4));
				$objPHPExcel->getActiveSheet()->mergeCells('C'.($i+3).':C'.($i+4));
			}
		}

		//设置工作表名称
		$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');
	}
}
?>