#yyds干货盘点#Hyperf结合PhpOffice/PhpSpreadsheet实现Excel&CSV文件导出导入
本文环境 Hyperf2.1，PHP7.3，Mysql5.7不懂的可以评论或联系我邮箱：owen@owenzhang.com
Hyperf & PhpSpreadsheet介绍
Hyperf 介绍
Hyperf 是基于 Swoole 4.5+ 实现的高性能、高灵活性的 PHP 协程框架，内置协程服务器及大量常用的组件，性能较传统基于 PHP-FPM 的框架有质的提升，提供超高性能的同时，也保持着极其灵活的可扩展性，标准组件均基于 PSR 标准 实现，基于强大的依赖注入设计，保证了绝大部分组件或类都是 可替换 与 可复用 的。
PhpOffice/PhpSpreadsheet 介绍
PhpSpreadsheet是一个用纯PHP编写的库，它提供了一组类，允许您读取和写入各种电子表格文件格式，如Excel和LibreOffice Calc。
PhpSpreadsheet是PHPExcel的下一个版本。它破坏了兼容性，从而大大提高了代码库质量（命名空间、PSR 合规性、使用最新的 PHP 语言功能等）。
因为所有的努力都转移到了PhpSpreadsheet上，PHPExcel将不再被维护。所有对 PHPExcel 的贡献、补丁和新功能都应针对 PhpSpreadsheet 分支。master
[*]GitHub PHPOffice/PhpSpreadsheet：用于读取和写入电子表格文件的纯PHP库 (github.com)
[*]官方使用文档Welcome to PhpSpreadsheet’s documentation - PhpSpreadsheet Documentation
[*]api文档 Documentation (phpoffice.github.io)
PhpOffice/PhpSpreadsheet 安装
使用composer将 PhpSpreadsheet 安装到你的项目中：
1.composer require phpoffice/phpspreadsheet
或者，如果您计划使用它们，还可以下载文档和示例：
1.composer require phpoffice/phpspreadsheet --prefer-source
文件导出导入&代码实例
csv文件导出
导出实例类文件
函数说明：使用hyperf框架的跨域中间件
[*]->withHeader 添加浏览器响应头
[*]->withBody 添加浏览器内容主体
[*]Headers 可以根据实际情况进行改写。
代码实例：
<?php
/**
* Created by PhpStorm.
* Created by OwenZhang at 2021/11/8 14:39
*/
namespace App\Common;
use Hyperf\HttpMessage\Stream\SwooleStream;
use Hyperf\HttpServer\Response;
class Csv
{
/**
$head = ['name'=>'名字','score'=>'得分'];
$data = [
['name' => '张三', 'score' => '80'],
['name' => '李四', 'score' => '90'],
['name' => '王五', 'score' => '60'],
];
$fileName = '测试'
*/
/**
* Describe: 导数数据 （csv 格式）
* @param array $head
* @param array $body
* @param string $fileName '测试.csv','测试.xlsx'
* @return \Psr\Http\Message\ResponseInterface
* Created by lkz at 2021/11/8 14:47
*/
static function export(array $head, array $body, string $fileName)
{
$head_keys = array_keys($head);
$head_values = array_values($head);
$fileData = self::utfToGbk(implode(',', $head_values)) . "\n";
foreach ($body as $value) {
$temp_arr = [];
foreach ($head_keys as $key) {
$temp_arr[] = $value[$key] ?? '';
}
$fileData .= self::utfToGbk(implode(',', $temp_arr)) . "\n";
}
$response = new Response();
$contentType = 'text/csv';
return $response->withHeader('content-description', 'File Transfer')
->withHeader('content-type', $contentType)
->withHeader('content-disposition', "attachment; filename={$fileName}")
->withHeader('content-transfer-encoding', 'binary')
->withHeader('pragma', 'public')
->withBody(new SwooleStream($fileData));
}
/**
* 字符转换（utf-8 => GBK）
* @param $data
* @return false|string
*/
static function utfToGbk($data)
{
return mb_convert_encoding($data,"GBK","UTF-8");
# return iconv('utf-8', 'GBK', $data);
}
}
调用导出实例函数方法
调用上面的csv文件导出类，浏览器调整新页面直接下载导出。
代码实例：
<?php
/**
* Describe: 列表导出
* Route: get /admin/badword_list_export
* Created by OwenZhang at 2021/12/13 10:14
*/
public function getBadwordListExport(): ResponseInterface
{
$page = (int)$this->request->input('page', 1);
$pageSize = (int)$this->request->input('page_size', 15);
$word = (string)$this->request->input('word', '');
$type = (string)$this->request->input('type', '');
$container = ApplicationContext::getContainer();
$exportArray = $container->get(BadwordServiceInterface::class)->getBadwordListExport($page, $pageSize, $word, $type);
//$exportArray 数组，每个里面键值和$header一样
set_time_limit(0);
ini_set('memory_limit', '5048M');
$filename = '敏感词数据' . date('ymdHis');
$header = [
'badword_id' => '敏感词id',
'word' => '敏感词',
'type' => '分类',
'style_name' => '应用区域',
'replace_word' => '替换词',
];
return Csv::export($header, $exportArray, $filename);
}
excel文件导出
导出实例类文件
[*]xls后缀excel文件导出的heard头：
xls='application/vnd.ms-excel'
[*]xlsx后缀excel文件导出的heard头：
xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
函数说明：
[*]构造函数 创建一个PhpSpreadsheet实例 __construct（）
[*]设置表头 $title=['id','标题','内容'] setHeader($title)
[*]添加表内容$data=[ , , ... ] addData($data)
[*]保存到服务器本地 $fileName=文件名 saveToLocal($fileName)
[*]直接从浏览器下载到本地，有问题，不使用，php://output 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复 saveToBrowser($fileName)
[*]保存临时文件在从浏览器自动下载到本地 saveToBrowserByTmp($fileName)
代码实例：
<?php
/**
* Created by PhpStorm.
* Created by OwenZhang at 2021/12/28 14:39
*/
namespace App\Common;
use Hyperf\HttpMessage\Stream\SwooleStream;
use Hyperf\HttpServer\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class ExportExcelHandle
{
private $sheet;
private $spreadsheet;
private $row;
//构造函数 创建一个PhpSpreadsheet实例
public function __construct()
{
// Create new Spreadsheet object
$this->spreadsheet = new Spreadsheet();
// Set document properties
$this->spreadsheet->getProperties()->setCreator('Maarten Balliauw')
->setLastModifiedBy('Maarten Balliauw')
->setTitle('Office 2007 XLSX Test Document')
->setSubject('Office 2007 XLSX Test Document')
->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
->setKeywords('office 2007 openxml php')
->setCategory('Test result file');
// Add some data
$this->spreadsheet->setActiveSheetIndex(0);
$this->sheet = $this->spreadsheet->getActiveSheet();
// Rename worksheet
$this->spreadsheet->getActiveSheet()->setTitle('Sheet1');
}
//设置表头
public function setHeader($title)
{
foreach ($title as $key => $item) {
$this->sheet->setCellValue(chr($key + 65) . '1', $item);
}
$this->row = 2; // 从第二行开始
return $this;
}
//添加表内容
public function addData($data)
{
foreach ($data as $item) {
$dataCol = 'A';
foreach ($item as $value) {
// 单元格内容写入
$this->sheet->setCellValue($dataCol . $this->row, $value);
$dataCol++;
}
$this->row++;
}
return $this;
}
//保存到服务器本地
public function saveToLocal($fileName)
{
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$this->spreadsheet->setActiveSheetIndex(0);
$fileName = $fileName . '.xlsx';
$url = '/storage/' . $fileName;
$outFilename = BASE_PATH . $url;
$writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
$writer->save($outFilename);
$this->spreadsheet->disconnectWorksheets();
unset($this->spreadsheet);
return ['path' => $outFilename, 'filename' => $fileName];
}
//直接从浏览器下载到本地，有问题，不使用
// php://output 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复
public function saveToBrowser($fileName)
{
$fileName = $fileName . '.xlsx';
//xls='application/vnd.ms-excel'
//xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
$writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
return $writer->save('php://output');
}
//保存临时文件在从浏览器自动下载到本地
public function saveToBrowserByTmp($fileName)
{
$fileName = $fileName . '.xlsx';
$writer = IOFactory::createWriter($this->spreadsheet, "Xlsx");
//保存到服务器的临时文件下
$writer->save("./tmp.xlsx");
//将文件转字符串
$content = file_get_contents('./tmp.xlsx');
//删除临时文件
unlink("./tmp.xlsx");
$response = new Response();
//xls='application/vnd.ms-excel'
//xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
$contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
return $response->withHeader('content-description', 'File Transfer')
->withHeader('content-type', $contentType)
->withHeader('content-disposition', "attachment; filename={$fileName}")
->withHeader('content-transfer-encoding', 'binary')
->withHeader('pragma', 'public')
->withBody(new SwooleStream((string)$content));
}
}
调用导出实例函数方法
函数说明:
[*]保存到服务器本地
$exportService->setHeader($title)->addData($data)->saveToLocal($fileName);
[*]下载服务器的文件到本地
$this->response->download($result['path'], $result['filename']);
[*]保存临时文件在从浏览器自动下载到本地
$exportService->setHeader($title)->addData($data)->saveToBrowserByTmp($fileName);
代码实例：
<?php
/**
* Describe: 列表导出
* Route: get /admin/badword_list_export
* Created by OwenZhang at 2021/12/13 10:14
*/
public function getBadwordListExport()
{
set_time_limit(0);
ini_set('memory_limit', '5048M');
$fileName = '敏感词数据' . date('YmdHis');
//表头
$title = [
'敏感词id',
'敏感词',
'分类',
'应用区域',
'替换词',
];
//表体数据
$list = ($this->applicationContext::getContainer())->get(BadwordServiceInterface::class)->getBadwordListExport();
$data = [];
foreach ($list as $value) {
$data[] = [
$value['badword_id'],
$value['word'],
$value['type'],
$value['style_name'],
$value['replace_word'],
];
}
$exportService = new ExportExcelHandle();
// //保存到服务器本地
// $result = $exportService->setHeader($title)->addData($data)->saveToLocal($fileName);
// //下载服务器的文件到本地
// return $this->response->download($result['path'], $result['filename']);
//保存临时文件在从浏览器自动下载到本地
return $exportService->setHeader($title)->addData($data)->saveToBrowserByTmp($fileName);
}
php://output问题分享
直接从浏览器下载到本地，有问题，不使用
php://output 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复
https://github.com/PHPOffice/PhpSpreadsheet/issues/28#issuecomment-263101387
excle文件导入（批量添加数据到Mysql）
调用PhpSpreadsheet读取函数方法
函数说明：
[*]获取上传文件的临时路径 $file['tmp_file']
[*]指定第一个工作表为当前 $spreadsheet->getSheet(0)->toArray();
[*]execl文件读取数据$container->get(BadwordServiceInterface::class)->getBadwordListImport($data);
[*]批量添加数据到mysql getBadwordListImportToMysql
代码实例：
<?php
/**
* Describe: 列表导入
* Route: get /admin/badword_list_import
* Created by OwenZhang at 2021/12/13 10:14
*/
public function getBadwordListImport(): ResponseInterface
{
$file = $this->request->file('import_file')->toArray();
//获取上传文件的临时路径
if (!isset($file['tmp_file'])) {
throw new BusinessException(ErrorCode::BUSINESS_ERROR, "文件上传失败");
}
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_file']);
//指定第一个工作表为当前
$data = $spreadsheet->getSheet(0)->toArray();
$container = ApplicationContext::getContainer();
$result = $container->get(BadwordServiceInterface::class)->getBadwordListImport($data);
return $this->success($result);
}
/**
* Describe: 列表导入-批量添加数据到mysql
* Route: get /admin/badword_list_import
* @param array $data 数据
* @return string
* Created by OwenZhang at 2021/12/16 10:14
*/
public function getBadwordListImportToMysql(array $data): string
{
$insertData = [];
$badwordId = (int)$this->ZucaiBadwordModel->orderBy('badword_id', 'desc')->value('badword_id');
foreach ($data as $key => $datum) {
//第一个数据是表头
if ($key != 0) {
$insertData[] = [
'badword_id' => $badwordId + $key + 1,
'word' => $datum,
'type' => $datum,
'style' => 1,
'replace_word' => '',
'app' => Context::get('app') ?? 1,
'created_at' => date('Y-m-d H:i:s'),
'updated_at' => date('Y-m-d H:i:s'),
];
}
}
if (!$insertData) {
return '添加失败';
}
return $this->ZucaiBadwordModel->insert($insertData) ? '添加成功' : '添加失败';
}
Buy me a cup of coffee ?
觉得对你有帮助，就给我打赏吧，谢谢！
微信赞赏码链接，点击跳转 ?
https://www.owenzhang.com/wechat_reward.png
https://blog.51cto.com/u_14633188/4859354
页:
[1]