[FIXED] How to export data to an excel file using PHPExcel Cakephp 2.0

Issue

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.

Solution

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');

Answered By - 502_Geek

Answer Checked By - Katrina (PHPFixing Volunteer)