6

I'm trying to download an excel file using ajax method in laravel. Controller function:

$myFile = Excel::create($name, function ($excel) use ($export) { $excel->sheet('Data', function ($sheet) use ($export) { $sheet->fromArray($export); $sheet->cells('A1:N1', function ($cells) { $cells->setBackground('#dbdbdb'); $cells->setFontColor('#000000'); $cells->setFontWeight('bold'); $cells->setFont(array( 'family' => 'Calibri', 'size' => '9', )); }); $sheet->setStyle(array( 'font' => array( 'name' => 'Calibri', 'size' => 9, ), )); }); }); $myFile = $myFile->string('xlsx'); $response = array( 'name' => $name, 'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," . base64_encode($myFile), ); return response()->json($response);

Ajax function:

$(document).on('click', '.ExportJobs', function() { $.ajaxSetup({ headers: { 'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content') } }); var ids = []; $(".InvoiceCheckBox:checked").each(function(e) { ids.push(this.value); }); data = { "ids": ids, }; $.ajax({ method: "POST", url: "/exportNew", data: data, success: function(response) { var a = document.createElement("a"); a.href = response.file; a.download = response.name; document.body.appendChild(a); a.click(); a.remove(); } }); });

But using above controller method is not returning excel formatted file if I change string value from xlsx to csv then csv formatted file is getting downloaded.

How do we make the excel formatted file downloaded? Any suggestions, Please!

Improve this question

5 Answers 5

Reset to default
19

I know this is quite late, but posting for others who struggle with same issue like me

I also needed to download excel from using Maatwebsite excel library by using ajax post call.

  1. added a button to fire the ajax call to download excel file 

     <button onclick="downloadExcel()" id="btn-download-payroll" class="btn btn-dark-success btn-md" style="transform: translateY(50%); top: 50%; font-size: 13px;"><i aria-hidden="true" class="fa fa-cog mr-10"></i> Download </button>

Used following js code to post ajax request

function downloadExcel() { var salaryMonth = $("#dp-salary-month").datepicker("getDate"); var department = $("#cbox-department"); var month = new Date(salaryMonth).getMonth() + 1; var year = new Date(salaryMonth).getFullYear(); $.ajax({ xhrFields: { responseType: 'blob', }, type: 'POST', url: '/downloadPayroll', data: { salaryMonth: month, salaryYear: year, is_employee_salary: 1, department: department.val() }, success: function(result, status, xhr) { var disposition = xhr.getResponseHeader('content-disposition'); var matches = /"([^"]*)"/.exec(disposition); var filename = (matches != null && matches[1] ? matches[1] : 'salary.xlsx'); // The actual download var blob = new Blob([result], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); var link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = filename; document.body.appendChild(link); link.click(); document.body.removeChild(link); } }); }

in routes/web.php file set the reoute for my controller

Route::post('/downloadPayroll', 'Payroll\\Process\\PayrollController@downloadPayroll');

Here I used maatwebsite/excel library to generate excel file with FromQuery approach but due to library update Excel::create has been replaced by Excel::download in "maatwebsite/excel": "^3.1" I used download method in my case here is my HelperClass to generate records according to my requirement

PayrollHelper.php

namespace App\Http\Helpers; use App\PayrollEmployee; use Maatwebsite\Excel\Concerns\FromQuery; use Maatwebsite\Excel\Concerns\Exportable; class PayrollHelper implements FromQuery { use Exportable; public function forDepartment(int $department) { $this->department = $department; return $this; } public function forMonth(string $month) { $this->month = $month; return $this; } public function query() { // get the salary information for the given month and given department return PayrollEmployee::query()->where(['salary_month' => $this->month,'department_id'=>$this->department]); } }

finally in my controller

class PayrollController extends Controller { public function downloadPayroll(Request $request) { $file_name = ''; try { $requestData = $request->all(); $salary_month = $requestData['salaryMonth']; $salary_year = $requestData['salaryYear']; $department = $requestData['department']; $is_employee_salary = boolval($requestData['is_employee_salary']); $month = Carbon::createFromDate($salary_year, $salary_month); $month_start = Carbon::parse($month)->startOfMonth(); $formated_month = Carbon::parse($month)->format('F Y'); $file_name = 'Employee_salary_' . $formated_month . '.xlsx'; // to download directly need to return file return Excel::download((new PayrollHelper)->forMonth($month_start)->forDepartment($department), $file_name, null, [\Maatwebsite\Excel\Excel::XLSX]); } catch (exception $e) { } } }

After creating excel file return file to get as ajax response as blob

That's all

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I got "Uncaught DOMException: XMLHttpRequest.responseText getter: responseText is only available if responseType is '' or 'text'." if the returned data is from view(), any way to do it?
2

Just see the xhrFields to set responseType as blob and then see the ajax success part. Hope you everyone find the solution:

<script> $(document).ready(function(){ $("#ExportData").click(function() { dataCaptureExport(); }); }); function dataCaptureExport(){ var FromDate = $('#dateFrom').val(); var ToDate = $('#dateTo').val(); var dataString = { FromDate: FromDate, ToDate:ToDate, _token: '{{csrf_token()}}'}; $.ajax ({ type: "POST", url: '{{ route('invoice_details_export') }}', data: dataString, cache: false, xhrFields:{ responseType: 'blob' }, success: function(data) { var link = document.createElement('a'); link.href = window.URL.createObjectURL(data); link.download = `Invoice_details_report.xlsx`; link.click(); }, fail: function(data) { alert('Not downloaded'); //console.log('fail', data); } }); }
Improve this answer

Comments

2

It's late but help for others

You can do this way

In Ajax

 $(document).on('click', '#downloadExcel', function () { $("#downloadExcel").hide(); $("#ExcelDownloadLoader").show(); $.ajax({ url: '{{ route("admin.export_pending_submitted_tasks") }}', method: "GET", cache: false, data: { search_partner, search_state, search_city, _token, }, success: function (response) { var a = document.createElement("a"); a.href = response.file; a.download = response.name; document.body.appendChild(a); a.click(); a.remove(); $("#downloadExcel").show(); $("#ExcelDownloadLoader").hide(); }, error: function (ajaxContext) { $("#downloadExcel").show(); $("#ExcelDownloadLoader").hide(); alert('Export error: '+ajaxContext.responseText); } }); });

In Controller

 // Get pending submitted tasks export excel public function export_pending_submitted_tasks(Request $request){ $input = $request->input(); $pending_submitted_tasks = SubmittedTask::select('id', 'partner', 'se_id', 'description', 'created_at', 'status', 'updated_at'); (isset($input['search_partner'])) ? $pending_submitted_tasks->where('partner_id', $input['search_partner']) : ''; (isset($input['search_partner'])) ? $pending_submitted_tasks->where('state', 'like', '%'.$input['search_state'].'%') : ''; (isset($input['search_partner'])) ? $pending_submitted_tasks->where('city', 'like', '%'.$input['search_city'].'%') : ''; $pendingTaskList = $pending_submitted_tasks->where('status', 'pending')->get(); if($pendingTaskList->count() > 0): $myFile = Excel::raw(new ExportPendingTaskHelper($pendingTaskList), 'Xlsx'); $response = array( 'name' => "Pending-Task-List.xlsx", 'file' => "data:application/vnd.ms-excel;base64,".base64_encode($myFile) ); return response()->json($response); else: return back()->with('message', 'No Pending tasks available to download!!'); endif; }
Improve this answer

Comments

0

If you are using 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) { // } });
Improve this answer

Comments

0 
// Controller public function export() { $random = rand(100000, 999999); $fileName = 'fresh_data_'.$random.'.xlsx'; // Excel::store(new FreshDataExport, $fileName); Excel::store(new FreshDataExport, $fileName, 'public'); $filepath = Storage::url($fileName); return response()->json(['file' => $fileName, 'path' => $filepath]); }
Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.