Laravel Export Data to a pre designed format of excel

Level 70

@numaan Example Export class:

use Exporter;

class ReservationsExport implements FromCollection, WithHeadings, WithMapping
{
    public function collection()
    {
        return Reservation::all();
    }

    public function headings(): array
    {
        return [
            'ID',
            'Reservation Code',
            'Restaurant ID',
            'Meal Time ID',
            'Reservation Time',
            'Reservation Date',
            'Adults',
            'Kids',
            'Table No.',
            'Full Name',
            'Room No.',
            'Meal Plan',
            'Description',
            'User ID',
            'Status',
            'Created At',
            'Updated At',
        ];
    }

    public function map($reservation): array
    {
        return [
            $reservation->id,
            $reservation->reservation_code,
            $reservation->restaurant_id,
            $reservation->meal_time_id,
            $reservation->reservation_time,
            $reservation->reservation_date,
            $reservation->adults,
            $reservation->kids,
            implode(',', $reservation->table_no),
            $reservation->full_name,
            implode(',', $reservation->room_no),
            $reservation->meal_plan,
            $reservation->description,
            $reservation->user_id,
            $reservation->status,
            $reservation->created_at,
            $reservation->updated_at,
        ];
    }
}

The implode() function is used to convert the table_no and room_no arrays into a comma-separated string, so they can be properly displayed in the Excel file.

Once you have created the Excel class, you can use it in a controller or command to export the data to an Excel file. Here's an example of how you can use it in a controller:

class ReservationsController extends Controller
{
    public function export()
    {
        return Excel::download(new ReservationsExport, 'reservations.xlsx');
    }
}

You can also use the store method to store the file in a specific directory instead of downloading it.

class ReservationsController extends Controller
{
    public function export()
    {
        return Excel::store(new ReservationsExport, 'reservations.xlsx', 'public');
    }
}

2 likes

Level 2

@tisuchi Thank you for the reply, but I know to up to where you've shown. The thing is I want to export the excel file in a specific format that I've shown on the picture. I want to have logo, the restaurant name, total covers, meal time, date/time, exported time, after these detail then I want to draw the format with borders headings, margins, double borders etc.....

1 like

Kurai

Level 4

@numaan using the Laravel Excel Library, there is a option to use views as the excel export format. Which uses the blade syntax to pass data to the view. There you can create your tables whatever format you would like. https://docs.laravel-excel.com/3.1/exports/from-view.html

There is also a drawing option in Laravel Excel, which allow you to enter images onto the excel by specifying the position. This could help you with the images part. https://docs.laravel-excel.com/3.1/exports/drawings.html

Ill try mic mick your excel format using the above resource.

2 likes

Level 2

@Kurai Thank you for your reply. It's really hard for me to do something like this easily. But I'm try my best already the things you've suggested. I'm now able to add logo, borders, cella height and width, alignment of cells. I'm still stuck as I'm still not able to achieve what I'm looking for which is the format I've shown on above picture. Also I'm looking how to group the booking time and merge then center the time column as on the format with the booking which are made for that particular time and for other times as well the rows goes on increasing whenever a new booking is added. The other thing is that right now I'm having 5 reservations to accept for every 30 mins which I'm thinking to change and add it as a loop to go on as if I add more than 5 reservations for that time it should continue adding new rows plus the time merged cell also should merge accordingly. I hope you've understood and I hope you could mimick what I'm looking for. Thanks in advance for helping....

1 like

Level 51

@numaan, add a new question for the grouping/mapping and share some code?

Level 2

@webrobert I figured out to do that, I am actually doing something like this: correct me if I'm not using best practice.

$reservationsData = Reservation::where('status', 1)
	->whereBetween('reservation_date', [$start_date, $end_date])
	->get();
$reservationsData = $reservations->groupBy('reservation_time');

foreach ($reservationsData as $time => $reservations) {
	$reservation_time = "";
	foreach ($reservations as $reservation) {
		if ($reservation_time != $reservation->reservation_time) {
			$reservation_time = $reservation->reservation_time;
		}
	}
}

I am actually stuck at creating the format and populate data. Please help me with making the format.

Or maybe guide me how to do something like loading an existing excel file format and then populate the data to that excel file which I think will be much better if I can make it.

Level 51

@numaan

$reservationsSlots = Reservation::where('status', 1)
	->whereBetween('reservation_date', [$start_date, $end_date])
	->get()
    ->groupBy('reservation_time');

and for the blade file... use pad() to fill empty reservations lots 😉

<table>
    <tr>
        <th>Time</th>
        <th>Table No</th>
        <th>Room</th>
        <th>Guest Name</th>
        <th>Adult</th>
        <th>Kids</th>
        <th>Meal Plan</th>
        <th>Taken by</th>
        <th>Updated by</th>
        <th>Booking</th>
        <th>Booking time</th>
        <th>Remarks</th>
    </tr>

