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 协议》，转载必须注明作者和本文链接