Laravel - 如何从视图中导出具有Eloquent关系的Excel数据？

Question

I'm currently using "maatwebsite/excel": "3.1.10", recently switched from 2.x version, and I'm having a problem with displaying excel data from view that is using Eloquent relationship. Here's all my code below.

UsersEarningHistory.php:

<?php

namespace App\Exports;

use App\EarningHistory;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithMapping;

class UsersEarningHistory implements FromView, ShouldAutoSize, WithEvents, WithMapping
{
    protected $history;

public function __construct($history = null)
{
    $this->history = $history;
}

public function view(): View
{
    return view('admin.commission.excel_table', [
        'history' => $this->history ?: EarningHistory::all()
    ]);
}

public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            $cellRange = 'A1:W1'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14)->setBold($cellRange);
        },
    ];
    }
}

UserController where my method for exporting data is:

public function index(Request $request)
    {
        //
        $active = $this->active;
        $active[1] = 'commission';
        view()->share('active', $active);

        $breadcrumbs = [
            ['link' => '/admin', 'text' => 'Administration'],
            ['text' => 'Commission']
        ];
        view()->share('breadcrumbs', $breadcrumbs);

        $styles[] = '//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.css';
        view()->share('styles', $styles);

        $scripts[] = '//cdn.jsdelivr.net/momentjs/latest/moment.min.js';
        $scripts[] = '//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.js';
        $scripts[] = '/js/admin/commission.js';
        view()->share('scripts', $scripts);
        $history = new EarningHistory();

        if ($request->filled('q')) {
            $history = $history->whereHas('user', function ($query) use ($request) {
                $query->where('username', 'like', '%' . $request->q . '%');
            });
        }
        if ($request->filled('qn')) {
            $history = $history->whereHas('user', function ($query) use ($request) {
                $query->where('first_name', 'like', '%' . $request->qn . '%');
                $query->orWhere('last_name', 'like', '%' . $request->qn . '%');
                if (strpos( $request->qn, ' ') !== false) { // is both
                    $both = explode(" ",$request->qn);
                    if(isset($both[0]))
                        $query->orWhere('first_name', 'like', '%' . $both[0] . '%');
                    if(isset($both[1]))
                        $query->orWhere('last_name', 'like', '%' . $both[1] . '%');
                }
            });
        }
        if($request->filled('has_correct_ratio')) {
            $history = $history->where('has_correct_ratio', $request->filled_correct_ratio);
        }
        if (!$request->filled('null')) {
            $history = $history->where(function ($query) {
                $query->where('personal_balance', '!=', 0)
                    ->orWhere('group_balance', '!=', 0);
            });
        }
        if ($request->filled('date')) {
            $history = $history->whereBetween('created_at', [Carbon::parse('01.' . $request->date)->firstOfMonth(),
                Carbon::parse('01.' . $request->date)->addMonth()->firstOfMonth()]);
        }

        if ($request->filled('export')) {
            $date = $request->filled('date') ? 'Earning history for ' . Carbon::parse('01.' . $request->date)->format('F') :
                'Earning history for all time';

            return Excel::download( new UsersEarningHistory($history), $date.'history.xls');

        }

        $data['history'] = $history->paginate(15);
        $data['request'] = $request;
        return view('admin.commission.index', $data);
    }

Export table blade:

<table class="table table-bordered table-striped">
    <thead>
    <tr>
        <th>Name</th>
        <th>Month</th>
        <th>Amount</th>
        <th>World Bonus</th>
        <th>Total amount</th>
        <th>Personal points</th>
        <th>Group points</th>
        {{--<td></td>--}}
    </tr>
    </thead>
    <tbody>
    @foreach($history as $obj)
        <tr>
            <td>
                {{ $obj->user->first_name . ' ' . $obj->user->last_name }}
            </td>
            <td>{{$obj->created_at->format('F')}}</td>
            <td>€{{  number_format($obj->personal_balance + $obj->group_balance, 2)}}</td>
            <td>€{{  number_format($obj->world_bonus, 2)}}</td>
            <td>€{{  number_format($obj->personal_balance + $obj->group_balance + $obj->world_bonus, 2)}}</td>
            <td>
                {{ intval($obj->personal_points) }}
            </td>
            <td>
                {{ intval($obj->group_points) }}
            </td>
            <td>
                {{ App\User::$_RANK[$obj->rank]['title'] }}
            </td>
            {{--<td align="center">--}}

            {{--<a href="/admin/payouts/{{$obj->id}}" class="btn btn-primary btn-xs">--}}
            {{--<i class="icon-eye"></i>--}}
            {{--</a>--}}

            {{--<!--TODO Prikaži akciju na osnovu trenutnog statusa-->--}}
            {{--@if($obj->status=='pending')--}}
            {{--<a href="javascript:;" class="ajax-action btn btn-sm btn-success" data-action="/ajax/payout-change-status" --}}
            {{--data-obj-id="{{$obj->id}}" data-status="approved">@lang('form.approve')</a>--}}
            {{--<a href="/admin/payouts/reject/{{$obj->id}}" class="btn btn-sm btn-danger" >@lang('form.reject')</a>--}}
            {{--@endif--}}
            {{--</td>--}}
        </tr>
    @endforeach
    </tbody>
</table>

As you can see, I'm trying to get user name from his earning history, but when I try to export data, my excel file is empty, but it's not giving me any errors.

Note: EarningHistory is related with User model:

//EarningHistory model
public function user()
    {
        return $this->belongsTo('App\User', 'user_id', 'id')->withTrashed();
    }

//User model
public function earning_history()
    {
        return $this->hasMany('App\EarningHistory');
    }

Answer 1

Found a solution. My $history variable in UserController was returning Query builder, because I forgot to add ->get(); method :

return Excel::download( new UsersEarningHistory($history->get()), $date.'history.xls');

Now everything works as it should.