@foreach($reservationSlots as $reservations)
    @foreach($reservations->pad(5, null) as $reservation)
    <tr>
        <td>{{ $reservation->reservation_time ?? '' }}</td>
        <td>{{ $reservation->table_no ?? '' }}</td>
        <td>{{ $reservation->room ?? '' }}</td>
        <td>{{ $reservation->guest_name ?? '' }}</td>
        <td>{{ $reservation->adult ?? '' }}</td>
        <td>{{ $reservation->kids ?? '' }}</td>
        <td>{{ $reservation->meal_plan ?? '' }}</td>
        <td>{{ $reservation->taken_by ?? '' }}</td>
        <td>{{ $reservation->updated_by ?? '' }}</td>
        <td>{{ $reservation->booking_date ?? '' }}</td>
        <td>{{ $reservation->booking_time ?? '' }}</td>
        <td>{{ $reservation->remarks ?? '' }}</td>
    </tr>
    @endforeach
@endforeach
</table>

this doesn't account for the styling nor does it account for the merged A column but you're close.

Best Answer

Level 2

Hi All, Thank you for all your replies. Finally I was able to recreate the format I was looking for. Below is screenshot how it looks.

Recreated Format

Below is the solution (Full Export Class) code for my works. It was actually really simple while I was figuring out how.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Style\Border;

use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithDrawings;
use Maatwebsite\Excel\Concerns\WithHeadings;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithProperties;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;


class ReservationsExport implements
    FromCollection,
    WithHeadings,
    WithMapping,
    WithStyles,
    WithProperties,
    WithDrawings,
    WithCustomStartCell
{
    use Exportable;

    protected $reservations;
    protected $restaurantName;
    protected $mealTime;
    protected $dateRange;

    public function __construct($reservations, $restaurantName, $mealTime, $dateRange)
    {
        $this->reservations = $reservations;
        $this->restaurantName = $restaurantName;
        $this->mealTime = $mealTime;
        $this->dateRange = $dateRange;
    }

    public function collection()
    {
        return $this->reservations->sortBy('reservation_time');
    }

    public function map($reservation): array
    {

        return [
            $reservation->reservation_time,
            implode(',', $reservation->table_no),
            $reservation->room_number,
            $reservation->guest_name,
            $reservation->adults,
            $reservation->kids,
            $reservation->meal_plan,
            $reservation->mealTime->name,
            $reservation->user->username,
            $reservation->reservation_date,
            $reservation->restaurant->name,
            $reservation->description,
            $reservation->reservation_time,
        ];
    }

    public function headings(): array
    {
        return [
            'Time',
            'Table No.',
            'Room',
            'Name',
            'Adults',
            'Kids',
            'Meal Plan',
            'Meal Time',
            'User',
            'Date',
            'Restaurant',
            'Description',
            'Time',
        ];
    }

    public function startCell(): string
    {
        return 'B11';
    }

    public function styles(Worksheet $sheet)
    {

        $sheet->setAutoFilter('B11:N11');

        $sheet->getStyle(11)->getFont()->setBold(true);
        $sheet->getStyle(11)->getFont()->setSize(12);
        
        $sheet->getStyle('B11:N11')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('B11:N11')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->getStyle('B11:N11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        
        $sheet->getRowDimension('11')->setRowHeight(20);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(30);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(20);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(25);
        $sheet->getColumnDimension('M')->setWidth(40);

        $sheet->setShowGridlines(false);

        $sheet->setCellValue('C5', 'Restaurant: ');
        $sheet->setCellValue('D5', $this->restaurantName);
        $sheet->setCellValue('C6', 'Total Covers: ');
        $sheet->setCellValue('D6', $this->reservations->sum('adults') + $this->reservations->sum('kids'));
        $sheet->getStyle('D6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->setCellValue('C7', 'Meal Time: ');
        $sheet->setCellValue('D7', $this->mealTime);
        $sheet->setCellValue('C8', 'Date Range: ');
        $sheet->setCellValue('D8', $this->dateRange);
        $sheet->setCellValue('C9', 'Exported At: ');
        $sheet->setCellValue('D9', date('Y-m-d H:i:s'));

        $reservations = $this->reservations->sortBy('reservation_time')->groupBy('reservation_time');
        $currentRow = 12;
        $lastRow = 0;

        foreach ($reservations as $time => $group) {
            $sheet->mergeCells("B{$currentRow}:B".($currentRow+count($group)-1));
            $sheet->setCellValue("B{$currentRow}", $time);
            $sheet->getStyle("B{$currentRow}")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            $sheet->getStyle("B{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            foreach ($group as $reservation) {
                $row = $currentRow++;
                $sheet->getRowDimension($row)->setRowHeight(20);
                $sheet->getStyle("B{$row}:N{$row}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $sheet->getStyle("B{$row}:N{$row}")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
                $sheet->getStyle("B{$row}:N{$row}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
            }
            $lastRow = $currentRow-1;
            $sheet->getStyle("B{$lastRow}:N{$lastRow}")->getBorders()->getBottom()->setBorderStyle(Border::BORDER_DOUBLE);
        }

    }

    public function drawings()
    {
        $drawing = new Drawing();
        $drawing->setName('Logo');
        $drawing->setDescription('This is my logo');
        $drawing->setPath(public_path('/assets/img/logo/logo.png'));
        $drawing->setHeight(90);
        $drawing->setCoordinates('B5');

        $drawing->setWidth(250);
        $drawing->setHeight(100);

        return $drawing;
    }
    
}

And here is my Controller export method code.

public function export(Request $request) 
    {  
        $reservations = $this->reservationRepo->search($request->all())->get();
        
        if (!$request->has('restaurant')) {
            $restaurantId = null;
            $restaurantNames = $reservations->unique('restaurant.name')->pluck('restaurant.name')->implode(', ');
            $restaurantName = $restaurantNames;
        } else {
            $restaurantId = $request->input('restaurant');
            $restaurantName = Restaurant::where('id', $restaurantId)->first()?->name;
        }

        if (!$request->has('meal_time')) {
            $mealTimeId = null;
            $mealTimeNames = $reservations->unique('mealTime.name')->pluck('mealTime.name')->implode(', ');
            $mealTime = $mealTimeNames;
        } else {
            $mealTimeId = $request->input('meal_time');
            $mealTime = MealTime::where('id', $mealTimeId)->first()->name;
        }

        if (!$request->has('date_range')) {
            $dateFrom = now()->startOfDay();
            $dateTo = now()->endOfDay();
            $dateRange = $dateFrom->format('Y-m-d').' - '.$dateTo->format('Y-m-d');
        } else {
            $dateRange = $request->input('date_from').' - '.$request->input('date_to');
        }

        if ($reservations->isEmpty())
        {
            $restaurantName = '';
            $mealTime = '';
            $dateRange = '';
        }

        return Excel::download(new ReservationsExport($reservations, $restaurantName, $mealTime, $dateRange), 'Reservations Report (General) '.now().'.xlsx');
    }

In case if anyone is wondering what is the "reservationRepo->search()" here is the code for ReservationRepository class

<?php

namespace App\Repositories\Reservations;

use App\Models\Reservation;

class ReservationRepository
{
    public function search(array $params)
    {
        $reservations = Reservation::query();

        if (isset($params['search'])) {
            $reservations->search($params['search']);
        }

        if (isset($params['date_from'])) {
            $reservations->where('reservation_date', '>=', $params['date_from']);
        }

        if (isset($params['date_to'])) {
            $reservations->where('reservation_date', '<=', $params['date_to']);
        }

        if (isset($params['restaurant'])) {
            $reservations->where('restaurant_id', $params['restaurant']);
        }

        if (isset($params['status'])) {
            $reservations->where('status', $params['status']);
        }

        if (isset($params['meal_time'])) {
            $reservations->where('meal_time_id', $params['meal_time']);
        }

        return $reservations;
    }
}

And here is what I did on front end with the export button to get the requested parameters for filtering. (Additional for anyone else who is having same issue)

<button class="btn btn-warning d-flex align-items-center justify-content-between export" type="button" onclick="location.href='{{ route('admin.reservationsystem.reporting.export') }}?{{ http_build_query(request()->query()) }}'">
      <i class="bx bx-export me-1 mt-0"></i>
      Export
    </button>

Let me know if anyone is having any questions about this. Also please suggest me the best practices if I'm doing anything wrong here.

2 likes

Level 51

@numaan

Best practice, around here, don’t give yourself the credit. If you got a reply that helped point you to the solution - give them the credit.

Glad you solved it ;)

Level 2

@webrobert I think you misunderstood and the way you said not to give credit to myself I felt so disrespectful. I totally understand what you're saying but if there's no replies here which helped me or pointed me to the solution I got by myself, then I don't think that I've to credit anyone else but me as I was the one figured out the solution myself. If anyone has helped or replied I've thanked them all including you. So please don't say things like that on the forum. Everyone is here to share information not to get credit or high on their levels. Don't make this forum stackoverflow 🙏

Level 51

@numaan it’s definitely not stackoverflow. What I mean is you take the best reply, you got the help, and you got the solution. And everyone else helped guide you toward that. Clearly you wrote code and it works, so Why feel disrespected?