1.引用扩展包
oss用于文件上传
composer require aliyuncs/oss-sdk-php
composer require phpoffice/phpspreadsheet
2.定义excel模板字段
2.1模板接口定义
<?php
declare(strict_types=1);
namespace App\Service\ExcelTemplate;
interface ExcelTemplateInterface
{
/**
* 获取sheet名称
* @return string
*/
public function getSheetName():string;
/**
* 获取字段配置
* @return array
*/
public function getField():array;
/**
* 获取模板名称
* @return string
*/
public function getTemplateName():string;
}
2.2实现接口定义模板
<?php
declare(strict_types=1);
namespace App\Service\ExcelTemplate;
/**
* 模板1
* @package App\Service\ExcelTemplate;
*/
class TestTemplate implements ExcelTemplateInterface
{
/**
* 配置解析字段
* @return string[]
*/
public function getField(): array
{
return [
"name" => "姓名",
"remark" => "备注",
];
}
/**
* 配置sheet名称
* @return string
*/
public function getSheetName(): string
{
return "测试";
}
/**
* 配置模板名称
* @return string
*/
public function getTemplateName(): string
{
return "测试模板";
}
}
3.模板处理类
<?php
declare(strict_types=1);
namespace App\Service;
use App\Exception\ErrorException;
use App\Service\ExcelTemplate\ExcelTemplateInterface;
use App\Utils\OssClient;
use Hyperf\Context\Context;
use Hyperf\HttpMessage\Upload\UploadedFile;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
/**
* 表格处理类
* Class ExcelService
* @package App\Service;
*/
class ExcelService
{
/**
* @var ExcelTemplateInterface
*/
private ExcelTemplateInterface $template;
/**
* 传入解析模板类
* ExcelService constructor.
* @param ExcelTemplateInterface $template
*/
public function __construct(ExcelTemplateInterface $template)
{
$this->template = $template;
}
/**
* 解析excel文件内容(根据模板)
* @param UploadedFile $file
* @return array
* @throws Exception
*/
public function parsing(UploadedFile $file): array
{
$fileName = $file->getFilename();
$fileName = $fileName.'-'.$file->getClientFilename();
$this->initDir();
$filePath = BASE_PATH .'/exls/'.$fileName;
$file->moveTo($filePath);
// 通过 IOFactory 加载 Excel 文件
$spreadsheet = IOFactory::load($filePath);
// 获取模板需要取值的工作表
$sheetName = $this->template->getSheetName();
$sheetNames = $spreadsheet->getSheetNames();
if(!in_array($sheetName,$sheetNames)){
throw new ErrorException("模板工作表[{$sheetName}]不存在,无法解析");
}
//获取单个tab对象
$sheet = $spreadsheet->getSheetByName($sheetName);
//获取单个tab对象的列数和行数
$row = $sheet->getHighestRow();
$column = $sheet->getHighestColumn();//英文最大值
//数值最大值
$columnIndex = Coordinate::columnIndexFromString($column);
//头部字段解析
$columnHeaders = [];
$fieldList = array_flip($this->template->getField());
for ($i = 1; $i <= $columnIndex; $i++) {
$excelKey = Coordinate::stringFromColumnIndex($i);
$headerName = $sheet->getCell($excelKey.'1')->getValue();
if(isset($fieldList[$headerName])){
$columnHeaders[$i] = [
"excel_key" => $excelKey,
"field" => $fieldList[$headerName]??'',
"field_name" => $headerName
];
}
}
//头部以下的内容解析
$dataList = [];
for ($rowIndex=2;$rowIndex<=$row;$rowIndex++){
foreach ($columnHeaders as $column){
$value = $sheet->getCell($column['excel_key'].$rowIndex)->getValue();
if($value instanceof RichText){
$value = $value->getPlainText();
}
$dataList[$rowIndex-2][$column['field']] = (string)$value;
}
}
return $dataList;
}
/**
* 数据写入excel文件类
* @param array $dataList
* @return Spreadsheet
* @throws Exception
*/
private function generate(array $dataList): Spreadsheet
{
$fieldList = $this->template->getField();
$workSheet = new Worksheet();
//sheet名称定义
$workSheet->setTitle($this->template->getSheetName());
//第一行写入头信息
$columnIndex = 1;
foreach ($fieldList as $fieldName){
$excelKey = Coordinate::stringFromColumnIndex($columnIndex);var_dump($excelKey);
$workSheet->setCellValue($excelKey.'1',$fieldName);
$columnIndex++;
}
//遍历数据写入表格
foreach ($dataList as $k=>$data){
$columnIndex = 1;
$rowIndex = $k+2;
foreach ($fieldList as $field=>$fieldName){
$excelKey = Coordinate::stringFromColumnIndex($columnIndex);
$workSheet->setCellValue($excelKey.$rowIndex,$data[$field]??"");
$columnIndex++;
}
}
//创建excel文件类
$spreadsheet = new Spreadsheet();
$spreadsheet->addSheet($workSheet,0);
$spreadsheet->setActiveSheetIndex(0);
return $spreadsheet;
}
/**
* 数据导出
* @param array $dataList
* @return mixed
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception|Exception
*/
public function exportExcel(array $dataList)
{
Log::getInstance()->info("导出数据",['count'=>count($dataList)]);
$this->initDir();
$fileName = $this->template->getSheetName().(Context::get("userInfo")["id"]??0).'.xls';
$spreadsheet = $this->generate($dataList);
$writer = IOFactory::createWriter($spreadsheet,IOFactory::WRITER_XLSX);
$filePath = BASE_PATH .'/exls/'.$fileName;
$writer->save($filePath);
//上传OSS
$result = (new OssClient())->uploadFile('zz_goods_center/'.$fileName,$filePath);
if(!isset($result['oss-request-url'])){
throw new ErrorException("OSS处理失败");
}
return $result['oss-request-url'];
}
/**
* 获取模板下载链接
* @return mixed
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception|Exception
*/
public function getTemplateDownloadUrl()
{
$this->initDir();
$spreadsheet = $this->generate([]);
$writer = IOFactory::createWriter($spreadsheet,IOFactory::WRITER_XLSX);
$filePath = BASE_PATH .'/exls/'.$this->template->getTemplateName().'.xls';
$writer->save($filePath);
//上传OSS
$result = (new OssClient())->uploadFile($this->template->getTemplateName().'.xls',$filePath);
if(!isset($result['oss-request-url'])){
throw new ErrorException("OSS处理失败");
}
return $result['oss-request-url'];
}
/**
* 目录初始化
*/
private function initDir()
{
if(!is_dir(BASE_PATH .'/exls/')){
$dir = opendir(BASE_PATH);
mkdir('exls');
closedir($dir);
}
}
}