php - 带有查询限制和偏移量的 Laravel Excel

问题描述

我想问一下，我可以使用查询在excel级别上使用限制和偏移查询吗？

在这种情况下，我通过添加一些附加选项来尝试 Laravel Excel 多张工作表。

示例： 数据量= 10

我想通过将它们分成几张来导出所有 10 个数据。例如，第一个工作表将采用 5 个数据，第二个工作表将采用接下来的 5 个数据。

我就是这样做的。

ParentSheetExport.php

<?php

namespace App\Exports;

use App\Model\Report;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class ParentSheetExport implements WithMultipleSheets
{
    /**
     * CoinExport constructor.
     * @param $startDate
     * @param $endDate
     */
    public function __construct($startDate, $endDate)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
    }

    public function sheets(): array
    {
        $counter = Report::where('status', true)->count();

        if ($counter > 6 && $counter <= 10) {
            return [
                new ChildExport(0, 5, $this->startDate, $this->endDate),
                new ChildExport(5, 10, $this->startDate, $this->endDate),
            ];
        } elseif ($counter > 10 && $counter <= 15) {
            return [
                new ChildExport(0, 5, $this->startDate, $this->endDate),
                new ChildExport(5, 10, $this->startDate, $this->endDate),
                new ChildExport(10, 15, $this->startDate, $this->endDate)
            ];
        }

        return [
            new ChildExport(0, 5, $this->startDate, $this->endDate)
        ];
    }
}

儿童导出.php

<?php

namespace App\Exports;

use App\Model\Report;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class ChildExport implements FromQuery, WithMapping, ShouldAutoSize, WithHeadings
{
    use Exportable;

    /**
     * CoinExport constructor.
     * @param $startDate
     * @param $endDate
     */
    public function __construct($start, $take, $startDate, $endDate)
    {
        $this->start = $start;
        $this->take = $take;
        $this->startDate = $startDate;
        $this->endDate = $endDate;
    }

    public function query()
    {
        return Report::query()->where('status', true)
            ->whereBetween('created_at', [$this->startDate . ' 00:00:00', $this->endDate . ' 23:59:59'])
            ->orderBy('created_at', 'asc')
            ->skip($this->start)->take($this->take);
    }

    public function headings(): array
    {
        return [
            'Transaction Date',
            'Transaction Amount',
            'Customer Name',
        ];
    }

    public function map($report): array
    {
        return [
            $report->created_at,
            $report->amount,
            $report->user->name
        ];
    }
}

导出控制器.php

<?php

namespace App\Http\Controllers\API;

use App\Exports\ParentSheetExport;
use Carbon\Carbon;
use Excel;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use App\Http\Controllers\API\ApiController;

class ExportController extends ApiController
{
    protected $response = [
        'meta' => [
            'code' => 200,
            'status' => 'success',
            'message' => null,
        ],
        'data' => null,
    ];
    /**
     * @var Request
     */
    protected $request;

    /**
     * @var array
     */
    private $posted;

    /**
     * ExportController constructor.
     * @param Request $request
     */
    public function __construct(Request $request)
    {
        $this->request = $request;
        $this->posted = $this->request->except('_token', '_method');
    }

    /**
     * @param null $startDate
     * @param null $endDate
     * @return Response|BinaryFileResponse
     */
    public function getReport($startDate = null, $endDate = null)
    {
        $filename = 'Report' . Carbon::parse($startDate)->format('Y-m-d_H:i') . '.xlsx';
        $path = config('app.url') . '/storage/export/report' . $filename;

        Excel::store(
            new ParentSheetExport($startDate, $endDate), 
            'Report' . Carbon::parse($startDate)->format('Y-m-d_H:i') . '.xlsx', 
            'export-report');

        $this->response['meta']['code'] = 200;
        $this->response['meta']['status'] = 'success';
        $this->response['meta']['message'] = null;
        $this->response['meta']['data'] = $path;
        return response()->json($this->response, $this->response['meta']['code']);
    }
}

查询成功执行。但是，每张纸上的数据都是相同的，并且不使用我应该发送的限制和偏移量。

是否可以在 Laravel 导出查询中使用Limit和Offset或者有其他方法吗？

谢谢。

标签： phpexcellaravel

public function collection() { return Model::query() ->limit($limit) ->offset($offset) ->orderBy('name_of_column','DESC') ->get(); // Or using Query: return Model::query() ->limit($limit) ->offset($offset) ->orderBy('name_of_column','DESC') ->cursor(); }

php - 带有查询限制和偏移量的 Laravel Excel

问题描述

解决方案

推荐阅读