When exporting dcat data, you need to use row and column merging. There seems to be no introduction to row merging in the easy excel document provided by dcat. I plan to use larevel excel instead. Because I installed version 3.1 and the dcat document is 2.x, it is incompatible and needs to be modified.
When laravel admin was used to export, its own package was laravel excel. After comparing the two, it was found that the export class of dcat's export data example code directly inherited Dcat\Admin\Grid\Exporters\AbstractExporter, while laravel admin's export class first inherited Encore\Admin\Grid\Exporters\ExcelExporter, and then the export class inherited ExcelExporter.
Therefore, first create an App\Admin\Extensions\ExcelExporter.php, directly copy the code over there, simply change it, and then let the export class to be used by the controller inherit it. At this time, clicking export will report an error, because dcat and laravel admin are very similar, but some internal methods have different logic, which can be used for reference, but can not be copied directly.
An easy way to change is to directly change FromQuery implemented by Excel exporter to FromCollection, and then define the collection method, where you can get the dcat data set through the $this - > builddata() method. The disadvantage of this method is that it cannot be exported in blocks. If the amount of data is large and the blocking function needs to be used, implement FromQuery.
Why FromQuery? In laravel admin, I saw that the block export method is chunk(), but dcat's AbstractExporter does not have this method (in easy laravel), while Laravel-Excel2.x has the block function. Guess 3 is also available, but it is not written in the document. So I searched for chunk() in the Maat website package directory of the vendor to see if there were any related methods. There were three matches in total.
The first is the exportCollection method of QueuedWriter, which is queue related according to the name. Because I don't use the queue, I skipped it. The second is ChunkReader, which is called ReadChunk(). Combined with the name and the code inside, it should be import related. The third is the fromQuery method of maatnesite \ excel \ sheet.
/**
* @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(), you can see that $sheetExport is the instance of the import class of the current excel table, and then query() calls the export class to implement query() defined by the FormQuery interface , and then block the query and add rows according to the ChunkSize. So just go to the query method in Excel exporter to get the query builder of dcat export grid and return it, or you can rewrite query () in the export class of subclass.
However, some methods are protected and cannot be called in the exported class. You need to create a new class to inherit, and then change it to public. How to change it will be supplemented later.
In addition, resetting forPage in the chunk method will cause the forPage in the export class to become invalid, that is, exporting the data of this page will become invalid. You need to create a new Builder, inherit Illuminate\Database\Eloquent\Builder, and then override the chunk method to change $page=1 to
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;
}When neither limit nor offset is empty, $page is not set to 1.
The following are the relevant documents
<?php
/**
* A simple example of an exported class
*/
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 = 'Leave pending.xlsx';
protected $statusMap;
protected $columns
= [
'member_id' => 'full name',
'member.unit_id' => 'Company',
'out_id' => 'Out of office type',
'leave_at' => 'Departure time',
'return_at' => 'Return time',
//'status' = > 'approval 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 When an object is passed in, all attributes of the target are copied (the protected attribute should inherit the target)
*/
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;
/**
* Query Grid table data
*
* @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);
}
// Exclude the get method and only get the 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;
}
}