Download maatwebsite excel using ajax in laravel

Question

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!

Bouilli

Answer 1

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

Saprophyte

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? – Stipel

Answer 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);
        }
    });
}

Istria

Answer 3

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;
}

Bihari

Answer 4

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) {
                //
            }
        });

Thionic

Answer 5

// 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]);
    }

Beals

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. – Action