phpexcel二级表头

自己写的一个excel文件导出，用来学习一下。表头格式：

$head=['字段a'=>'销售额','字段b'=>'货品a','字段c'=>'货品b' ]

或者

$head=[
    '字段a'=>'销售额',
    '字段bc'=>[
        'name'=>'详情',
        'children'=>['字段b'=>''货品a','字段c'=>'货品b']
    ]
]

使用的时候调用export_help就可以了，标题不是必须的。下面是文件代码：

<?php
namespace excel;

class MultiHeadExcel
{

    protected $max_width;
    public function __construct()
    {
        require_once(__DIR__.'/PHPExcel.php');
    }

    /**
     * 处理表头数据
     * @param array $head  表头数组
     * @param array $heads 返回数组
     * @param int $x  x轴
     * @param int $y  y轴
     * @return array
     */
    public function changehead($head=[],&$heads=[],$x=0,$y=0)
    {
        if(empty($heads[$y])){
            $heads[$y] = [];
        }
        $w = 0;  //宽度
        $h = $y; //高度
        foreach ($head as $v){
            if(!empty($v['children'])){
                $heads[$y][$x] = $v['name'];
                if($h == $y){
                    $h++;
                }
                if(empty($heads[$y])){
                    $heads[$h] = [];
                }
                $res = $this->changehead($v['children'],$heads,$x,$h);
                $x+=$res[3];
            }else{
                $heads[$y][$x] = $v;
                $x++;
            }
            $w++;
        }
        $this->max_width = $this->max_width>$x?$this->max_width:$x;
        return [$heads,$x,$y,$w,$h];
    }

    /**
     * 处理表头单元格
     * @param $head
     * @param $lenth
     * @param $letter
     * @return array
     */
    public function mergehead($head,$lenth,$letter,$row=1)
    {
        $check = [];$widthmerge = [];

        $check_ = [];$heightmerge = [];

        foreach ($head as $k=>$v) {
            $check_[$k] = [];
            for ($i = 0; $i < $lenth; $i++) {
                $check_[$k][$i] = empty($v[$i])?0:$v[$i];
                if (empty($v[$i])){
                    //左右合并
                    if($i>0){
                        if(empty($widthmerge[$k])){
                            $widthmerge[$k] = [];
                        }
                        if(!empty($check[$i])){
                            goto a;
                        }else{
                            $l = count($widthmerge[$k]);
                            if($l>0){
                                $end = $widthmerge[$k][$l-1];
                            }else{
                                $end = [];
                            }
                            if(empty($end[0])){
                                $widthmerge[$k][] = [$i-1];
                            }else if(empty($end[1])){
                                if(($i-$end[0])==3){
                                    $widthmerge[$k][$l-1][1] = $i-2;
                                    $widthmerge[$k][] = [$i-1];
                                }else{
                                    $widthmerge[$k][$l-1][1] = $i;
                                }
                            }else if(($i-$end[1])==1){
                                $widthmerge[$k][$l-1][1] = $i;
                            }else{
                                $widthmerge[$k][] = [$i-1];
                            }
                            $check_[$k][$i] = $check_[$k][$i-1];
                        }
                    }
                    //上下合并
                    a:
                    if($k>0){
                        if(empty($check_[$k][$i])){
                            if(empty($heightmerge[$i])){
                                $heightmerge[$i] = [$letter[$i] . ($row-1),$letter[$i] . $row];
                            }else{
                                $heightmerge[$i][1] = $letter[$i] . $row;
                            }
                        }
                    }
                }else{
                    $check[$i] = true;
                }
            }
            $row++;
        }
        return [$widthmerge,$heightmerge];
    }

    /**
     * 返回A-ZZ
     * @return array
     */
    public function getA_ZZ()
    {
        $letter = range('A', 'Z');
        $x = $letter;
        $y = $letter;
        foreach ($x as $xv){
            foreach ($y as $yv){
                $letter[] = $xv.$yv;
            }
        }
        return $letter;
    }

    /**
     * @title: 判断是否为图片
     * @param $str
     * @return bool
     * @Author: wangkewei
     * @Date: 2021/9/27 19:48
     */
    public function is_image_string($str)
    {
        if(strstr($str,'jpg')!=false
            || strstr($str,'jpeg')!=false
            || strstr($str,'png')!=false
            || strstr($str,'gif')!=false
            || strstr($str,'bmp')!=false){
            if(is_file(public_path().$str)){
                return true;
            }
        }
        return false;
    }


    /**
     * 导出
     * @param array $xlsData 表格数据
     * @param array $head 表格头
     * @param array $save_path 文件保存路径
     * @param string $title 标题(非必填)
     * @throws \Exception
     */
    public function export_help($xlsData=[],$head=[],$save_path='',$title='')
    {
        //A-ZZ
        $letter = $this->getA_ZZ();
        //实例化
        $objExcel = new \PHPExcel();
        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        //处理表头
        $heads = $this->changehead($head);
        //返回的一维数组表头
        $head = $heads[0];
        //表格列数
        $lenth = $this->max_width;
        //是否存在标题
        if(empty($title)){
            $row = 1;
        }else{
            //设置标题
            $styleArray = array(
                'font'  => array(
                    'bold'  => true,
                    'color' => array('rgb' => 'FFFFFF'),
                    'size'  => '20'
                ),
                'fill' => array(
                    'type'=>\PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '0070C0')
                ));
            $objActSheet->setCellValue("A1", ''."$title");
            $objActSheet->mergeCells('A1:'.$letter[($lenth-1)].'1');
            $objActSheet->getStyle( 'A1:'.$letter[($lenth-1)].'1')->applyFromArray($styleArray);
            $row = 2;
        }
        //解决标题居中问题
        $merges = $this->mergehead($head,$lenth,$letter,$row);
        //合并标题单元格
        //上下合并
        $heightmerge = $merges[1];
        foreach ($heightmerge as $v){
            $objActSheet->mergeCells($v[0].':'.$v[1]);
        }
        //左右合并
        $widthmerge = $merges[0];
        for ($i = 0; $i < count($head); $i++) {
            if(!empty($widthmerge[$i])){
                foreach ($widthmerge[$i] as $v){
                    if(empty($v[1])){
                        $v[1] = $v[0]+1;
                    }
                    $objActSheet->mergeCells($letter[$v[0]].($i+$row).':'.$letter[$v[1]].($i+$row));
                }
            }
        }
        //填充表头信息
        foreach ($head as $k=>$v) {
            for ($i = 0; $i < $lenth; $i++) {
                if (!empty($v[$i])){
                    $objActSheet->setCellValue($letter[$i] . $row, ''."$v[$i]");
                }
            }
            $row++;
        }
        $row--;
        // 表格高度
        $objActSheet->getRowDimension($row)->setRowHeight(25);
        //填充表格信息
        foreach ($xlsData as $k => $v) {
            $row ++;
            $i=0;
            $v = array_values($v);
            //表格内容
            //判断是否为图片
            foreach($v as $jv){
                $image_slice = [];
                if(is_array($jv)){
                    $image_slice = $jv;
                }
                if(is_string($jv) && strstr($jv,',')){
                    $image_slice = explode(',',$jv);
                }
                if(empty($image_slice)){
                    if($this->is_image_string($jv)){
                        $objDrawing = new \PHPExcel_Worksheet_Drawing();
                        $objDrawing->setPath(public_path().$jv);
                        $objDrawing->setWidthAndHeight(30,30);
                        $objDrawing->setCoordinates($letter[$i].$row);
                        $objDrawing->setWorksheet($objActSheet);
                        $i++;
                        continue;
                    }
                }else{
                    $image_slice_export = [];
                    foreach ($image_slice as $isv){
                        if($this->is_image_string($isv)){
                            $image_slice_export[] = $isv;
                        }
                    }
                    if(!empty($image_slice_export)){
                        foreach ($image_slice_export as $k=>$ise){
                            $objDrawing = new \PHPExcel_Worksheet_Drawing();
                            $objDrawing->setPath(public_path().$ise);
                            $objDrawing->setOffsetX(35*$k);
                            $objDrawing->setOffsetY(5);
                            $objDrawing->setWidthAndHeight(30,30);
                            $objDrawing->setCoordinates($letter[$i].$row);
                            $objDrawing->setWorksheet($objActSheet);
                        }
                        $i++;
                        continue;
                    }
                }
                //不为图片直接填充
                $objActSheet->setCellValue($letter[$i].$row, ' '."$v[$i]");
                $i++;
            }
            // 表格高度
            $objActSheet->getRowDimension($row)->setRowHeight(25);
        }
        $end=$letter[$lenth].$row;
        // 设置垂直居中
        $objActSheet->getStyle('A1:'.$end)->getAlignment()
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置水平居中
        $objActSheet->getStyle('A1:'.$end)->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置表格的宽度
        $width = 20;
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->getColumnDimension($letter[$i])->setWidth($width);
        }
        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objWriter->save($save_path);
    }

}