[QUESTION] How to Insert Image on a template file with multiple sheets

[luccasrafael]

Versions

• PHP version: 7.4
• Laravel version: 7.0
• Package version: 3.1

Description

I want to create a report that gets a template for the first worksheet, insert an image on this first worksheet, and insert more worksheets that don't need that image.

How can I:

• Create the first worksheet of the report using a template
• Insert an image on the worksheet created using the template
• Create more worksheets based on other reports

Additional Information

My export class example is able to create the first worksheet based on the template. Also, it creates additional worksheets, but the image is not inserted on the first worksheet.

This is the current code.

<?php

class ReportsWithInfoPageExport implements WithEvents, WithDrawings, WithMultipleSheets
{
    use Exportable;

    protected $data;
    protected $jobDetails;

    public function __construct($data,$jobDetails)
    {
        $this->data = $data;
        $this->jobDetails = $jobDetails;
    }

    /**
    * @return array
    */
    public function sheets(): array
    {
        $sheets = [];

        foreach ($this->data as $block => $data) {
            $sheets[] = new MultipleReports($block, $data);
        }

        return $sheets;
    }

    /**
    * @return Array
    */
    public function registerEvents(): array
    {
      return [

        BeforeExport::class => function(BeforeExport $event){
            $event->writer->reopen(new \Maatwebsite\Excel\Files\LocalTemporaryFile(storage_path('exports/report-front-page.xlsx')),Excel::XLSX);

            $event->writer->getSheetByIndex(0);
            $event->getWriter()->getSheetByIndex(0)->setCellValue('C2', 'Some Information');
            $event->getWriter()->getSheetByIndex(0)->setCellValue('G4', 'Some Information');
            return $event->getWriter()->getSheetByIndex(0);
         }
      ];
    }

    public function drawings()
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(storage_path('/img/logo.jpg'));
        $drawing->setHeight(50);
        $drawing->setWidth(50);
        $drawing->setCoordinates('A1');

        return $drawing;
    }
}

This code creates the worksheet based on the template, creates many worksheets but does not add the image on the first worksheet.

Could this be a problem with WithDrawings not working with WithEvents and WithMultipleSheets or am I missing something?

[luccasrafael]

I could find a way of inserting the image using the test code below

class TestExport implements WithEvents

{
    use Exportable, RegistersEventListeners;

    public static function beforeExport(BeforeExport $event)
    {
        $event->writer->reopen(new \Maatwebsite\Excel\Files\LocalTemporaryFile(storage_path('app/exports/report-front-page.xlsx')),Excel::XLSX);
        $event->writer->getSheetByIndex(0);
        $event->getWriter()->getSheetByIndex(0)->setCellValue('A1','Your Logo Goes Here');

        return $event->getWriter()->getSheetByIndex(0);
    }

    public static function beforeWriting(BeforeWriting $event)
    {
        // 
    }

    public static function beforeSheet(BeforeSheet $event)
    {
        // 
    }

    public static function afterSheet(AfterSheet $event)
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(storage_path('/img/other.png'));
        $drawing->setHeight(50);
        $drawing->setWidth(50);
        $drawing->setCoordinates('D10');

        $drawing->setWorksheet($event->sheet->getDelegate());
    }

}

However, now the image is inserted in a new worksheet. It seems that the afterSheet creates another worksheet to insert the image, but I want the image to be inserted in the worksheet that is using the template

[patrickbrouwers]

We don't officially support this reopen function, so events will probably not have the correct sheet reference, you'll have to select the correct one yourself.

[luccasrafael]

Thank you Patrick.
Yes, you're totally right, the event can't get the correct sheet reference.
Do you know where I can find documentation or some information about how to choose the correct sheet reference.

[patrickbrouwers]

Get the parent of the sheet and select the sheet by the index as you have done in beforeexport. We don't have more docs about that, but you can most likely find it all in phpspreadsheet docs.

[luccasrafael]

Ok, I got it Thanks again, but how exactly do I get the parent of the sheet. Sorry for the simple question.