laravel maatwebsite/excel 简单使用-导出

最新推荐文章于 2024-08-27 08:29:09 发布

西局幼儿园扛把子

最新推荐文章于 2024-08-27 08:29:09 发布

阅读量2.4k

点赞数 4

文章标签： laravel php 开发语言

本文链接：https://blog.csdn.net/mercy_php/article/details/126642046

版权

环境

php:7.3.4
框架版本:lumen7.0
Excel包版本:maatwebsite/excel3.1

安装

安装 composer 包

composer require maatwebsite/excel

修改配置文件 bootstrap/app.php

# 添加excel配置
$app->register(Maatwebsite\Excel\ExcelServiceProvider::class);

导出

Laravel Excel 支持query导出, array导出, Collection导出, view导出等多种导出方式. 以下为array导出示例

<?php

namespace App\Services;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class TestService implements FromArray, WithStyles {

    public $data;
    public $sheetStyle;

    public function __construct (array $data = [], array $sheetStyle = []) {
        $this->data = empty($data) ? [] : $data;
        $this->sheetStyle = empty($sheetStyle) ? [] : $sheetStyle;
    }


    /**
     * @return array
     */
    public function array (): array {
        return empty($this->data) ? [['数据导出失败', '', '']] : $this->data;
    }

    /**
     * 设置格式
     * merge 坐标 [横标1,纵标1,横标2,纵标1] (e.g. [3, 5, 6, 8])
     * @param Worksheet $sheet
     * @throws Exception
     */
    public function styles (Worksheet $sheet): void {
        if (empty($this->sheetStyle) && empty($this->data)) {
            $sheet->mergeCellsByColumnAndRow(1, 1, 3, 1)
                ->getStyleByColumnAndRow(1, 1, 3, 1)
                ->getFont()
                ->setSize(15)
                ->getColor()
                ->setARGB(Color::COLOR_RED);
        }

        foreach ($this->sheetStyle as $key => $value) {
            if (empty($value)) continue;

            switch ($key) {
                case 'merge':
                    # 合并单元格
                    foreach ($value as $item) {
                        $sheet->mergeCellsByColumnAndRow($item[0], $item[1], $item[2], $item[3]);
                    }
                    break;
                case 'alignment':
                    # 设置文字位置 (Vertical 垂直, Horizontal 水平, WrapText 自动换行)
                    $sheet->getStyleByColumnAndRow(1, 1, count($this->data[0]), count($this->data))
                        ->getAlignment()
                        ->setVertical($item['vertical'] ?? Alignment::VERTICAL_CENTER)
                        ->setHorizontal($item['horizontal'] ?? Alignment::HORIZONTAL_CENTER)
                        ->setWrapText(true);
                    break;
                case 'title':
                    # 表头设置
                    $column = count($this->data[0]);
                    $sheet->mergeCellsByColumnAndRow(1, 1, $column, 1)
                        ->getStyleByColumnAndRow(1, 1, $column, 1)
                        ->getAlignment()
                        ->setVertical(Alignment::VERTICAL_CENTER)
                        ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                        ->setWrapText(true);
                    $sheet->getStyleByColumnAndRow(1, 1, $column, 1)
                        ->getBorders()
                        ->getAllBorders()
                        ->setBorderStyle(Border::BORDER_DOUBLE);
                    break;
                case 'width':
                    # 单元格宽度设置
                    foreach ($value as $k => $item) {
                        $sheet->getColumnDimension($k)->setWidth($item);
                    }
                    break;
                case 'height':
                    # 单元格高度设置
                    foreach ($value as $k => $item) {
                        $sheet->getRowDimension($k + 1)->setRowHeight($item);
                    }
                    break;
                case 'font':
                    # 文字设置 (size 尺寸, color 文字颜色)
                    foreach ($value as $item) {
                        $sheet->getStyleByColumnAndRow($item['cells'][0], $item['cells'][1], $item['cells'][2], $item['cells'][3])
                            ->getFont()
                            ->setSize($item['size'] ?? 10)
                            ->getColor()
                            ->setARGB($item['color'] ?? Color::COLOR_BLACK);
                    }
                    break;
                case 'fill':
                    # 背景样式设置 (fill 背景样式, color 颜色)
                    foreach ($value as $item) {
                        $sheet->getStyleByColumnAndRow($item['cells'][0], $item['cells'][1], $item['cells'][2], $item['cells'][3])
                            ->getFill()
                            ->setFillType($item['type'] ?? Fill::FILL_NONE)
                            ->getStartColor()
                            ->setRGB($item['color'] ?? Color::COLOR_WHITE);
                    }
                    break;
                case 'borders':
                    # 边框样式设置
                    foreach ($value as $item) {
                        $sheet->getStyleByColumnAndRow($item['cells'][0], $item['cells'][1], $item['cells'][2], $item['cells'][3])
                            ->getBorders()
                            ->getAllBorders()
                            ->setBorderStyle($item['style'] ?? Border::BORDER_NONE);
                    }
                    break;
                default:
                    break;
            }
        }
    }
}

