SolvedLaravel Excel Error Opening file with Excel. Possible data corrupted or bad file extension.

Prerequisites

  • [ x] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
  • [x ] Checked that your issue isn't already filed.
  • [ x] Checked if no PR was submitted that fixes this problem.

Versions

  • PHP version: 7.1
  • Laravel version: 5.5.*
  • Package version: ^3.0

Description

Downloading file from a FromQuery method.... file downloads, upon opening XLSX file, receive an error message from Office:
"Excel cannot open the file <filename.xlxs> because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

Steps to Reproduce

Here is my method

namespace Modules\Profile\Exports;

use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Modules\Profile\Entities\Profile;

class ProfileExport implements FromQuery
{
    use Exportable;

    public function __construct($range)
    {
        $this->start = $range['start'];
        $this->end = $range['end'];
    }

    public function query()
    {
      return Profile::query()->where('created_at', '>=', $this->start . ' 00:00:00')
                             ->where('created_at', '<=', $this->end . ' 23:59:59')
                             ->whereHas('user.roles', function($query){
                                  $query->where('id', 2);
                             });
    }
}

I call it like this from a controller

//$request->start = '2018-01-01';
//$request->end = '2018-05-01';

$range = ['start'=>$request->start, 'end'=>$request->end];
return (new ProfileExport($range))->download($request->start . '-' . $request->end .'-pet-parent-signups.xlsx');

Expected behavior:

The file to be downloaded with data

Actual behavior:

The file downloads but Excel gives error opening it

Additional Information

I have tested the query in eloquent and it returns a collection with data.

(https://user-images.githubusercontent.com/18451450/40511758-f61e6570-5f5d-11e8-9f37-0b594f60be59.png)

17 Answers

✔️Accepted Answer

Hi everyone,

I spend my day to understand this unworkable download function. PHPSpreadSheet seems to use PHP buffer. And depending on your PHP configuration some warnings or errors could interfer with the output.

Using :

// in controller
ob_end_clean(); // this
ob_start(); // and this
return Excel::download(new MyExport, 'file.xls');

solves for me.
Maybe it should help some people.

Thank you for this awesome library.

Other Answers:

Hi everyone,

This solution :

ob_end_clean(); // this
ob_start(); // and this
return Excel::download(new MyExport, 'file.xls');

Works and solve the corrupt problem. But still have trouble with export using Xlsx/Xls.
The downloaded file looks like this :
image

Any idea how to fix this ?

Thx !

If you are using ajax to download,
please add responseType: 'blob' to your ajax request (I'm using axios)

My example code:

axios.post('/path/to/export', data, {
    responseType: 'blob',
})
.then(response => {
    const filename = 'file.xlsx';
    let blob = new Blob([response.data], {
        type: 'application/octet-stream',
    });

    if (typeof window.navigator.msSaveBlob !== 'undefined') {
        // IE workaround for "HTML7007: One or more blob URLs were
        // revoked by closing the blob for which they were created.
        // These URLs will no longer resolve as the data backing
        // the URL has been freed."
        window.navigator.msSaveBlob(blob, filename);
    } else {
        let blobURL = window.URL.createObjectURL(blob);
        let tempLink = document.createElement('a');
        tempLink.style.display = 'none';
        tempLink.href = blobURL;
        tempLink.download = filename;
        tempLink.click();
        window.URL.revokeObjectURL(blobURL);
    }
})

Without responseType: 'blob', the downloaded file can not be open

Hi everyone,

I spend my day to understand this unworkable download function. PHPSpreadSheet seems to use PHP buffer. And depending on your PHP configuration some warnings or errors could interfer with the output.

Using :

// in controller
ob_end_clean(); // this
ob_start(); // and this
return Excel::download(new MyExport, 'file.xls');

solves for me.
Maybe it should help some people.

Thank you for this awesome library.

THanks alot its perfect answer

u can open error file with nano or vim.
If you see a space before PK,just like this :
image
Maybe error happened in your code or vendor. I met the same problem and deal with remove vendor folder . It works well After reinstall the vendor

More Issues: