Phpspreadsheet: Output xlsx for download

Created on 13 Sep 2018 · 6Comments · Source: PHPOffice/PhpSpreadsheet

instead of saving the file on local directory i want the user to download it.
i've tried '$writer->save("php://output");' but it's now working.
can someone help me please? thank you

question

Source

buddyy93

👍2

Most helpful comment

Save the text button, no the data;

<html>
    <head>
        <title>PHP Excel</title>
    </head>
    <body>
        <form action="" method="POST" enctype="multipart/form-data">
            <label for="planilha"></label>
            <input type="file" id="planilha" name="planilha">
            <br>
            <br>
            <button type="submit">Enviar</button>
        </form>
    </body>
</html>

<?php

    require 'vendor/autoload.php';

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Reader\IReader;
    use PhpOffice\PhpSpreadsheet\Writer\IWriter;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use Symfony\Component\HttpFoundation\StreamedResponse;
    use PhpOffice\PhpSpreadsheet\Writer as Writer;

    if (isset($_FILES['planilha'])) {
        $file = $_FILES['planilha']['name'];
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $count = $sheet->getHighestDataRow();
        $writer = new Writer\Xls($spreadsheet);

        for($i=1;$i<=$count;$i++){
            $nome = $sheet->getCell('A'.$i)->getValue();
            $telefone = $sheet->getCell('B'.$i)->getValue();

            if(
                $telefone == "" ||
                strpos($nome, "Nome do Cliente") === true ||
                strpos($nome, "Total") === true
            ){
                $sheet->removeRow($count);
            }

            if(
                strpos($nome, "Nome do Cliente") === false &&
                strpos($nome, "Total") === false &&
                $telefone != ""
            ){

                $nome = explode(" ", $nome);
                $telefone = str_replace(['-',' ','-','(',')'],'',$telefone);

                if(strlen($telefone) < 11){
                    $telefone = substr_replace($telefone, '9', 2, -strlen($telefone));
                }

                $sheet->setCellValue('A'.$i, $nome[0]);
                $sheet->setCellValue('B'.$i, $telefone);

            }

        }

        // $writer = new Xlsx($spreadsheet);
        // $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        // $writer->save("Contatos-.xls");
        $streamedResponse = new StreamedResponse();
        $streamedResponse->setCallback(function () use ($spreadsheet) {
              // $spreadsheet = //create you spreadsheet here;
              $writer =  new Xlsx($spreadsheet);
              $writer->save('php://output');
        });

        $streamedResponse->setStatusCode(200);
        $streamedResponse->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $streamedResponse->headers->set('Content-Disposition', 'attachment; filename="your_file.xls"');
        return $streamedResponse->send();

    }

?>

danillo10 on 12 Mar 2019

👍2

All 6 comments

You can check this sample

We use something similar in our projects and work perfectly.

damijanc on 13 Sep 2018

👍1

This looks like a support question. Please ask your support questions on StackOverflow, or Gitter.
Thank you for your contributions.

support[bot] on 21 Oct 2018

Save the text button, no the data;

<html>
    <head>
        <title>PHP Excel</title>
    </head>
    <body>
        <form action="" method="POST" enctype="multipart/form-data">
            <label for="planilha"></label>
            <input type="file" id="planilha" name="planilha">
            <br>
            <br>
            <button type="submit">Enviar</button>
        </form>
    </body>
</html>

<?php

    require 'vendor/autoload.php';

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\IOFactory;
    use PhpOffice\PhpSpreadsheet\Reader\IReader;
    use PhpOffice\PhpSpreadsheet\Writer\IWriter;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    use Symfony\Component\HttpFoundation\StreamedResponse;
    use PhpOffice\PhpSpreadsheet\Writer as Writer;

    if (isset($_FILES['planilha'])) {
        $file = $_FILES['planilha']['name'];
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $count = $sheet->getHighestDataRow();
        $writer = new Writer\Xls($spreadsheet);

        for($i=1;$i<=$count;$i++){
            $nome = $sheet->getCell('A'.$i)->getValue();
            $telefone = $sheet->getCell('B'.$i)->getValue();

            if(
                $telefone == "" ||
                strpos($nome, "Nome do Cliente") === true ||
                strpos($nome, "Total") === true
            ){
                $sheet->removeRow($count);
            }

            if(
                strpos($nome, "Nome do Cliente") === false &&
                strpos($nome, "Total") === false &&
                $telefone != ""
            ){

                $nome = explode(" ", $nome);
                $telefone = str_replace(['-',' ','-','(',')'],'',$telefone);

                if(strlen($telefone) < 11){
                    $telefone = substr_replace($telefone, '9', 2, -strlen($telefone));
                }

                $sheet->setCellValue('A'.$i, $nome[0]);
                $sheet->setCellValue('B'.$i, $telefone);

            }

        }

        // $writer = new Xlsx($spreadsheet);
        // $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        // $writer->save("Contatos-.xls");
        $streamedResponse = new StreamedResponse();
        $streamedResponse->setCallback(function () use ($spreadsheet) {
              // $spreadsheet = //create you spreadsheet here;
              $writer =  new Xlsx($spreadsheet);
              $writer->save('php://output');
        });

        $streamedResponse->setStatusCode(200);
        $streamedResponse->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $streamedResponse->headers->set('Content-Disposition', 'attachment; filename="your_file.xls"');
        return $streamedResponse->send();

    }

?>

danillo10 on 12 Mar 2019

👍2

Start by not sending html to the browser as well as the spreadsheet

MarkBaker on 13 Mar 2019

In Laravel framework use this:

$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');
$response->headers->set('Content-Disposition', 'attachment; filename="your_file.xls"');
$response->send();