Yii2 "phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLE/PPS/Root.php on line 292" "Headers already sent"

Yii 2.0 General Discussions
1

I have a button on a modal and when clicked I want to generate an Excel workbook based off of a xls template file and then prompt it for the user to download/open.

The button uses javascript to open a new blank window and call the following controller Action

public function actionGenTSXls($proj, $leg, $driver){
    // https://phpspreadsheet.readthedocs.io/en/develop/topics/recipes/
    if(!is_numeric($proj) || !is_numeric($leg) || !is_numeric($driver)){
        echo 'Invalid entry.';
        die();
    }
    //Get the Project Model
    $model = Projects::find()
             ->where(['=', 'ProjNo', $proj])
             ->one();
    if (!$model) {
        echo "There was a problem with the submitted request. (1)";
        die();
    }
    //Get the Project Leg Model
    $modelLeg = ProjectsLegs::find()
                ->where(['=', 'ProjId', $model->ProjId])
                ->andWhere(['=', 'LegNo', $leg])
                ->andWhere(['=', 'DriverId', $driver])
                ->one();
    if (!$modelLeg) {
        echo "There was a problem with the submitted request. (2)";
        die();
    }

    $timestamp = date('Ymd\THis');  // Date/Time stamp
    $filename = 'TripSheet_'.$model->ProjNo.'_'.$modelLeg->LegId.'_'.$timestamp.'.xls';
    if(YII_ENV_DEV){
        $filepath = Yii::$app->basePath.'\web\files\excel\\';
    }else{
        $filepath = \Yii::getAlias('@webroot').'/files/excel/';
    }
    $file = $filepath . $filename;
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');

    $sFile = Yii::$app->basePath.DIRECTORY_SEPARATOR.'templates'.DIRECTORY_SEPARATOR.'TS_Tmplt.xlsx';
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load( $sFile );
    $spreadsheet->getProperties()
                ->setCreator("TS")
                ->setLastModifiedBy(User::findOne(Yii::$app->user->getId())->Dispatcher)
                ->setTitle("TS ".$model->ProjNo.'_'.$modelLeg->LegId)
                ->setSubject("TS ".$model->ProjNo.'_'.$modelLeg->LegId)
                ->setDescription("TS ".$model->ProjNo.'_'.$modelLeg->LegId);
    //working on something with the spreadsheet/worksheet
    $sheet = $spreadsheet->getSheetByName('Sheet1');

    //Date
    $sheet->setCellValue('K3', date("n/j/Y"));
    $sheet->getStyle('K3')
          ->getNumberFormat()
          ->setFormatCode('m/d/yy');
    //Order
    if(!is_null($model->ProjNo)){$sheet->setCellValue('K4', $model->ProjNo);}
    //Dispatcher
    if(!is_null($model->DispatcherId)){$sheet->setCellValue('J11', User::findOne($modelLeg->DispatcherId)->Dispatcher);}
    //Company Name
    if(!is_null($model->ClientId)){
        $sheet->setCellValue('E4', Clients::findOne($model->ClientId)->Company);
        $sheet->getStyle('E4')->getAlignment()->setShrinkToFit(true);
    }
    //Start Date
    if(!is_null($modelLeg->StartDt)){
        $date = \DateTime::createFromFormat('Y-m-d H:i:s', $modelLeg->StartDt);
        $cellVal = $date->format("n/j/Y g:i A");
        $sheet->setCellValue('E8', $cellVal); 
    }

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
    $writer->save('php://output');
}

Now, in a general sense, it works. It create the xls, the user is prompted to open/download. But it generates an error in my yii log.

[error][yii\web\HeadersAlreadySentException] exception ‘yii\web\HeadersAlreadySentException’ with message 'Headers already sent “phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLE/PPS/Root.php on line 252”

I’ve been turning in circles for quite some time and thought I’d get some experts advice and learn. I am a novice when it come to this aspect, so if you can explain so I can learn it would be greatly appreciated.

I’ve read that it can have to do with output buffering and one solution is to use ob_start() at the beginning of my code, but that doesn’t seem to work and then the page return gibberish, I no longer get a xls file.

I decided to simplify my code to see what the issue was, so I generated the xls file, so I could temporarily eliminate that code and even so, with only 7 lines of code, I get the same error!

public function actionDownload(){
    $filename = "TS.xls";
    $file = "C:\Users\Dev\Downloads\TS.xls";
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load( $file );
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter( $spreadsheet, 'Xls');
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    $writer->save("php://output");
}
2

I have a workaround which I found searching countless similar threads, but I don’t understand it. I’m hoping someone here can explain, and confirm it it is a proper solution or if there is a better/proper way to handle this.

If I modify the Download() to

public function actionDownload(){
    $filename = "TS.xls";
    $file = "C:\Users\Dev\Downloads\TS.xls";
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load( $file );
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter( $spreadsheet, 'Xls')
    if (ob_get_length()) ob_end_clean(); //This doesn't appear to be necessary after all
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    $writer->save("php://output");
    die();
}

