So the button looks like this in index.php:

Form->create(null, ['url'=> ['action' => 'excel',], 'type'=>'POST']); ?>
    <button type="submit" class="btn btn-primary">Download</button>
Form->end() ?>

And the SampleController.php :

// src/Controller/SampleController.php

namespace App\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet; 
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Cake\Http\CallbackStream; // ← Added new in this sample

class SampleController extends AppController
{
    public function excel()
    {
        // Create a new spreadsheet
        $spreadsheet = new Spreadsheet();

        // Add value in a sheet inside of that spreadsheet. 
        // // (It's possible to have multiple sheets in a single spreadsheet)
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');
        $writer = new Xlsx($spreadsheet);

        // ↓↓ Added new code from here in the eariler sample code
        // Save the file in a stream
        $stream = new CallbackStream(function () use ($writer) {
            $writer->save('php://output');
        });

        $filename = 'sample_'.date('ymd_His');
        $response = $this->response;
		
        // Return the stream in a response
        return $response->withType('xlsx')
            ->withHeader('Content-Disposition', "attachment;filename=\"{$filename}.xlsx\"")
            ->withBody($stream);
            }
}

( To understand the specifics of the code, please read the comments which is in the code )

Use a template file

So let’s say you have lots of design in your spreadsheet and you want to use that template and just change the data in that file before download.So this is how you can do that.

The code will be like this in the controller :

// src/Controller/SampleController.php

namespace App\Controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet; 
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Cake\Http\CallbackStream;
use PhpOffice\PhpSpreadsheet\IOFactory; // ← Added new in this

class SampleController extends AppController
{
    public function excel()
	{
		// Load the template file from the root folder
		$templateFile = WWW_ROOT . 'sample.xlsx';
		$spreadsheet = IOFactory::load($templateFile);

		// Add data in that file
		$templateSheet = $spreadsheet->getActiveSheet();
		$templateSheet->setCellValue('B1', 'Hello World !');

		// Save the data in a stream
		$writer = new Xlsx($spreadsheet);
		$stream = new CallbackStream(function () use ($writer) {
			$writer->save('php://output');
		});

		$filename = 'sample_'.date('ymd_His');
		$response = $this->response;

		// Return the stream to the user
		return $response->withType('xlsx')
			->withHeader('Content-Disposition', "attachment;filename=\"{$filename}.xlsx\"")
			->withBody($stream);
    }
}

Best Practice

So in the above controller, we wrote the data, hello world, in the B1 cell. In here, we are actually creating the view from data. And we never create view in controller because we already have a specific place to write view. And that is in the template folder .

The reason I wrote in the controller above is to make the code simpler and easier to understand.

As now you have got the idea already on how to do this. Let’s learn how we should same code we wrote in the above section named Use a template file.

So the SampleController.php will look like this :

class SampleController extends AppController
{
    public function excel()
            
        $this->viewBuilder()->disableAutoLayout();

        $this->set('some_string', 'This is so better !');
        
        // Take the view and return as a response to the user/browser
        $this->response = $this->response->withDownload('test.xlsx');
    
    }
}

So simpler and not a fat controller at all 😄.

All the code related to the view has moved to the view in template.

So the view excel.php looks like this :

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;


// Load the template file from the root folder
$templateFile = WWW_ROOT . 'sample.xlsx';
$spreadsheet = IOFactory::load($templateFile);

// Add data in that file
$spreadsheet->getActiveSheet()
			->setCellValue('B1', $some_string)
;

// Save the data in a stream
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');