PHP数据跨行跨列处理

上海菜鸡毛

已于 2022-12-26 17:38:32 修改

阅读量370

点赞数

分类专栏： laravel 开发集成 laravel php 文章标签： php Laravel Dcat

于 2022-12-26 17:26:50 首次发布

本文链接：https://blog.csdn.net/qq_29755359/article/details/128446718

版权

php 同时被 3 个专栏收录

81 篇文章 0 订阅

订阅专栏

laravel

42 篇文章 0 订阅

订阅专栏

laravel 开发集成

7 篇文章 0 订阅

订阅专栏

PS

PHP数据跨行跨列处理，表格展示，Excel导出。后台是Dcat Admin,导出是"maatwebsite/excel": “^3.1”

Excel效果

在这里插入图片描述

后台页面效果

在这里插入图片描述

示例代码

数据调用

可以放在Dcat对应Controller的数据仓库里面。

    public function get(Grid\Model $model)
    {
    	// 返回Controller数据
        return $this->getdataInit();

//        return parent::get($model); // TODO: Change the autogenerated stub
    }
	// 调用方法
    public function getdataInit()
    {
        // 数据查询
        $dataModel = $this->getData();
        if(!count($dataModel)) return [];
        // 数据格式化
        try {
			// 这是有3列汇总的情况
			// 如果只有2列汇总，传参column4注释
			// 如果只有1列汇总，传参column4、column3注释
			// 对应的字段得到控制属性,默认是等于true显示的，等于false表示不显示
			// $dataRowCol->isColumn4 = false; $dataRowCol->isColumn3 = false;
            $dataRowCol = new dataRowCol([
                'column1'   => 'pay_policy_time',  // 投保日期列 key
                'column2'   => 'channel_1',			// 一级渠道 key
                'column3'   => 'channel_2',			// 二级渠道 key
                'column4'   => 'channel_3',			// 三级渠道 key
            ],$this);

            // 模拟数据写入
//            $this->dbTestData(300);
            $data1 = $dataRowCol->dataInit($dataModel);
        }catch (\Exception $e)
        {
            echo $e->getTraceAsString();exit();
        }
        return $data1;
    }
    /**
     * 数据统一计算
     * 返回版
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/19
     * Time: 17:50
     * @param $re
     * @param $v
     * @return mixed
     */
    public function reNumBc($re,$v,$isKey = 'id')
    {

        if(!isset($re[$isKey]))
        {
            return $v;
        }
        // 计数器,累加了几次
        $re['order_num'] += $v['order_num'];
        $re['unpaid_pay'] += $v['unpaid_pay'];
        $re['sign_num'] += $v['sign_num'];
        $re['premium'] = bcadd($re['premium'],$v['premium'],ConfigService::bc_float);
        $re['piece_avg'] = bcadd($re['piece_avg'],$v['piece_avg'],ConfigService::bc_float);
        $re['re_piece'] += $v['re_piece'];
        $re['re_premium'] = bcadd($re['re_premium'],$v['re_premium'],ConfigService::bc_float);
        $re['add_premium'] = bcadd($re['add_premium'],$v['add_premium'],ConfigService::bc_float);
        return $re;
    }

数据格式化类

<?php
/**
 *
 * Created by PhpStorm
 * User: Noah
 * Date: 2022/12/20
 * Time: 11:01
 */

namespace App\Admin\Server;


class dataRowCol
{

    /**
     * 四级栏目是否显示
     * 不显示的情况需要把数据组合到3级栏目
     * 且上级栏目跨行数也需要更改
     * @var bool
     */
    public $isColumn3 = true;
    public $isColumn4 = true;

    protected $summaryField1 = 'column1';
    protected $summaryField2 = 'column2';
    protected $summaryField3 = 'column3';
    protected $summaryField4 = 'column4';
    protected $summaryField5 = 'column5';

    /**
     * @var string[]
     *
        'column1'   => 'pay_policy_time',   // 一级栏目字段
        'column2'   => 'channel_1',         // 二级栏目字段
        'column3'   => 'channel_2',         // 三级栏目字段
        'column4'   => 'channel_3',         // 四级栏目字段
     *
     *
     */
    protected $columnArr  = [];

    /**
     * 明细数据累加方法
     * @var
     */
    protected  $ob;

    protected $channel;

