#yyds干货盘点#Hyperf结合PhpOffice/PhpSpreadsheet实现Excel&CSV文件导出导入

本文环境 Hyperf2.1，PHP7.3，Mysql5.7

不懂的可以评论或联系我邮箱：owen@owenzhang.com

Hyperf 是基于 ​​Swoole 4.5+​​​ 实现的高性能、高灵活性的 PHP 协程框架，内置协程服务器及大量常用的组件，性能较传统基于 ​​PHP-FPM​​​ 的框架有质的提升，提供超高性能的同时，也保持着极其灵活的可扩展性，标准组件均基于 ​​PSR 标准​​​ 实现，基于强大的依赖注入设计，保证了绝大部分组件或类都是 ​​可替换​​​ 与 ​​可复用​​ 的。

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)​​

使用​​composer​​将 PhpSpreadsheet 安装到你的项目中：

1.composer require phpoffice/phpspreadsheet

或者，如果您计划使用它们，还可以下载文档和示例：

1.composer require phpoffice/phpspreadsheet --prefer-source

函数说明：使用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 owenzhang 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);
}

1. xls后缀excel文件导出的heard头：
   ​​​xls='application/vnd.ms-excel'​​
2. xlsx后缀excel文件导出的heard头：
   ​​​xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'​​

函数说明：

• 构造函数 创建一个PhpSpreadsheet实例 ​​__construct（）​​
• 设置表头 ​​$title=['id','标题','内容'] setHeader($title)​​
• 添加表内容​​$data=[ [1,标题1，内容1], [2,标题2，内容2], ... ] 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​​ 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复

​​https://github.com/PHPOffice/PhpSpreadsheet/issues/28#issuecomment-263101387​​

函数说明：

• 获取上传文件的临时路径 ​​$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[1],
 'type' => $datum[2],
 '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) ? '添加成功' : '添加失败';
}

觉得对你有帮助，就给我打赏吧，谢谢！

微信赞赏码链接，点击跳转:

​​https://www.owenzhang.com/wechat_reward.png​​