Error in Maatwebsite to export excel file in laravel

Remove the toArray(); from this line

 $sales = Sales_details::get()->toArray();

And please use camelcase and sigular form for you class names SalesDetail instead of the mix Sales_details

@Tray2 thank you so much it worked but

there is numbers in the first row why?? like this 1,2,3,...

Refer : https://imgur.com/a/Trv0wp5

what is the probem

also suggest an idea need last row as total of the columns in the excel file that is the total column must be fully added and the result must be displayed below the last row in the excel

Never converted to excel in Laravel but it must be some parameter that needs be set to remove them.

If i'm not mistaking, Maatwebsite excel will automatically add the headers. So you now have 2 small bugs:

1. You are trying to set headers yourself:

$sales_array[]=array('Date','Time','Customer Id','Customer Type','Customer Name','Customer Location','customer Area','Customer Booth','Emplyee','Rate per litre','No of litre','Total');

This is the reason you have that first row (it's using the keys of this array to create the headers, 0, 1, 2, 3 etc.)

2 ) Mind the spaces in the next part of your array:

    $sales_array[]=array(
       //  All of these keys have extra spacing in front of the name.... 
       ' Date'=>$e->date,
       ' Time'=>$e->time,
       ' Customer Id'=>$e->customer_id,
       ' Customer Type'=>$e->customer_type,
       ' Customer Name'=>$e->customer_name,
       ' Customer Location'=>$e->customer_location,
       ' customer Area'=>$e->customer_area,
       ' Customer Booth'=>$e->customer_booth,
       ' Emplyee'=>$e->emp,
       ' Rate per litre'=>$e->rate_per_litre,
       ' No of litre'=>$e->no_of_litre,
       ' Total'=>$e->total
    );

I think this should work:

    $sales = Sales_details::get()->toArray();

    $sales_array = array();

    foreach($sales as $e)
    {
        $sales_array[] = array(
           'Date'=>$e->date,
           'Time'=>$e->time,
           'Customer Id'=>$e->customer_id,
           'Customer Type'=>$e->customer_type,
           'Customer Name'=>$e->customer_name,
           'Customer Location'=>$e->customer_location,
           'Customer Area'=>$e->customer_area,
           'Customer Booth'=>$e->customer_booth,
           'Emplyee'=>$e->emp,
           'Rate per litre'=>$e->rate_per_litre,
           'No of litre'=>$e->no_of_litre,
           'Total'=>$e->total
        );
    }


    Excel::create('salesdet', function($excel) use ($sales_array) {
            $excel->sheet('mySheet', function($sheet) use ($sales_array)
            {
                $sheet->fromArray($sales_array);
            });
        })->download($type);

@lostdreamer_nl thank youu sooo much it worked.

But another small doubt

wherever there is 0 in my table that are not displayed in the excel file. those columns are empty.

if values is there in the table then the column is filled. if values is zero then those columns are empty and kindly suggest to sum the columns and it must display in the last row in the excel

sum the no_of _litres and total column and must display in the last row..

@lostdreamer_nl Kindly reply for this please

I'd probably just use PHP to calculate and add the values of the sums to those rows:

    $sales = Sales_details::get()->toArray();

    $sales_array = array();
    $total = 0;
    foreach($sales as $e)
    {
        $sales_array[] = array(
           'Date'=>$e->date,
           'Time'=>$e->time,
           'Customer Id'=>$e->customer_id,
           'Customer Type'=>$e->customer_type,
           'Customer Name'=>$e->customer_name,
           'Customer Location'=>$e->customer_location,
           'Customer Area'=>$e->customer_area,
           'Customer Booth'=>$e->customer_booth,
           'Emplyee'=>$e->emp,
           'Rate per litre'=>$e->rate_per_litre,
           'No of litre'=>$e->no_of_litre,
           'Total'=>$e->total
        );
        $total += $e->total;
    }
    $sales_array[] = array(
       'Date'=>'',
       'Time'=>'',
       'Customer Id'=> '',
       'Customer Type'=>'',
       'Customer Name'=>'',
       'Customer Location'=>'',
       'Customer Area'=>'',
       'Customer Booth'=>'',
       'Emplyee'=> '',
       'Rate per litre'=>'',
       'No of litre'=>'',
       'Total'=> $total
    );


    Excel::create('salesdet', function($excel) use ($sales_array) {
            $excel->sheet('mySheet', function($sheet) use ($sales_array)
            {
                $sheet->fromArray($sales_array);
            });
        })->download($type);

You could also add an actual function to the excel sheet, but I hate calculating the row and column for that:

    $sales = Sales_details::get()->toArray();

    $sales_array = array();
    foreach($sales as $e)
    {
        $sales_array[] = array(
           'Date'=>$e->date,
           'Time'=>$e->time,
           'Customer Id'=>$e->customer_id,
           'Customer Type'=>$e->customer_type,
           'Customer Name'=>$e->customer_name,
           'Customer Location'=>$e->customer_location,
           'Customer Area'=>$e->customer_area,
           'Customer Booth'=>$e->customer_booth,
           'Emplyee'=>$e->emp,
           'Rate per litre'=>$e->rate_per_litre,
           'No of litre'=>$e->no_of_litre,
           'Total'=>$e->total
        );
    }


    Excel::create('salesdet', function($excel) use ($sales_array) {
            $excel->sheet('mySheet', function($sheet) use ($sales_array)
            {
                $sheet->fromArray($sales_array);

                $columns = range('A', 'Z');
                $column = count($sales_array[0]) - 1;  // get number of last column
                $column = $columns[$column]; // change it from number to char
                $row = count($sales_array); // get the number of the row we want to add this

                $sheet->setCellValue($column .  $row, '=SUM('. $column .'2:'. $column . ($row-1) );
            });
        })->download($type);

@lostdreamer_nl

is this that much difficult to sum the columns, why cant we use the same code used for pdf

 $pdf=PDF::loadView('Sales_details.view1', [
        'sales' => $sales,
        'total1' => $sales->sum('total'),
        'no_of_litre1' => $sales->sum('no_of_litre')
    ]);

the total1 can be added with array. it is possible??

and another doubt the zero values are empty in my excel

how to solve this

@AbdulBazith Yes, you can use the $sales->sum('total') (I forgot to check that $sales already had a collection)

For the zero values, BobbyBouwman gave a good answer on that in your other topic:

 $sheet->setColumnFormat([
    'B' => '0', // Column B is number
    'C' => '0', // Column C is number
    'D' => '$#,##0_-', // Column D is currency
]);

@lostdreamer_nl BobbyBouwman answer not worked

see that question, i have mentioned where i added the code

and even though the cells are empty

Kindly help me get out from this

and another doubt if i can use $sales->sum('total') then where should i place it..

i need it in the end of the (rate_per_litre and total) columns refer the image and also see the empty columns in that image

refer: https://imgur.com/a/FHK4LHo

@lostdreamer_nl

what should i do please suggest some idea..

also refer i have pasted another question

question: Problem in performing Excel conversion through ajax call in laravel

please suggest idea for this alsoo

@AbdulBazith try changing

$sheet->fromArray($sales_array);

with this one

$sheet->fromArray($sales_array, null, 'A1', false, false);

this will add header

@somnathsah i added the headers

my problem is wherever the data is zero in table that are empty in the excel file. how to solve this.

and how to add total in the excel sheet??

Kindly refer the above conversation fully

@lostdreamer_nl thankz for your answers

i got the answers for both null value and total.

for null value

instead of doing

 $sheet->fromArray($ex_array);

do like this

$sheet->fromArray($ex_array, null, 'A1', true);
it works..!!

for total your answer worked.. thank you sooo much

@lostdreamer_nl

 $sales = Sales_details::get()->toArray();

    $sales_array = array();
    $total = 0;
    foreach($sales as $e)
    {
        $sales_array[] = array(
           'Date'=>$e->date,
           'Time'=>$e->time,
           'Customer Id'=>$e->customer_id,
           'Customer Type'=>$e->customer_type,
           'Customer Name'=>$e->customer_name,
           'Customer Location'=>$e->customer_location,
           'Customer Area'=>$e->customer_area,
           'Customer Booth'=>$e->customer_booth,
           'Emplyee'=>$e->emp,
           'Rate per litre'=>$e->rate_per_litre,
           'No of litre'=>$e->no_of_litre,
           'Total'=>$e->total
        );
        $total += $e->total;
    }
    $sales_array[] = array(
       'Date'=>'',
       'Time'=>'',
       'Customer Id'=> '',
       'Customer Type'=>'',
       'Customer Name'=>'',
       'Customer Location'=>'',
       'Customer Area'=>'',
       'Customer Booth'=>'',
       'Emplyee'=> '',
       'Rate per litre'=>'',
       'No of litre'=>'',
       'Total'=> $total
    );


    Excel::create('salesdet', function($excel) use ($sales_array) {
            $excel->sheet('mySheet', function($sheet) use ($sales_array)
            {
                $sheet->fromArray($sales_array);
            });
        })->download($type);

@AbdulBazith

There's still a bug in that code ;) I dont think this can work:

$sales = Sales_details::get()->toArray();
foreach($sales as $e) {
    $sales_array[] = array(
        'Date'=>$e->date,
        ....
        ....
    );
}

You get the result as an array, but are using it as an object.... That will simply throw errors. (we all copy pasted that code in all coments :p)

Simply remove the ->toArray() part on that first line and it should work.

@lostdreamer_nl yaa you are right..!!

forgot that..

another doubt.

Refer this in my thread:

Problem in performing Excel conversion through ajax call in laravel -- > in this question i mentioned with image

everything is fine in my excel conversion.

but i did it in ajax. the file is not downloading.. in inspect->network i need to double click that for download whats the problem

this is my ajax

$('#excel').on('click',function(){

alert("t");
$.ajax({
    dataType: 'json',
    type : 'get',
    url : '{{URL::to('downloadexcel_paid_details')}}',

    data:{
        'search_name': $('#search_name').val(),
                'search_location': $('#search_location').val(),
                'search_booth': $('#search_booth').val()
    },


    success:function(data)
    {
        console.log("welcome");

        alert('success excel');

    }

    });

    alert('Outside');

})

it moves to controller, and excel is converted but it is not downloading

i thing there is no response to ajax succes thats the reason i think soo..

becoz the alert message in my success function also not displayed

this is my controller

 $ex = Paid_details::get()

 $ex_array = array();


     $total_total_amount =0;
     $total_paid =0;
     $total_balance_amount =0;
     $total_personal_amount=0;

           foreach($ex as $e)
       {

           $ex_array[]=array(

               'Vendor Name'=>$e->vendor_name,
               ' Vendor Location'=>$e->vendor_location,
               'Vendor Booth'=>$e->vendor_booth,
               ' Total Amount'=>$e->total_amount,
               ' Paid'=>$e->paid,
               'Balance Amount'=>$e->balance_amount,
               'Personal Amount'=>$e->personal_amount

           );
            $total_total_amount += $e->total_amount;
            $total_paid += $e->paid;
            $total_balance_amount += $e->balance_amount;
            $total_personal_amount += $e->personal_amount;



       }

       $ex_array[]=array(

        'Vendor Name'=>'',
        ' Vendor Location'=>'',
        'Vendor Booth'=>'',
        'total_total_amount' => $total_total_amount,
        'total_paid' => $total_paid,
        'total_balance_amount' => $total_balance_amount,
        'total_personal_amount' => $total_personal_amount
    );
       Excel::create('PaidDetails', function($excel) use ($ex_array) {
                   $excel->sheet('mySheet', function($sheet) use ($ex_array)
                   {

                    // $sheet->fromArray(array($ex_array),true);


                    $sheet->fromArray($ex_array, null, 'A1', true);

                   });
              })->download('xlsx');                      

       }

in pdf it works.. the download is done perfectly

this is my pdf controller

public function downloadpdf_paid_details(Request $request)
 $paid_details=Paid_details::get();
    

        $pdf=PDF::loadView('Overall.paid_detailspdf', [
            'paids' => $paid_details,
            'name'=>$request->search_name,
            'location' =>$request->search_location,
            'booth'=>$request->search_booth,
            'total_total_amount'=>$paid_details->sum('total_amount'),
            'total_paid'=>$paid_details->sum('paid'),
            'total_balance'=>$paid_details->sum('balance_amount'),
            'total_personal'=>$paid_details->sum('personal_amount')
        ]);

       $pdf->setPaper('A4','landscape');
        return $pdf->download('Paid Details.pdf');

       }

this is my ajax for pdf

$('#pdf').on('click',function(){


$.ajax({
    type : 'get',
    url : '{{URL::to('downloadpdf_paid_details')}}',

    data:{
        'search_name': $('#search_name').val(),
                'search_location': $('#search_location').val(),
                'search_booth': $('#search_booth').val()
    },

    success:function(data)
    {
    }
    });

})