PHP code example of goletter / hyperf-excel
1. Go to this page and download the library: Download goletter/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/ */
goletter / hyperf-excel example snippets
$excel = ApplicationContext::getContainer()->get(\Goletter\HyperfExcel\ExcelInterface::class);
/**
* @var \Goletter\HyperfExcel\ExcelInterface $excel
*/
$exportData = $excel->export(new DemoExportConfig([
// 额外参数
'params'=> $request->all(),
]));
/**
* @var \Goletter\HyperfExcel\ExcelInterface $excel
* @
*/
$exportData = $excel->import(new DemoImportConfig()->setPath('/d/xxx.xlsx'));
/**
* @var \Goletter\HyperfExcel\ExcelInterface $excel
* @var \Goletter\HyperfExcel\Progress\ProgressRecord $progressRecord
*/
$progressRecord = $excel->getProgressRecord($token);
/**
* @var \Goletter\HyperfExcel\ExcelInterface $excel
* @var \Goletter\HyperfExcel\Progress\ProgressRecord $progressRecord
*/
$isEnd = false; // 是否结束
$progressRecord = $excel->popMessageAndIsEnd($token, 50, $isEnd);
namespace App\Excel\Export;
use Goletter\HyperfExcel\Data\Export\ExportConfig;
use Goletter\HyperfExcel\Data\Export\Column;
use Goletter\HyperfExcel\Data\Export\ExportCallbackParam;
use Goletter\HyperfExcel\Data\Export\Sheet;
use Goletter\HyperfExcel\Data\Export\SheetStyle;
class DemoExportConfig extends ExportConfig
{
public string $serviceName = 'demo';
// 是否异步
public bool $isAsync = true;
// 输出类型
// OUT_PUT_TYPE_UPLOAD 导出并上传
// OUT_PUT_TYPE_OUT 直接同步输出
public string $outPutType = self::OUT_PUT_TYPE_UPLOAD;
// 页码配置
public function getSheets(): array
{
$this->setSheets([
new Sheet([
'name' => 'sheet1',
'columns' => [
new Column([
'title' => '用户名',
'field' => 'username',
// 子列
'children' => []
]),
new Column([
'title' => '姓名',
'field' => 'name',
]),
new Column([
'title' => '年龄',
'field' => 'age',
]),
// ...
],
'count' => $this->getDataCount(), // 数据数量
'data' => [$this, 'getData'], // 数据
'pageSize' => 500, // 每页导出数量<分批导出>
'style'=> new SheetStyle(), // 页码样式
])
]);
return $this->sheets;
}
/**
* 获取数据数量
*
* @return int
*/
public function getDataCount(): int
{
// 测试数据 <实际业务可能是查询数据库>
return 1000;
}
/**
* 获取数据
*
* @param ExportCallbackParam $exportCallbackParam
* @return array
*/
public function getData(ExportCallbackParam $exportCallbackParam): array
{
// $exportCallbackParam->page; // 当前页码
// $exportCallbackParam->pageSize;// 页码数量
msleep(500);
var_dump($this->params);
// 测试数据 <实际业务可能是查询数据库>
for ($i = 0; $i < $exportCallbackParam->pageSize; $i++) {
$d[] = [
'username' => '哈哈',
'name' => '测试'
'age' => 11,
];
}
// 输出信息
$progress= ApplicationContext::getContainer()->get(ProgressInterface::class);
$progress->pushMessage($this->token,"页码:".$exportCallbackParam->page .",数量：". $exportCallbackParam->pageSize);
return $d ?? [];
}
}
new Sheet([
// 页码名
'name' => 'sheet1',
// 列配置
'columns' => [
new \Goletter\HyperfExcel\Data\Export\Column([]),
],
// 数据数量
'count' => 0,
// 数据(array|callback)
'data' => function(\Goletter\HyperfExcel\Data\Export\ExportCallbackParam $callbackParam){
return [];
},
// 分批导出数
'pageSize' => 1,
// 页码样式
'style'=> new \Goletter\HyperfExcel\Data\Export\SheetStyle([]);
]),
new Column([
// 列名
'title' => "一级列",
// 宽度
//'width' => 32,
// 高度
'height' => 58,
// header 单元样式
'headerStyle' => new Style([
'wrap' => true,
'fontColor' => 0x2972F4,
'font' => '等线',
'align' => [Style::FORMAT_ALIGN_LEFT, Style::FORMAT_ALIGN_VERTICAL_CENTER],
'fontSize' => 10,
]),
// 子列 <自动跨列>
'children' => [
new Column([
'title' => '二级列1',
'field' => 'key1', // 数据字段名
'width' => 32, // 宽度
// 头部单元格样式
'headerStyle' => new Style([
'align' => [Style::FORMAT_ALIGN_CENTER],
'bold' => true,
]),
]),
// ...
],
]),
new \Goletter\HyperfExcel\Data\Export\SheetStyle([
// 网格线
'gridline'=> \Goletter\HyperfExcel\Data\Export\SheetStyle::GRIDLINES_HIDE_ALL,
// 缩放 (10 <= $scale <= 400)
'zoom'=> 50,
// 隐藏当前页码
'hide' => false,
// 选中当前页码
'isFirst' => true,
])
new Style([
'wrap' => true,
'fontColor' => 0x2972F4,
'font' => '等线',
'align' => [Style::FORMAT_ALIGN_LEFT, Style::FORMAT_ALIGN_VERTICAL_CENTER],
'fontSize' => 10,
])
namespace App\Excel\Import;
use Goletter\HyperfExcel\Data\Import\ImportConfig;
use App\Exception\BusinessException;
use Hyperf\Collection\Arr;
use Goletter\HyperfExcel\Data\Import\ImportRowCallbackParam;
use Goletter\HyperfExcel\Data\Import\Sheet;
use Goletter\HyperfExcel\Data\Import\Column;
class DemoImportConfig extends AbstractImportConfig
{
public string $serviceName = 'demo';
// 是否异步 <默认 async-queue>
public bool $isAsync = true;
public function getSheets(): array
{
$this->setSheets([
new Sheet([
'name' => 'sheet1',
'headerIndex' => 1, // 列头下标<0则无列头>
'columns' => [
new Column([
'title' => '用户名', // excel中列头
'field' => 'username', // 映射字段名
'type' => Column::TYPE_STRING, // 数据类型(默认 string)
]),
new Column([
'title' => '年龄',
'field' => 'age',
'type' => Column::TYPE_INT,
]),
new Column([
'title' => '身高',
'field' => 'height',
'type' => Column::TYPE_INT,
]),
],
// 数据回调
'callback' => [$this, 'rowCallback']
])
]);
return parent::getSheets();
}
public function rowCallback(ImportRowCallbackParam $importRowCallbackParam)
{
// $importRowCallbackParam->row; // 行数据
// $importRowCallbackParam->rowIndex; // 行下标
// $importRowCallbackParam->sheet;// 当前页码
try {
// 参数校验
// 业务操作
var_dump($importRowCallbackParam->row);
} catch (\Throwable $throwable) {
// 异常信息将会推入进度消息中 | 自动归为失败数
throw new BusinessException(ResultCode::FAIL, '第' . $param->rowIndex . '行:' . $throwable->getMessage());
}
}
}
new Sheet([
// 页码名
'name' => 'sheet1',
// 列头下标<0则无列头>
'headerIndex' => 1,
// 列配置
'columns' => [
new Column([
'title' => '用户名', // excel中列头
'field' => 'username', // 映射字段名
'type' => Column::TYPE_STRING, // 数据类型(默认 string)
]),
],
// 数据回调
'callback' => function(\Goletter\HyperfExcel\Data\Import\ImportRowCallbackParam $callbackParam){}
])
new Column([
// 列头
'title' => '身高',
// 映射字段名
'field' => 'height',
// 读取类型
'type' => Column::TYPE_INT,
]),
declare(strict_types=1);
return [
'default' => 'xlswriter',
'drivers' => [
'xlswriter' => [
'driver' => \Goletter\HyperfExcel\Driver\XlsWriterDriver::class,
]
],
'options' => [
// filesystem 配置
'filesystem' => [
'storage' => 'local', // 默认本地
],
// 导出配置
'export' => [
'rootDir' => 'export', // 导出根目录
],
],
// 日志
'logger' => [
'name' => 'hyperf-excel',
],
// queue配置
'queue' => [
'name' => 'default',
],
// 进度处理
'progress' => [
'enable' => true,
'prefix' => 'HyperfExcel',
'expire' => 3600, // 数据失效时间
],
// db日志
'dbLog' => [
'enable' => true,
'model' => \Goletter\HyperfExcel\Db\Model\ExcelLog::class,
],
// 清除临时文件
'cleanTempFile' => [
'enable' => true, // 是否允许
'time' => 1800, // 文件未操作时间(秒)
'interval' => 3600,// 间隔检查时间
],
];
[
\Goletter\HyperfExcel\Strategy\Token\TokenStrategyInterface::class => \Goletter\HyperfExcel\Strategy\Token\UuidStrategy::class
]
[
\Goletter\HyperfExcel\Strategy\Path\ExportPathStrategyInterface::class => \Goletter\HyperfExcel\Strategy\Path\DateTimeExportPathStrategy::class
]
[
\Goletter\HyperfExcel\Queue\ExcelQueueInterface::class => Goletter\HyperfExcel\Queue\AsyncQueue\ExcelQueue::class
]
// config/autoload/listeners.php
return [
Goletter\HyperfExcel\Listener\ExcelLogListener::class,
];
// config/autoload/listeners.php
return [
Goletter\HyperfExcel\Listener\ExcelLogDbListener::class,
];
shell
php bin/hyperf.php vendor:publish goletter/hyperf-excel
bash
php bin/hyperf.php excel:export "\App\Excel\DemoExportConfig"
bash
# 本地文件
php bin/hyperf.php excel:import "\App\Excel\DemoImportConfig" "/d/xxx.xlsx"
# 远程文件
php bin/hyperf.php excel:import "\App\Excel\DemoImportConfig" "https://xxx.com/xxx.xlsx"
bash
php bin/hyperf.php excel:progress 424ee1bd6db248e09b514231edea5f04
bash
php bin/hyperf.php excel:message 424ee1bd6db248e09b514231edea5f04
bash
php bin/hyperf.php migrate --path=./vendor/goletter/hyperf-excel/src/migrations