Question 304691 in Laravel Question posted in
The official Laravel Documentation can be found here.

Prevent blank sheets with Laravel Excel

I’m implementing Excel download on my app using Laravel Excel. It works well except that it creates an empty sheet if the query finds no data. Is there any way to prevent this?

This is the code for the sheets:

namespace AppExportsSheets;

use AppModelsWebArchiveTest;
use IlluminateDatabaseEloquentBuilder;
use MaatwebsiteExcelConcernsFromQuery;
use MaatwebsiteExcelConcernsWithTitle;

class WebArchiveSheet implements FromQuery, WithTitle
{
    protected string $category;
    protected string $server;

    public function __construct(string $server, string $category)
    {
        $this->category = $category;
        $this->server = $server;
    }

    public function query(): Builder
    {
        return WebArchiveTest
            ::query()
            ->select(['web_root', 'page_title'])
            ->where('server', $this->server)
            ->where('category', $this->category);
    }

    public function title(): string
    {
        return ucfirst($this->category);
    }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    With thanks to Adam Faturahman, I refactored both the Export and Sheet code as follows:

    Export:

    namespace AppExports;

use AppExportsSheetsWebArchiveSheet;
use AppModelsWebArchiveTest;
use MaatwebsiteExcelConcernsExportable;
use MaatwebsiteExcelConcernsWithMultipleSheets;
use IlluminateDatabaseEloquentBuilder;

class WebArchiveExport implements WithMultipleSheets
{
    use Exportable;

    protected string $server;

    public function __construct(string $server)
    {
        $this->server = $server;
    }

    public function sheets(): array
    {
        $sheets = [];

        $this->getCategories()
            ->each(function ($result) use (&$sheets) {
                $query = $this->getSheetData($result->category);
                if (! $query) {
                    return;
                }
                $sheet = new WebArchiveSheet($query, $result->category);
                $sheets[] = $sheet;
            });

        return $sheets;
    }

    protected function getCategories(): Builder
    {
        return WebArchiveTest::query()
            ->select(['category'])
            ->where('server', $this->server)
            ->orderBy('category')
            ->distinct();
    }

    protected function getSheetData(string $category): ?Builder
    {
        $query = WebArchiveTest
            ::query()
            ->select(['web_root', 'page_title'])
            ->where('server', $this->server)
            ->where('category', $category);

        return $query->exists() ? $query :null;
    }
}

    Sheet:

    namespace AppExportsSheets;

use IlluminateDatabaseEloquentBuilder;
use MaatwebsiteExcelConcernsFromQuery;
use MaatwebsiteExcelConcernsWithTitle;

class WebArchiveSheet implements FromQuery, WithTitle
{
    protected Builder $query;
    protected string $category;

    public function __construct(Builder $query, string $category)
    {
        $this->query = $query;
        $this->category = $category;
    }
    
    public function query(): Builder
    {
        return $this->query;
    }

    public function title(): string
    {
        return ucfirst($this->category);
    }
}

  2. 0

    It seems like you need to check whether the query result is empty or not before generating the Excel worksheet. If the query result contains data (is not empty), then the Excel worksheet will contain that data. However, if the query result is empty, then the code ensures that the Excel worksheet is not created, so you won’t receive an empty worksheet in the export result.

    namespace AppExportsSheets;

use AppModelsWebArchiveTest;
use IlluminateDatabaseEloquentBuilder;
use MaatwebsiteExcelConcernsFromQuery;
use MaatwebsiteExcelConcernsWithTitle;
use MaatwebsiteExcelConcernsExportable;

class WebArchiveSheet implements FromQuery, WithTitle
{
    use Exportable;

    protected string $category;
    protected string $server;

    public function __construct(string $server, string $category)
    {
        $this->category = $category;
        $this->server = $server;
    }

    public function query(): Builder
    {
        $query = WebArchiveTest::query()
            ->select(['web_root', 'page_title'])
            ->where('server', $this->server)
            ->where('category', $this->category);

        // Check if the query has any results, if not, return an empty result set
        if (!$query->exists()) {
            $query->whereRaw('1 = 0'); // This will ensure the query returns an empty result set
        }

        return $query;
    }

    public function title(): string
    {
        return ucfirst($this->category);
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search