    public function __construct($columnArr, $ob)
    {
        if(!isset($columnArr[$this->summaryField4])) $columnArr[$this->summaryField4] = $this->summaryField4;
        if(!isset($columnArr[$this->summaryField3])) $columnArr[$this->summaryField3] = $this->summaryField3;
        $this->columnArr = $columnArr;
        $this->ob = $ob;
    }

    /**
     * 调用外部方法计算累加
     * $re 累加并返回的数据
     * $v 计算的数据
     * $isKey = 'id' 判断否是为空的字段,字段不存在直接$v
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 11:09
     * @param $sum
     * @param $v
     * @return mixed
     */
    private function reNumBc($sum,$v,$isKey = 'id')
    {
        return $this->ob->reNumBc($sum,$v,$isKey);
    }

    /**
     * 数据格式化
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/16
     * Time: 11:25
     * @param $data'
     */
    public function dataInit($data)
    {
        $res = [];
        $sum = []; // 总汇-总合计
        $huizongs = []; // 总汇-数据格式化
        $dayDataInit = [];    //  每日合计-数据格式化
        $daySum = [];
        if(count($data))
        {
            foreach ($data as $v)
            {
                // 总汇-总合计
                $sum = $this->reNumBc($sum,$v);

                $this->channelInit($this->channel,$v);

                $key = date('Y-m-d',strtotime($v[$this->columnArr[$this->summaryField1]]));

                // 每日合计-数据格式化
                list($dayDataInit,$daySum) = $this->dayChannelInit($dayDataInit,$daySum,$v,$key);
            }
        }
//        dd($this->channel);
//        dd([
//            $dayDataInit,
//            $daySum
//        ]);
        // 总汇总-数据组合
        // 总合计
        $res[0] = $sum;
        $this->resFirst($res[0]);
        // 总汇总-数组组合
        $this->sumDataInit($this->channel,$res,0);

        // 每日汇总-数组组合
        $this->sumDayDataInit($dayDataInit,$daySum,$res);

        return $res;
    }


    /**
     * 首行合计
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 0:37
     * @param $sum
     * @param string $value
     */
    private function resFirst(&$sum,$value = '汇总(截止到昨日)')
    {
        $sum[$this->summaryField1] = [
            'value' => $value,
        ];
        if($this->isColumn4)
        {
            $colspan = 3;

        }else
        {
            $colspan = 2;
        }

        $sum[$this->summaryField2] = [
            'value' => '汇总',
            'colspan'   => $colspan
        ];
        unset($sum[$this->summaryField3]);
        unset($sum[$this->summaryField4]);
    }

    /**
     * 每天-数组组合
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 1:00
     * @param $dayDataInit
     * @param $daySum
     * @param $res
     */
    private function sumDayDataInit($dayDataInit,$daySum,&$res)
    {
        foreach ($dayDataInit as $k => $v)
        {
//            dd($daySum);
            $res[] = $daySum[$k];
            $resKey = count($res)?count($res)-1:0;
            $this->resFirst($res[$resKey],$k);
            $this->sumDataInit($v,$res,$resKey);
        }
    }

    /**
     * 总汇总-数组组合
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 0:29
     * @param $res
     * @param int $type
     */
    private function sumDataInit($data,&$res,$resKey)
    {
        $inarray = ['data','num',$this->summaryField2,$this->summaryField3];
        $sumNum = 1;

        if($this->isColumn3) $sumNum += count($data[$this->summaryField2]);
        unset($data[$this->summaryField2]);
//        unset($data[$this->summaryField3]); login_time
        foreach ($data as $k1 => $v1)
        {
            // 二级栏目
            if($this->isColumn3)
            {
                $res[] = $this->column1Init($k1,$v1);
                if($this->isColumn4) $sumNum += count($v1[$this->summaryField2]);
            }
            if(!in_array($k1,$inarray))
            {
                foreach ($v1 as $k2 => $v2)
                {
                    $this->v2Init($res,$sumNum,$k2,$v2);
                }
            }

        }

        // 一级栏目跨行=二级栏目数量(去重)+三级栏目数量(对应二级栏目下去重)+归属一级栏目的行数
        $res[$resKey][$this->summaryField1]['rowspan'] = $sumNum;
    }

