dcat Laravel-Excel(Maatwebsite\Excel)的分塊匯出

DogLoML 發表於 2021-11-18
Laravel Excel

在做dcat的資料匯出,需要使用行和列的合併,dcat自帶的easy-excel的文件裡面好像沒有行合併的介紹,我打算改成用Laravel-Excel。因為我安裝的是3.1版本,而dcat文件是2.x的,不相容了,需要修改一下。

由於之前用laravel-admin搞匯出的時候,自帶的包就是 Laravel-Excel,對比兩者後發現dcat的匯出資料示例程式碼的匯出類是直接繼承了Dcat\Admin\Grid\Exporters\AbstractExporter,而laravel-admin的匯出類先繼承Encore\Admin\Grid\Exporters\ExcelExporter,然後再由匯出類繼承ExcelExporter。

所以這邊先建立一個App\Admin\Extensions\ExcelExporter.php,直接把那邊的程式碼複製過來,簡單改一改,然後讓控制器要用的匯出類繼承他,這時候點匯出卻會報錯,因為dcat和laravel-admin雖然長得很像,但內部有些方法還有邏輯不一樣,可以借鑑但不能直接照搬。

比較省事的一種改法是,直接把ExcelExporter實現的FromQuery改成FromCollection,然後定義collection方法,在裡面通過$this->buildData()方法就可以拿到dcat的資料集合,這樣個辦法的缺點是無法分塊匯出。如果資料量較大,需要使用分塊功能,則要實現FromQuery。

