ezijing/hyperf-excel

hyperf excel工具包

V1.1.25 2022-04-21 08:46 UTC

Requires

• php: >=7.2
• ext-json: *
• ext-swoole: >=4.5
• hyperf/config: 2.2.*
• hyperf/constants: 2.2.*
• hyperf/di: 2.2.*
• hyperf/framework: 2.2.*
• hyperf/utils: 2.2.*
• hyperf/validation: 2.2.*
• phpoffice/phpspreadsheet: 1.*

Requires (Dev)

• friendsofphp/php-cs-fixer: ^3.0
• hyperf/devtool: ~2.2.0
• hyperf/ide-helper: ~2.2.0
• hyperf/testing: ~2.2.0
• mockery/mockery: ^1.0
• phpstan/phpstan: ^0.12
• swoole/ide-helper: ^4.5

Suggests

• ext-json: Required to use JSON.
• ext-openssl: Required to use HTTPS.
• ext-pdo: Required to use MySQL Client.
• ext-pdo_mysql: Required to use MySQL Client.
• ext-redis: Required to use Redis Client.

MIT

• 啊正

• php
• excel
• hyperf

README

Table of Contents generated with DocToc

• hyperf-excel
  • 安装准备
    • 1、确保在项目中安装了hyperf验证器
    • 2、添加验证中间件 config/autoload/middlewares.php
    • 3、添加异常处理器 config/autoload/exceptions.php
  • 安装
    • 1、在项目根目录下执行
    • 2、发布配置文件
    • 3、配置文件 config/autoload/excel_plugin.php
    • 4、配置异常处理 app/Exception/Handler/AppExceptionHandler.php
  • 使用
    • 1、导出单个sheet的excel到本地
    • 2、从浏览器导出单个sheet的excel
    • 3、导出多个sheet的excel到本地
    • 4、从浏览器导出多个sheet的excel
    • 5、导入单个sheet的excel
      • 测试数据
      • (1) 本地导入
        • 代码
        • 导入结果
      • (2) 接口导入
        • 请求方式
        • 请求参数
        • 代码
        • 导入结果

hyperf-excel

安装准备

1、确保在项目中安装了hyperf验证器

> composer require hyperf/validation -vvv
> php bin/hyperf.php vendor:publish hyperf/translation # 发布 Translation 组件的文件
> php bin/hyperf.php vendor:publish hyperf/validation # 发布验证器组件的文件：

2、添加验证中间件 config/autoload/middlewares.php

<?php
return [
    // 下面的 http 字符串对应 config/autoload/server.php 内每个 server 的 name 属性对应的值，意味着对应的中间件配置仅应用在该 Server 中
    'http' => [
        // 数组内配置您的全局中间件，顺序根据该数组的顺序
        \Hyperf\Validation\Middleware\ValidationMiddleware::class
        // 这里隐藏了其它中间件
    ],
];

3、添加异常处理器 config/autoload/exceptions.php

<?php
return [
    'handler' => [
        // 这里对应您当前的 Server 名称
        'http' => [
            \Hyperf\Validation\ValidationExceptionHandler::class,
        ],
    ],
];

安装

1、在项目根目录下执行

> composer require ezijing/hyperf-excel -vvv

2、发布配置文件

>  php bin/hyperf.php vendor:publish ezijing/hyperf-excel

3、配置文件 config/autoload/excel_plugin.php

<?php

declare(strict_types=1);

return [
    // 保存到本地的地址
    'local_file_address' => BASE_PATH . '/storage/excel',
];

4、配置异常处理 app/Exception/Handler/AppExceptionHandler.php

public function handle(Throwable $throwable, ResponseInterface $response)
{
    switch (true) {
        case $throwable instanceof ExcelException:
            return $response
                ->withHeader('Sever', 'test')
                ->withStatus(200)
                ->withBody(new SwooleStream(Json::encode([
                    'code' => $throwable->getCode(),
                    'message' => $throwable->getMessage(),
                ])));

        default:
            $this->logger->error(sprintf('%s[%s] in %s', $throwable->getMessage(), $throwable->getLine(), $throwable->getFile()));
            $this->logger->error($throwable->getTraceAsString());
            return $response->withHeader('Server', 'Hyperf')->withStatus(500)->withBody(new SwooleStream('Internal Server Error.'));
    }
}

使用

1、导出单个sheet的excel到本地

