PHP code example of ezijing / hyperf-excel

1. Go to this page and download the library: Download ezijing/hyperf-excel library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.
    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

ezijing / hyperf-excel example snippets



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


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



declare(strict_types=1);

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

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.'));
    }
}

$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);



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);
    }
}

$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);



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);
    }
}

$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);



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);
    }
}

shell
> composer f.php vendor:publish hyperf/translation # 发布 Translation 组件的文件
> php bin/hyperf.php vendor:publish hyperf/validation # 发布验证器组件的文件:
shell
>  php bin/hyperf.php vendor:publish ezijing/hyperf-excel