PHP Spreadsheet 1 value per spreadsheet

I’ve got an array of hotels below. I’m planning to export it to a spreadsheet using Php spreadsheet. My goal is to get 1 spreadsheet per hotel so in the array $hotels below, I’d expect 2 spreadsheets will be created.

This is what I wanted to render in each spreadsheet.

Spreadsheet 1:

   | Property  | Hotel 1 | Hotel 1
   | Room Name | Room 1  | Room 2
   ...

Spreadsheet 2:

   | Property  | Hotel 2 | Hotel 2 | Hotel 2
   | Room Name | Room 3  | Room 4  | Room 5
   ...

But what I’ve got so far is it shows all of the hotels like this

   | Property  | Hotel 1 | Hotel 1 | Hotel 2 | Hotel 2 | Hotel 3 
   | Room Name | Room 1  | Room 2  | Room 3  | Room 4  | Room 5
   ...

The hotel array.

$hotels = [
    'id' => 1,
    'title' => 'Hotel 1',
    'rooms' => [
        0 => [
            'id' => 1,
            'title' => 'Room1',
            'default_price' => 50,
            'options' => [
                0 => [
                    'date' => '12-04-2022',
                    'price' => 100,
                ]...
            ]
        ],
        1 => [
            'id' => 2,
            'title' => 'Room2',
            'default_price' => 120,
            'options' => [
                0 => [
                    'date' => '11-04-2022',
                    'price' => 200,
                ]...
            ]
        ],
    ],
    
    'id' => 2,
    'title' => 'Hotel 2',
    'rooms' => [
        0 => [
            'id' => 3,
            'title' => 'Room3',
            'default_price' => 50,
            'options' => [
                0 => [
                    'date' => '12-04-2022',
                    'price' => 100,
                ]...
            ]
        ],
        1 => [
            'id' => 4,
            'title' => 'Room4',
            'default_price' => 120,
            'options' => [
                0 => [
                    'date' => '11-04-2022',
                    'price' => 200,
                ]...
            ]
        ],
        1 => [
            'id' => 5,
            'title' => 'Room5',
            'default_price' => 120,
            'options' => [
                0 => [
                    'date' => '11-04-2022',
                    'price' => 200,
                ]...
            ]
        ],
    ]
];

This is what I’ve done so far: The first one ($sheetPrice->setCellValue($alphabets[$hotelKey+2].'2' , $hotel->title)) is not working like what I wanted.

The second one ($sheetPrice->setCellValue($alphabets[$key].'2' , $hotel->title)) is showing all the hotels instead of 1 hotel rooms value per spreadsheet.

$sheetPrice->setCellValue('B2', 'Property');
$sheetAvailability->setCellValue('B2', 'Property');

$sheetPrice->setCellValue('B3', 'Unit Type');
$sheetAvailability->setCellValue('B3', 'Unit Type');

$sheetPrice->setCellValue('B4', 'Unit Guests');
$sheetAvailability->setCellValue('B4', 'Unit Guests');

foreach ($hotels as $hotelKey => $hotel) {

    // not working
    //$sheetPrice->setCellValue($alphabets[$hotelKey+2].'2' , $hotel->title);

    // check if it has rooms
    if (count($rooms) > 0) {
        // extract rooms
        foreach ($rooms as $key => $room) {
             $key = $key+2;
             $sheetPrice->setCellValue($alphabets[$key].'2' , $hotel->title);
             // rest of the rooms set cell value
             ...
        }

        $filename = $hotel->title . '.xlsx';
        $file = $this->basename . '/excel_export/' . $filename;
        $writer->save($file);
    }
}

Also, I’m not sure if there are any post like this or maybe I just need the right words to google the problem but if there is please link it below. Thanks!

Let me know if you have any questions. Thanks in advance!

Answer

You are writing in the same sheet over and over. You need to create a new spreadsheet, fill it with rows then save for each hotel. Also that array structure you did provide is invalid there can’t be two indexes with the same name.

  foreach ($hotels as $hotel){
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // do your things, set the header, add the rows etc
        
        $filename = $hotel->title . '.xlsx';
        $file = $this->basename . '/excel_export/' . $filename;
        $writer = new Xlsx($spreadsheet);
        $writer->save($filename);
    }

Advertisement

Answer