$tableName = 'test';
$data = [
    'export_way' => ExcelConstant::SAVE_TO_A_LOCAL_DIRECTORY, // 导出方式
    'enable_number' => true, // 是否开启序号
    'titles' => ['ID', '用户名', '部门', '职位'], // 设置表头
    'keys' => ['id', 'username', 'department', 'position'], // 设置表头标识，必须与要导出的数据的key对应
    // 要导出的数据
    'data' => [
        ['id' => '1', 'username' => '小明', 'department' => '运营部', 'position' => '产品运营'],
        ['id' => '2', 'username' => '小王', 'department' => '技术部', 'position' => 'PHP'],
    ],
    // 验证规则, 本地导入也适用
    'value_type' => [
        // 强转string
        ['key' => 'position', 'type' => 'string'],
        // 强转int
        ['key' => 'id', 'type' => 'int'],
        // 回调处理
        [
            'key' => 'department',
            'type' => 'function',
            'func' => function($value) {
                return (string) $value;
            },
        ],
    ],
];

$res = (new Excel())->exportExcelForASingleSheet($tableName, $data);

2、从浏览器导出单个sheet的excel

<?php

declare(strict_types=1);

namespace App\Controller;

use Ezijing\HyperfExcel\Core\Constants\ExcelConstant;
use Ezijing\HyperfExcel\Core\Services\Excel;
use Hyperf\HttpServer\Annotation\AutoController;

/**
 * @AutoController
 */
class ExcelController extends AbstractController
{
    /**
     * @var Excel
     */
    protected $excel;

    public function __construct()
    {
        $this->excel = make(Excel::class);
    }

    public function download()
    {
        $tableName = 'test';
        $data = [
            'export_way' => ExcelConstant::DOWNLOAD_TO_BROWSER_BY_TMP,
            'enable_number' => false,
            'titles' => ['ID', '用户名', '部门', '职位'],
            'keys' => ['id', 'username', 'department', 'position'],
            'data' => [
                ['id' => '1', 'username' => '小明', 'department' => '运营部', 'position' => '产品运营'],
                ['id' => '2', 'username' => '小王', 'department' => '技术部', 'position' => 'PHP'],
            ],
            // 验证规则, 本地导入也适用
            'value_type' => [
                // 强转string
                ['key' => 'position', 'type' => 'string'],
                // 强转int
                ['key' => 'id', 'type' => 'int'],
                // 回调处理
                [
                    'key' => 'department',
                    'type' => 'function',
                    'func' => function($value) {
                        return (string) $value;
                    },
                ],
            ],
        ];

        return $this->excel->exportExcelForASingleSheet($tableName, $data);
    }
}

3、导出多个sheet的excel到本地

$tableName = 'sheets';
$data = [
    'export_way' => ExcelConstant::SAVE_TO_A_LOCAL_DIRECTORY,
    'sheets_params' => [
        [
            'sheet_title' => '企业1',
            'enable_number' => true, // 是否开启序号
            'titles' => ['ID', '用户名', '部门', '职位'],
            'keys' => ['id', 'username', 'department', 'position'],
            'data' => [
                ['id' => '1', 'username' => '小明', 'department' => '运营部', 'position' => '产品运营'],
                ['id' => '2', 'username' => '小王', 'department' => '技术部', 'position' => 'PHP'],
            ],
            // 验证规则, 本地导入也适用
            'value_type' => [
                // 强转string
                ['key' => 'position', 'type' => 'string'],
                // 强转int
                ['key' => 'id', 'type' => 'int'],
                // 回调处理
                [
                    'key' => 'department',
                    'type' => 'function',
                    'func' => function($value) {
                        return (string) $value;
                    },
                ],
            ]
        ],
        [
            'sheet_title' => '企业2',
            'titles' => ['ID', '用户名', '部门', '职位'],
            'keys' => ['id', 'username', 'department', 'position'],
            'data' => [
                ['id' => '3', 'username' => '小李', 'department' => '运营部', 'position' => '产品运营'],
                ['id' => '4', 'username' => '小赵', 'department' => '技术部', 'position' => 'PHP'],
            ],
        ],
        [
            'sheet_title' => '部门',
            'enable_number' => false, // 是否开启序号
            'titles' => ['ID', '部门', '职位'],
            'keys' => ['id', 'department', 'position'],
            'data' => [
                ['id' => 1, 'department' => '运营部', 'position' => '产品运营'],
                ['id' => 2, 'department' => '技术部', 'position' => 'PHP'],
            ],
        ],
    ]
];

$res = (new Excel())->exportExcelWithMultipleSheets($tableName, $data);
print_r($res);

4、从浏览器导出多个sheet的excel

<?php

declare(strict_types=1);

namespace App\Controller;

