[QUESTION] Use existing file as a template to append new data before export

[marcfeliu]

Prerequisites

Versions

• PHP version: 7.2
• Laravel version: 5.7
• Package version: 3.1

Description

I'm trying to edit an existing file to use as a template. I now that for this version, this function is not developed to use easily. But I would like to know if using the functions inside the plugin and playing with the function resisterEvents() I could do it by myself.

I got to download an existing file and select a sheet. But not to edit it.

This is my code:

    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            BeforeWriting::class  => function(BeforeWriting $event) {
                $event->writer->reopen(storage_path('app/public/files/mytemplate.xlsx'),Excel::XLSX);
                $event->writer->getSheetByIndex(0);
                return $event->getWriter()->getSheetByIndex(0);
            }
        ];
    }

Some suggestions?

Many thanks

Additional Information

I tried to add info in a value with this code inside the function resisterEvents() and doesn't work:

AfterSheet::class => function(AfterSheet $event) {
       $event->sheet->setCellValue('G2', 'Hello world');
}

And also to append data through function query()

[marcfeliu]

Finally this worked for me on version 3.1.0. In the actual version 3.1.10 this doesn't work because reopen function has changed. Waiting for new updates and improvements of editing an existing file.

[patrickbrouwers]

->reopen() is an internal method and should be used with caution. You can probably work around it by passing new LocalTemporaryFile(storage_path('app/public/files/mytemplate.xlsx')) to reopen.

Editing existing files (templates) is on our roadmap (version 3.3), but without a specific release date.

If you need it on priority, please have a look at our Commercial Support section: https://laravel-excel.com/commercial-support .

[marcfeliu]

Thanks for this quick answer.

I will try this way.

This is not a priority right now but I'm testing because I will need to add data to an existing excel with macros and dynamic tables, adding data and recalculating excel formulas. I'm trying all of this because I know this is not easy to do with a complex excel.

If I find out something good I will report you

[MarFelix]

In case of an update please comment as soon as possible

[petriuslima]

Hello, @MarFelix. I was facing same problem and now I have a workaround that I'm using. This may help you.
I digged deep in Mattwebsite code to understand how the export is done. So remember this steps that is used to download the file:

1. Call Excel class passing your Export class (this example is using download method)
2. The Excel class identify which Writer should be used and call the export method in this Writer.
3. The Writer open your Export (and call BeforeExport event), then populate a NEW sheet, then call its own write method.
4. The write method (Writer class) will call the BeforeWriting event and write on file for others types than XLSX

So that's why the BeforeWriting don't work, and if you use it on BeforeExport it will work but in a NEW sheet.

To workaround I add a flag on the method to get the export data (in my case FromCollection). This way it will not be process on step 2. Then, on BeforeWriting I mark the flag, get the wanted sheet and call the export method on it.

That's the code:

public function collection()
{
    if ($this->calledByEvent) { // flag
        return $this->myCollectionToExport;
    }

    return collect([]);
}

public function registerEvents(): array
{
    return [
        BeforeWriting::class => function(BeforeWriting $event) {
            $templateFile = new LocalTemporaryFile(storage_path('app/public/files/mytemplate.xlsx'));
            $event->writer->reopen($templateFile, Excel::XLSX);
            $event->writer->getSheetByIndex(0);

            $this->calledByEvent = true; // set the flag
            $event->writer->getSheetByIndex(0)->export($event->getConcernable()); // call the export on the first sheet

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

Take a look on download and write methods of Maatwebsite\Excel\Writer, it will be very helpfull.

With this information and steps, it would be possible to create a more complex workaround.

[Jonmiii]

Its not working

[ahmedosaka]

Sorry If I'm late to the party, but I found the solution.
Make Sure the Excel file located under storage folder and then pass the path after storage.
this is an example
new LocalTemporaryFile(storage_path('app\weeklyReport.xlsx'))
this is my Export file code

<?php

namespace App\Exports;

use App\Order;
use App\Models\Inspection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Files\LocalTemporaryFile;
use Maatwebsite\Excel\Excel;

class InspectionsExport implements WithEvents
{
    public $startYear;
    public $startMonth;
    public $startDay;

    public $endYear;
    public $endMonth;
    public $endDay;

    public $va;
    public $vb;
    public $vc;

    public function __construct(array $array)
    {
        $this->startYear = $array[0];
        $this->startMonth = $array[1];
        $this->startDay = $array[2];

        $this->endYear = $array[3];
        $this->endMonth = $array[4];
        $this->endDay = $array[5];
        
        $this->va = $array[6];
        $this->vb = $array[7];
        $this->vc = $array[8];

    }
    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(storage_path('app\weeklyReport.xlsx'));
                $event->writer->reopen($templateFile, Excel::XLSX);
                $sheet = $event->writer->getSheetByIndex(0);

                $this->populateSheet($sheet);
                
                $event->writer->getSheetByIndex(0)->export($event->getConcernable()); // call the export on the first sheet

                return $event->getWriter()->getSheetByIndex(0);
            },
        ];
    }
    private function populateSheet($sheet){

        // Populate the static cells
        $sheet->setCellValue('B2', $this->startYear.'年'.$this->startMonth.'月'.$this->startDay.'日（月）～'.
                                    $this->endYear.'年'.$this->endMonth.'月'.$this->endDay.'日（日）分'  );
        $sheet->setCellValue('D6', $this->va);
        $sheet->setCellValue('D11', $this->vb);
        $sheet->setCellValue('D16', $this->vc);   

    }

}

this is my controller :

  public function export(Request $request){
       $admin_id = Auth::guard('admin')->user()->id;

       $start_date=$request->start_date;
       $end_date=$request->end_date;
       // dummy data 
       $startYear = 2022;
       $startMonth = 06;
       $startDay = 22;

       $endYear = 2022;
       $endMonth= 06;
       $endDay =30;

       $va = 10;
       $vb = 20;
       $vc = $va + $vb;
       $filenameExport = "export.xlsx";
       try{
           return Excel::download(new InspectionsExport([$startYear,$startMonth,$startDay,
                                                   $endYear, $endMonth, $endDay,
                                                   $va, $vb, $vc]), $filenameExport); 
       } catch (Exception $e) {
           dd($e);
       }
       
   } 

hope it helps

[3141bishwa]

Sorry If I'm late to the party, but I found the solution. Make Sure the Excel file located under storage folder and then pass the path after storage. this is an example new LocalTemporaryFile(storage_path('app\weeklyReport.xlsx')) this is my Export file code

<?php

namespace App\Exports;

use App\Order;
use App\Models\Inspection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Files\LocalTemporaryFile;
use Maatwebsite\Excel\Excel;

class InspectionsExport implements WithEvents
{
    public $startYear;
    public $startMonth;
    public $startDay;

    public $endYear;
    public $endMonth;
    public $endDay;

    public $va;
    public $vb;
    public $vc;

    public function __construct(array $array)
    {
        $this->startYear = $array[0];
        $this->startMonth = $array[1];
        $this->startDay = $array[2];

        $this->endYear = $array[3];
        $this->endMonth = $array[4];
        $this->endDay = $array[5];
        
        $this->va = $array[6];
        $this->vb = $array[7];
        $this->vc = $array[8];

    }
    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(storage_path('app\weeklyReport.xlsx'));
                $event->writer->reopen($templateFile, Excel::XLSX);
                $sheet = $event->writer->getSheetByIndex(0);

                $this->populateSheet($sheet);
                
                $event->writer->getSheetByIndex(0)->export($event->getConcernable()); // call the export on the first sheet

                return $event->getWriter()->getSheetByIndex(0);
            },
        ];
    }
    private function populateSheet($sheet){

        // Populate the static cells
        $sheet->setCellValue('B2', $this->startYear.'年'.$this->startMonth.'月'.$this->startDay.'日（月）～'.
                                    $this->endYear.'年'.$this->endMonth.'月'.$this->endDay.'日（日）分'  );
        $sheet->setCellValue('D6', $this->va);
        $sheet->setCellValue('D11', $this->vb);
        $sheet->setCellValue('D16', $this->vc);   

    }

}

this is my controller :

  public function export(Request $request){
       $admin_id = Auth::guard('admin')->user()->id;

       $start_date=$request->start_date;
       $end_date=$request->end_date;
       // dummy data 
       $startYear = 2022;
       $startMonth = 06;
       $startDay = 22;

       $endYear = 2022;
       $endMonth= 06;
       $endDay =30;

       $va = 10;
       $vb = 20;
       $vc = $va + $vb;
       $filenameExport = "export.xlsx";
       try{
           return Excel::download(new InspectionsExport([$startYear,$startMonth,$startDay,
                                                   $endYear, $endMonth, $endDay,
                                                   $va, $vb, $vc]), $filenameExport); 
       } catch (Exception $e) {
           dd($e);
       }
       
   } 

hope it helps

This doesn't seem to work when I use this in conjunction with the example for MultipleSheets for some reason. All I get are empty sheets.

[feryae]

This doesn't seem to work when I use this in conjunction with the example for MultipleSheets for some reason. All I get are empty sheets.

I guess this is a bit unrelated since I didn't use MultipleSheets, but I did this for multiple sheets.

<?php

namespace App\Exports;

use App\Models\Model;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Excel;
use Maatwebsite\Excel\Files\LocalTemporaryFile;
use Maatwebsite\Excel\Writer;

class ModelExport implements WithEvents
{
    /**
     * @return \Illuminate\Support\Collection
     */

    const TEMPLATE_FILE = 'app/report/ModelReport.xlsx';
    const SHEET_CELL = 'B6'; // Example Cell. 

    protected $reportColumn;
    public function __construct(array $data)
    {
        $this->reportColumn = $data['reportColumn'];

    }
    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function (BeforeWriting $event) {
                $this->configureSheet($event->writer);

                $sheet = $event->writer->getSheetByIndex(0);
                $this->populateSheet($sheet); // Populate function
                // This will alter the first sheet.

                $sheet = $event->writer->getSheetByIndex(1);
                $this->populateSheet($sheet); 
                // This will alter the second sheet.
            },
        ];
    }

    protected function configureSheet(Writer $writer)
    {
        $templateFile = new LocalTemporaryFile(storage_path(self::TEMPLATE_FILE));
        $writer->reopen($templateFile, Excel::XLSX);
    }

    protected function populateSheet($sheet)
    {
        $sheet->setCellValue(self::SHEET_CELL, $this->reportColumn);
    }

}