Closed marcfeliu closed 5 years ago
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.
->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 .
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
In case of an update please comment as soon as possible
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:
Excel
class passing your Export
class (this example is using download
method)Excel
class identify which Writer
should be used and call the export
method in this Writer
.Writer
open your Export
(and call BeforeExport
event), then populate a NEW sheet, then call its own write
method.write
method (Writer
class) will call the BeforeWriting
event and write on file for others types than XLSXSo 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.
Its not working
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
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.
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);
}
}
This don't sees to work anymore i think
Prerequisites
Versions
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:
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:And also to append data through
function query()