    /**
     * v2数据格式化
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 14:24
     * @param $res
     * @param $sumNum
     * @param $k2
     * @param $v2
     */
    private function v2Init(&$res,&$sumNum,$k2,$v2)
    {
        $inarray = ['data','num',$this->summaryField2,$this->summaryField3];
        if(!$this->isColumn3) $inarray = ['data','num',$this->summaryField2];

        if(!in_array($k2,$inarray))
        {
            if($this->isColumn4)
            {
                // 四级栏目显示
                $column2 = $this->column2Init($k2,$v2);
                $res[] = $column2;
            }

            foreach ($v2 as $k3 => $v3)
            {
                $this->v3Init($res,$sumNum,$k3,$v3);
            }
        }
    }

    /**
     * v3数据处理
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 14:21
     * @param $res
     * @param $sumNum
     * @param $k3
     * @param $v3
     * @param $k3inarray
     */
    private function v3Init(&$res,&$sumNum,$k3,$v3)
    {
        $k3inarray = ['data','num',$this->summaryField2,$this->summaryField3];

        if(!in_array($k3,$k3inarray))
        {
            $sumNum += count($v3['data']);
            foreach ($v3['data'] as $k4 => $v4)
            {
                if($this->isColumn4)
                {
                    // 四级栏目显示
                    $v4[$this->summaryField4] = $v4[$this->columnArr[$this->summaryField4]];
                    unset($v4[$this->columnArr[$this->summaryField1]]);
                    unset($v4[$this->columnArr[$this->summaryField2]]);
                    unset($v4[$this->columnArr[$this->summaryField3]]);
                }
                else
                {
                    if($this->isColumn3)
                    {
                        // 四级栏目隐藏
                        $v4[$this->summaryField3] = $v4[$this->columnArr[$this->summaryField3]];
                        unset($v4[$this->columnArr[$this->summaryField1]]);
                        unset($v4[$this->columnArr[$this->summaryField2]]);
                    }else
                    {
                        // 三级栏目隐藏
                        $v4[$this->summaryField2] = $v4[$this->columnArr[$this->summaryField2]];
                        unset($v4[$this->columnArr[$this->summaryField1]]);
//                        unset($v4[$this->columnArr[$this->summaryField2]]);
                    }

                }

                // 详细数据


                $res[] = $v4;
            }
        }
    }

    /**
     * 一级栏目数据组合
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/19
     * Time: 22:49
     * @param $k1
     * @param $v1
     * @return mixed
     */
    private function column1Init($k1,$v1)
    {

        if($this->isColumn4)
        {
            $colspan = 2;
            $rowspan = count($v1[$this->summaryField2])+$v1['num'];

        }else
        {
            $colspan = 1;
            $rowspan = $v1['num'];
        }
        $column1 = $v1['data'];
        $column1[$this->summaryField1] = [
            'value' => $k1,
            'rowspan'   => $rowspan+1
        ];



        $column1[$this->summaryField2] = [
            'value' => '汇总',
            'colspan'   => $colspan
        ];
        unset($column1[$this->summaryField3]);
        unset($column1[$this->summaryField4]);
        return $column1;
    }

    /**
     * 二级栏目数据组合
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/19
     * Time: 22:49
     * @param $k1
     * @param $v1
     * @return mixed
     */
    private function column2Init($k2,$v2)
    {
        $column2 = $v2['data'];
        $column2[$this->summaryField2] = [
            'value' => $k2,
            'rowspan'   => $v2['num']+1,
        ];
        $column2[$this->summaryField3] = [
            'value' => '汇总',
            'colspan'   => 1
        ];
        unset($column2[$this->summaryField1]);
        unset($column2[$this->summaryField4]);
        return $column2;
    }



    /**
     * 合计汇总
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/16
     * Time: 13:41
     * @param $sum
     * @param $v
     * @return mixed
     */
    private function zongSum(&$sum,$v)
    {
        if(count($sum))
        {
            $this->numBc($sum,$v);
        }
        else{
            $sum = $v;
        }

        return $sum;
    }


    /**
     * 每天渠道数据存储
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/20
     * Time: 0:35
     * @param $dayDataInit
     * @param $daySum
     * @param $v
     * @param $key
     * @return array
     */
    private function dayChannelInit($dayDataInit,$daySum,$v,$key)
    {
        if(!isset($dayDataInit[$key])) $dayDataInit[$key] = [];

        $this->channelInit($dayDataInit[$key],$v);


        if(!isset($daySum[$key])) $daySum[$key] = [];
        $daySum[$key] = $this->reNumBc($daySum[$key],$v);

        return [$dayDataInit,$daySum];
    }


    /**
     * 渠道数据存储
     * Created by PhpStorm
     * User: Noah
     * Date: 2022/12/19
     * Time: 20:22
     * @param $data
     */
    private function channelInit(&$dbData,$data)
    {
        $channel = $dbData;


        // 获取key
        $channel1Key = isset($data[$this->columnArr[$this->summaryField2]])?$data[$this->columnArr[$this->summaryField2]]:$this->summaryField2;
        $channel2Key = isset($data[$this->columnArr[$this->summaryField3]])?$data[$this->columnArr[$this->summaryField3]]:$this->summaryField3;
        $channel3Key = isset($data[$this->columnArr[$this->summaryField4]])?$data[$this->columnArr[$this->summaryField4]]:$this->summaryField4;

        // 初始化
        $channel[$channel1Key] = isset($channel[$channel1Key])?$channel[$channel1Key]:[];
        $channel[$channel1Key][$channel2Key] = isset($channel[$channel1Key][$channel2Key])?$channel[$channel1Key][$channel2Key]:[];
        $channel[$channel1Key][$channel2Key][$channel3Key] = isset($channel[$channel1Key][$channel2Key][$channel3Key])?$channel[$channel1Key][$channel2Key][$channel3Key]:[];

        $channel1Data = $channel[$channel1Key];
        $channel2Data = $channel[$channel1Key][$channel2Key];
        $channel3Data = $channel[$channel1Key][$channel2Key][$channel3Key];

        // 数据
        if(!isset($channel1Data['data'])) $channel1Data['data'] = [];
        if(!isset($channel2Data['data'])) $channel2Data['data'] = [];
        if(!isset($channel3Data['data'])) $channel3Data['data'] = [];
        if(!isset($channel3Data['sumdata'])) $channel3Data['sumdata'] = [];
        $channel1Data['data'] = $this->reNumBc($channel1Data['data'],$data);
        $channel2Data['data'] = $this->reNumBc($channel2Data['data'],$data);
        $channel3Data['data'][] = $data;
        $channel3Data['sumdata'] = $this->reNumBc($channel3Data['sumdata'],$data);


        // 值操作
        $channel1Data['num'] = isset($channel1Data['num'])?$channel1Data['num']+1:1;
        $channel2Data['num'] = isset($channel2Data['num'])?$channel2Data['num']+1:1;
        $channel3Data['num'] = isset($channel3Data['num'])?$channel3Data['num']+1:1;

//        // 二级级栏目不重复key
        $channel1Data[$this->summaryField2][$channel2Key] = 1;
//        // 二级级栏目不重复key
        $channel2Data[$this->summaryField3][$channel3Key] = 1;
        // 一级栏目不重复key
        $channel[$this->summaryField2][$channel1Key] = 1;
//        $channel[$this->summaryField2][$channel1Key][$channel2Key] = 1;
//        $channel[$this->summaryField3][$channel2Key] = 1;
//        // 栏目二不重复key
//        $channel[$this->summaryField3][$channel2Key] = 1;

        // 赋值
        $channel[$channel1Key] = $channel1Data;
        $channel[$channel1Key][$channel2Key] = $channel2Data;
        $channel[$channel1Key][$channel2Key][$channel3Key] = $channel3Data;

        $dbData = $channel;
    }
}

Dcat Admin Grid

            $grid->column('column1','投保日期')->view('admin.column1');
            $grid->column('column2','活动一级渠道')->view('admin.column2');
            $grid->column('column3','活动二级渠道')->view('admin.column3');
            $grid->column('column4','活动三级渠道')->view('admin.column4');
            $grid->disablePagination();

Dcat Admin View

链接：https://pan.baidu.com/s/1fel4RgzJtdvBIk6CfmSRvA
提取码：nw6l

Excel导出调用

        // 02
$excel = new BwPosition();
Excel::store($excel,'02-table-'.date('Y-m-d',strtotime('-1 day')).'.xlsx');

Excel导出示例

<?php
/**
 *
 * Created by PhpStorm
 * User: Noah
 * Date: 2022/12/20
 * Time: 19:37
 */

namespace App\Admin\Server\Excel;


