Laravel Excel 3.1在迭代之间添加额外的行 [英] Laravel excel 3.1 add extra rows between iterations
本文介绍了Laravel Excel 3.1在迭代之间添加额外的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在两个表之间有一对多的关系,一个是"alquileres"(租金),另一个是"cobros"(付款). 我需要这样显示每个租金的总额: 我需要什么
I have one to many relationship between two tables one is "alquileres" (rents) and the other is "cobros" (payments). I need to show the totals of each rental like this: what i need
但是请获取以下内容: 结果
这是我的导出代码:
class ComprobantesExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithEvents
{
use Exportable, RegistersEventListeners;
protected $alquileres;
public function __construct($alquileres = null)
{
$this->alquileres = $alquileres;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return $this->alquileres;
}
public function headings(): array
{
return [
'#',
'Fecha',
'Inquilino',
'Propiedad',
'Subpropiedad',
'Atraso',
'Monto'
];
}
public function map($alquiler): array
{
return $alquiler->cobros->map(function ($cobro, $nro) use ($alquiler){
return [
$cobro->comprobante->id,
$cobro->created_at->format("d/m/Y"),
$alquiler->inquilino->full_name,
$alquiler->propiedad->titulo,
$alquiler->subpropiedad->titulo,
'si',
$alquiler->moneda->simbolo . $cobro->comprobante->monto,
];
})->toArray();
}
/**
* @return array
*/
public function registerEvents(): array
{
return [
BeforeExport::class => function(BeforeExport $event) {
$event->writer->getProperties()->setCreator('Sistema de alquileres');
},
];
}
}
我的控制器:
public function comprobantes()
{
$alquileres = Alquiler::with('moneda', 'inquilino', 'propiedad', 'subpropiedad', 'cobros')
->get();
return(new ComprobantesExport($alquileres))->download('comprobantes.xlsx');
}
推荐答案
解决它.
class ComprobantesExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithEvents
{
use Exportable, RegistersEventListeners;
protected $alquileres;
protected $filas = [];
protected $total = 0;
public function __construct($alquileres = null)
{
$this->alquileres = $alquileres;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return $this->alquileres;
}
public function headings(): array
{
return [
'#',
'Fecha',
'Inquilino',
'Propiedad',
'Subpropiedad',
'Atraso',
'Monto'
];
}
public function map($alquiler): array
{
$this->filas[] = $alquiler->cobros->count() + array_sum($this->filas) + 1 ;
$this->limites[] = $alquiler->cobros->count();
$this->total += $alquiler->cobros->sum("monto");
return $alquiler->cobros->map(function ($cobro, $nro) use ($alquiler){
return [
$cobro->comprobante->id,
$cobro->created_at->format("d/m/Y"),
$alquiler->inquilino->full_name,
$alquiler->propiedad->titulo,
$alquiler->subpropiedad->titulo,
'si',
floatval($cobro->comprobante->monto),
];
})->toArray();
}
/**
* @return array
*/
public function registerEvents(): array
{
$styleTitulos = [
'font' => [
'bold' => true,
'size' => 12
]
];
return [
BeforeExport::class => function(BeforeExport $event) {
$event->writer->getProperties()->setCreator('Sistema de alquileres');
},
AfterSheet::class => function(AfterSheet $event) use ($styleTitulos){
$event->sheet->getStyle("A1:G1")->applyFromArray($styleTitulos);
$event->sheet->setCellValue('A'. ($event->sheet->getHighestRow()+1),"Total");
foreach ($this->filas as $index => $fila){
$fila++;
$event->sheet->insertNewRowBefore($fila, 1);
$event->sheet->getStyle("A{$fila}:G{$fila}")->applyFromArray($styleTitulos)->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setARGB('FFFF0000');
$event->sheet->setCellValue("A{$fila}","Subtotal Propiedad");
$event->sheet->setCellValue("G{$fila}", "=SUM(G".($fila - $this->limites[$index]).":G".($fila - 1).")");
}
$event->sheet->getDelegate()->mergeCells("A{$event->sheet->getHighestRow()}:F{$event->sheet->getHighestRow()}");
$event->sheet->setCellValue('G'. ($event->sheet->getHighestRow()), $this->total);
}
];
}
}
这篇关于Laravel Excel 3.1在迭代之间添加额外的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文