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

[IanPercival]

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)

[IanPercival]

Also thank you in advance :)

Especially for taking the time to write this wrapper to begin with

[patrickbrouwers]

I'm not sure if it's still the same problem with PhpSpreadsheet, but PHPExcel had problems with having spaces ( ) in front of <?php open tags. That used to cause this error in the past.

[dark-wind]

u can open error file with nano or vim.
If you see a space before PK,just like this ：

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

[IanPercival]

Hey all, thanks for the lead.

I think you are correct that there is an extra line break or something at the start of the file. I haven't traced it yet but when I switch to CSV, I can open the file and the data is there but the first row is blank....

Going to look some more, ill report back. I grepped for a file starting with a line break and then <?php but it didn't yield anything.

[IanPercival]

I am going to close this out and open a new one with a better description of the problem. I can't find the source of the blank row

[PhamLinh0889]

i met same issue. i fixed by

• Excel::store to store file on server
• return redirect( Storage::url("storage/{$filename}" ));
  sample

public function export(Request $request)
    {

        $filename = 'meal_list_'.date('YmdHis').'.xlsx';
         Excel::store(new MealExport($request), $filename, 'public');
         return redirect( Storage::url("storage/{$filename}" ));
    }

[Leenzuur]

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.

[sanjaysamant]

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

[SwithFr]

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 :

Any idea how to fix this ?

Thx !

[Midun555]

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.

Love You man.. You saved my head...

[huynhlehoangduc]

I'm not sure if it's still the same problem with PhpSpreadsheet, but PHPExcel had problems with having spaces ( ) in front of <?php open tags. That used to cause this error in the past.

Hello @patrickbrouwers
Thanks for your comment.
Solves for me.

[ductv-vega]

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

[jkteoh]

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

Thank!! save my life, i keep looking at which line of php code that corrupted my exported file.. turn out is axios

[VuongTran21]

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 @Leenzuur , you saved my day! It worked! I wonder is there any any to fix this but @Leenzuur way, you know what's I mean, if I have 10 Export class for 10 Model, when download, I have to add in 10 places.

[Naghal]

This problem started to happen to me today with no change to any export. Using @Leenzuur code sample fixed it after hours of research.

[ibnfarouk]

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.

This worked for me .. thanks <3

[Otienoh]

You should create a symbolic link at public/storage which points to the storage/app/public directory every time you deploy
Add it to the "@php artisan storage:link --force" to your composer post-autoload-dump script so that you have this

 "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi",
            "@php artisan storage:link --force",
            "@php artisan view:clear"
        ],

This should fix it without the need of changing your code

[Mikeonii]

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.

This solves all my problems. Also, if you cant install PHP spreadsheet and composer because it needs all the things u don't know, try adding --ignore-platform-reqs in your composer require/update

[bureaug]

Clearing the buffer was throwing a different error. No empty spaces before <?php have been found. With trial, error and luck I've arrived at this solution.

Instead of
return Excel::download($exportedItems, $fileName.'.xlsx');

I now have
return Excel::download($exportedItems, $fileName.'.xls', \Maatwebsite\Excel\Excel::XLS);

however my frontend is still giving it as a .xlsx file back (blob with an extension). I don't know why and how, really, but the problem is solved now.
Interestingly, having this line (an option to export as a PDF)

return Excel::download($exportedItems, $fileName.'.pdf', \Maatwebsite\Excel\Excel::MPDF);

was getting a pretty uncorrupted PDF, every time. So it's either the added format or masking an .xls file as a .xlsx and making Excel work it out on its own.

[codingstudio38]

function index(Request $req){
$users = DB::table('tbl_users')->select('id','name','email','address')->get(); //my data colection
ob_end_clean(); // use this
ob_start(); // and this
return Excel::download(new NewUserExport($users),'usersList.xlsx',\Maatwebsite\Excel\Excel::XLSX);
}

[elvismatus]

If you are working with jquery:

// In controller:
return Excel::download(new SomeExport, 'Some_Report.xlsx', null, [\Maatwebsite\Excel\Excel::XLSX]);

// Ajax:
$.ajax({
type: 'GET',
url: '{{ route("some.route") }}',
data: {
"_token": "{{ csrf_token() }}"
},
xhrFields:{
responseType: 'blob'
},
beforeSend: function() {
//
},
success: function(data) {
var url = window.URL || window.webkitURL;
var objectUrl = url.createObjectURL(data);
window.open(objectUrl);
},
error: function(data) {
//
}
});

[dev-abrascort]

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! I used on
namespace Maatwebsite\Excel;

Solved all my download actions.

[fernandokbs]

Not sure why but I solved, storing the file

Excel::store(new PaymentsExport($date), 'payments.xlsx', 'public');

and then redirecting to the file.

return redirect('storage/payments.xlsx');

[Ribbon-Brooke]

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

Any idea how to fix this ?

Thx !

@SwithFr did you ever find the solution for this one?

[SwithFr]

@Ribbon-Brooke Sorry don't remember and I don't have project sources anymore :/