use App\Admin\Repositories\BwChannel_canalDb;
use App\Api\Service\ConfigService;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * 02
 */
class BwPosition extends ExcelServer
{


    public function __construct()
    {
        parent::__construct();
    }

    /**
     *  业务代码
     * @return array|string[][]
     */
    public function createData()
    {
        $start = date('Y-m-d 00:00:00',strtotime("-1 day"));
        $end = date('Y-m-d 23:59:59',strtotime("-1 day"));
        $login_time = [
            'start' => $start,
            'end'=> $end
        ];
        request()->request->set('login_time',$login_time);
        $BwChannel_canalDb = new \App\Admin\Repositories\BwPosition();
        $data = $BwChannel_canalDb->getdataInit();
        $headTitle = [
            '投保日期',
            '活动一级渠道',
            '活动二级渠道',
            '活动三级渠道',
            '产品名称',
            '订单数',
            '未支付订单数',
            '签单件数',
            '签单保费',
            '件均',
            '退保件数',
            '退保保费',
            '净增保费',
        ];

        $bodyData = [];
//        dd($data);


        // 数据循环
        foreach ($data as $k => $item) {


            list($column1,$column1Me) = $this->getValueArr($item,'column1');
            list($column2,$column2Me) = $this->getValueArr($item,'column2');
            list($column3,$column3Me) = $this->getValueArr($item,'column3');
            $column4 = $this->getStrValue($item,'column4');
            $column1Show = $column1;
            $column2Show = $column2;
            $column3Show = $column3;
            $column4Show = $column4;

            if($k == 0)
            {
                // 一级栏目第一行换行
                $login_time = $column1;
                $this->mergeCells['A2'] = 'A'.($column1Me+1);
                $this->mergeCells['B2'] = 'D2';
//                echo $column1Me.'<br/>';
            }

            /**
             * 判断如果是一级渠道得移动到一级渠道
             * 和后端用同一个数据源
             */
            if(in_array($column1,ConfigService::arr_channel_1))
            {
                // 二级栏目单元格合并
                $start2key = count($bodyData)+1+1;
                $this->mergeCells['B'.$start2key] = 'B'.($start2key+$column1Me-1);
                $this->mergeCells['C'.$start2key] = 'D'.$start2key;
                $column3Show = $column2;
                $column2Show = $column1;
            } else if (strtotime($column1))
            {
                // 一级栏目单元格合并
                $start_key = count($bodyData)+1+1;
                $this->mergeCells['A'.$start_key] = 'A'.($start_key+$column1Me-1);
                $this->mergeCells['B'.$start_key] = 'D'.$start_key;
            }

            /**
             * 三级栏目
             */
            if(in_array($column2,ConfigService::arr_channel_2))
            {
                // 三级栏目单元格合并
                $start3key = count($bodyData)+1+1;
                $this->mergeCells['C'.$start3key] = 'C'.($start3key+$column2Me-1);
//                echo $start3key.'|'.$column2Me.'<br/>';

                $column4Show = $column3;
                $column3Show = $column2;
            }

            $arr[0] = $column1Show;
            $arr[1] = $column2Show;
            $arr[2] = $column3Show;
            $arr[3] = $column4Show;
            $arr[4] = $this->getStrValue($item,'name');
            $arr[5] = $this->getValue($item,'order_num');
            $arr[6] = $this->getValue($item,'unpaid_pay');
            $arr[7] = $this->getValue($item,'sign_num');
            $arr[8] = $this->getValue($item,'premium');
            $arr[9] = $this->getValue($item,'piece_avg');
            $arr[10] = $this->getValue($item,'re_piece');
            $arr[11] = $this->getValue($item,'re_premium');
            $arr[12] = $this->getValue($item,'add_premium');

            $bodyData[] = $arr;
        }
        $this->dataCount = count($bodyData)+1;
//        dd($bodyData);
        // 单元格背景颜色
        $this->bgColor = [
            'A1'    => 'M1',
        ];
        $this->last = 'M';

        // 清除掉 $arr
        unset($arr);

        return [$headTitle, $bodyData];
    }


    public function setColumnDimension(Worksheet $sheet)
    {
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(18);
        $sheet->getColumnDimension('E')->setWidth(18);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getColumnDimension('L')->setWidth(15);
        $sheet->getColumnDimension('M')->setWidth(15);
    }
}