PhpSpreadsheet - Download file instead of saving it

Black picture Black · Nov 21, 2019 · Viewed 15.4k times · Source

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.

enter image description here

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

Answer

Sean picture Sean · Sep 5, 2020
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.