<?php /** $service = new KernelExcelExportService('accountStatement', '对账单'); $service->setTitle([ 'a' => 'testA', 'b' => 'testB', 'product:c' => 'testC', 'product:d' => 'testD', 'product:e' => 'testE' ]) //设置标题 合并单元格在数组product ->setDefaultStyle(12) //设置列宽样式 ->format([ ['a' => 1, 'b' => 2, 'product' => [['c' => 3, 'd' => 4, ]]], ['a' => 2, 'b' => 3, 'product' => [['c' => 4, 'd' => 5, ], ['c' => 6, 'd' => 7, ]]], ['a' => 3, 'b' => 4, 'product' => [['c' => 5, 'd' => 6, ], ['c' => 7, 'd' => 8, ], ['c' => 9, 'd' => 10,]]], ], 'product' )->output(); 返回真实下载路径 * * 导出如下 +-------+------------+--------------------+------------+ | testA | testB | testC | testD | +-------+------------+--------------------+------------+ | 1 | 2 | 3 | 4 | +-------+------------+--------------------+------------+ | | | 4 | 5 | | 2 | 3 |--------------------+------------+ | | | 5 | 6 | +-------+------------+--------------------+------------+ | | | 5 | 6 | | | |--------------------+------------+ | 3 | 4 | 7 | 8 | | | |--------------------+------------+ | | | 9 | 10 | +-------+------------+--------------------+------------+ */ namespace Meibuyu\Micro\Tools; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; class KernelExcelExportService { private $fileObject; private $headers ; //标题 private $dataRowStart =2; //起始行 /** * * KernelExcelExportService constructor. * @param $dir 文件存储目录 * @param $fileName 导出文件名 默认加上时间 */ public function __construct($dir,$fileName) { $fileName = "{$fileName}_" .date('YmdHis') . '.xlsx'; $dir = BASE_PATH . '/public/export/'.$dir; !is_dir($dir) && !mkdir($dir, 0777, true); //不存在则创建 $this->fileObject = (new \Vtiful\Kernel\Excel(['path' => $dir]))->constMemory($fileName); } /** * @return mixed */ public function getFileObject() { return $this->fileObject; } /** * 设置居中样式 * @param $width 固定自动列宽 * @param $definedWith 自定义列宽 * @author Liu lu * date 2023-04-14 */ public function setDefaultStyle($autoWidth=12,$definedWith=null) { $fileHandle = $this->fileObject->getHandle(); $alignStyle = (new \Vtiful\Kernel\Format($fileHandle)) ->align( \Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER) ->align( \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER) ->toResource(); if(is_null($definedWith)){ $this->fileObject->setColumn( 'A:'.Coordinate::stringFromColumnIndex(count($this->headers)), $autoWidth, $alignStyle ); }else{ foreach ($definedWith as $columnTag=>$width){ $this->fileObject->setColumn( $columnTag, $width, $alignStyle ); } } return $this; } /** * 设置标题 * @param array $titles * @return $this * @author Liu lu * date 2023-04-14 */ public function setTitle(array $titles) { $this->headers = $titles; $this->fileObject->header(array_values($titles)); return $this; } private function setCellValue($column,$row,$value) { $this->fileObject->insertText($row-1,$column-1,$value); } /** * @param $data * @param null $splitField * @return $this * @author Liu lu * date 2023-04-14 */ public function format( $data, $splitField = null) { if (empty($data)) return $this; $titleKeys = array_keys($this->headers); $recordLine = $this->dataRowStart; foreach ($data as $i => $value) { $mergeArr = []; //需要合并的项 $renderingArr = []; //每列的写 array_walk($titleKeys, function ($title, $index) use ($recordLine, $value, $splitField,&$mergeArr,&$renderingArr) { if (!$splitField) { $this->setCellValue($index+1 , $recordLine, array_reduce(explode('.',$title),function ($carry,$i){ return $carry[$i]??''; },$value)); return; } $count = count($value[$splitField])?:1; //合并单元格 if (!strstr($title, ':')) { $mergeValue = array_reduce(explode('.',$title),function ($carry,$i){ return $carry[$i]??''; },$value); if($count>1){ $mergeArr[] = [ Coordinate::stringFromColumnIndex($index+1).$recordLine .':'. Coordinate::stringFromColumnIndex($index+1).($recordLine + $count-1), $mergeValue]; }else{ $renderingArr[$recordLine] [] = [$index+1,$mergeValue]; } return ; } if(empty($value[$splitField])){ return ; } foreach ($value[$splitField] as $i =>$v){ $matchKey = str_replace($splitField.':','',$title); $renderingArr[$recordLine+$i] [] = [$index+1,array_reduce(explode('.',$matchKey),function ($carry,$i){ return $carry[$i]??''; },$v)]; } }); if(!empty($mergeArr)){ foreach ($mergeArr as $merge){ $this->fileObject->mergeCells($merge[0],$merge[1]); } } if(!empty($renderingArr)){ foreach ( $renderingArr as $row=> $render){ foreach ($render as $r){ list($column,$columnValue) = $r; $this->setCellValue($column,$row,$columnValue); } } } if ($splitField) { $recordLine += count($value[$splitField])?:1; } else { $recordLine++; } } $this->dataRowStart = $recordLine; //供下次导出循环使用 return $this; } public function output() { return $this->fileObject->output(); } }