CODEWITHSUNDEEP
phpexcellaravel-5exportmaatwebsite-excel
06011991
06011991

Reputation: 807

Excel download - Laravel 5.1

I am using eloquent query with three tables called invoice,invoiceduedates,payments.So all the data i am exporting to excel by using the below query in laravel.

$pay=Invoice::with('invoiceduedates','payments')->where('Eventcode','=',$eventcode)->get();
$payment='';$invoicepay=array();
foreach($pay as $payble){
            $x=0;$due='';$payment='';$i=0;
            foreach($payble->invoiceduedates as $duedate){
                        $x++;
                        $due .= $duedate->date.','.$duedate->amount;
                        if($x <= count($payble->invoiceduedates)-1){
                        $due.=",";
                        }


            }

            foreach($payble->payments as $paydate){
                        $i++;
                        $payment .= $paydate->adjust_mode.','.$paydate->recieved_amount;
                        if($i <= count($payble->payments)-1){
                        $payment.=",";
                        }


            }

            $invoicepay[]= array_merge(explode(',',$due),explode(',',$payment));
            unset($due);unset($payment);unset($i);unset($x);
}
$export = json_decode(json_encode((array) $invoicepay), true);

          Excel::create('Data', function($excel) use ($export)
             {
                $excel->sheet('Inovice Data', function($sheet) use ($export)
                {
                    $sheet->fromArray($export);

                    $sheet->cells('A1:AE1', function($cells)
                     {

                                $cells->setBackground('#000000');
                                $cells->setFontColor('#fff');

                    });
                    $sheet->row(1, array(
                        'Due Date1','Due Amount1','Due Date2','Due Amount2','AdjustMode1','Rcv Amount1','AdjustMode2','Rcv Amount2'

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

Below is my excel results:

| Due Date1  | Due Amount1 | Due Date2             | Due Amount2 | AdjustMode1           | Rcv Amount1 | AdjustMode2           | Rcv Amount2 |
|------------|-------------|-----------------------|-------------|-----------------------|-------------|-----------------------|-------------|
| 2016-03-25 | 2000        | 2016-02-29            | 2000        | Overseas Bank Charges | 2000        | Overseas Bank Charges | 2743        |
| 2016-03-31 | 3750        | Overseas Bank Charges | 3708        | Overseas Bank Charges | 2750        |                       |             |

But whats happening here is when there is no second due dates in invoiceduedates table the columns of Due Date2, Due Amount2 are getting overlapped with Adjust Mode1 and RCV Amount1.

The Actual excel how i want is below 

| Due Date1  | Due Amount1 | Due Date2  | Due Amount2 | AdjustMode1           | Rcv Amount1 | AdjustMode2           | Rcv Amount2 |
|------------|-------------|------------|-------------|-----------------------|-------------|-----------------------|-------------|
| 2016-03-25 | 2000        | 2016-02-29 | 2000        | Overseas Bank Charges | 2000        | Overseas Bank Charges | 2743        |
| 2016-03-31 | 3750        |            |             | Overseas Bank Charges | 3708        | Overseas Bank Charges | 2750        |

Ya when there is no second due date i want those columns to be empty.How can i control that in foreach loop or in header part of excel? Please help .

Thank you.

Upvotes: 0

Views: 889

Answers (1)

weigreen
weigreen

Reputation: 1242

Your problem is that you didn't give $export empty space

So maatwebsite-excel's ->fromArray() function will put data into excel one by one

These code can fix your problem:(part of your code)

        $x=0;$due='';$payment='';$i=0;
        foreach($payble->invoiceduedates as $duedate){
                    $x++;
                    $due .= $duedate->date.','.$duedate->amount;
                    if($x <= count($payble->invoiceduedates)-1){
                    $due.=",";
                    }


        }
        //added
        if($x == 1){
            $due.=",,"; //add two empty column for  Due Date2 & Due Amount2
        }

        foreach($payble->payments as $paydate){
                    $i++;
                    $payment .= $paydate->adjust_mode.','.$paydate->recieved_amount;
                    if($i <= count($payble->payments)-1){
                    $payment.=",";
                    }


        }
        //added
        if($i == 1){
            $payment.=",,"; //add two empty column for  AdjustMode2 & Rcv Amount2
        }

Upvotes: 1

Related Questions