PHPExcel : CodeIgniter error on output

Question

I have this controller which renders my data from database to the PHPExcel library.

https://arjunphp.com/how-to-use-phpexcel-with-codeigniter/

this is my controller :

public function downloadTournamentData() {
    $this->load->model("Tournament");

    $var_data = array();

    $var_data['tournament'] = $this->Tournament->getTournamentData();

    $this->load->library('excel');

    $this->excel->setActiveSheetIndex(0);

    $this->excel->getActiveSheet()->setTitle('test');

    $this->excel->getActiveSheet()->setCellValue('A1', 'member_username');
    $this->excel->getActiveSheet()->setCellValue('B1', 'member_name');
    $this->excel->getActiveSheet()->setCellValue('C1', 'member_phone');
    $this->excel->getActiveSheet()->setCellValue('D1', 'member_email');
    $this->excel->getActiveSheet()->setCellValue('E1', 'member_idcard');
    $this->excel->getActiveSheet()->setCellValue('F1', 'Date_created');
    $this->excel->getActiveSheet()->setCellValue('G1', 'team_name');
    $this->excel->getActiveSheet()->setCellValue('H1', 'warnet_name');
    $this->excel->getActiveSheet()->setCellValue('I1', 'warnet_cp');
    $this->excel->getActiveSheet()->setCellValue('J1', 'warnet_phone');
    $this->excel->getActiveSheet()->setCellValue('K1', 'region_name');
    $this->excel->getActiveSheet()->setCellValue('L1', 'City');


    $cell_inc = 2;
    foreach($var_data['tournament'] as $k => $v) {
        $this->excel->getActiveSheet()->setCellValue('A'.$cell_inc, $v['member_username']);
        $this->excel->getActiveSheet()->setCellValue('B'.$cell_inc, $v['member_name']);
        $this->excel->getActiveSheet()->setCellValue('C'.$cell_inc, $v['member_phone']);
        $this->excel->getActiveSheet()->setCellValue('D'.$cell_inc, $v['member_email']);
        $this->excel->getActiveSheet()->setCellValue('E'.$cell_inc, $v['member_idcard']);
        $this->excel->getActiveSheet()->setCellValue('F'.$cell_inc, $v['Date_created']);
        $this->excel->getActiveSheet()->setCellValue('G'.$cell_inc, $v['team_name']);
        $this->excel->getActiveSheet()->setCellValue('H'.$cell_inc, $v['warnet_name']);
        $this->excel->getActiveSheet()->setCellValue('I'.$cell_inc, $v['warnet_cp']);
        $this->excel->getActiveSheet()->setCellValue('J'.$cell_inc, $v['warnet_phone']);
        $this->excel->getActiveSheet()->setCellValue('K'.$cell_inc, $v['region_name']);
        $this->excel->getActiveSheet()->setCellValue('L'.$cell_inc, $v['City']);

        $cell_inc++;
    }

    date_default_timezone_set("Asia/Jakarta");

    $this_date = date("Y-m-d");

    $filename='pb_turnamen_data-'.$this_date.'.xls'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel; charset=UTF-8'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache

    //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
    //if you want to save it as .XLSX Excel 2007 format
    $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
    //force user to download the Excel file without writing it to server's HD
    $objWriter->save('php://output');
}

It does download the excel file. Only problem is that the data is mixed up and it's all wrong.

Screenshot of downloaded excel file

Why is this happening? Is there something wrong with my controller?

Please provide solution...

Is it prompting you to choose character set while opening it in excel? Try setting character set to UTF-8 while opening it to excel. — Disha V., Jul 21 '15 at 10:42
If you're sending a spreadsheet directly to the browser, then there must be absolutely no additional output generated anywhere within your code or by the framework — Mark Baker, Jul 21 '15 at 10:44
@DishaV. : no, it doesn't prompt me to set the charset. The error is "The file format and extension of 'xxx.xls' don't match..." — godheaper, Jul 22 '15 at 03:34
@MarkBaker : I don't see any additional output being generated after / before the header and createWriter. But I'm not reading a spreadsheet, I'm generating one from database data. Could the issue lies with the way I load the PHPExcel library? Does CodeIgniter have limitation on loading this library? — godheaper, Jul 22 '15 at 03:34

score 2 · Answer 1 · answered Jan 13 '17 at 11:15

2

Add ob_end_clean(); after $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');

final code is

$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
ob_end_clean();
$objWriter->save('php://output');

answered Jan 13 '17 at 11:15

Avinash Saini

1,203
11
10

PHPExcel : CodeIgniter error on output

1 Answers1