dcat Laravel-Excel(Maatwebsite\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 協議》，轉載必須註明作者和本文連結