love_php/app/Exports/Sheets/SaasLoveQuarterIncome.php

<?php

namespace App\Exports\Sheets;

use App\Models\Server\MerchantAccount;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
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 SaasLoveQuarterIncome 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 [
            'E' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
        ];
    }

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

        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::with(['earnings'=> function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            }, 'anchorV2'])->whereIn('id', [491, 71])->get();
            $sum1 = 0;
            foreach ($merchants as $merchant) {
                $amount = $merchant->earnings->sum('value');
                $sum1 += $amount;
            }
            $rows[] = ['',1, $date, '悦睦、福恋', $sum1, 0, '', '微信', ''];

            //SAAS平台收款收入
            $merchants = MerchantAccount::with(['earnings'=> function($sql) use($start_date, $end_date){
                $sql->where('m_user_id', 0)->whereBetween('created_at', [$start_date, $end_date]);
            }, 'anchorV2'])->whereNotIn('id', [491, 71])->get();
            $sum2 = 0;
            foreach ($merchants as $merchant) {
                $amount = $merchant->earnings->sum('value');
                $sum2 += $amount;
            }
            $rows[] = ['',2, $date, 'SAAS平台收款', $sum2, '收取2%手续费收入', '', '微信', ''];

            //小程序收入
            $sum3 = DB::table("orders")->where('pay_status','PAID')->whereBetween('created_at', [$start_date, $end_date])->sum('price');
            $rows[] = ['',3, $date, '小程序会员收款', $sum3, 0, '', '微信', ''];

            $rows[] = ['','', '','合计', $sum1+$sum2+$sum3, '', '', ''];
        }
        return collect($rows);
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $arr  = ['B','C','D','E','F',"G","H"];
                foreach ($arr as $value) {
                    $event->sheet->getDelegate()->getColumnDimension($value)->setWidth(20);
                }
                //自动换行
                $event->sheet->getStyle("B1: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');
//                $event->sheet->getDelegate()->getStyle('A11:F18')->getAlignment()->setHorizontal('center');
//
//                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('B2:H14')->applyFromArray([
                    'borders'=> [
                        'allBorders' => [
                            'borderStyle' =>'thin',
                        ]
                    ]
                ]);
                //抬头
                $event->sheet->getDelegate()->getStyle('B1:H1')->applyFromArray([
                    'font' => [
                        'name' => '宋体',
                        'bold' => false,
                        'italic' => false,
                        'strikethrough' => false,
                        'size' => 14,
                        'color' => [
                            'rgb' => '000000'
                        ],
                    ]

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

                ]);
//                $event->sheet->getDelegate()->getStyle('A11:F19')->applyFromArray([
//                    'borders'=> [
//                        'allBorders' => [
//                            'borderStyle' =>'thin',
//                        ]
//                    ]
//                ]);
//                $event->sheet->getDelegate()->getStyle('1')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => false,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 14,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//                    ],
                    // 'fill' => [
                    //     'fillType' => 'linear', //线性填充，类似渐变
                    //     'rotation' => 45, //渐变角度
                    //     'startColor' => [
                    //         'rgb' => '000000' //初始颜色
                    //     ],
                    //     //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                    //     'endColor' => [
                    //         'argb' => 'FFFFFF'
                    //     ]
                    // ],

//                ]);
//                $event->sheet->getDelegate()->getStyle('2:9')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => false,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 12,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//
//                    ],
//                ]);
//                $event->sheet->getDelegate()->getStyle('11')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => true,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 20,
//                        'color' => [
//                            'rgb' => '000000'
//                        ]
//                    ],
//
//                ]);
//                $event->sheet->getDelegate()->getStyle('12:19')->applyFromArray([
//                    'font' => [
//                        'name' => 'Arial',
//                        'bold' => false,
//                        'italic' => false,
//                        'strikethrough' => false,
//                        'size' => 12,
//                        'color' => [
//                            'rgb' => '000000'
//                        ],
//
//                    ],
//                ]);
                //合并单元格
                $event->sheet->getDelegate()->mergeCells('B1:H1');
                $event->sheet->getDelegate()->mergeCells('B6:C6');
                $event->sheet->getDelegate()->mergeCells('B10:C10');
                $event->sheet->getDelegate()->mergeCells('B14:C14');
            }
        ];
    }

    /**
     * sheet标题
     * @return string
     */
    public function title(): string
    {
        return "第".($this->key + 1)."季度福恋各项收款汇总表";
    }
}