PhpSpreadsheet - Download file instead of saving it

Question

I need to generate an excel file (xls) and trigger the download after it is generated. I found this example in the documentation.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

It shows how to create a excel file and save it on the server. How can I serve the result to the client instead and "force" him to download it? I need to get the data of the $writer somehow.

I am currently solving it without PhpSpreadsheet:

// Excel Export 
    $filename = 'export_'.date('d-m-y').'.xls';
    $filename = $validator->removeWhitespace($filename);

    header('Content-type: application/ms-excel');
    header('Content-Disposition: attachment; filename='.$filename);
    exit($response["output"]);  // <-- contains excel file content

But it is not working with my delimiter (semicolon). The semicolon is not getting interpreted and everything is getting written into one column.

If I export it as .csv, then it works. But I need it as .xls or .xlsx

Hello, can you try that like in link: https://github.com/PHPOffice/PhpSpreadsheet/issues/28#issuecomment-263101387 — Emircan Ok, Nov 21 '19 at 09:16
Does not work, it crashes at `$handle = $writer->save('php://memory');` — Black, Nov 21 '19 at 09:23

score 52 · Answer 1 · answered Sep 05 '20 at 13:26

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class DownloadExcel
{
    public static function createExcel(array $data, array $headers = [],
                                       $fileName = 'data.xlsx')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 0, $l = sizeof($headers); $i < $l; $i++) {
            $sheet->setCellValueByColumnAndRow($i + 1, 1, $headers[$i]);
        }

        for ($i = 0, $l = sizeof($data); $i < $l; $i++) { // row $i
            $j = 0;
            foreach ($data[$i] as $k => $v) { // column $j
                $sheet->setCellValueByColumnAndRow($j + 1, ($i + 1 + 1), $v);
                $j++;
            }
        }

        $writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="'. urlencode($fileName).'"');
        $writer->save('php://output');
    }

}

This is what I use to create a spreadsheet with PhpSpreadsheet and output directly to php://output for download.

Very good but if you have any output header sent earlier, the file downloaded will contain html data and excel will see it as a corrupted file, so you can add just in case `ob_clean();` before `$writer = new Xlsx($spreadsheet);`. — Bogdan C, Oct 31 '20 at 16:16
I cant confirm right now if your method is working for me, because I currently work on another project. But I will definitely come back to this answer and then accept it if it works. — Black, Nov 03 '20 at 14:40

score 11 · Answer 2 · answered Mar 10 '20 at 09:47

11

I had the same problem and found a solution here : https://github.com/PHPOffice/PhpSpreadsheet/issues/217

I ended my method with $writer->save('php://output'); then exit()

answered Mar 10 '20 at 09:47

Joachim

111
1
6

score 10 · Answer 3 · answered Dec 14 '20 at 15:13

My answer : PHP:

$writer = new Xlsx($spreadsheet);
ob_start();
$writer->save('php://output');
$ret['data'] = base64_encode(ob_get_contents());
ob_end_clean();

JS:

var linkSource = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,'+ response.data ;
var downloadLink = document.createElement("a");
var fileName = 'clients.' + format;

downloadLink.href = linkSource;
downloadLink.download = fileName;
downloadLink.click();

score 8 · Accepted Answer · answered Nov 21 '19 at 13:18

I solved it with a workaround. I temporarily save the file on the server, then I load the content into a variable and serve it as a download file. Then I delete the file from the server.

Workaround:

$date = date('d-m-y-'.substr((string)microtime(), 1, 8));
$date = str_replace(".", "", $date);
$filename = "export_".$date.".xlsx";

try {
    $writer = new Xlsx($response["spreadsheet"]);
    $writer->save($filename);
    $content = file_get_contents($filename);
} catch(Exception $e) {
    exit($e->getMessage());
}

header("Content-Disposition: attachment; filename=".$filename);

unlink($filename);
exit($content);

score 2 · Answer 5 · answered Oct 22 '20 at 11:53

2

call ob_end_clean(); just before the $writer->save('php://output').

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

answered Oct 22 '20 at 11:53

Zaheer Babar

1,636
1
15
17

score 1 · Answer 6 · answered Jul 14 '22 at 13:37

This worked for me:

$excel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $excel->getActiveSheet();
$sheet->setTitle('This is a test', true);

ob_end_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="filename_' . time() . '.xlsx"');
header('Cache-Control: max-age=0');

$xlsxWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel, 'Xlsx');
$xlsxWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel);
exit($xlsxWriter->save('php://output'));

score 0 · Answer 7 · answered Jan 25 '23 at 16:11

If you have problems where the files download corrupted, it is always good to check if there is any extra whitespace at the top of your file output. If your PHP files have blank white lines, whilst HTML won't have a problem, your phpspreadsheet file will. Spent a good chunk of time trying to fix these issues but the problem was with the whitespace!

PhpSpreadsheet - Download file instead of saving it

7 Answers7

Linked