Hi all,
in my project, I'm exporting a sorted Collection to Excel, using the Maatwebsite/Excel functionality. It is working fine for the moment.
The result is a collection of cars with tasks/times. A car can have 1+ tasks/times. After each car I would like to have an empty line containing a COUNT of the time worked on it.
I started to try the following, but the result is not what I expected. So I think I'm misunderstanding the map function. Can somebody tell me if my idea is totally off and indeed I'm misunderstanding it?
It seems it always goes into the "else" part (or at least it looks like that).
PS the "0" or "1" I'm returning in last columns is only for test.
public function map($time): array
{
$dt = Carbon::parse($time->stop)->diffInSeconds(Carbon::parse($time->start));
$duur = (is_null($time->stop)) ? 0 : $dt;
$car = $time->car->brand . " " . $time->car->type . " (" . $time->car->license . ")";
if (!isset($lastcar)){
$lastcar = $car;
}
if ($lastcar != $car) {
return [
[
'-',
'-',
'1',
],
[
$time->car->brand . " " . $time->car->type . " (" . $time->car->license . ")",
$time->task->name,
$time->employee->name,
$time->start,
$time->stop,
'=' . $duur . '/86400',
'0',
]
];
}
else {
return [
$time->car->brand . " " . $time->car->type . " (" . $time->car->license . ")",
$time->task->name,
$time->employee->name,
$time->start,
$time->stop,
'=' . $duur . '/86400',
'1',
];
}
if (isset($totTime[$car])) {
$totTime[$car]['totaltime'] = $totTime[$car]['totaltime'] + $dt;
}
else {
$totTime[$car]['totaltime'] = $dt;
}
$lastcar = $car;
}
Is it uberhaupt possible to run an if/else in the map function?
In case this is totally stupid, what would be the best to get this exported please ?
Thanks in advance.
Br,
Davy
Sure you can run an if-else statement. However, the map function doesn't know anything about the previous item you mapped over. So in this case $lastcar will always be null.
You can however get around this by doing the looping yourself. You return any collection here yourself. This also means you need to retrieve the data yourself.
Documentation: https://docs.laravel-excel.com/3.1/exports/collection.html#using-custom-structures
Hey BobbyBouwmann,
I like your answer, thanks for it.
I noticed I did not fully copy the code of the export.
<?php
namespace App\Exports;
use App\Time;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
class Times2Export implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithColumnFormatting
{
public function collection()
{
return Time::all()->sortBy("car_id");
}
public function headings(): array
{
return [
'Dossiernummer',
'Auto',
'Taak',
'Werknemer',
'Starttijd',
'Stoptijd',
'Duur',
];
}
/**
* @var Time $time
*/
public function map($time): array
{
$dt = Carbon::parse($time->stop)->diffInSeconds(Carbon::parse($time->start));
$duur = (is_null($time->stop)) ? 0 : $dt;
return [
$time->car->dossiernr,
$time->car->brand . " " . $time->car->type . " (" . $time->car->license . ")",
$time->task->name,
$time->employee->name,
$time->start,
$time->stop,
'=' . $duur . '/86400',
];
}
public function columnFormats(): array
{
return [
'G' => NumberFormat::FORMAT_DATE_TIME4,
];
}
}
When looking on the page you pasted, I see this:
namespace App\Exports;
use App\Invoice;
use Maatwebsite\Excel\Concerns\FromCollection;
class InvoicesExport implements FromCollection
{
public function collection()
{
return new Collection([
[1, 2, 3],
[4, 5, 6]
]);
}
}
I cannot really find what should be the [1,2,3], [4,5,6] in my case then.
Sorry I'm a newbie and maybe it is a stupid question, but a small hint could probably help.
Thanks in Advance,
Davy
I thought of testing simply like this:
<?php
namespace App\Exports;
use App\Time;
use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
class Times2Export implements FromCollection, WithMapping, WithHeadings, ShouldAutoSize, WithColumnFormatting
{
public function collection()
{
return new Collection([
[1,2,3],
[4,5,6]
]);
//return Time::all()->sortBy("car_id");
}
But then I get
Class 'App\Exports\Collection' not found
another approach is to group the collection by car id and then loop over all the records for one car within the map function. It's possible to return multiple lines for the one car
https://docs.laravel-excel.com/3.1/exports/mapping.html#multiple-rows
Thanks very much for your response. But that's exactly the page I used as info for this test.
Do you mean an extra "GroupBy"? And how would I need to incorporate that please? I never know how many lines will be for each car.
Please or to participate in this conversation.