skip to Main Content

I have recently installed PhpSpreadsheet using composer, I am using php 7.4.

I am trying to open an excel file, change some of the cell values and then save the file (download to client) with these changes

after some research and adding the headers to accept a .xlsx file and for the auto-download to work, this worked perfectly, however only once. without changing anything and running the same the next day, nothing seemed to be happening in the background. it would appear that the IOfactory::load works to read the file, but the auto-download is not happening anymore.

I tried to log the loaded $spreadsheet to the console from the client side via javascript and could see the sheetml gibberish. This tells me that the loader is working and the writer is working, perhaps the header for the download is wrong, but I can’t see anything wrong with it when compared to numerous online examples. I am flabbergasted as to why it could work only once and am now not sure where the problem could be.

Any help with figuring out why this would work and then stop working without changing anything would be appreciated.

excelCompleteExport.php:

require 'PHPSpreadsheet/vendor/autoload.php';

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetIOFactory;
use PhpOfficePhpSpreadsheetWriterXlsx;

$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load("NedbankBBCommTemplate_REMSYSTEM.xlsx");

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="result2.xlsx"');

set_error_handler (
    function($errno, $errstr, $errfile, $errline) {
        throw new ErrorException($errstr, $errno, 0, $errfile, $errline);     
    }
);

try
{
    PhpOfficePhpSpreadsheetSharedFile::setUseUploadTempDirectory(true);
    $writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
    //echo "<script type='text/javascript'>console.log('done');</script>";
}

catch(Exception $e) 
{
    echo "<script type='text/javascript'>console.log('".$e->getMessage()."');</script>";
}

call from javascript:

function ExpExcel()
{
    $("#InvisElement").load("excelCompleteExport.php");
}

HTML button call to javascript function:

<button id="ExpExcel" style="width: 200px;" class="navbtn" type="submit" onclick="ExpExcel(); return false;">Export to Excel</button>

2

Answers


  1. Chosen as BEST ANSWER
    function ExpExcel()
    {
    $.ajax({
    type: 'GET',
    cache: false,
    url: "excelCompleteExport.php",
      
    xhrFields: {
            // make sure the response knows we're expecting a binary type in 
    return.
            // this is important, without it the excel file is marked corrupted.
            responseType: 'arraybuffer'
        }
    })
        .done(function (data, status, xmlHeaderRequest) {
            var downloadLink = document.createElement('a');
            var blob = new Blob([data],
                {
                    type: xmlHeaderRequest.getResponseHeader('Content-Type')
                });
            var url = window.URL || window.webkitURL;
            var downloadUrl = url.createObjectURL(blob);
            var fileName = 'result.xlsx';
    
          
    
            if (typeof window.navigator.msSaveBlob !== 'undefined') {
                window.navigator.msSaveBlob(blob, fileName);
            } else {
                if (fileName) {
                    if (typeof downloadLink.download === 'undefined') {
                        window.location = downloadUrl;
                    } else {
                        downloadLink.href = downloadUrl;
                        downloadLink.download = fileName;
                        document.body.appendChild(downloadLink);
                        downloadLink.click();
                    }
                } else {
                    window.location = downloadUrl;
                }
    
                setTimeout(function () {
                    url.revokeObjectURL(downloadUrl);
                },
                    500);
            }
        });
    }
    

  2. Autodownloads is mostly restricted by the browser (prevent malware downloads). You can try to open a generated .xlsx-File in a new Tab via jQuery on the a link/button.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search