Level 56
Try using the xlsx extension instead.
xls is an extension for Excel files created with versions before 2003, so I am assuming most online viewers won't bother to support those.
1 like
Feb 22, 2022
20
Level 2
How to download created excel file with right format??!!
Hi. Please some one help me with this problem. I am using PhpSpreadsheet and making and downloading a simple excel file using following code:
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
public function test(Request $req){
$spreadsheet = new Spreadsheet;
$active_sheet = $spreadsheet->getActiveSheet();
$active_sheet->setCellValue('A1', 'Firstname');
$active_sheet->setCellValue('A2', 'Alex');
$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$filename = "alex.xls";
$writer->save(base_path($filename));
return response()->download(base_path('alex.xls'));
}
The process creates a simple excel file with 2 rows and one column. The created file works great and I can show it on both local and web application excel viewers. However when I upload downloaded version of the same file, excel viewers say "Upload excel file with right format". I also tried headers on download method of Reponse and set it to `application/vnd.ms-excel` and also tried some other headers but none work.
Pleaes help me fix it, thank you.
Level 2
@rodrigo.pedra Thank you for response. I did but it also does not work
Level 56
Did you change it on all 3 places?
public function test(Request $req){
$spreadsheet = new Spreadsheet;
$active_sheet = $spreadsheet->getActiveSheet();
$active_sheet->setCellValue('A1', 'Firstname');
$active_sheet->setCellValue('A2', 'Alex');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // <<< HERE
$filename = "alex.xlsx"; // <<< HERE
$writer->save(base_path($filename));
return response()->download(base_path('alex.xlsx')); // <<< HERE
}
Also there is a package called laravel-excel that wraps PhpSpreadsheet in a more Laravel friendly API
Level 2
@rodrigo.pedra Yes I did in all 3 places. I know about other package. But here this works well and creates a healthy excel file. That file is easily viewable on all platforms. The problem is downloaded file.
Level 56
Just tested the code above and the downloaded file opens in Office 365's Excel:
1 like
Level 56
One other detail, do no save files on you base path. Use the storage path instead:
// ...
$writer->save(storage_path('app/'. $filename));
return response()->download(storage_path('app/alex.xlsx'));
The base path should no be writable in production, to prevent remote code execution and other issues.
1 like
Level 2
@rodrigo.pedra This is so weird! I am doing the same on muliple online viewers and it is not working. I also passed file to my friend and tested on local excel software! Thank you
Level 56
@danimohamadnejad really weird... I just searched on DuckDuckGo for online excel viewer and tried the same file on the first result:
And it loaded fine... Not sure what the problem can be.... Sorry
1 like
Level 2
@rodrigo.pedra Thank you. It still does not work for me
Level 56
@danimohamadnejad I thought of a possibility
Are your PHP files encoded in UTF-8 without BOM?
I tried changing the file encoding of the test file I created and when I tell the editor to save it as UTF-8 with BOM (Byte Order Mask) the downloaded excel file is broken.
Not very likely to be it, as most modern code editors or IDE default to UTF-8 without BOM by default, but you could try checking it out.
1 like
Level 2
@rodrigo.pedra I tried my solution on a fresh laravel installation and it works. I don't know why but it seems response()->download() does not download file with right format on my current project only. Do you have any opinion about this?
Level 122
@danimohamadnejad Maybe if you have a file somewhere in your application which starts with an odd character other than <?php This would cause any browser output to be prefixed with a character
2 likes
Level 2
@Snapey Hi. Thank you for response. Maybe it is as you say but code base is large and finding such characters is hard if they exist at all
Level 122
@danimohamadnejad a way to test ids to create a route that just returns 'hello' and then view the network response in the browser tools. Check if 'hello' is the only thing returned
If it isn't, then you would need to only look at files that are loaded on every request, such as routes, service providers, middleware, http kernel etc
1 like
Level 2
@Snapey Thank you a lot. You were right. I do not know where I am doing some output but inside test method first line, I put ob_end_clean(); and then created and downloaded excel file and it works fine. I also created a route and returned "hello" but it's not printing anywhere in network tab
Level 122
@danimohamadnejad i'm guessing your server uses nginx. many times i have seen this on apache, and then apache refuses to send cookies which causes other session related issues.
1 like
Level 2
@Snapey I am running this on a linux local machine. I can see that apache2 is active and there is no sign of nginx
Level 1
you may want to provide the version type of Excel.
// Use PHPExcel Writer to output .xlsx file
$writer = PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007'); // 'Excel2007' is compatible with Excel 365
In addition, it can be good practice to send headers with your request:
// Set headers for downloading an .xlsx file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // for latest Excel format for Excel 365.
header('Content-Disposition: attachment;filename="spreadsheet.xlsx"');
when making requests, it may be best to wrap everything in a try {} catch{} block in dev environment given it is an external library you are using. therefore if an error occurs, the caller/requestor (code snippet) can handle the errors.
Level 122
guys, it was fixed 2 years ago...
Level 7
@Snapey Shhhh ... they don't know that.
Please or to participate in this conversation.