dwbs-user-api/app/Exports/Dwbs/UserOrderGoodExport.php

<?php

namespace App\Exports\Dwbs;

use App\Repositories\Enums\Dwbs\DayTypeEnum;
use App\Repositories\Enums\ModelStatusEnum;
use App\Repositories\Models\Dwbs\Good;
use App\Repositories\Models\Dwbs\OrderGood;
use App\Repositories\Models\Dwbs\Ranking;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;


class UserOrderGoodExport implements FromQuery, WithMapping, WithHeadings, ShouldAutoSize, WithColumnWidths, WithStyles
{
    use Exportable;

    const Name = "用户订单商品数量清单";
    const BaseFields = [
        [
            'name' => '用户昵称',
            'width' => 20,
        ],
        [
            'name' => '用户手机号',
            'width' => 15,
        ],
        [
            'name' => '累计学分',
            'width' => 15,
        ],
        [
            'name' => '商品总计',
            'width' => 15,
        ],
    ];
    protected $rowNums = 0;
    protected $data = [];
    protected $gids = [];
    protected $lastC = 'A';
    protected $fields = [];
    protected $goods = [];

    public function __construct($data)
    {
        $this->data = $data;

        $fields = self::BaseFields;

        $goods = Good::query()->where('status', ModelStatusEnum::OK)->select(['id', 'name'])->orderBy('id')->get();
        $this->goods = $goods;
        $gids = [];
        foreach ($goods as $good) {
            $fields[] = [
                'name' => $good['name'],
                'width' => 15,
            ];
            $gids[] = $good['id'];
        }

        $this->gids = $gids;

        $this->fields = $fields;
    }

    public function query()
    {
        $da = $this->data;
        return Ranking::query()->when($da['start_day'], function ($query) use ($da) {
            return $query->where('day', '>=', $da['start_day']);
        })->when($da['end_day'], function ($query) use ($da) {
            return $query->where('day', '<=', $da['end_day']);
        })->where('day_type', DayTypeEnum::day)->with('user')->select(['user_id', DB::raw("sum(day_xuefen) as dxf")])->groupBy('user_id')->orderByDesc('dxf')->limit($da['limit']);
    }

    public function map($row): array
    {
        $this->rowNums++;

        $da = $this->data;
        $goods = OrderGood::query()->when($da['start_day'], function ($query) use ($da) {
            return $query->where('order_time', '>=', $da['start_day']);
        })->when($da['end_day'], function ($query) use ($da) {
            return $query->where('order_time', '<=', $da['end_day']);
        })->where('user_id', $row['user_id'])->whereIn('good_id', $this->gids)->select(['user_id', 'good_id', DB::raw('count(nums) as good_nums')])->groupBy('good_id')->pluck('good_nums', 'good_id')->toArray();

        $data = [];
        $data[] = isset($row['user']['nickname']) ? $row['user']['nickname'] : '';
        $data[] = isset($row['user']['mobile_encryption']) ? Crypt::decryptString($row['user']['mobile_encryption']) : '';
        $data[] = $row['dxf'];
        $data[] = array_sum($goods);

        foreach ($this->gids as $gid) {
            $data[] = isset($goods[$gid]) ? $goods[$gid] : 0;
        }

        return $data;
    }


    public function headings(): array
    {
        $room_name = self::Name;
        $data[] = [$room_name];

        $data[] = array_column($this->fields, 'name');
        return $data;
    }

    public function headingRow(): int
    {
        return 2;
    }

    public function columnWidths(): array
    {
        $i = 65;
        $data = [];
        foreach ($this->fields as $k => $field) {
            $s = intval($k / 26);

            if (!$s) $key = chr($i + $k);
            if ($s > 0) {
                $y = intval($k % 26);
                $key = chr($s + $i - 1);
                $key .= chr($y + $i);
            }
            $data["{$key}"] = $field['width'];
            $this->lastC = $key;
        }
        return $data;
    }

    public function styles(Worksheet $sheet)
    {
        $last = $this->lastC;
        $cellRange = "A1:{$last}1";
        $sheet->getStyle($cellRange)->getFont()->setSize(12);

        $sheet->getDefaultRowDimension()->setRowHeight(40);//设置行高

        // 文字居中
        $lastrow = $sheet->getHighestRow();
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//垂直居中
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle("A1:{$last}" . $lastrow)->getAlignment()->setWrapText(true);

        $sheet->mergeCells("A1:{$last}1"); //合并
        $sheet->getStyle("A1:{$last}1")->getFont()->setSize(16);
    }
}