[Laravel-Excel中文文档] Laravel从Modal数据中query查询数据并导出

Song379 次浏览0个评论2022年03月19日
Laravel使用maatwebsite/excel从数据表中筛选并且导出为Excel/CSV文件

在前面的示例中,我们在导出类中进行了查询。虽然这对于小型出口来说是一个很好的解决方案,但对于较大的出口来说,这将付出高昂的性能代价。

通过使用FromQuery关注点,我们可以为导出准备查询。在幕后,这个查询是分块执行的。

先上实现代码Demo

首先我们在控制器引入导出类

use App\Exports\UsersipsExport;
...
# 在控制中
$users_id = $request->users_id;
$start_time = $request->start_time;
return (new UsersipsExport(["users_id"=>$users_id,"start_time"=>$start_time]))->download(访问记录.xlsx);

接下来我们在UsersipsExport中接收参数查询,然后Return即可。

<?php

namespace App\Exports;

use App\Models\Usersips;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class UsersipsExport implements FromQuery
{
    use Exportable;

    public function __construct(array $params)
    {
        $this->params = $params;
    }

    public function query()
    {
        # select选择要导出到Excel的字符串
        $query = Usersips::select("id","province","city","ip","created_at")->where(users_id, $this->params["users_id"]);
        # 当start_time没有参数是不查询
        if (!empty($this->params["start_time"])) $query->where(created_at,>=, $this->params["start_time"]);
        return $query;
    }
}


参考文档讲解

InvoicesExport类中,添加FromQuery关注点并返回查询。一定不要 ->get()结果!

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function query()
    {
        return Invoice::query();
    }
}

我们仍然可以用同样的方式下载导出:

return (new InvoicesExport)->download(invoices.xlsx);

自定义查询

将自定义参数传递给查询很容易,只需将它们作为依赖项传递给导出类即可。

作为构造函数参数

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function __construct(int $year)
    {
        $this->year = $year;
    }

    public function query()
    {
        return Invoice::query()->whereYear(created_at, $this->year);
    }
}

现在可以将年份作为依赖项传递给导出类:

return (new InvoicesExport(2018))->download(invoices.xlsx);

作为setter

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;

class InvoicesExport implements FromQuery
{
    use Exportable;

    public function forYear(int $year)
    {
        $this->year = $year;
        
        return $this;
    }

    public function query()
    {
        return Invoice::query()->whereYear(created_at, $this->year);
    }
}

我们可以通过以下forYear方法调整年份:

return (new InvoicesExport)->forYear(2018)->download(invoices.xlsx);

提交评论

请登录后评论

用户评论

    当前暂无评价,快来发表您的观点吧...

更多相关好文