ニュース・ブログ

ニュース・ブログ一覧PHPの記事情報How to use PhpSpreadsheet / excel with CakePHP 4

How to use PhpSpreadsheet / excel with CakePHP 4

Contents :

  • What is PhpSpreadsheet
  • How to install
  • Create a test spreadsheet
  • Spreadsheet download from a button
  • Use a template file
  • Best Practice

What is PhpSpreadsheet ?

Everyone who is reading this article have an idea already what is this, so in short, this is a library to create spreadsheet in PHP.

And this is the most popular one among others and maintained properly.

How to Install

You need composer to install this in your CakePHP project. This is the command for the composer :

composer require phpoffice/phpspreadsheet

For people who are using docker :

  • Run the command in your project directory to install this inside your container :

docker-compose exec app php ../composer.phar require phpoffice/phpspreadsheet

  • Optional : PhpSpreadsheet needs a zip extension for PHP, so if you don’t have this already install in your container this with the following command :

docker-compose exec app install-php-extensions zip

Create a test spreadsheet

Write the following codes inside a controller. In this case, I have created a SampleController.php

 

// src/Controller/SampleController.php

namespace App\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet; // ← PhpSpreadsheet class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // ← PhpSpreadsheet class

class SampleController extends AppController
{
    public function index()
    {
        // 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 !');

        // Save the spreadsheet in the webroot folder
        $writer = new Xlsx($spreadsheet);
        $writer->save('test.xlsx');
        exit;
    }
}

 

This will save the file in the webrootfolder. If you open the file it will look like this :

 

Spreadsheet download from a button

The above example created the xlsx file in the webroot folder. Now let’s create the code so that when the user clicks a button in a page, the spreadsheets downloads.

 

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');