How to export data to an excel file using PHPExcel Cakephp 2.0

Question

I an trying to export data to an Excel file using PHPExcel libraries with Cakephp 2.5. My Codes :

<?php

App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel'.DS.'PHPExcel.php'));
App::import('Vendor', 'PHPExcel_IOFactory', array('file' => 'PHPExcel'.DS.'PHPExcel'.DS.'IOFactory.php'));
App::import('Vendor', 'PHPExcel_IOFactory', array('file' => 'PHPExcel'.DS.'PHPExcel'.DS.'Style.php'));

class LeadUploadController extends AppController {

    public function exel_download($emp_id='')
    {
        $this->autoRender = false;
        $this-> layout='ajax';

        $objPHPExcel = new PHPExcel();
        $serialnumber=0;
        $tmparray =array("Sr.Number","Employee ID","Employee Name");
        $sheet =array($tmparray);
        $tmparray =array();
        $serialnumber = $serialnumber + 1;
        array_push($tmparray,$serialnumber);
        $employeelogin = 'aa';
        array_push($tmparray,$employeelogin);
        $employeename = 'bb';
        array_push($tmparray,$employeename);   
        array_push($sheet,$tmparray);
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="file.xlsx"');
        $worksheet = $objPHPExcel->getActiveSheet();
        foreach($sheet as $row => $columns) {
        foreach($columns as $column => $data) {
        $worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);
        }
        }
        $objPHPExcel->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
    }
}

The problem is the downloaded Excel file contain no any data it completely blank with an error "can not open the file because of file format or extension not valid ". Have not any idea what's wrong with these code.

You might try sending the output as a txt file instead of xlsx. Generate it all the same, just change the file name. Make sure that debugging is turned on. What it sends you should then open in a text editor (like Notepad, for example), and you can look at it to see if the output is correct, or if there are helpful debugging notices embedded in it. — Greg Schmidt

502_Geek 502_Geek · Accepted Answer · 2018-03-29T05:20:53

The following code should work on xls extension.

    $objPHPExcel = new PHPExcel();
    $serialnumber=0;
    $tmparray =array("Sr.Number","Employee ID","Employee Name");
    $sheet =array($tmparray);
    $tmparray =array();
    $serialnumber = $serialnumber + 1;
    array_push($tmparray,$serialnumber);
    $employeelogin = 'aa';
    array_push($tmparray,$employeelogin);
    $employeename = 'bb';
    array_push($tmparray,$employeename);   
    array_push($sheet,$tmparray);
    header('Content-type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="file.xls"');
    $worksheet = $objPHPExcel->getActiveSheet();
    foreach($sheet as $row => $columns) {
        foreach($columns as $column => $data) {
            $worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);
        }
    }
    $objPHPExcel->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);
    $objPHPExcel->setActiveSheetIndex(0);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');

How to export data to an excel file using PHPExcel Cakephp 2.0

1 Answers