Laravel/Excel导出订单

2019-10-12  本文已影响0人  三喵w

本导出使用了maatwebsite/excel 3.1版本composer包
导出包含 订单数据,商品数据,付款数据,退款数据
样式包含 水平垂直居中,合并单元格,设置行高

控制器中

public function exportOrder() {
    return new OrderExport();
}

Export目录中 OrderExport导出类

<?php

namespace App\Exports;

use App\Order;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Excel;
use PhpOffice\PhpSpreadsheet\Exception;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Illuminate\Contracts\Support\Responsable;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class OrderExport implements FromArray, WithTitle, Responsable, ShouldAutoSize, WithEvents, WithHeadings, WithColumnFormatting, WithStrictNullComparison
{
    use Exportable, RegistersEventListeners;

    /**
     * 响应头
     * @var array
     */
    private $headers = ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

    /**
     * excel文件名称
     * @var string
     */
    private $fileName = '订单信息.xlsx';

    /**
     * 设置导出excel的后缀类型
     * @var string
     */
    private $writerType = Excel::XLSX;

    /**
     * 从模型中获取数据,并且格式化好写入excel中
     * @return array
     */
    public function array(): array
    {
        $data = Order::query()
            ->select([
                'order_no', 'status', 'delivery_name', 'created_at', 'cash_fee', 'logistics_price', 'cash_fee',
                'cash_fee', 'cash_fee', 'delivery_name', 'delivery_mobile', 'province', 'city', 'area',
                'delivery_address', 'logistic_identity', 'logistic_no', 'remarks', 'sign_at', 'send_at',
            ])
            ->with([
                'orderGoods:order_no,sku_no,spu_name,category_name,sell_price,quantity,sub_price',
                'payment:created_at,third_payment_no',
                'payRefund:refund_fee,created_at,transaction_id'
            ])
            ->orderBy('id', 'desc')
            ->limit(2000)
            ->get()
            ->toArray();
        return $this->format($data);
    }

    /**
     * 设置excel第一行头信息
     * @return array
     */
    public function headings(): array
    {
        return [
            '订单编号', '订单状态', '下单账户', '下单时间', '商品编号', '商品名称', '商品规格', '商品单价', '商品数量', '小计', '商品总额',
            '运费', '订单总额', '实付金额', '平台实收金额', '支付时间', '支付流水号(微信支付)', '收货人姓名', '收货人联系电话', '省', '市',
            '区', '详细地址', '物流公司', '物流编号', '发货时间', '收货时间', '退款总金额', '退款金额', '退款到账时间',
            '退款流水单号(单号皆对应订单信息非具体商品)', '备注',
        ];
    }

    /**
     * 格式化每一行要写入的数据
     * @param $data
     * @return array
     */
    public function format($data)
    {
        $result = [];

        foreach ($data as $item) {
            foreach ($item['order_goods'] as $goods) {
                $result[] = [
                    $item['order_no'],
                    $item['status'],
                    $item['delivery_name'],
                    $item['created_at'],
                    $goods['sku_no'],
                    $goods['spu_name'],
                    implode(',', json_decode($goods['category_name'], true)),
                    $goods['sell_price'] / 100,
                    $goods['quantity'],
                    $goods['sub_price'] / 100,
                    $item['cash_fee'] / 100,
                    $item['logistics_price'] / 100,
                    $item['cash_fee'] / 100,
                    $item['cash_fee'] / 100,
                    $item['cash_fee'] / 100,
                    $item['payment']['created_at'],
                    $item['payment']['third_payment_no'],
                    $item['delivery_name'],
                    $item['delivery_mobile'],
                    $item['province'],
                    $item['city'],
                    $item['area'],
                    $item['delivery_address'],
                    $item['logistic_identity'],
                    $item['logistic_no'],
                    date('Y-m-d H:i:s', $item['send_at']),
                    date('Y-m-d H:i:s', $item['sign_at']),
                    $item['pay_refund']['refund_fee'] ?? '',
                    $item['pay_refund']['refund_fee'] ?? '',
                    $item['pay_refund']['created_at'] ?? '',
                    $item['pay_refund']['transaction_id'] ?? '',
                    $item['remarks'],
                ];
            }
        }

        return $result;
    }

    /**
     * 在数据写入sheet表后, 触发此事件, 进行样式调整,宽高、合并单元格等
     * @param AfterSheet $event
     * @throws Exception
     */
    public static function afterSheet(AfterSheet $event)
    {
        // 总行数(包含标题头)
        $heights = $event->sheet->getDelegate()->getHighestRow();
        // 总列数(字母 A B 等)
        $rows = $event->sheet->getDelegate()->getHighestColumn();

        //区域单元格 - 字体、颜色、背景、对齐等
        $event->sheet->getDelegate()->getStyle('A1:AF' . $heights)->applyFromArray([
            // vertical 水平对齐 - horizontal 垂直居中
            'alignment' => [
                'vertical'   => Alignment::VERTICAL_CENTER,
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ]);

        // 设置行高,$i为数据行数
        for ($i = 1; $i <= $heights; $i++) {
            $event->sheet->getDelegate()->getRowDimension($i)->setRowHeight(34);
        }

        // 获取每个单元格与下边的对比, 一样进行合并
        $row = 'A';
        do {
            if (in_array($row, ['E', 'F', 'G', 'H', 'I', 'J']) === false) {
                for ($height = 2; $height <= $heights; $height++) {
                    // 当前迭代单元格的值
                    $current = $event->sheet->getDelegate()->getCell($row . $height)->getValue();
                    // 值有效的话, 进行传递去查找下一个单元格的值进行对比
                    if (empty($current) === false || $current === 0) {
                        // 去找最后一个和当前单元格值一样的位置
                        $realHeight = self::matchData($row, $height, $heights, $event, $current);
                        // 找到最后一个一样的值后, 进行合并
                        if ($height != $realHeight) {
                            $event->sheet->getDelegate()->mergeCells("{$row}{$height}:{$row}{$realHeight}");
                        }
                    }

                }
            }
            $row++;
        } while (static::strSize($row, $rows));
    }

    /**
     * 递归查询列中一样值, 返回最后一个一样的值得位置
     * @param $row
     * @param $height
     * @param $heights
     * @param $event
     * @param $current
     * @return mixed
     */
    public static function matchData($row, $height, $heights, $event, $current)
    {
        // 获取当前列单元格的前面的订单号 和 下一个订单号
        $currentOrder = $event->sheet->getDelegate()->getCell('A' . $height)->getValue();
        $nextOrder = $event->sheet->getDelegate()->getCell('A' . ($height + 1))->getValue();

        // 如果是订单号一样, 或者下一个单元格的订单号值为null, 那么说明要合并的值是同一个订单的信息
        if ($currentOrder === $nextOrder || $nextOrder === null) {
            // 如果是最后一个单元, 不进行递归查找是否还有一样的值
            if ($height == $heights) {
                return $height;
            }
            // 下一个值得坐标
            $nextHeight = $height + 1;
            // 拿到下一个单元的值
            $down = $event->sheet->getDelegate()->getCell($row . $nextHeight)->getValue();
            // 当前的值和下一个值一样, 就继续找后面一个值得位置
            if ($down == $current) {
                return self::matchData($row, $nextHeight, $heights, $event, $current);
            }
        }
        // 不是一个订单,就返回原单元的位置,不向下找
        return $height;
    }

    /**
     * 匹配两个字母的大小 A B AA AB 此类型的值
     * @param $str1
     * @param $str2
     * @return bool
     */
    public static function strSize($str1, $str2)
    {
        $len1 = strlen($str1);
        $len2 = strlen($str2);
        // 左边短, 右边长
        if ($len1 < $len2) {
            return true;
        }
        // 左右一样长
        for ($i = 0; $i < $len1; $i++) {
            $outChar1 = substr($str1, $i, 1);
            $outChar2 = substr($str2, $i, 1);
            if ($outChar1 < $outChar2) {
                return true;
            }
            if ($outChar1 > $outChar2) {
                return false;
            }
            if ($outChar1 == $outChar2) {
                $char1 = substr($str1, $i + 1, 1);
                $char2 = substr($str2, $i + 1, 1);
                if ($char1 <= $char2) {
                    return true;
                }
                if ($char1 > $char2) {
                    return false;
                }
            }
        }
    }

    /**
     * 设置每一列的值类型
     * @return array
     */
    public function columnFormats(): array
    {
        return [
            'A'  => NumberFormat::FORMAT_NUMBER,
            'B'  => NumberFormat::FORMAT_NUMBER,
            'C'  => NumberFormat::FORMAT_TEXT,
            'D'  => NumberFormat::FORMAT_TEXT,
            'E'  => NumberFormat::FORMAT_TEXT,
            'F'  => NumberFormat::FORMAT_TEXT,
            'G'  => NumberFormat::FORMAT_TEXT,
            'H'  => NumberFormat::FORMAT_NUMBER_00,
            'I'  => NumberFormat::FORMAT_NUMBER,
            'J'  => NumberFormat::FORMAT_NUMBER_00,
            'K'  => NumberFormat::FORMAT_NUMBER_00,
            'L'  => NumberFormat::FORMAT_NUMBER_00,
            'M'  => NumberFormat::FORMAT_NUMBER_00,
            'N'  => NumberFormat::FORMAT_NUMBER_00,
            'O'  => NumberFormat::FORMAT_NUMBER_00,
            'P'  => NumberFormat::FORMAT_TEXT,
            'Q'  => NumberFormat::FORMAT_NUMBER,
            'R'  => NumberFormat::FORMAT_TEXT,
            'S'  => NumberFormat::FORMAT_NUMBER,
            'T'  => NumberFormat::FORMAT_TEXT,
            'U'  => NumberFormat::FORMAT_TEXT,
            'V'  => NumberFormat::FORMAT_TEXT,
            'W'  => NumberFormat::FORMAT_TEXT,
            'X'  => NumberFormat::FORMAT_TEXT,
            'Y'  => NumberFormat::FORMAT_TEXT,
            'Z'  => NumberFormat::FORMAT_TEXT,
            'AA' => NumberFormat::FORMAT_TEXT,
            'AB' => NumberFormat::FORMAT_TEXT,
            'AC' => NumberFormat::FORMAT_TEXT,
            'AD' => NumberFormat::FORMAT_TEXT,
            'AE' => NumberFormat::FORMAT_TEXT,
            'AF' => NumberFormat::FORMAT_TEXT,
        ];
    }

    /**
     * 设置sheet的名称
     * @return string
     */
    public function title(): string
    {
        return '订单信息表';
    }
}

上一篇 下一篇

猜你喜欢

热点阅读