PHPExcel - CakeExcel

Need Help
1

Hello!!
I have a problem when I want to create a xlsx file. I’d done it in another version of Cake (3.3) but in this (3.4) I can’t.

I did this:

  1. I installed PHPExcel - CakeExcel

  2. In routes.php Router::extensions(‘xlsx’);

  3. Then: Plugin::load(‘CakeExcel’, [‘bootstrap’ => true, ‘routes’ => true]);

4)APPController :

$this->loadComponent(‘RequestHandler’,[

                    'viewClassMap' => [
                            'xlsx' => 'CakeExcel.Excel',
                    ],
               ]);
  1. Layout /xlsx/default.ctp and in my Controller /xlsx/index.ctp

The ctp:

<?php 
$this->PhpExcel->
        getProperties()
        ->setTitle("Cumpleanos $month");
        $styleCells = array(
                'borders' => array(
                        'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN
                        )
                )
        );
        $this->PhpExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Nombre y Apellido')
        ->setCellValue('B1', 'Día')
        ->setCellValue('C1', 'Correo')
        ->setCellValue('D1', 'Correo madre')
        ->setCellValue('E1', 'Correo padre');
        $_row = 1;
        foreach ($alumnos as $alumno)
        {
            $_row = $_row +1;
            $this->PhpExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$_row,h($alumno->presentacion))
            ->setCellValue('B'.$_row,h($alumno->fecha_nacimiento->format('j')))
            ->setCellValue('C'.$_row,h($alumno->email))
            ->setCellValue('D'.$_row,h($alumno->email_madre))
            ->setCellValue('E'.$_row,h($alumno->email_padre))
            ;
            // Le aplico a todas las celdas el formato de borde.
            $this->PhpExcel->getActiveSheet()->getStyle('A'.$_row)->applyFromArray($styleCells);
            $this->PhpExcel->getActiveSheet()->getStyle('B'.$_row)->applyFromArray($styleCells);
            $this->PhpExcel->getActiveSheet()->getStyle('C'.$_row)->applyFromArray($styleCells);
            $this->PhpExcel->getActiveSheet()->getStyle('D'.$_row)->applyFromArray($styleCells);
            $this->PhpExcel->getActiveSheet()->getStyle('E'.$_row)->applyFromArray($styleCells);
        }
        // Ajusto el ancho de las columnas
        foreach (range('A', $this->PhpExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
            $this->PhpExcel->getActiveSheet()
            ->getColumnDimension($col)
            ->setAutoSize(true);
        }
        //
        // Seteo el formato por default de los bordes para las celdas del encabezado y las pongo en negrita
        $styleHeader = array(
                'borders' => array(
                        'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN
                        )
                ),
                'font' => array(
                        'bold' => true
                )
        );
        $this->PhpExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleHeader);
        $this->PhpExcel->getActiveSheet()->getStyle('B1')->applyFromArray($styleHeader);
        $this->PhpExcel->getActiveSheet()->getStyle('C1')->applyFromArray($styleHeader);
        $this->PhpExcel->getActiveSheet()->getStyle('D1')->applyFromArray($styleHeader);
        $this->PhpExcel->getActiveSheet()->getStyle('E1')->applyFromArray($styleHeader);
        // Seteo el nombre del archivo
        $_file_name_aux = "Cumple mes de $month";
        $objWriter= PHPExcel_IOFactory::createWriter($this->PhpExcel,'Excel5');

Here some errors:

Cake\Http\ResponseEmitter::emit() - CORE\src\Http\ResponseEmitter.php, line 48                              
Cake\Http\Server::emit() - CORE\src\Http\Server.php, line 105                               
[main] - ROOT\webroot\index.php, line 37
Warning (2): Cannot modify header information - headers already sent by (output started at C:\wamp\www\siba\vendor\phpoffice\phpexcel\Classes\PHPExcel\Shared\OLE\PPS\Root.php:283) [CORE\src\Http\ResponseEmitter.php, line 149]
Warning (2): Cannot modify header information - headers already sent by (output started at C:\wamp\www\siba\vendor\phpoffice\phpexcel\Classes\PHPExcel\Shared\OLE\PPS\Root.php:283) [CORE\src\Http\ResponseEmitter.php, line 181]
Warning (2): Cannot modify header information - headers already sent by (output started at C:\wamp\www\siba\vendor\phpoffice\phpexcel\Classes\PHPExcel\Shared\OLE\PPS\Root.php:283) [CORE\src\Http\ResponseEmitter.php, line 181]

I tried several ways, but nothing.

The file always is corrupt!!! (Windows)

Thanks, and sorry for my English!

2

i think you need set headers and redirect output to browser

Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Disposition: attachment;filename="report.xlsx"
Cache-Control: max-age=0
    
$objWriter = PHPExcel_IOFactory::createWriter($this->PhpExcel, 'Excel2007');
$objWriter->save('php://output');

i recomend the use of


http://opensource.box.com/spout/docs/
its way better than phpexcell, lightweight and handle large data exports too

2 Likes
3

Thanks Diego!
I 'll tried with Spout!

Grettings

4

install with composer and in Controller

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

    public function xlsxSpout($id = null) {
             $user = $this->Users->get($id);
             $fileName = 'spreadsheet.xlsx';
     
             $writer = WriterFactory::create(Type::XLSX); // for XLSX files
             //$writer->openToFile($fullFilePath); // write data to a file or to a PHP stream
             $writer->openToBrowser($fileName); // stream data directly to the browser
             
             $writer->addRow(['ID', 'Name', 'Email']); // header
             $writer->addRow([$user->id, $user->name, $user->email]); // add a row at a time
             //$writer->addRows($rows); // add multiple rows at a time

             $this->RequestHandler->respondAs('xlsx');
             $writer->close();
             $this->autoRender = false;
        }
2 Likes