Force download of multiple dynamically generated files with Phpexcel

Question

I have a class responsible for receiving the upload of one or several worksheets .XLS with lots of detailed data (This spreadsheet is default), and convert into another file .XLS leaner for printing.

class convertTimeWorkFileSheets {

    public function convert_sheets() {

        $sheets = [];
        for ($i = 0; $i < count($_FILES['files']['name']); $i++) {

            $name[$i] = $_FILES['files']['name'][$i];

            $xls[$i] = $_FILES['files']['tmp_name'][$i];

            $excelReader[$i] = \PHPExcel_IOFactory::createReaderForFile($xls[$i]);

            $excelReader[$i]->setReadDataOnly();

            $excelReader[$i]->setLoadAllSheets();

            $excelObj[$i] = $excelReader[$i]->load($xls[$i]);

            $row_max[$i] = $excelObj[$i]->getActiveSheet()->getHighestRow();
            $col_name[$i] = $excelObj[$i]->getActiveSheet()->getHighestColumn();
            $col_max[$i] = \PHPExcel_Cell::columnIndexFromString($col_name[$i]);


            for ($row[$i] = 2; $row[$i] <= 83; $row[$i] ++) {
                for ($col[$i] = 1; $col[$i] <= 52; $col[$i] ++) {
                    $colL[$i] = \geralComponents\substrings::subs_numb_by_letter($col[$i]);
                    $sheets[$i][$colL[$i] . $row[$i]] = $excelObj[$i]->getActiveSheet()->getCell($colL[$i] . $row[$i])->getCalculatedValue();
                }
            }

            self::make_sheet($sheets, $name);
        }
    }

    private static function make_sheet($sheets, $name) {
        $a = 0;

        foreach ($sheets as $file) {
            $plan = '';
            $OBJxlsMaker[$a] = new \PHPExcel();
            $OBJxlsMaker[$a]->setActiveSheetIndex(0);
            foreach ($file as $cell => $value) {
                if ($cell == 'B2') {
                    $OBJxlsMaker[$a]->getActiveSheet()->SetCellValue($cell, $value);
                }
                //AQUI É ONDE A PLANILHA NOVA É FEITA, MAS NÃO É AQUI O PROBLEMA
            }
            //O PROBLEMA É AQUI
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$name[$a].'.xls"');
            header('Cache-Control: max-age=0');

            $writer[$a] = \PHPExcel_IOFactory::createWriter($OBJxlsMaker[$a], 'Excel5');
            $writer[$a]->save('assets/converteds-xls/'.$name[$a]);

            $a += 1;
        }
    }

}

Well, the spreadsheet is generated, and saved in the folder 'assets/converteds-xls/', but I cannot force the download of this spreadsheet, replace the output with php:/output returns only the attributes of the file, name, size, etc. and all.

I need to generate the files in dynamic amount (works), save the files individually (works), I just can’t force the download of more than one file in the same request, someone would have some idea to do it ?

NOTE: This is not saved in the database, it only receives the upload, wipes the information, and the answer should be to download all the files uploaded again.

OBS 2 : The request comes via AJAX.

OBS 3 : If I generate only one file, reply with $writer->save('php://output'); force the download correctly.

Answer 1

First, you cannot make multiple responses to an HTTP request:

foreach ($sheets as $file) {

    ...

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$name[$a].'.xls"');
    header('Cache-Control: max-age=0');

    ...
}

It doesn’t even make sense how HTTP works, HTTP is request and response, not multiple responses.

Another thing, Ajax won’t interpret things like:

• Content-Disposition: attachment
• Content-Type: application/vnd.ms-excel

The browser can even see how this, but Ajax will not interact with the download manager, Ajax is this, take the UNICA HTTP response of a specific request and save in a variable, and then you decide what to do with the response.

How to solve

You could all generated files in assets/converteds-xls/ in the current request and generate a response pro ajax like this:

[
    'assets/converteds-xls/file200.xls',
    'assets/converteds-xls/file201.xls',
    'assets/converteds-xls/file202.xls'
]

Must send Content-type: application/json if you are using jQuery to facilitate "parse" and then in Ajax to do this:

function forceDownload(files) {
    var current = 0;

    function trigger() {
        var a = document.createElement('a');
        a.href = files[current];
        a.download = true;
        a.click();

        current++; //Proximo download

        setTimeout(trigger, 500); //Timeout necessário
    }

    trigger();
}

var jqxhr = $.ajax( "gerarxls.php" ).done(function(xls) {
    forceDownload(xls);
}).fail(function() {
    alert( "error" );
});

Compacting in the back-end

You can also compress the generated files using Ziparchive

$caminho = 'assets/converteds-xls/';

$zip = new ZipArchive;

$zipfile = 'pasta_foo_bar/[NOME TEMPORARIO].zip';

$new = $zip->open($zipfile, ZIPARCHIVE::CREATE);

$zip->addFile($caminho . $entry, '[nome do xls].xls');

$zip->close();

If it is a Linux server it may be more efficient to use exec, something like:

exec('tar -czf [NOME TEMPORARIO].tar.gz file1 file2 file3'); //Compacta tar.gz

And then use readfile PHP to download the [NOME TEMPORARIO].tar.gz or use header('Location: [NOME TEMPORARIO].tar.gz');, if you are Ajax you will have to make use of something like in . php:

echo 'pasta_foo_bar/[NOME TEMPORARIO].tar.gz';

And in Ajax’s answer:

var jqxhr = $.ajax( "gerarxls.php" ).done(function(resposta) {
    var a = document.createElement('a');
    a.href = resposta; //Resposta que contem a url do compactado
    a.download = true;
    a.click();
}).fail(function() {
    alert( "error" );
});