love_php/app/Exports/Sheets/LoveIncome.php

<?php

namespace App\Exports\Sheets;

use App\Models\Consultation;
use App\Models\Course\Course;
use App\Models\Live\Anchor;
use App\Models\MerchantShop;
use App\Models\Order;
use App\Models\Server\CommunityActivity;
use App\Models\Server\MEarning;
use App\Models\Server\MerchantAccount;
use App\Models\Server\MerchantEvaluate;
use App\Models\Server\MerchantTransferLog;
use App\Models\Server\MerchantUser;
use App\Models\User;
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 LoveIncome implements FromCollection, WithTitle, WithEvents, WithStrictNullComparison, ShouldAutoSize,WithColumnFormatting
{
    protected $start_date, $end_date;
    public function __construct($start_date, $end_date)
    {
        $this->start_date = $start_date;
        $this->end_date = $end_date;
    }

    public function loveContent()
    {
        $rows = [
            ["深圳福恋智能信息科技有限公司
            
            福恋产品收入明细表", '', '', '', '', '', '', '', '', '', ''],
            ['ID', "订单号", "商户", "类型", '商品名称', '金额', '订单退款金额', '手续费率', '手续费', '订单时间', '月份',"支付方式"]
        ];
        $orders = Order::with('payOrder')->whereBetween('created_at', [$this->start_date, $this->end_date])->where('pay_status', '<>', "UNPAID")->get();
        foreach ($orders as $order)
        {
            $pay_type = "微信支付";
            if ($order->pay_type == 'ios') {
                $pay_type = "苹果支付";
            }
            $merchant_name = "商家(福恋平台)";
            $refund_fee = $order->refundOrders()->sum("refund_fee");
            $service_fee = number_format(($order->price - $refund_fee) * 0.006 * -1, 2, '.', '');
            $earnings = $order->earning()->where('value', '>', 0)->get();
            $index = 1;
            if (count($earnings->toArray()) && $earnings->where('user_id', '<>', 0)->count()) {
                foreach ($earnings as $earning) {
                    $charge = 0.006;
                    $type = "营收";
                    $value = $earning->value;
                    if ($earning->user_id) {
                        $name = User::where('id', $earning->user_id)->value('nickname');
                        $merchant_name = "用户($name)";
                        $charge = 0.02;
                        $service_fee = number_format($earning->value * $charge,2, '.','');
                        $type = "服务费";
                        $value = $earning->value * -1;
                    }
                    $rows[] = [$order->id."-$index", $order->trade_no.' ', $merchant_name, $type, $order->goods, $value, 0, $charge, $service_fee, $order->created_at, date('m', strtotime($this->start_date)), $pay_type];
                    $index++;
                }
            }else {
                Log::info("订单号 ". $order->trade_no);
                $price = $order->payOrder->cash;
                $rows[] = [$order->id.'-1', $order->trade_no.' ', $merchant_name, "营收", $order->goods, $price, $refund_fee, '0.006', $service_fee, $order->created_at, date('m', strtotime($this->start_date)), $pay_type];
            }


        }
        return $rows;
    }


    public function collection()
    {

        $rows = $this->loveContent();

        return collect($rows);
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $arr  = ['B','C','D','E','F',"G","H",'I','J','K', 'L'];
                foreach ($arr as $value) {
                    $event->sheet->getDelegate()->getColumnDimension($value)->setWidth(20);
                }
                $event->sheet->getDelegate()->getColumnDimension("C")->setWidth(50);
                $event->sheet->getDelegate()->getColumnDimension("E")->setWidth(50);

                //自动换行
                $event->sheet->getStyle("A1:L1")->getAlignment()->setWrapText(TRUE);
                $event->sheet->getDelegate()->getRowDimension(1)->setRowHeight(50);
//                //设置行高，$i为数据行数
                for ($i = 2; $i<=1265; $i++) {
                    $event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(30);
                }
//                //设置区域单元格垂直居中
                $event->sheet->getDelegate()->getStyle('A1:L1265')->getAlignment()->setVertical('center');
//                //设置区域单元格水平居中
                $event->sheet->getDelegate()->getStyle('A1:L1265')->getAlignment()->setHorizontal('center');
                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:L1');

            }
        ];
    }

    public function columnFormats(): array
    {
        return [
//            'B' => NumberFormat::FORMAT_NUMBER, //订单号
            'F' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
            'G' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
            'H' => NumberFormat::FORMAT_PERCENTAGE_00, //金额保留两位小数
            'I' => NumberFormat::FORMAT_NUMBER_00, //金额保留两位小数
        ];
    }
    /**
     * @return string
     */
    public function title(): string
    {
        return '福恋产品收入';
    }
}