ezijing/yii2-excel

yii2 excel工具包

Maintainers

Details

github.com/wojiushidashen/yii2-excel

Homepage

Source

Issues

Installs: 5

Dependents: 0

Suggesters: 0

Security: 0

Stars: 1

Watchers: 1

Forks: 0

Open Issues: 0

v1.0.6 2022-09-22 00:28 UTC

Requires

MIT 69001297247968a766e9996eaeabac7386ce4578

  • Yizheng Wang <18435134258.woop@163.com>

excelyii2

This package is auto-updated.

Last update: 2024-12-25 06:49:34 UTC

README

安装

3、配置文件 main-local.php

<?php

declare(strict_types=1);

return [
    'components' => [
        'excel' => 'Ezijing\Yii2Excel\Excel',
    ],
];

使用

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 = \Yii::$app->excel->exportExcelForASingleSheet($tableName, $data);

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

<?php

declare(strict_types=1);

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

    public function init()
    {
        $this->excel = \Yii::$app->excel;
    }

    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 = \Yii::$app->excel->exportExcelWithMultipleSheets($tableName, $data);
print_r($res);

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

<?php

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

    public function init()
    {
        $this->excel = \Yii::$app->excel;
    }

    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 Controller
{
    /**
     * @var Excel
     */
    protected $excel;

    public function init()
    {
         $this->excel = \Yii::$app->excel;
    }

    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"
    }
]