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

Need Help
1

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.

Thanks!

2 Likes
2

Its not the more elegant way but works:

1 - extract lib in:
src/Vendor/phpexcel

2 - create xls layout
src/Template/Layout/xls/default.ctp:

<?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'));
    $this->viewBuilder()->layout('xls/default');
    $this->viewBuilder()->template('xls/spreadsheet_user');
    $this->RequestHandler->respondAs('xlsx');
    $this->render();
}

4 - Template view:
src/Template/Users/xls/spreadsheet_user.ctp

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

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

//HEADER
$i=1;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, 'User ID');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, 'User Name');

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

/*
if u have a collection of users just loop
//DATA
foreach($users as $user){
    $i++;
    $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
$objPHPExcel->setActiveSheetIndex(0);

//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');
    $objWriter->save($file);
 } 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');
    $objWriter->save('php://output');
}
2 Likes
3

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

Thanks!

1 Like
4

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


u6oWi.png1488×664 39.5 KB

2 Likes
5

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?

Thanks!

6

composer.json
"require": { "box/spout" : "dev-master" }

composer update

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 = 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
             $writer->close();
             
             //$this->RequestHandler->respondAs('xlsx');
             $this->autoRender = false;
        }
1 Like
7

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.