laravel-excel 3.1 with laravel framework 6.2 and php 7.4

Question

I have problem with laravel-excel 3.1 after i have been upgrade laravel version and php version i got some requirement from laravel to upgrade laravel-excel too. after that i got problem with excel-export not support new version(3.1) before i use 2.1. anyone can help to update my currently code above? pls! and Thanks Guys!

private function export_customer_invoice_report($customer_invs)
    {
        $data_array = array([
            'Issue Date',
            'Purchase Order Invoice',
            'Bag ID',
            'SKU',
            'Color-Size',
            'QTY',
            'Sale Price',
            'Additional Discount',
            'Actual Sale Price',
            'Delivery Fee',
            'Customer Balance',
            'Pre-Paid Amount',
            'Supplier Actual Price',
            'Remark']);

        foreach ($customer_invs as $key => $value) {
            $product_variants = Helper::get_pro_option_id($value->order_item_id);
            $doubleSpace = strip_tags($product_variants);
            $singleSpace_product_variants = str_replace("\n ", "", $doubleSpace);
            $issue_date = Helper::dateTimeDisplay($value->created_at);
            $additional_dis = $value->additional_discount_percent ? $value->additional_discount_percent : 0;
            $sale_price_after_disc = ($value->unit_price * $value->count_item) - ((($value->unit_price * $value->count_item) * $value->discount_percent) / 100);
            $total_sale_price_discount_addit = ($sale_price_after_disc * $value->additional_discount_percent) / 100;
            $actual_sale_price = $sale_price_after_disc - $total_sale_price_discount_addit;

            // check if supplier actual price is zero, take from supplier price instead
            $supplier_actual_price = $value->supplier_order_actual_price > 0
            ? $value->supplier_order_actual_price
            : $value->supplier_price;
            $data_list = array(
                $issue_date,
                $value->invoice_id,
                $value->bag_id,
                OrderItem::getProductSKU($value->order_item_id),
                $singleSpace_product_variants,
                $value->count_item,
                '$' . number_format($sale_price_after_disc, 2),
                '%' . $additional_dis,
                '$' . number_format($actual_sale_price, 2),
                '$' . number_format($value->delivery_price, 2),
                '$' . number_format($value->customer_balance, 2),
                '$' . number_format($value->prepaid_amount, 2),
                '¥' . number_format($supplier_actual_price, 2),
                $value->note,
            );
            array_push($data_array, $data_list);
        }

        Excel::create('customer_invoice_report', function ($excel) use ($data_array) {
            // Set the title
            $excel->setTitle('no title');
            $excel->setCreator('no no creator')->setCompany('no company');
            $excel->setDescription('report file');
            $excel->sheet('sheet1', function ($sheet) use ($data_array) {
                $sheet->cells('A1:M1', function ($cells) {
                    $cells->setBackground('#e7e7e7');
                    $cells->setFontWeight('bold');
                });
                $row = 1;
                $startRow = -1;
                $previousKey = '';

                foreach ($data_array as $index => $value) {
                    if ($startRow == -1) {
                        $startRow = $row;
                        $previousKey = $value[2];
                    }
                    $sheet->setCellValue('A' . $row, $value[0]);
                    $sheet->setCellValue('B' . $row, $value[1]);
                    $sheet->setCellValue('C' . $row, $value[2]);
                    $sheet->setCellValue('D' . $row, $value[3]);
                    $sheet->setCellValue('E' . $row, $value[4]);
                    $sheet->setCellValue('F' . $row, $value[5]);
                    $sheet->setCellValue('G' . $row, $value[6]);
                    $sheet->setCellValue('H' . $row, $value[7]);
                    $sheet->setCellValue('I' . $row, $value[8]);
                    $sheet->setCellValue('J' . $row, $value[9]);
                    $sheet->setCellValue('K' . $row, $value[10]);
                    $sheet->setCellValue('L' . $row, $value[11]);
                    $sheet->setCellValue('M' . $row, $value[12]);
                    $sheet->setCellValue('N' . $row, $value[13]);
                    $nextKey = isset($data_array[$index + 1]) ? $data_array[$index + 1][2] : null;

                    if ($row >= $startRow && (($previousKey != $nextKey) || ($nextKey == null))) {
                        $cellToMergeJ = 'J' . $startRow . ':J' . $row;
                        $cellToMergeK = 'K' . $startRow . ':K' . $row;
                        $cellToMergeL = 'L' . $startRow . ':L' . $row;
                        $sheet->mergeCells($cellToMergeJ);
                        $sheet->mergeCells($cellToMergeK);
                        $sheet->mergeCells($cellToMergeL);
                        $sheet->cells('J' . $startRow . ':J' . $row, function ($cellsJ) {$cellsJ->setValignment('center');});
                        $sheet->cells('K' . $startRow . ':K' . $row, function ($cellsK) {$cellsK->setValignment('center');});
                        $sheet->cells('L' . $startRow . ':L' . $row, function ($cellsL) {$cellsL->setValignment('center');});
                        $startRow = -1;
                    }
                    $row++;
                }
            });
        })->download('xlsx');
    }

Answer 1

We just did this for a project. The laravel-excel package is very different when moving from 2.x to 3.x. The paradigm shifted from simply being a utility to representing imports and exports as self-contained classes. I would recommend moving all of your logic for generating the the export out of your controller and into this class, but you can keep it in your controller if you prefer. Here's the general idea of what you need to do:

First, create an export class:

php artisan make:export CustomerInvoiceReport

Then, edit the newly created class, which should be in app/Exports/CustomerInvoiceReport.php. If $customer_invs is an array, implement the FromArray interface. If it's a Laravel Collection, implement the FromCollection interface. Also, you can use the Exportable trait to add the download() method to the class (which we'll use later).

For this example, I'll assume it's an array. You need to modify the array() method so that it returns your modified array (I'll leave that to you):

namespace App\Exports;

use App\Invoice;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\Exportable;

class CustomerInvoiceExport implements FromArray
{
    use Exportable;

    protected $invoices;

    public function __construct(array $invoices)
    {
        $this->invoices = $invoices;
    }

    public function array(): array
    {
        // insert/move your logic for modifying the array here

        return $this->invoices;
    }
}

Finally, in your controller, simply instantiate your export class and return a download:

use App\Exports\CustomerInvoiceReport;

...

private function export_customer_invoice_report($customer_invs)
{
    return (new CustomerInvoiceReport($customer_invs))
        ->download('customer_invoice_report.xlsx');
}

There are other things you can control in the export class, such as column headers and formatting. Refer to the documentation on creating exports for more information.