Laravel Excel 3.1 导出表格详解（自定义sheet，合并单元格，设置样式，格式化列数据）

y-sir

已于 2023-10-09 11:39:17 修改

阅读量1.1w

点赞数 11

分类专栏： Lavarel 文章标签： excel

于 2020-09-27 14:51:20 首次发布

本文链接：https://blog.csdn.net/qq_41688060/article/details/108827432

版权

Lavarel 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

安装

执行如下命令引入包

composer require maatwebsite/excel

Maatwebsite\Excel\ExcelServiceProvider是自动发现并默认注册。

请在config/app.php以下位置添加如下代码

'providers' => [
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

在中config/app.php添加门面代码如下

'aliases' => [
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

执行如下命令

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

这将创建一个名为的新配置文件config/excel.php。

使用（我以导出订单为例）

在这里插入图片描述

1，创建路由
2，访问获取数据（我是以数组的形式）
3，自定义一个导出订单类
在这里插入图片描述

4，获取订单列表数据并调用导出类

<?php
/**
 * admin service file Created by PhpStorm.
 * Date: 2020/09/27
 */

namespace Modules\Admin\Service;

use Common\Repository\MallOrderRepository;
use App\Exports\OrderExport;
use Maatwebsite\Excel\Facades\Excel;

class MallOrderService
{
    /**
     * 导出订单
     * @param array $get_data
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function getAll($get_data = [])
    {
        $arr_option = self::option($get_data);
        $arr_option['with'] = ['order_goods', 'get_address', 'order_logistics'];
        $result = MallOrderRepository::getAll($arr_option);

        $orderList = [];
        foreach ($result as &$item) {
            $item['order_status'] = MallOrderRepository::orderStatus($item['status']);
            $orderList[$item['order_status']][] = $item;
        }

        return Excel::download(new OrderExport($orderList), '订单列表' . date('Y:m:d').'.xls');
    }
}

OrderExport 导出订单类代码

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithTitle; //sheet名称
use Maatwebsite\Excel\Concerns\WithMultipleSheets;//sheet
use Maatwebsite\Excel\Concerns\WithStyles;//造型
use Maatwebsite\Excel\Concerns\WithColumnWidths; //列宽
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

class OrderExport implements WithHeadings, FromCollection, WithMultipleSheets, WithTitle, WithColumnWidths, WithStyles, WithColumnFormatting
{
    use Exportable;

    private $data;//订单数据
    private $status; //sheet名称（订单状态）
    private $column; //总行数
    private $goodsNum = []; //一个订单的商品数量

    public function __construct($data, $status = '')
    {
        $this->data = $data;
        $this->status = $status;
    }

    /**
     * 表头
     * @return string[]
     */
    public function headings(): array
    {
        return [
            '订单编号', '收货人', '联系方式', '收货地址', '商品名称【规格】',
            '数量', '价格', '订单金额', '运费', '优惠金额', '支付金额', '支付方式',
            '付款时间', '下单时间', '快递公司', '快递单号', '发货时间', '收货时间',
            '买家留言', '买家备注'
        ];
    }

	/**
     * 导出
     * @return \Illuminate\Support\Collection|\Tightenco\Collect\Support\Collection
     */
    public function collection()
    {
        $data = $this->data;
        $list = [];
        foreach ($data as $key => $value) {
            $this->goodsNum[] = count($value['order_goods']);
            foreach ($value['order_goods'] as $k => $item) {
                $list[] = [
                    'order_no'        => $value['order_no'],
                    'consignee'       => $value['get_address']['name'],
                    'user_phone'      => $value['get_address']['phone'],
                    'address'         => $value['get_address']['get_city']['mername'] . $value['get_address']['address'],
                    'goods_name'      => $item['goods_name'] . ' 【' . $item['specs_title'] . '】',
                    'goods_num'       => $item['goods_num'],
                    'specs_price'     => $item['specs_price'],
                    'order_amount'    => $value['order_amount'],
                    'freight_amount'  => $value['freight_amount'],
                    'discount_amount' => $value['discount_amount'],
                    'pay_amount'      => $value['pay_amount'],
                    'pay_type'        => $value['pay_type'] == 1 ? '微信' : '支付宝',
                    'pay_time'        => $value['pay_time'],
                    'created_at'      => $value['created_at'],
                    'express_name'    => $value['order_logistics']['get_express']['name'],
                    'express_no'      => $value['order_logistics']['express_no'],
                    'delivery_time'   => $value['order_logistics']['created_at'],
                    'receiving_time'  => $value['order_logistics']['receiving_time'],
                    'remark'          => $value['remark'],
                    'seller_notes'    => $value['seller_notes']
                ];
            }
        }

        $this->column = count($list);

        return collect($list);
    }

    /**
     * 创建sheet
     * @return array
     */
    public function sheets(): array
    {
        $list = $this->data;
        $sheets = [];
        foreach ($list as $key => $value) {
            $sheets[] = new OrderExport($value, $key);
        }

        return $sheets;
    }

    /**
     * sheet名称
     * @return string
     */
    public function title(): string
    {
        return $this->status;
    }

    /**
     * 设置列宽
     * @return array
     */
    public function columnWidths(): array
    {
        return [
            'A' => 16, 'B' => 13, 'C' => 13, 'D' => 20, 'E' => 50, 'F' => 8, 'G' => 10, 'H' => 13,
            'I' => 8, 'J' => 12, 'K' => 12, 'L' => 12, 'M' => 20, 'N' => 20, 'O' => 15, 'P' => 20,
            'Q' => 20, 'R' => 20, 'S' => 30, 'T' => 30, 'U' => 20, 'V' => 20, 'W' => 20, 'X' => 20,
        ];
    }

    /**
     * 格式化列
     * @return array
     */
    public function columnFormats(): array
    {
        $format = NumberFormat::FORMAT_NUMBER_00;//金额保留两位小数
        return ['G' => $format, 'H' => $format, 'I' => $format, 'J' => $format, 'K' => $format];
    }

    /**
     * 样式设置
     * @param Worksheet $sheet
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function styles(Worksheet $sheet)
    {
        $sheet->getDefaultRowDimension()->setRowHeight(22);//设置行高
        $sheet->getStyle('A1:Z' . $this->column)->getAlignment()->setVertical('center');//垂直居中
        $sheet->getStyle('F1:K' . $this->column)->applyFromArray(['alignment' => ['horizontal' => 'center']]);//设置水平居中
        $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true, 'color' => ['rgb' => '0072ff']]]);//字体设置
        $cell = ['A', 'B', 'C', 'D', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T'];//需要合并的单元格
        //$sheet->mergeCells('A18:A22'); //合并单元格
        foreach ($cell as $item) {
            $start = 2;
            foreach ($this->goodsNum as $key => $value) {
                $end = $start + $value;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                $start = $end + 1;
            }
        }
    }
}

看看导出结果
在这里插入图片描述
复杂表头处理

    /**
     * 复杂表头
     * @return string[]
     */
    public function headings(): array
    {
        $one = $this->data['range_one'];
        $two = $this->data['range_two'];
        return [
            ['孵化中心', 'A：' . $one . '', '', '', '', 'B：' . $two . '', '', '', '', '同比情况 ( B - A )', '', '', '会员与推广员', '', '累计情况', '', '', ''],
            ['', '产生销量的店铺数', '产生业绩总额', '3000元以上的店铺数', '使用率', '产生销量的店铺数', '产生业绩总额', '3000元以上的店铺数', '使用率', '产生销量的店铺数', '产生业绩总额', '使用率', '有会员的店家数', '有推广员的店家数', '产生销量的店铺数', '销售总额', '使用率']
        ];
    }

    /**
     * 样式设置
     * @param Worksheet $sheet
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function styles(Worksheet $sheet)
    {
        //合并单元格
        $sheet->mergeCells('A1:A2');
        $sheet->mergeCells('B1:E1');
        $sheet->mergeCells('F1:I1');
        $sheet->mergeCells('J1:L1');
        $sheet->mergeCells('M1:N1');
        $sheet->mergeCells('O1:Q1');
        $sheet->getDefaultRowDimension()->setRowHeight(22);//设置默认行高
        $sheet->getRowDimension('1')->setRowHeight(25);//设置指定行高
        $sheet->getRowDimension('2')->setRowHeight(50);//设置指定行高
        $sheet->getStyle('A1:Q18')->getAlignment()->setVertical('center');//垂直居中
        $sheet->getStyle('A1:Q18')->applyFromArray(['alignment' => ['horizontal' => 'center']]);//设置水平居中
        $sheet->getStyle('A1:Q2')->applyFromArray(['font' => ['bold' => true]]);//字体设置
        $sheet->getCell('B2');//设置换行
        $sheet->getStyle('B2:Q2')->getAlignment()->setWrapText(true);
    }

在这里插入图片描述
Laravel Excel官网：
https://docs.laravel-excel.com/3.1/getting-started/installation.html

PhpSpreadsheet文档：
https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#mergeunmerge-cells

y-sir

关注

11
点赞
踩
25

收藏

觉得还不错? 一键收藏
9
评论
Laravel Excel 3.1 导出表格详解（自定义sheet，合并单元格，设置样式，格式化列数据）

安装在composer.json您的Laravel项目中需要此软件包。这将下载软件包和PhpSpreadsheet。composer require maatwebsite/excel该Maatwebsite\Excel\ExcelServiceProvider是自动发现并默认注册。如果要自己注册，请在config/app.php以下位置添加ServiceProvider ：'providers' => [ /* * Package Service Providers..
复制链接

扫一扫