love_php/app/Exports/Sheets/SaasQuarterIncomeDetail.php

<?php

namespace App\Exports\Sheets;

use App\Models\Server\MerchantAccount;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class SaasQuarterIncomeDetail implements FromCollection, WithTitle, WithEvents, WithStrictNullComparison, ShouldAutoSize,WithColumnFormatting
{
    protected $date_arr, $key;
    public function __construct($key, $date_arr)
    {
        $this->key = $key;
        $this->date_arr = $date_arr;
    }

    //格式化数据
    public function columnFormats(): array
    {
        return [
            'D' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
            'F' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数

        ];
    }

    /**
     * sheet数据
     * @return \Illuminate\Support\Collection
     */
    public function collection()
    {
        $rows = [
            ["深圳福恋智能信息科技有限公司
            
SAAS平台收款明细表", '', '', '', '','','',''],
            ["序号", '日期', '收款名称', '金额', '收款手续费2%', 'SAAS平台收款','收入到账银行','备注']
        ];

        foreach ($this->date_arr as $date) {
            $start_date = $date['start_date'];
            $end_date = $date['end_date'];
            $date = date('Y-m', strtotime($start_date));

            $merchants = MerchantAccount::whereNotIn('id', [71,491])->with(['earnings'=> function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            }, 'anchorV2'])->whereHas('anchorV2')->whereHas('earnings', function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            })->get();
            $index = 1;
            $sum = 0;
            foreach ($merchants as $merchant) {
                $amount = $merchant->earnings->sum('value');
                $fee = number_format($amount * $merchant->poundage / 100, 2, '.', '');
                $rows[] = [$index, $date, $merchant->anchorV2->name, $amount, $merchant->poundage  .'%',  $fee, '微信基本户', ''];
                $index++;
                $sum += $fee;
            }
            if ($sum) {
                $rows[] = ['','','', '合计', '', $sum, '', ''];
            }
        }
        return collect($rows);
    }

    public function registerEvents(): array
    {
        $data = [];
        $sum = 0;
        $index = 0;
        foreach ($this->date_arr as $date)
        {
            $start_date = $date['start_date'];
            $end_date = $date['end_date'];
            $count = MerchantAccount::whereNotIn('id', [71,491])->with(['earnings'=> function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            }, 'anchorV2'])->whereHas('anchorV2')->whereHas('earnings', function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            })->count();
            if ($count == 0) {
                $data[] = $sum;
                continue;
            }
            if ($index == 0) {
                $sum += $count+3;
            }else {
                $sum += $count+1;
            }
            $index ++;
            $data[] = $sum;
        }
        return [
            AfterSheet::class  => function(AfterSheet $event) use($data){
                //设置列宽
                $arr  = ['A','B','C','D','E','F',"G","H"];
                foreach ($arr as $value) {
                    $event->sheet->getDelegate()->getColumnDimension($value)->setWidth(20);
                }
                //自动换行
                $event->sheet->getStyle("A1:F1")->getAlignment()->setWrapText(TRUE);
                $event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(80);
                //设置行高，$i为数据行数
                for ($i = 2; $i<=1265; $i++) {
                    $event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(30);
                }
                //设置区域单元格垂直居中
                $event->sheet->getDelegate()->getStyle('A1:K1265')->getAlignment()->setVertical('center');
                //设置区域单元格水平居中
                $event->sheet->getDelegate()->getStyle('A1:K1265')->getAlignment()->setHorizontal('center');
                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('A2:H'.$data[2])->applyFromArray([
                    'borders'=> [
                        'allBorders' => [
                            'borderStyle' =>'thin',
                        ]
                    ]
                ]);
                $event->sheet->getDelegate()->getStyle('A1:H1')->applyFromArray([
                    'font' => [
                        'name' => '宋体',
                        'bold' => false,
                        'italic' => false,
                        'strikethrough' => false,
                        'size' => 14,
                        'color' => [
                            'rgb' => '000000'
                        ],
                    ]

                ]);
                $event->sheet->getDelegate()->getStyle('A2:H1080')->applyFromArray([
                    'font' => [
                        'name' => '宋体',
                        'bold' => false,
                        'italic' => false,
                        'strikethrough' => false,
                        'size' => 11,
                        'color' => [
                            'rgb' => '000000'
                        ],
                    ]

                ]);
                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:H1');
                if (($data[0])) {
                    $event->sheet->getDelegate()->mergeCells('D'.($data[0]).':E'.($data[0]));
                    $event->sheet->getDelegate()->mergeCells('D'.($data[1]).':E'.($data[1]));
                    $event->sheet->getDelegate()->mergeCells('D'.($data[2]).':E'.($data[2]));
                }

            }
        ];
    }

    /**
     * sheet标题
     * @return string
     */
    public function title(): string
    {
        return "第".($this->key + 1)."季度Saas收款明细";
    }
}