Laravel-excel: How do I write to an existing excel sheet in 3.1

[akeebismail]

Prerequisites

Versions

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

Description

in version 2.1, after loading the excel file, I can set the activesheet index

<?php
/**
 * Created by PhpStorm.
 * User: abeeb
 * Date: 7/10/18
 * Time: 5:07 PM
 */

namespace App\Contents;


use App\Models\Content;
use Carbon\Carbon;
use Maatwebsite\Excel\Facades\Excel;

class ExcelRepository
{

    public function sample(){

        return storage_path('app/public/sample/0649964931793.xlsx');
    }



    public function singleTrack($content){
        Excel::load($this->sample(), function ($excel) use ($content){
            $sheet = $excel->setActiveSheetIndex(1);
            $xx = 15;
            $this->excelSheet($sheet, $xx, $content);
        })->setFilename($content->productBarcode->code ?? '')
            ->store('xlsx', storage_path('app/public/contents'))
            ->download('xlsx');
    }

    public function album($content){
        Excel::load($this->sample(), function ($excel) use ($content){
            $sheet = $excel->setActiveSheetIndex(1);
            $xx = 15;
            foreach ($content as $item) {
                $this->excelSheet($sheet,$xx,$item);
                $xx++;
            }
        })->setFilename($content[0]->productBarcode->code)
            ->store('xlsx', storage_path('app/public/contents'))
            ->download('xlsx');
    }




    public function excelSheet($sheet, $xx, $item){
        $sheet->setCellValue('C'.$xx, $item->title);
        $sheet->setCellValue('E'.$xx, $item->artist->name);
        $sheet->setCellValue('F'.$xx, $item->artist->name);
        $sheet->setCellValue('G'.$xx, $item->productBarcode->code ?? '');
        $sheet->setCellValue('H'.$xx, $item->ci_catalogue_number);
        $sheet->setCellValue('I'.$xx, $item->format);
        $sheet->setCellValue('K'.$xx, 'Full');
        $sheet->setCellValue('L'.$xx, 'WORLD');
        $sheet->setCellValue('N'.$xx, $item->release_date->format('d-m-Y'));
        $sheet->setCellValue('R'.$xx, $item->label->name ?? "");
        $sheet->setCellValue('S'.$xx, (new Carbon($item->release_date))->year);
        $sheet->setCellValue('T'.$xx, $item->label->name ?? "");
        $sheet->setCellValue('V'.$xx, $item->label->name ?? "");
        $sheet->setCellValue('W'.$xx, 'World');
        $sheet->setCellValue('X'.$xx, $item->genre->name ?? "");
        $sheet->setCellValue('Y'.$xx,'Alternative');
        $sheet->setCellValue('AA'.$xx, $item->explicit_content);
        $sheet->setCellValue('AB'.$xx,1);
        $sheet->setCellValue('AC'.$xx,1);
        $sheet->setCellValue('AD'.$xx, 'DEE|SHZ|YAT|GGL|MNT|SPO|ASP|SCD|SIA|PDX|UMA|KUA|KKB|AKA|MPC|AFT|NEE|ALI|MXM|FBK|TCT|ANG|AUD');
        $sheet->setCellValue('AE'.$xx, $item->id);
        $sheet->setCellValue('AF'.$xx, $item->title);
        if (empty($item->performing_artists)){

            $sheet->setCellValue('AH' . $xx, $item->artist->name );
            $sheet->setCellValue('AI' . $xx, $item->artist->name );
        }else {
            $sheet->setCellValue('AH' . $xx, $item->artist->name . '|' . $item->performing_artists);
            $sheet->setCellValue('AI' . $xx, $item->artist->name . ' feat. ' . $item->performing_artists);
        }
        $sheet->setCellValue('AJ'.$xx,$item->isrc);
        $sheet->setCellValue('AL'.$xx, 'Y');
        $sheet->setCellValue('AM'.$xx, (new Carbon($item->release_date))->year);
        $sheet->setCellValue('AN'.$xx, $item->label->name ?? "");
        $sheet->setCellValue('AO'.$xx, (new Carbon($item->release_date))->year);
        $sheet->setCellValue('AP'.$xx, $item->label->name ?? "");
        $sheet->setCellValue('AQ'.$xx, 'World');
        $sheet->setCellValue('AR'.$xx, $item->genre->name);
        $sheet->setCellValue('AS'.$xx,'Alternative');
        $sheet->setCellValue('AU'.$xx,$item->explicit_content);

        return $sheet;
    }



}

now in 3.1 how set sctivesheetindex and cellvalues
how do I achieve this in 3.1

<?php

namespace App\Exports\Excel;

use App\Models\Content;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Events\BeforeSheet;
use Maatwebsite\Excel\Events\BeforeWriting;
use Maatwebsite\Excel\Excel;

class CIContentCollection extends ContentItem implements FromCollection, WithMapping, WithEvents, WithCustomStartCell
{

    private $contents;

    public function __construct($contents)
    {
        $this->contents = $contents;
        parent::__construct($this->content);
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return $this->contents;
    }

    /**
     * @param mixed $row
     *
     * @return array
     */

    /**
     * @return int
     */

    /**
     * @return array
     */
    public function registerEvents(): array
    {
       return [
           BeforeExport::class => function(BeforeExport $event){
          $event->writer->reopen(storage_path('app/public/sample/0649964931793.xlsx'),Excel::XLSX);
          $event->writer->getSheetByIndex(1);
          return $event->getWriter()->getSheetByIndex(1);
           },
         AfterSheet::class => function(AfterSheet $event){
          $event->sheet->getStartRow(1);
         }
       ];
    }

    /**
     * @return string
     */
    public function startCell(): string
    {
        return 'A15';
    }
}

[GlennM]

Editing existing files is a planned feature for a future version.

[marcfeliu]

Hello @GlennM,

I need to use existing files.
When do you come up with this new features? Or I should think to use an old version 2.1?

Thanks

[GlennM]

Hello @GlennM,

I need to use existing files.
When do you come up with this new features? Or I should think to use an old version 2.1?

Thanks

It's on our roadmap, but without a specific target version or release date.

If you need it on priority, please have a look at our Commercial Support section.

[anisfrontmen]

@GlennM how is the roadmap going for editing existing files??

[molnarjg]

I found the bellow way of filling in an existing Excel template in version 3.1. I used some ideas previously written here in other related issues. The cell population happens ad hoc. Maybe it is not the most beautiful solution but does the job for now...

<?php

namespace App\Exports;

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

class SeriesAccompanimentExport implements WithEvents
{
    public $orderIds;
    public $series_acc;

    public function __construct(array $array)
    {
        $this->orderIds = $array[0];
        $this->series_acc = $array[1];
    }

    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(storage_path('app/excel_templates/template.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('A2', $this->series_acc);
        $sheet->setCellValue('B2', $this->series_acc);
        $sheet->setCellValue('C2', $this->series_acc);
        $sheet->setCellValue('D2', $this->series_acc);
        $sheet->setCellValue('E2', $this->series_acc);
        $sheet->setCellValue('F1', $this->series_acc);
        $sheet->setCellValue('H1', Carbon::now()->format('Y-m-d'));

        // Create the collection based on received ids
        $orders = Order::whereIn('id', $this->orderIds)->get();

        // Party starts at row 3
        $iteration = 3;

        foreach ($orders as $order) {

            // Create cell definitions
            $A = "A".($iteration);
            $B = "B".($iteration);
            $C = "C".($iteration);
            $D = "D".($iteration);
            $E = "E".($iteration);
            $F = "F".($iteration);
            $G = "G".($iteration);
            $H = "H".($iteration);

            // Populate dynamic content
            $sheet->setCellValue($A, $order->production."\n".$order->name);
            $sheet->setCellValue($B, $order->production."\n".$order->name);
            $sheet->setCellValue($C, $order->production."\n".$order->name);
            $sheet->setCellValue($D, $order->production."\n".$order->name);
            $sheet->setCellValue($E, $order->deadline);
            $sheet->setCellValue($F, $iteration - 2); // Deducting the number of base rows
            $sheet->setCellValue($G, $order->production);
            $sheet->setCellValue($H, $order->name);

            $cellRangeTarget = $A.':'.$H;

            // Copy style of Row 3 onto new rows - RowHeight is not being copied, need to adjust manually...
            if($iteration > 3)
            {
                $sheet->duplicateStyle($sheet->getStyle('A3'), $cellRangeTarget);
                $sheet->getRowDimension($iteration)->setRowHeight(83);
            }

            $iteration++;
        }

    }
}

The controller is simply...

public function export(Request $request)
    {
        try {
            $requestData = $request->all();
            $ids = $requestData['ids'];
            $series_accompaniment = $requestData['series_accompaniment'];
            $filenameExport = "export.xlsx";

            return Excel::download(new SeriesAccompanimentExport([$ids, $series_accompaniment]), $filenameExport);

        } catch (Exception $e) {

        }
    }

[ilanbelfer-inmanage]

I found the bellow way of filling in an existing Excel template in version 3.1. I used some ideas previously written here in other related issues. The cell population happens ad hoc. Maybe it is not the most beautiful solution but does the job for now...

<?php

namespace App\Exports;

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

class SeriesAccompanimentExport implements WithEvents
{
    public $orderIds;
    public $series_acc;

    public function __construct(array $array)
    {
        $this->orderIds = $array[0];
        $this->series_acc = $array[1];
    }

    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(storage_path('app/excel_templates/template.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('A2', $this->series_acc);
        $sheet->setCellValue('B2', $this->series_acc);
        $sheet->setCellValue('C2', $this->series_acc);
        $sheet->setCellValue('D2', $this->series_acc);
        $sheet->setCellValue('E2', $this->series_acc);
        $sheet->setCellValue('F1', $this->series_acc);
        $sheet->setCellValue('H1', Carbon::now()->format('Y-m-d'));

        // Create the collection based on received ids
        $orders = Order::whereIn('id', $this->orderIds)->get();

        // Party starts at row 3
        $iteration = 3;

        foreach ($orders as $order) {

            // Create cell definitions
            $A = "A".($iteration);
            $B = "B".($iteration);
            $C = "C".($iteration);
            $D = "D".($iteration);
            $E = "E".($iteration);
            $F = "F".($iteration);
            $G = "G".($iteration);
            $H = "H".($iteration);

            // Populate dynamic content
            $sheet->setCellValue($A, $order->production."\n".$order->name);
            $sheet->setCellValue($B, $order->production."\n".$order->name);
            $sheet->setCellValue($C, $order->production."\n".$order->name);
            $sheet->setCellValue($D, $order->production."\n".$order->name);
            $sheet->setCellValue($E, $order->deadline);
            $sheet->setCellValue($F, $iteration - 2); // Deducting the number of base rows
            $sheet->setCellValue($G, $order->production);
            $sheet->setCellValue($H, $order->name);

            $cellRangeTarget = $A.':'.$H;

            // Copy style of Row 3 onto new rows - RowHeight is not being copied, need to adjust manually...
            if($iteration > 3)
            {
                $sheet->duplicateStyle($sheet->getStyle('A3'), $cellRangeTarget);
                $sheet->getRowDimension($iteration)->setRowHeight(83);
            }

            $iteration++;
        }

    }
}

The controller is simply...

public function export(Request $request)
    {
        try {
            $requestData = $request->all();
            $ids = $requestData['ids'];
            $series_accompaniment = $requestData['series_accompaniment'];
            $filenameExport = "export.xlsx";

            return Excel::download(new SeriesAccompanimentExport([$ids, $series_accompaniment]), $filenameExport);

        } catch (Exception $e) {

        }
    }

Saved my life. Thank you

[iscjiha]

I found the bellow way of filling in an existing Excel template in version 3.1. I used some ideas previously written here in other related issues. The cell population happens ad hoc. Maybe it is not the most beautiful solution but does the job for now...

<?php

namespace App\Exports;

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

class SeriesAccompanimentExport implements WithEvents
{
    public $orderIds;
    public $series_acc;

    public function __construct(array $array)
    {
        $this->orderIds = $array[0];
        $this->series_acc = $array[1];
    }

    public function registerEvents(): array
    {
        return [
            BeforeWriting::class => function(BeforeWriting $event) {
                $templateFile = new LocalTemporaryFile(storage_path('app/excel_templates/template.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('A2', $this->series_acc);
        $sheet->setCellValue('B2', $this->series_acc);
        $sheet->setCellValue('C2', $this->series_acc);
        $sheet->setCellValue('D2', $this->series_acc);
        $sheet->setCellValue('E2', $this->series_acc);
        $sheet->setCellValue('F1', $this->series_acc);
        $sheet->setCellValue('H1', Carbon::now()->format('Y-m-d'));

        // Create the collection based on received ids
        $orders = Order::whereIn('id', $this->orderIds)->get();

        // Party starts at row 3
        $iteration = 3;

        foreach ($orders as $order) {

            // Create cell definitions
            $A = "A".($iteration);
            $B = "B".($iteration);
            $C = "C".($iteration);
            $D = "D".($iteration);
            $E = "E".($iteration);
            $F = "F".($iteration);
            $G = "G".($iteration);
            $H = "H".($iteration);

            // Populate dynamic content
            $sheet->setCellValue($A, $order->production."\n".$order->name);
            $sheet->setCellValue($B, $order->production."\n".$order->name);
            $sheet->setCellValue($C, $order->production."\n".$order->name);
            $sheet->setCellValue($D, $order->production."\n".$order->name);
            $sheet->setCellValue($E, $order->deadline);
            $sheet->setCellValue($F, $iteration - 2); // Deducting the number of base rows
            $sheet->setCellValue($G, $order->production);
            $sheet->setCellValue($H, $order->name);

            $cellRangeTarget = $A.':'.$H;

            // Copy style of Row 3 onto new rows - RowHeight is not being copied, need to adjust manually...
            if($iteration > 3)
            {
                $sheet->duplicateStyle($sheet->getStyle('A3'), $cellRangeTarget);
                $sheet->getRowDimension($iteration)->setRowHeight(83);
            }

            $iteration++;
        }

    }
}

The controller is simply...

public function export(Request $request)
    {
        try {
            $requestData = $request->all();
            $ids = $requestData['ids'];
            $series_accompaniment = $requestData['series_accompaniment'];
            $filenameExport = "export.xlsx";

            return Excel::download(new SeriesAccompanimentExport([$ids, $series_accompaniment]), $filenameExport);

        } catch (Exception $e) {

        }
    }

This helped me, I thank you very much