JDF_ElectronicPublicity_admin/extend/excel/MultiHeadExcel.php

<?php
namespace excel;

class MultiHeadExcel
{

    //直接调用export_help方法
    //
    //-->二级表头格式
    //$head=[
    //  '字段a'=>'标题名称',
    //  '字段bc'=>[
    //      'name'=>'标题名称',
    //      'children'=>[
    //          '字段b'=>'标题名称',
    //          '字段c'=>'标题名称'
    //      ]
    //   ]
    //]
    //
    //-->三级表头格式
    //$head=[
    //    '字段a'=>'a',
    //    '字段bcd'=>[
    //        'name'=>'b',
    //        'children'=>[
    //            '字段bc'=>[
    //                'name'=>'bc',
    //                'children'=>[
    //                    '字段b'=>'b',
    //                    '字段c'=>'c',
    //                ],
    //            ],
    //            '字段d'=>'d'
    //        ],
    //    ],
    //];
    //


    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);
    }
    /**
     * @title: 导出工作簿
     * @desc: 同时将多张工作表导出在同一个工作簿中
     * @param {array} {bookData} {} {工作簿数据}
     * @param {string} {bookData.title} {} {工作表标题}
     * @param {array} {bookData.head} {} {工作表表头}
     * @param {array} {bookData.body} {} {工作表内容}
     * @param {string} {save_path} {} {文件保存路径}
     * @return {*}
     * @author: Rock
     * @method: POST
     * @Date: 2023-03-07 15:10:30
     */    
    public function exportBook($bookData,$save_path)
    {
        //A-ZZ
        $letter = $this->getA_ZZ();
        //实例化
        $objExcel = new \PHPExcel();
        foreach($bookData as $sheet=>$xlsData){
            $objExcel->setActiveSheetIndex($sheet);//选择选择工作表
            $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);
                $objActSheet->setTitle($title);//设置工作表标题
                $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);
    }
}