vendredi 3 juin 2016

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.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire