php - Laravel excel 3.1 add extra rows between iterations

Question

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

But obtain this: result

This is the code of my export:

class ComprobantesExport implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithEvents
{
use Exportable, RegistersEventListeners;

protected $alquileres;

public function __construct($alquileres = null)
{
    $this->alquileres = $alquileres;
}
/**
 * @return IlluminateSupportCollection
 */
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');
        },

    ];
}

}

My controller:

public function comprobantes()
{
    $alquileres = Alquiler::with('moneda', 'inquilino', 'propiedad', 'subpropiedad', 'cobros')
        ->get();

    return(new ComprobantesExport($alquileres))->download('comprobantes.xlsx');
}

Answer 1

Solve it.

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 IlluminateSupportCollection
 */
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(PhpOfficePhpSpreadsheetStyleFill::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);
        }
    ];
}

}