BorderStyle 枚举对照表:

const BORDER_NONE    = 'none';              //去掉边框线
const BORDER_DASHDOT   = 'dashDot';         //细虚线1
const BORDER_DASHDOTDOT  = 'dashDotDot';    //细虚线2
const BORDER_DASHED   = 'dashed';           //细虚线3
const BORDER_DOTTED   = 'dotted';           //细虚线4
const BORDER_DOUBLE   = 'double';           //双实线
const BORDER_HAIR    = 'hair';              //细虚线5
const BORDER_MEDIUMDASHDOT = 'mediumDashDot';       //中虚线1
const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot'; //中虚线2
const BORDER_MEDIUMDASHED  = 'mediumDashed';        //中虚线3
const BORDER_SLANTDASHDOT  = 'slantDashDot';        //粗虚线
const BORDER_THICK   = 'thick';             //粗实线
const BORDER_MEDIUM   = 'medium';           //中等实线
const BORDER_THIN    = 'thin';              //细实线

FillStyle对照图:

使用示例:

<?php


namespace App\Console\Commands;


use App\Services\TestService;
use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;

class DemoCommand extends Command {

    protected $signature = 'demo';

    public function handle () {
        # 导出内容
        $data = [
            ['职工登记表', '', '', '', '', ''],
            ['姓名', '', 'qq地址', '', '', '照片'],
            ['性别', '', '联系方式', '', '', ''],
            ['籍贯', '', '', '', '', ''],
            ['现住址', '', '', '', '', ''],
        ];

        # excel样式设置
        $style = [
            'title'     => true,
            'merge'     => [
                [6, 2, 6, 4],
                [4, 2, 5, 2],
                [4, 3, 5, 3],
                [2, 4, 5, 4],
                [6, 2, 6, 4],
                [2, 5, 6, 5],
            ],
            'width'     => [
                "A" => 10,
                "B" => 10,
                "C" => 10,
                "D" => 10,
                "E" => 10,
                "F" => 10,
            ],
            'height'    => [30, 20, 20, 20, 20],
            'font'      => [
                [
                    'cells' => [1, 1, 6, 1],
                    'color' => Color::COLOR_BLUE,
                    'size'  => 20,
                ],
                //                [
                //                    'cells' => [2, 3, 4, 2],
                //                    'color' => '',
                //                    'size'  => 12,
                //                ],
            ],
            'alignment' => true,
            'borders'   => [
                [
                    'cells' => [1, 1, 6, 5],
                    'style' => Border::BORDER_THICK,
                ],
                [
                    'cells' => [1, 1, 6, 1],
                    'style' => Border::BORDER_DOUBLE,
                ],
            ],
            'fill'      => [
                [
                    'cells' => [1, 1, 6, 1],
                    'type'  => Fill::FILL_PATTERN_MEDIUMGRAY,
                    'color' => '66ACF5',
                ],
            ],
        ];

        # 导出到文件 储存位置( 项目目录/storage/app/export )
        return Excel::store(new TestService($data, $style), 'export/'.time().'customers.xlsx');
    }
}

导出结果: