Export to excel xls not working on live server

Question

I am trying to export the gridview to the excel xls in Yii. For this I used PHPExcel extension. It is working on localhost but not on live server.

Following is my model function:

public function downloadOrderReport($model){

        Yii::import('ext.phpexcel.XPHPExcel');

        $objPHPExcel= XPHPExcel::createPHPExcel();

        ini_set ( 'memory_limit', '150M' );

        $fileName=DOWNLOADFILENAME.'.xls'; // download file name

        // Add some data
        $objPHPExcel->setActiveSheetIndex(0);   

        $columnName=array("ID"=>"Order Number","AddedOn"=>"Order Date","DealerID"=>"Dealer Name","SubmittedByUserID"=>"Executive Name","OrderStatusID"=>"Order Status","TotalAmount"=>"Order Amount");  

        $col = 0;
        $row = 1;       
        foreach($columnName as $key=>$value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $col++;
        }

        $criteria=new CDbCriteria;
        $criteria->with = array('dealer'=>array("select"=>"Name"),'submittedByUser'=>array("select"=>"Name"),'ApprovedOrRejectedByUser'=>array("select"=>"Name"),'orderStatus'=>array('select'=>'OrderStatus,ID'));
        $criteria->condition = "t.IsDeleted <> '".DELETED."'";

        if((isset($model->from_date) && trim($model->from_date) != "") && (isset($model->to_date) && trim($model->to_date) != "")){
            $criteria->condition .= ' AND t.AddedOn >="'.date("Y-m-d",strtotime($model->from_date)).'" AND t.AddedOn <="'.date("Y-m-d",strtotime($model->to_date)).'"';
        }

        $criteria->compare('t.ID',$model->ID);
        $criteria->compare('dealer.Name',$model->DealerID,true);
        $criteria->compare('submittedByUser.Name',$model->SubmittedByUserID,true);
        $criteria->compare('OrderStatusID',$model->OrderStatusID,true);
        $criteria->compare('ApprovedOrRejectedByUser.Name',$model->ApprovedOrRejectedByUserID,true);
        $criteria->compare('Remarks',$model->Remarks,true);
        $criteria->compare('TotalAmount',$model->TotalAmount);
        $criteria->compare('IsDeleted',$model->IsDeleted);
        $criteria->compare('AddedOn',$model->AddedOn,true);

        $criteria->order = "t.ID ASC";

        $getOrderDetails = Orders::model()->findAll($criteria);
        $row = 2;
        $grandTotal = 0.0;
        foreach($getOrderDetails as $details){
            $OrderID = $details->ID;
            $DealerName = $details->dealer->Name;
            $ExecutiveName = $details->submittedByUser->Name;
            $OrderStatus = $details->orderStatus->OrderStatus;
            $OrderDate = date('d-m-Y',strtotime($details->AddedOn));
            $OrderTotalAmount = $details->TotalAmount;
            $grandTotal = $grandTotal + $OrderTotalAmount;

            $columnValue=array($OrderID,$OrderDate,$DealerName,$ExecutiveName,$OrderStatus,$OrderTotalAmount);          


            for($col=0; $col<=ORDERREPORTCSVCOLUMNCOUNT; $col++){
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $columnValue[$col]); 
            }
            $row++;

        }
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(LABELCOUNT, $row, "Grand Total(Rs.)");
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(GRANDTOTALCOLUMNCOUNT, $row, $grandTotal);

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Order Report');


        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        // Redirect output to a client web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0


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

        Yii::app()->end();
    }

When I try to open the xls file on live server. It says "The file you are trying to open , 'XXXX.xls' is in different format than specified by the file extension.Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?".

If I say yes the file gets opened but it contains only special characters.

I am sure it has got something to do with header. But unable to find out the exact reason. Please help.

Screenshot: enter image description here

@DanyalSandeelo: I am not getting any error. But the file only contains special characters which aren't readable. — Chinmay Waghmare, Mar 18 '15 at 06:48
whole of file is full of special characters? or some of the characters are special? — Danyal Sandeelo, Mar 18 '15 at 06:49
@DanyalSandeelo: Only some characters are special. I have attached the screenshot. — Chinmay Waghmare, Mar 18 '15 at 06:53
Before the error was not mentioned, The error you mentioned now does not seem to be permissions issue. — arkoak, Mar 18 '15 at 07:00

score 1 · Accepted Answer · answered Mar 18 '15 at 09:12

Got it working :) Just clear the output buffer before the header to make sure nothing is buffered.

For anyone facing the same problem just add these 2 lines before the header in above code.

ob_end_clean();
ob_start();

What it does is first it erases/clear the output buffer and turn off output buffering.

Then we again turned ON the output buffering. So that no output is sent from the script (other than headers), instead the output is stored in an internal buffer.

I spent almost 2 days fixing this issue, thanks alot :) – Manoj S Kadlag Jan 10 '17 at 06:50 — Manoj S Kadlag, Jan 10 '17 at 06:50

Export to excel xls not working on live server

1 Answers1