為什麼是FromQuery?我在laravel-admin裡面看到分塊匯出的方法是chunk(),但是dcat的AbstractExporter沒有這個方法(easy-laravel裡面有),而 Laravel-Excel2.x是有分塊功能的,猜測3也是有的,只是文件裡面沒寫。所以我就在vendor的maatwebsite包目錄搜尋chunk(,看有沒有相關的方法,一共有三處匹配。

第一處是QueuedWriter的exportCollection方法裡面,看名字就知道是佇列相關的,因為我這個沒用佇列,所以略過。第二處是ChunkReader,裡面調了ReadChunk(),結合名字和裡面的程式碼,應該是匯入相關的。第三處則是Maatwebsite\Excel\Sheet的fromQuery方法。

 /**
     * @param FromQuery $sheetExport
     * @param Worksheet $worksheet
     */
    public function fromQuery(FromQuery $sheetExport, Worksheet $worksheet)
    {
        $sheetExport->query()->chunk($this->getChunkSize($sheetExport), function ($chunk) use ($sheetExport) {
            $this->appendRows($chunk, $sheetExport);
        });
    }

dd()一下,可以看到這裡$sheetExport就是當前excel表的匯入類例項,然後query()呼叫了匯出類實現FormQuery介面定義的query(),然後根據ChunkSize分塊查詢並且新增行。所以只要到ExcelExporter裡面的query方法獲取到dcat匯出grid的查詢builder,並且返回就好了,或者也可以到子類的匯出類裡面重寫query()。

不過裡面有些方法是protected,在匯出類裡面無法呼叫,需要先新建個類繼承下,然後改成public,具體怎麼改後面再補充完整。

另外chunk方法裡面重設了forPage,會導致匯出類裡面的forPage失效,也就是說匯出本頁資料會失效,需要新建個Builder繼承Illuminate\Database\Eloquent\Builder,然後重寫chunk方法,把$page=1改成

if(!is_null($this->query->limit)&&!is_null($this->query->offset)){
  $page=$this->query->offset/$this->query->limit+1;
  $count=$this->query->limit;
}else{
  $page = 1;
}

當limit和offset都不為空時,$page不設為1。

以下是相關的檔案

<?php
/**
* 匯出類的簡單示例
*/
namespace App\Admin\Extensions\Exporters;

use App\Admin\Extensions\ExcelExporter;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithMapping;


class LeaveAuditExporter extends ExcelExporter implements WithMapping,WithColumnWidths
{

    protected $fileName = '待批假.xlsx';

    protected $statusMap;

    protected $columns
        = [
            'member_id' => '姓名',
            'member.unit_id' => '單位',
            'out_id' => '外出型別',
            'leave_at' => '離隊時間',
            'return_at' => '歸隊時間',
            //'status' =>  '稽核狀態',
        ];


    public function map($data): array
    {
        return [
            data_get($data,'member.name'),
            data_get($data,'member.unit.name'),
            data_get($data,'out.name'),
            $data->leave_at,
            $data->return_at,
            //$this->statusMap[$data->status]
        ];
    }
    public function columnWidths(): array
    {
        return [
            'A'=>12,'B'=>12,'C'=>12,'D'=>20,'E'=>20,'F'=>12,
        ];
    }
}
<?php

namespace App\Admin\Extensions;

use Dcat\Admin\Grid;
use Dcat\Admin\Grid\Exporters\AbstractExporter;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;

abstract class ExcelExporter extends AbstractExporter implements FromQuery, WithHeadings
{
    use Exportable;

    /**
     * @var string
     */
    protected $fileName;

    /**
     * @var array
     */
    protected $headings = [];

    /**
     * @var array
     */
    protected $columns = [];

    /**
     * @return array
     */
    public function headings(): array
    {
        if (!empty($this->columns)) {
            return array_values($this->columns);
        }

        return $this->headings;
    }

    /**
     * @return int
     */
    protected function getChunkSize()
    {
        return config('excel.exports.chunk_size', 100);
    }

    public function getQuery()
    {
        $model = $this->getGridModel();
        $page = null;
        $perPage = null;

        // current page
        if ($this->scope === Grid\Exporter::SCOPE_CURRENT_PAGE) {
            $page = $model->getCurrentPage();
            $perPage = $model->getPerPage();
        }

        $model->usePaginate(false);

        if ($page && $this->scope !== Grid\Exporter::SCOPE_SELECTED_ROWS) {
            $perPage = $perPage ?: $this->getChunkSize();
            $model->forPage($page, $perPage);
        }

        $grid = new \App\Admin\Rewrites\Grid($this->grid);
        $query = $grid->processFilter2($grid);
        $model->reset();

        return new \App\Admin\Rewrites\Builder($query);

    }

    public function query()
    {
        return $this->getQuery();
    }

    public function export()
    {
        $this->download($this->fileName)->prepare(request())->send();
        exit;
    }

}
<?php
namespace App\Admin\Rewrites;

use App\Traits\CopyObjectAttributes;
use Dcat\Admin\Grid as BaseGrid;
use Illuminate\Database\Eloquent\Builder;

class Grid extends BaseGrid
{
  use CopyObjectAttributes;

  /**
 * Process the grid filter. * @param Grid $grid
 * @return Builder
  */
  public function processFilter2(\App\Admin\Rewrites\Grid $grid)
 {
  $this->callBuilder();
  $this->handleExportRequest();

  $this->applyQuickSearch();
  $this->applyColumnFilter();
  $this->applySelectorQuery();
  $filter=new Filter($grid->filter());

  return $filter->execute();
  }
}
<?php
/**
 * new物件時傳一個物件進來,複製目標的所有屬性(protected屬性要繼承目標)
 */

namespace App\Traits;


Trait CopyObjectAttributes
{
    public function __construct(object $Obj)
    {
        $this->copyParentAttributes($Obj);
    }
    function copyParentAttributes($Obj)
    {
        $objValues = get_object_vars($Obj); // return array of object values
        foreach($objValues AS $key=>$value)
        {
            $this->$key = $value;
        }
    }
}
<?php

namespace App\Admin\Rewrites;
use App\Traits\CopyObjectAttributes;
use Dcat\Admin\Grid\Filter as BaseFilter;
use Dcat\Admin\Grid\Model;
use Illuminate\Database\Eloquent\Builder;

class Filter extends BaseFilter
{
    use CopyObjectAttributes;

    public function getScopeConditions()
    {
        if ($scope = $this->getCurrentScope()) {
            return $scope->condition();
        }

        return [];
    }
    /**
     * Execute the filter with conditions.
     * @param $filter
     * @return Builder
     */
    public function execute()
    {
        $conditions = array_merge(
            $this->getConditions(),
            $this->getScopeConditions()
        );

        $this->model->addConditions($conditions);

        $model=new \App\Admin\Rewrites\Model($this->model);
        $query = $model->fetch();

        return $query;
    }

}
<?php
namespace App\Admin\Rewrites;

use App\Traits\CopyObjectAttributes;
use Dcat\Admin\Exception\AdminException;
use Illuminate\Database\Eloquent\Builder;

class Model extends \Dcat\Admin\Grid\Model
{
    use CopyObjectAttributes;

    /**
     * @return Builder
     * @throws \Exception
     */
    public function fetch()
    {

        $repository = new EloquentRepository($this->repository);
        $results = $repository->get($this);
        if (!is_null($results)) {
            return $results;
        }

        throw new AdminException('Grid query error');
    }
}
<?php

namespace App\Admin\Rewrites;
use App\Traits\CopyObjectAttributes;
use Dcat\Admin\Repositories\EloquentRepository as BaseClass;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Collection;

class EloquentRepository extends BaseClass
{
    use CopyObjectAttributes;
    /**
     * 查詢Grid表格資料.
     *
     * @param  Grid\Model  $model
     * @return Builder
     */
    public function get(\Dcat\Admin\Grid\Model $model)
    {
        /** @var Model $model */
        $this->setSort($model);
        $this->setPaginate($model);

        $query = $this->newQuery();

        if ($this->relations) {
            $query->with($this->relations);
        }
        // 排除get方法,只獲取builder
        $model->setQueries($model->getQueries()->filter(function($v){
            return $v['method']!=='get';
        }));
        //dd($query);
        return $model->apply($query, true, $this->getGridColumns());
    }
}
<?php

namespace App\Admin\Rewrites;

use App\Traits\CopyObjectAttributes;
use Illuminate\Database\Eloquent\Builder as Base;
class Builder extends Base
{
    use CopyObjectAttributes;

    /**
     * Chunk the results of the query.
     *
     * @param  int  $count
     * @param  callable  $callback
     * @return bool
     */
    public function chunk($count, callable $callback)
    {
        $this->enforceOrderBy();
        if (!is_null($this->query->limit) && !is_null($this->query->offset)) {
            $page = $this->query->offset / $this->query->limit + 1;
            $count = $this->query->limit;
        } else {
            $page = 1;
        }
        do {

            $results = $this->forPage($page, $count)->get();
            $countResults = $results->count();

            if ($countResults == 0) {
                break;
            }

            if ($callback($results, $page) === false) {
                return false;
            }

            unset($results);

            $page++;
        } while ($countResults == $count);

        return true;
    }


}
本作品採用《CC 協議》,轉載必須註明作者和本文連結

相關文章