then it works. The critical element though is the die(); at the very end of the function.

3

In the controller don’t use echo but return

4

Thank you for the suggestion Lordfef

That was one of the first things I tried when I first encountered this issue. I deleted those instances( which only occured when errors were there, so basically it never ran) and it made no difference. The echo statements aren’t the cause of the issue in this instance.

This is further proven by my simplified Download action that still erred.

Die remedies the issue, I just don’t get why.

5

Hi @DBCreator,

I’m doing almost the same thing as you do in your simplified Download action.
The following is from my “SaveForDownload()” method in my spreadsheet utility class.

static function SaveForDownload($objPHPExcel, $filename, $filetype = self::FILE_TYPE_EXCEL2007, $useChart = false)
{
    /* @var $objWriter IWriter */
    $objWriter = null;
    $content_type = '';
    $ext = '';

    // ... getting writer and setting other parameters ...

    header('Content-Type: ' . $content_type);
    header('Content-Disposition: attachment; ' . $fname_frag . $filename . $ext);
    header('Cache-Control: max-age=0');
    ob_end_clean();
    $objWriter->save('php://output');
    exit;
}

I call it from my controllers. Of course it doesn’t return to the caller, because it “exit” at the end.

It’s working fine as far as I know, but it’s true that this is not what Yii expects us to do, because it is calling header() php function to send the headers and save('php://output') to send the content directly without the help of yii\web\Response component.

In the design of the yii framework, the headers and the content of the response should be sent by yii\web\Response component after the controller returns the content. So if you use header() function in the controller, it will cause header already sent error when the Response component tries to send the headers.

So I use exit and you are using die(). It might be a dirty trick, but it works.

6

Exit or die is not the proper way. I will explain why and provide solutions.

7

Exactly. :sweat_smile:

8

Before posting my solutions, we should figure out what is the reason.

Firstly, we need to know how Yii handle request, this could be found in yii\base\Application::run:
The app fetch a response instance and send response via yii\web\Response::send, what send does is that send headers and content.

Secondly, on this basis, let’s take your code snippet above as example:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
$writer->save("php://output");

We sent headers and echo content in controller, and then app’s response sent it again, that is why we got Headers already sent.

So there are two ways to handle it, either avoid sending app’s response, or let app’s response send headers and content only.

  1. Avoid sending app’s response:
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
$writer->save("php://output");
Yii::$app->getResponse()->isSent = true;

Additional, exit and die can also prevent sending response since the script was exit, but it will effects the request lifecycle, such as Controller::afterAction will not be triggered etc, that is why we should not use exit or die.

  1. Let app’s response send headers:
$response = Yii::$app->getResponse();
$headers = $response->getHeaders();
$headers->set('Content-Type', 'application/vnd.ms-excel');
$headers->set('Content-Disposition', 'attachment;filename="'.$filename.'"');

ob_start();
$writer->save("php://output");
$content = ob_get_contents();
ob_clean();
return $content;

BTW, I prefer the second solution, it will be useful, if you need to know what content you send.

Hopefully, I explained clearly since I am not good at writing in English. I hope that helps.

If there is any mistakes, please point them out. :slight_smile:

4 Likes
9

Thank you @razonyang !! Perfect explanation and solutions.
I will consider refactoring my code.

10

razonyang, THANK YOU so very much for the solution, but more so the detailed explanation so I can learn and grow as a developer! I can’t thank you enough for taking the time to help me/us.

Could I ask you to review/criticize a similar bit of code to download a zip file. Based on your post this is what I’ve done, anything you’d do differently?

$response = Yii::$app->getResponse();
$headers = $response->getHeaders();
$headers->set('Content-Description: File Transfer');
$headers->set('Content-Type: application/zip');
$headers->set('Content-Disposition: attachment; filename='.basename($zipname));
$headers->set('Content-Transfer-Encoding: binary');
$headers->set('Expires: 0');
$headers->set('Cache-Control: must-revalidate');
$headers->set('Pragma: public');
$headers->set('Content-Length: ' . filesize($zipname));
return $response->sendFile($zipname);
11

@DBCreator

Response::sendFile has set default headers already, such as Content-Type, Content-Length, Content-Disposition etc.

You could write less code to achieve that:

$response = Yii::$app->getResponse();
$attachmentName = ‘specified-attachname.zip’; // $attachmentName is also optional, default as basename($zipname)
$options = [
    // 'mimeType' => 'application/zip', // specified content-type, optional
];
$headers = $response->getHeaders();
$headers->set('Content-Description: File Transfer');
$headers->set('Content-Transfer-Encoding: binary');

// $attachmentName and $options are optional, change them as needed.
return $response->sendFile($zipname, $attachmentName, $options);

The detailed information could be found in source code.

1 Like
12

I can’t thank you enough for all your help!