SolvedPhpSpreadsheet Created spreadsheets are corrupted

This is:

What is the expected behavior?

Serves blank spreadsheet to the browser

What is the current behavior?

Serves a corrupted excel file. Excel error message:

We found a problem with some content in test.xlsx. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes.

After Repair I get the following message:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

What are the steps to reproduce?

Run the code below

<?php

require __DIR__ . '/vendor/autoload.php';

// add code that show the issue here...
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test.xlsx"');
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

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

Which versions of PhpSpreadsheet and PHP are affected?

PHP: v7.0.22
Excel: 2016
OS: Windows 10, 64bit
Webserver: Apache httpd-2.4.27-win64-VC14 (via Laragon v3.1.4)

Notes

The same behaviour exists if I load a blank excel file via load()

33 Answers

✔️Accepted Answer

to anyone that having the problem, my team experience this problem, and it was caused by php output before the save(), sometimes it's part of the php framework you're using, and you don't notice in your code for generating the xls.

calling ob_end_clean(); just before the $writer->save() might fixed the corrupted file.
ob_end_clean();
$writer->save('php://output');

Other Answers:

For me the problem was that the framework I am using (and also most frameworks do the same) is adding some additional headers or something.

So the fix was just to terminate the script with die(); or exit(); IF you're outputting the generated file to the browser for download.

If you're just saving the file to the filesystem and that's it, there is no need to terminate the script.

For Laravel, call exit() right after $writer->save('php://output'); Apparently Laravel outputs other things that cause the corruption:

$extension = 'Xlsx';
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $extension);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"fileName.{$extension}\"");
$writer->save('php://output');
exit();

I fixed this issue by adding ob_end_clean() before save and exit; after save in IIS.

ob_end_clean();
$writer->save('php://output');
exit;

Had the same problem using Laravel, returned $writer
return $writer->save('php://output');

add die() or exit() and php output will work.

thanks, it work, here is my code

public function exportExcel($id){
    $filePath = '/app/TemplateFiles/INVOICE.xlsx';
    $spreadsheet = IOFactory::load(base_path().$filePath);
    $spreadsheet->getActiveSheet();

// manipulate file ...
// response - download file
$response = response()->streamDownload(function() use ($spreadsheet) {
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
});
$response->setStatusCode(200);
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=sjis');
$response->headers->set('Content-Disposition', 'attachment; filename= "'.'INVOICE-'.$id.'.xlsx" ');
ob_end_clean();
$response->send();
exit();
}

More Issues: