Where to put phpexcel lib and how use it in cakephp 3 x

I would like to know the steps to use this phpexcel lib(download from http://www.codeplex.com/PHPExcel) in our cakephp 3x and how to use it in controler or in ctp file. I have try many time but still can not use it.
please help.



Its not the more elegant way but works:

1 - extract lib in:

2 - create xls layout

<?php echo $this->fetch('content');

3 - Controller:

public function xls($id, $output_type = 'D', $file = 'my_spreadsheet.xlsx') {
    $user = $this->Users->get($id);
    $this->set(compact('user', 'output_type', 'file'));

4 - Template view:

require_once(APP . 'Vendor' . DS . 'phpexcel' . DS . 'PHPExcel.php');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("creator name");

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, 'User ID');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'User Name');

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $user->id);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $user->name);

if u have a collection of users just loop
foreach($users as $user){
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $user->id);
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $user->name);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('User Data');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

//call the function in the controller with $output_type = F and $file with complete path to the file, to generate the file in the server for example attach to email
if (isset($output_type) && $output_type == 'F') {
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 } else {
    // Redirect output to a client's web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$file.'"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

Yes, It works. you’re awesome, Thanks!
Are there any tips to handle exporting large data?


1 Like

phpexcel is not optimized for large data, i have seen some good comments about this lib for large xlsx data read /write:


Great, but does it support unit code (like Chinese, Thai, korean or Khmer character) and how to use it with cakephp 3 x? where can find the examples with cakephp 3x?


"require": { "box/spout" : "dev-master" }

composer update


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 = WriterFactory::create(Type::CSV); // for CSV 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->autoRender = false;
1 Like

Hello to all.
But I needed to work with ODT (LibreOffice, not Excel) files and I wrote a tool for this working.
Main function is present. For generation reports of vere deep details table.