PHP create an excel file and return it via a function

Question

I am working on yii2. I am creating an excel file via php spreadsheet. For now, I am downloading it in a browser. Now, I want to return the file without downloading it.

Code:

// Created 2 worksheet in one excel file successfully 

$filename = 'Meter Breakdown List '.date('d-m-y').'.xlsx'; //save our workbook as this file name
    // Redirect output to a client’s web browser (Xlsx)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    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');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

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

After that I want it to return the file like return $filename;

The returned file is then passed to a function named sendEmail($filename)

In this function, I have already setup email sending process via attachment.

How can I send the file without downloading it?

Note:

I have also looked at the question: PHP create Excel spreadsheet and then email it as an attachment.

@MagnusEriksson put this as an answer, as actually it is a concrete answer — fgamess, May 28 '18 at 06:22
Content-Disposition is the main one for downloading the file — ManiMuthuPandi, May 28 '18 at 06:23
you dont actually need to save the file if all your going to do is send it. phpmailer will take a stream as attachment, more efficient than file creation if you are not wanting to keep it — , May 28 '18 at 06:24
Ok, I have commented all of the headers and save. The only part which is not commented is `$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');` When I try to send the email I am getting `Swift_IoException` the error is `Unable to open file for reading [Meter Breakdown List 28-05-18.xlsx]` — Moeez, May 28 '18 at 06:28
@MagnusEriksson I have tried your way by commenting the header section and then by doing `$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('/inventory-web/Files/'.$filename); return Url::toRoute('/inventory-web/Files/'.$filename);` But I am getting error `Could not open /inventory-web/Files/Meter Breakdown List 28-05-18.xlsx for writing.` — Moeez, May 28 '18 at 06:54
@ManiMuthuPandi I have tried each and every possible way but still, I am unable to return the file. Can you please help me out on this? — Moeez, May 28 '18 at 09:21
Do you require the file to be stored on the server? Or is it a one-time deal? If you don't need to save it, it wouldn't make much sense to store it in a file, but `php://memory` might be better suited. You'd then might have to refactor your `sendEmail` to take in a filehandle instead of a filename. — k0pernikus, May 28 '18 at 10:20
@MrFaisal It appears that `PHPOffice/PhpSpreadsheet` only allows writing to a file, and if you try to replace `'php://output'` with `'php://memory` you'll lose the filehandle in the process. Due to that restriction of `PHPOffice/PhpSpreadsheet` I recommend creating a temporay file through [tempnam](http://php.net/manual/de/function.tempnam.php). — k0pernikus, May 28 '18 at 10:36
Interestingly enough, internally phpspreadshee is using tempnam when using `php://output` or `php://stdout`. See the implementation of the [xlx-writer](https://github.com/PHPOffice/PhpSpreadsheet/blob/dcc18322155aed681bc7b1c15d9fb055ee7c8afb/src/PhpSpreadsheet/Writer/Xlsx.php#L179) — k0pernikus, May 28 '18 at 10:42

score 2 · Accepted Answer · answered May 28 '18 at 10:09

Sample code to save the file

<?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');

Once file saved, you can send the file to mail

sendEmail('hello world.xlsx');

Once done everything you may delete the saved file

unlink('hello world.xlsx');

PHP create an excel file and return it via a function

1 Answers1