CakePHP 3.5 and spout/xlsx library : how to handle .xlsx extension

Need Help
1

Hello,

I installed the spout php library for handling xlsx files. see http://opensource.box.com/spout/

So far I am calling a method in my controller like:
mydomain.tld/articles/xlsx?article_id=1

I would prefer to ask for:
mydomain.tld/articles/1.xlsx

and manage a dedicated controller for handling my xlsx files.
How can I do that?

I started by adding in my routes.php :
$routes->extensions(['xlsx']);

But this is not enough.
Thank you for you kind attention

Kitcat711

2

Hi Kitcat711! :wink:

In your routes.php

<?php
use Cake\Core\Plugin;
use Cake\Routing\RouteBuilder;
use Cake\Routing\Router;
use Cake\Routing\Route\DashedRoute;

Router::defaultRouteClass(DashedRoute::class);

Router::scope('/', function (RouteBuilder $routes) {
    $routes->connect('/', ['controller' => 'Pages', 'action' => 'display', 'home']);
    $routes->connect('/pages/*', ['controller' => 'Pages', 'action' => 'display']);
    $routes->setExtensions(['xlsx']);
    $routes->connect('/articles/:id', [
        'controller' => 'Articles',
        'action' => 'xlsx'
    ])->setPass(['id']);
    $routes->fallbacks(DashedRoute::class);
});

Plugin::routes();
?>

In your ArticlesController.php

<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Utility\Hash;
use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

class ArticlesController extends AppController
{
    public function xlsx($id = null)
    {
        $query = $this->Articles->find();
        $query->formatResults(function ($results) {
            return $results->map(function ($row) {
                if (isset($row['created'])) {
                    $row['created'] = $row['created']->i18nFormat('dd/MM/yyyy HH:mm:ss', 'Europe/Paris');
                }
                return $row;
            });
        });
        $items = $query->enableHydration(false)->toArray();
        if (!$items) {
            $this->Flash->error('Nothing to export');
            return($this->redirect($this->referer()));
        }
        $rows = [];
        foreach($items as $item) {
            $rows[] = Hash::flatten($item);
        }
        $headers = array_keys($rows[0]);
        $writer = WriterFactory::create(Type::XLSX);
        $target = TMP . $id . '.xlsx';
        $writer->openToFile($target);
        $writer->addRow($headers);
        $writer->addRows($rows);
        $writer->close();

        $response = $this->response->withFile($target, ['download' => true]);
        return $response;
    }
}

Anywhere in your template

<?php
echo $this->Html->link('Export Articles', [
    'controller' => 'Articles', 'action' => 'xlsx', 1, '_ext' => 'xlsx'
]);
?>
3

Will try that Guillaume, and let you know :slight_smile: