vartruexuan/hyperf-excel

excel异步导入导出

v1.0.0 2025-06-13 06:24 UTC

Requires

• php: >=8.1
• hyperf/async-queue: ~3.1.0
• hyperf/codec: ~3.1.0
• hyperf/contract: ~3.1.0
• hyperf/event: ~3.1.0
• hyperf/filesystem: ~3.1.0
• hyperf/logger: ~3.1.0
• hyperf/redis: ~3.1.0
• hyperf/support: ~3.1.0
• overtrue/http: ^1.2
• psr/container: ^1.0 || ^2.0
• psr/event-dispatcher: ^1.0
• ramsey/uuid: *

Requires (Dev)

• friendsofphp/php-cs-fixer: ^3.0
• mockery/mockery: ^1.0
• phpstan/phpstan: ^1.0
• phpunit/phpunit: >=7.0
• swoole/ide-helper: ^4.5

Suggests

• swow/swow: Required to create swow components.

MIT

• php
• hyperf

README

概述

excel 导入导出,支持异步、进度构建。

组件能力

• 导入、导出excel
• 支持异步操作,进度构建,进度消息输出
• 格式 xlsx
• 支持驱动 xlswriter

安装

• 安装依赖拓展 xlswriter

pecl install xlswriter

• 依赖组件包 <项目中安装,构建配置>
  • hyperf/filesystem
  • hyperf/async-queue
  • hyperf/logger
  • hyperf/redis
• 安装组件

composer require vartruexuan/hyperf-excel

• 构建配置

php bin/hyperf.php vendor:publish vartruexuan/hyperf-excel

配置

<?php

declare(strict_types=1);

return [
    'default' => 'xlswriter',
    'drivers' => [
        'xlswriter' => [
            'driver' => \Vartruexuan\HyperfExcel\Driver\XlsWriterDriver::class,
            // redis 配置
            'redis' => [
                'pool' => 'default',
            ],
            // filesystem 配置
            'filesystem' => [
                'storage' => 'local', // 默认本地
            ],
            // queue配置
            'queue' => [
                'name' => 'default',
                'jobs' => [
                    'export' => \Vartruexuan\HyperfExcel\Job\ExportJob::class,
                    'import' => \Vartruexuan\HyperfExcel\Job\ImportJob::class,
                ],
            ],
            'logger' => [
                'name' => 'hyperf-excel',
            ],
            'export' => [
                'rootDir' => 'export',
                // 导出文件地址构建策略
                'pathStrategy' => \Vartruexuan\HyperfExcel\Strategy\Path\DateTimeStrategy::class,
            ],
            // 进度处理
            'progress' => [
                'enabled' => true,
                'prefix' => 'HyperfExcel', // 缓存key前缀
                'expire' => 3600, // 数据失效时间
            ]
        ]
    ],
];

使用

api

use Vartruexuan\HyperfExcel\Driver\DriverInterface;
use Vartruexuan\HyperfExcel\Driver\DriverFactory;
use \Vartruexuan\HyperfExcel\Data\Export\ExportData;
use \Vartruexuan\HyperfExcel\Data\Import\ImportData;
use \Vartruexuan\HyperfExcel\Data\Export\ExportConfig;
use \Vartruexuan\HyperfExcel\Data\Export\ImportConfig

$excel = ApplicationContext::getContainer()->get(DriverInterface::class);
// 工厂类方式
// $excel = ApplicationContext::getContainer()->get(DriverFactory::class)->get('xlswriter');

// 导出
$excel->export(ExportConfig $config):ExportData;
// 导入
$excel->import(ImportConfig $config):ImportData;
// 进度信息
$excel->progress->getRecordByToken($token);
// 进度消息
$excel->progress->popMessage($token);

导入导出config类配置

config

• 导出

<?php

namespace App\Excel\Export;

use Vartruexuan\HyperfExcel\Data\Export\ExportConfig;

use Vartruexuan\HyperfExcel\Data\Export\Column;
use Vartruexuan\HyperfExcel\Data\Export\ExportCallbackParam;
use Vartruexuan\HyperfExcel\Data\Export\Sheet;
use function Hyperf\Support\make;

class UserExportConfig extends ExportConfig
{
    public string $serviceName = '用户';

    /**
      * 
      *  输出类型
      *      OUT_PUT_TYPE_UPLOAD 导出=>上传<filesystem>
      *      OUT_PUT_TYPE_OUT    直接同步输出 <isAsync=false>    
      * @var string 
      */
    public string $outPutType = self::OUT_PUT_TYPE_UPLOAD;
    /**
     * 是否异步
     *   true 则会推入队列之中  
     *
     * @var bool
     */
    public bool $isAsync = true;


    public function getSheets(): array
    {
        $this->setSheets([
            new Sheet([
                'name' => 'sheet1',
                'columns' => [
                    new Column([
                        'title' => 'username',
                        'field' => '用户名',
                    ]),
                    new Column([
                        'title' => '姓名',
                        'field' => 'name',
                    ]),
                    new Column([
                        'title' => '年龄',
                        'field' => 'age',
                    ]),
                    // ...
                ],
                'count' => $this->getDataCount(),
                'data' => [$this, 'getData'],
                'pageSize' => 500,
            ])
        ]);
        return $this->sheets;
    }

    /**
     * 获取数据数量
     *
     * @return int
     */
    public function getDataCount(): int
    {
        return make(UserService::class)->getCount($this->getParams());
    }

    /**
     * 获取数据
     *
     * @param ExportCallbackParam $exportCallbackParam
     * @return array
     */
    public function getData(ExportCallbackParam $exportCallbackParam): array
    {
       return make(UserService::class)->getList($this->getParams(),$exportCallbackParam->pageSize,$exportCallbackParam->page);
    }
}

• 导入

<?php

namespace App\Excel\Import;

use Vartruexuan\HyperfExcel\Data\Import\ImportConfig;
use App\Exception\BusinessException;
use Hyperf\Collection\Arr;
use Vartruexuan\HyperfExcel\Data\Import\ImportRowCallbackParam;
use Vartruexuan\HyperfExcel\Data\Import\Sheet;
use function Hyperf\Support\make;

class UserImportConfig extends AbstractImportConfig
{
    public string $serviceName = '用户';

    /**
     * 是否异步
     *   true 则会推入队列之中  
     *
     * @var bool
     */
    public bool $isAsync = true;
    
    public function getSheets(): array
    {
        $this->setSheets([
            new Sheet([
                'name' => 'sheet1',
                'isSetHeader' => true,
                // 字段映射
                'headerMap' => [
                    '用户名' => 'username',
                    '姓名' => 'name',
                    '年龄' => 'age',
                    // ...
                ],
                // 数据回调
                'callback' => [$this, 'rowCallback']
            ])
        ]);
        return parent::getSheets();
    }

    public function rowCallback(ImportRowCallbackParam $param)
    {
        try {
            if (!empty($param->row)) {
                if (!Arr::get($param->row, 'username')) {
                    throw new BusinessException(ResultCode::FAIL, '用户名不能为空');
                }
                if (!Arr::get($param->row, 'name')) {
                    throw new BusinessException(ResultCode::FAIL, '姓名不能为空');
                }
                if (!Arr::get($param->row, 'age')) {
                    throw new BusinessException(ResultCode::FAIL, '年龄不能为空');
                }
                // 保存用户信息
                make(UserService::class)->saveUser($param->row);
               
                // ...
            }
        } catch (\Throwable $throwable) {
            // 异常信息将会推入进度消息中 <组件进度监听器会自动处理>
            // $param->driver->progress->pushMessage($param->config->getToken(),'也可以主动推送一些信息');
            throw new BusinessException(ResultCode::FAIL, '第' . $param->rowIndex . '行:' . $throwable->getMessage());
        }
    }
}

监听器

日志监听器

// config/autoload/listeners.php
return [
    Vartruexuan\HyperfExcel\Listener\ExcelLogListener::class
];

自定义监听器

• 继承Vartruexuan\HyperfExcel\Listener\BaseListener
• demo:实现一个自定义监听器,记录导入导出到数据库中 监听器

<?php

namespace App\Listener;

use App\Exception\BusinessException;
use App\Kernel\Http\ResultCode;
use App\Service\ExcelLogService;
use Hyperf\Di\Annotation\Inject;
use Vartruexuan\HyperfExcel\Event\AfterExport;
use Vartruexuan\HyperfExcel\Event\AfterExportSheet;
use Vartruexuan\HyperfExcel\Event\AfterImport;
use Vartruexuan\HyperfExcel\Event\AfterImportSheet;
use Vartruexuan\HyperfExcel\Event\BeforeExport;
use Vartruexuan\HyperfExcel\Event\BeforeImport;
use Vartruexuan\HyperfExcel\Listener\BaseListener;
use Vartruexuan\HyperfExcel\Event\Error;

class ExcelLogListener extends BaseListener
{
    #[inject]
    public ExcelLogService $excelLogService;
    
    function beforeExport(object $event)
    {
        $this->excelLogService->saveLog($event->config);
    }

    function beforeExportExcel(object $event)
    {
        // TODO: Implement beforeExportExcel() method.
    }

    function beforeExportData(object $event)
    {
        // TODO: Implement beforeExportData() method.
    }

    function beforeExportSheet(object $event)
    {
        // TODO: Implement beforeExportSheet() method.
    }

    function afterExport(object $event)
    {
        /**
         * @var AfterExport $event
         */
        $this->excelLogService->saveLog($event->config));
    }

    function afterExportData(object $event)
    {
        // TODO: Implement afterExportData() method.
    }

    function afterExportExcel(object $event)
    {
        // TODO: Implement afterExportExcel() method.
    }

    function afterExportSheet(object $event)
    {
        /**
         * @var AfterExportSheet $event
         */
        $this->excelLogService->saveLog($event->config));
    }

    function beforeImport(object $event)
    {
        /**
         * @var BeforeImport $event
         */
        $this->excelLogService->saveLog($event->config));
    }

    function beforeImportExcel(object $event)
    {
        // TODO: Implement beforeImportExcel() method.
    }

    function beforeImportData(object $event)
    {
        // TODO: Implement beforeImportData() method.
    }

    function beforeImportSheet(object $event)
    {
        // TODO: Implement beforeImportSheet() method.
    }

    function afterImport(object $event)
    {
        /**
         * @var AfterImport $event
         */
        $this->excelLogService->saveLog($event->config))
    }

    function afterImportData(object $event)
    {
        // TODO: Implement afterImportData() method.
    }

    function afterImportExcel(object $event)
    {
        // TODO: Implement afterImportExcel() method.
    }

    function afterImportSheet(object $event)
    {
        /**
         * @var AfterImportSheet $event
         */
       $this->excelLogService->saveLog($event->config));
    }

    function error(object $event)
    {
        /**
         * @var Error $event
         */
        $this->excelLogService->saveLog($event->config,[
            'remark' => $event->exception->getMessage(),
        ]));
    }
}

sql

CREATE TABLE `excel_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `token` varchar(64) NOT NULL DEFAULT '',
  `type` enum('export','import') NOT NULL DEFAULT 'export' COMMENT '类型:export导出import导入',
  `config_class` varchar(250) NOT NULL DEFAULT '',
  `config` json DEFAULT NULL COMMENT 'config信息',
  `service_name` varchar(20) NOT NULL DEFAULT '' COMMENT '服务名',
  `sheet_progress` json DEFAULT NULL COMMENT '页码进度',
  `progress` json DEFAULT NULL COMMENT '总进度信息',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态:1.待处理2.正在处理3.处理完成4.处理失败',
  `data` json NOT NULL COMMENT '数据信息',
  `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
  `url` varchar(300) NOT NULL DEFAULT '' COMMENT 'url地址',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_token` (`token`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='导入导出日志';

License

MIT