欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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');
	}
}
?>