Laravel Excel with query Limit and Offset

Question

I want to ask, can I use limit and offset queries on excel level using queries?

In this case I tried Laravel Excel multiple sheet by adding some additional options.

Example: Amount of data = 10

I want to export all 10 data by dividing them into several sheets. For example, the first sheet will take 5 data and the second sheet will take the next 5 data.

This is how I do it.

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)
        ];
    }
}

ChilldExport.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
        ];
    }
}

ExportController.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']);
    }
}

Query executed successfully. However, on each sheet the data is the same and does not use the limits and offsets that I should send.

Is it possible to use Limit and Offset in Laravel Export queries or is there any other way?

Thanks.

Answer 1

@Mochamad Akbar it's bit late but I hope it will help some one else.

while using query() you cannot set a custom limit and offset because query() handles the limit and offset itself, because of chunking functionality.

If you want to use your custom limit and offset, you can do so while using FromCollection

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();
}