Search code examples
phplaravellaravel-excel

Adding Custom Footer and Header with Laravel Excel Package

Am using Laravel Excel version 3.1 to generate CSV/XLS etc. I have to customized my csv or xls to have a custom footer and header spanned through the columns.

Its should look like in the screenshot attached.

Expected output

Also, I want to store the exported file to storage location but cant make iy work.

And the code I have done is: 

class ReportExport implements FromArray, WithHeadings
{
    protected $results;

    public function __construct(array $results, array $headings, array $fileAttributes)
    {
        $this->results = $results;
        $this->headings = $headings;
        $this->file_attributes = $fileAttributes;
    }

    /**
     * @return array
     */
    public function array(): array
    {
        return $this->results;
    }

    /**
     * @return array
     */
    public function headings(): array
    {
        return $this->headings;
    }

    public function registerEvents(): array
    {
        return [
            // Handle by a closure.
            BeforeExport::class => function(BeforeExport $event) {
                $event->writer->getProperties()->setTitle('Patrick');
            },
        ];
    }

}

Calling i as below:

Excel::store(["1","2"],"xyz.xlsx");

How can I add these extra lines to my exported results.

Solution

  • Laravel Excel - Extending
    https://docs.laravel-excel.com/3.1/exports/extending.html

    PHPSpreadsheet
    https://phpspreadsheet.readthedocs.io/en/latest/

    There is some room to clean things up, but this should give you the basics.

    public function registerEvents(): array
{
    return [
        // Handle by a closure.
        AfterSheet::class => function(AfterSheet $event) {

            // last column as letter value (e.g., D)
            $last_column = Coordinate::stringFromColumnIndex(count($this->results[0]));

            // calculate last row + 1 (total results + header rows + column headings row + new row)
            $last_row = count($this->results) + 2 + 1 + 1;

            // set up a style array for cell formatting
            $style_text_center = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_CENTER
                ]
            ];

            // at row 1, insert 2 rows
            $event->sheet->insertNewRowBefore(1, 2);

            // merge cells for full-width
            $event->sheet->mergeCells(sprintf('A1:%s1',$last_column));
            $event->sheet->mergeCells(sprintf('A2:%s2',$last_column));
            $event->sheet->mergeCells(sprintf('A%d:%s%d',$last_row,$last_column,$last_row));

            // assign cell values
            $event->sheet->setCellValue('A1','Top Triggers Report');
            $event->sheet->setCellValue('A2','SECURITY CLASSIFICATION - UNCLASSIFIED [Generator: Admin]');
            $event->sheet->setCellValue(sprintf('A%d',$last_row),'SECURITY CLASSIFICATION - UNCLASSIFIED [Generated: ...]');

            // assign cell styles
            $event->sheet->getStyle('A1:A2')->applyFromArray($style_text_center);
            $event->sheet->getStyle(sprintf('A%d',$last_row))->applyFromArray($style_text_center);
        },
    ];
}

    EDIT: Basic page formatting
    Here are a couple of extras to help with output formatting. These can be appended to the existing AfterSheet event. You may want to open another question for specifics on PDF manipulation.

    // set columns to autosize
for ($i = 1; $i <= count($this->results[0]); $i++) {
    $column = Coordinate::stringFromColumnIndex($i);
    $event->sheet->getColumnDimension($column)->setAutoSize(true);
}

// page formatting (orientation and size)
$event->sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$event->sheet->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_LETTER);