Adding Custom Footer and Header with Laravel Excel Package

Question

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.

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.

matticustard · Accepted Answer · 2019-10-14 03:02:09Z

6

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);

edited Oct 14, 2019 at 3:02

answered Oct 9, 2019 at 7:07

matticustard

5,1691 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Coder Over a year ago

Thanks @matticustard, this looks great. Just wanna know what if I want to make it more dynamic, like if I dont know that the last column is D

matticustard Over a year ago

I've added dynamic columns above. You may want to add a check to verify that $this->results is not empty or handle it before it gets to export. Otherwise, $this->results[0] could throw an error.

Coder Over a year ago

Thanks thats good, am also trying to export the same into Pdf with Laravel Excel and Tcpdf.. however the results are very shrinked. Is there a way I can use full page width.

Coder Over a year ago

I have tried $event->sheet->getDelegate()->getPageSetup()->setFitToPage(1); but nothings affecting it.

matticustard Over a year ago

I've added some basic page formatting, but I'm not really sure about specific PDF manipulation. You may want to ask about this as another question.

Collectives™ on Stack Overflow

Adding Custom Footer and Header with Laravel Excel Package

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related