use Ezijing\HyperfExcel\Core\Constants\ExcelConstant;
use Ezijing\HyperfExcel\Core\Services\Excel;
use Hyperf\HttpServer\Annotation\AutoController;

/**
 * @AutoController
 */
class ExcelController extends AbstractController
{
    /**
     * @var Excel
     */
    protected $excel;

    public function __construct()
    {
        $this->excel = make(Excel::class);
    }

    public function download()
    {
        $tableName = 'sheets';
        $data = [
            'export_way' => ExcelConstant::DOWNLOAD_TO_BROWSER_BY_TMP,
            'sheets_params' => [
                [
                    'sheet_title' => '企业1',
                    'enable_number' => true,
                    'titles' => ['ID', '用户名', '部门', '职位'],
                    'keys' => ['id', 'username', 'department', 'position'],
                    'data' => [
                        ['id' => '1', 'username' => '小明', 'department' => '运营部', 'position' => '产品运营'],
                        ['id' => '2', 'username' => '小王', 'department' => '技术部', 'position' => 'PHP'],
                    ],
                    // 验证规则, 本地导入也适用
                    'value_type' => [
                        // 强转string
                        ['key' => 'position', 'type' => 'string'],
                        // 强转int
                        ['key' => 'id', 'type' => 'int'],
                        // 回调处理
                        [
                            'key' => 'department',
                            'type' => 'function',
                            'func' => function($value) {
                                return (string) $value;
                            },
                        ],
                    ],
                ],
                [
                    'sheet_title' => '企业2',
                    'titles' => ['ID', '用户名', '部门', '职位'],
                    'keys' => ['id', 'username', 'department', 'position'],
                    'data' => [
                        ['id' => '3', 'username' => '小李', 'department' => '运营部', 'position' => '产品运营'],
                        ['id' => '4', 'username' => '小赵', 'department' => '技术部', 'position' => 'PHP'],
                    ],
                ],
                [
                    'sheet_title' => '部门',
                    'titles' => ['ID', '部门', '职位'],
                    'keys' => ['id', 'department', 'position'],
                    'data' => [
                        ['id' => 1, 'department' => '运营部', 'position' => '产品运营'],
                        ['id' => 2, 'department' => '技术部', 'position' => 'PHP'],
                    ],
                ],
            ]
        ];

        return $this->excel->exportExcelWithMultipleSheets($tableName, $data);
    }
}

5、导入单个sheet的excel

测试数据

(1) 本地导入

代码

$data = [
    // 带入方式
    'import_way' => ExcelConstant::THE_LOCAL_IMPORT,
    // 文件路径
    'file_path' => '/Users/ezijing/php_project/hyperf-demo/storage/excel/test_20220113_105250.xlsx',
    // 指定导入的title
    'titles' => ['部门', 'ID'],
    // 指定生成的key
    'keys' => ['position', 'id'],
];

$res = $this->excel->importExcelForASingleSheet($data);

print_r($res);

导入结果

Array (
    [0] => Array
        (
            [id] => 1
            [position] => 运营部
        )

    [1] => Array
        (
            [id] => 2
            [position] => 技术部
        )

)

(2) 接口导入

请求方式

POST

请求的类型

form-data

请求参数

代码

<?php

declare(strict_types=1);

namespace App\Controller;

use Ezijing\HyperfExcel\Core\Constants\ExcelConstant;
use Ezijing\HyperfExcel\Core\Services\Excel;
use Hyperf\HttpServer\Annotation\AutoController;

/**
 * @AutoController
 */
class ExcelController extends AbstractController
{
    /**
     * @var Excel
     */
    protected $excel;

    public function __construct()
    {
        $this->excel = make(Excel::class);
    }

    public function import()
    {
        $data = [
            'import_way' => ExcelConstant::BROWSER_IMPORT,
            // 指定导入的title
            'titles' => ['部门', 'ID', '职位'],
            // 指定生成的key
            'keys' => ['department', 'id', 'position'],
            // 验证规则, 本地导入也适用
            'value_type' => [
                // 强转string
                ['key' => 'position', 'type' => 'string'],
                // 强转int
                ['key' => 'id', 'type' => 'int'],
                // 回调处理
                [
                    'key' => 'department',
                    'type' => 'function',
                    'func' => function($value) {
                        return (string) $value;
                    },
                ],
            ]
        ];

        return $this->excel->importExcelForASingleSheet($data);
    }
}

导入结果

[
    {
        "id": 1,
        "department": "运营部",
        "position": "产品运营"
    },
    {
        "id": 2,
        "department": "技术部",
        "position": "PHP"
    }
]