使用前请先安装 phpoffice/phpexcel
安装命令:composer require phpoffice/phpexcel
namespace App\Tools;
use Exception;
use PHPExcel;
use PHPExcel_CachedObjectStorageFactory;
use PHPExcel_Cell_DataValidation;
use PHPExcel_Exception;
use PHPExcel_IOFactory;
use PHPExcel_Reader_Exception;
use PHPExcel_Settings;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Worksheet;
use PHPExcel_Writer_Exception;
use Swoft\Log\Log;
class ExcelTpl
{
/**
* Excel句柄
* @var
*/
private $objExcel;
/**
* sheet和文件名
* @var
*/
private $title;
/**
* 文件临时保存路径
* @var string
*/
private $filePath = "/var/cache/nginx/demo/";
/**
* @param string $title sheet和文件名
* @param array $fieldData 表头字段及格式
* @param array $dataInfo 数据信息(key,value必须与$fieldData字段对应)
* @param array $userInfo 用户信息(用于发送邮件,不为空则为邮件方式发送)
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
* fieldData.*.pColumn = 列 (string)
* fieldData.*.pValue = 值 (string)
* fieldData.*.width = 列宽,默认20 (string)
* fieldData.*.select = 下拉列表 (array)
* fieldData.*.comment = 备注 (string)
* fieldData.*.fontColor = 表头字体颜色 (string)
* fieldData.*.dateFormat = 字体格式 (string)
* fieldData.*.dateType = 单元格格式 (string)
* $fieldData = [
* [
* 'pColumn' => 'A',
* 'pValue' => '外观颜色',
* 'width' => 20,
* 'comment' => "多个颜色:多个以英文逗号隔开,如\n黑色,白色",
* 'fontColor' => "FFFFFF",
* ], [
* 'pColumn' => 'B',
* 'pValue' => '金额',
* 'width' => 20,
* 'dateType' => PHPExcel_Cell_DataType::TYPE_STRING,
* 'dateFormat' => "0.00",
* ], [
* 'pColumn' => 'C',
* 'pValue' => '变速箱类型',
* 'width' => 20,
* 'select' => [
* '手动变速箱', '自动变速箱', '手自一体变速箱', '无极变速箱', '双离合变速箱',
* ],
* ],
* ];
*
* $dataInfo = [
* [
* '红色', //对应 A:外观颜色
* '100', //对应 B:金额
* '手动变速箱', //对应 C:变速箱类型 (值需在select列表里)
* ], [
* '白色',
* '999',
* '双离合变速箱',
* ],
* ];
*/
public function handle(string $title, array $fieldData, array $dataInfo = [], array $userInfo = [])
{
ini_set('memory_limit','4096M');
$this->title = $title;
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize'=>'15MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
$objSheet = $this->getHandle();
$this->setHeader($objSheet, $fieldData);
$this->setData($objSheet, $fieldData, $dataInfo);
empty($userInfo) ? $this->outPut() : (new SendEmail())->handle($userInfo, [
'title' => $title,
'content' => "您导出的数据为 $title 请及时下载",
'attachment' => $this->saveFile()
]);
}
/**
* 返回工作表操作句柄
* @return PHPExcel_Worksheet 工作表操作句柄
* @throws PHPExcel_Exception
*/
protected function getHandle(): PHPExcel_Worksheet
{
$this->objExcel = new PHPExcel();
return $this->objExcel->getActiveSheet();
}
/**
* 设置表头
* @param PHPExcel_Worksheet $objSheet 工作表句柄
* @param array $fieldData 表头字段及格式
* @throws PHPExcel_Exception
*/
protected function setHeader(PHPExcel_Worksheet $objSheet, array $fieldData)
{
//设置sheet名
$objSheet->setTitle($this->title);
//所有单元格横向居中
$this->objExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//所有单元格纵向居中
$this->objExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置表头,行宽
$pCoordinate = '';
$isFontColor = false;
foreach ($fieldData as $fieldDatum) {
$pCoordinate = $fieldDatum['pColumn'] . '1';
$objSheet->setCellValue($pCoordinate, $fieldDatum['pValue']);
//设置行宽
$objSheet->getColumnDimension($fieldDatum['pColumn'])->setWidth($fieldDatum['width'] ?? 20);
if (!empty($fieldDatum['comment'])) {
$objSheet->getComment($pCoordinate)->getText()->createTextRun($fieldDatum['comment']);
}
if (!empty($fieldDatum['fontColor'])) {
$objSheet->getStyle($pCoordinate)->getFont()->getColor()->setRGB($fieldDatum['fontColor']);
$isFontColor = true;
}
if (!empty($fieldDatum['dateFormat'])) {
$objSheet->getStyle($fieldDatum['pColumn'])->getNumberFormat()->setFormatCode($fieldDatum['dateFormat']);
}
/*设置下拉*/
if (!empty($fieldDatum['select'])) {
$selectStr = implode(',', $fieldDatum['select']);
foreach ($fieldDatum['select'] as $key => $val) {
$objSheet->getCell($fieldDatum['pColumn'] . ($key + 2))->getDataValidation()
->setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
->setAllowBlank(false)
->setShowInputMessage(true)
->setShowErrorMessage(true)
->setShowDropDown(true)
->setErrorTitle('输入的值有误')
->setError('您输入的值不在下拉框列表内(请不要自己填写,应在下拉列表中选择)')
->setPromptTitle()
->setPrompt()
->setFormula1('"' . $selectStr . '"');
}
}
}
if (!$isFontColor) {
//表头字体颜色
$objSheet->getStyle('A1:' . $pCoordinate)->getFont()->getColor()->setRGB('FFFFFF');
//表头背景颜色
$objSheet->getStyle('A1:' . $pCoordinate)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('595959');
}
//设置表头字体大小和加粗
$objSheet->getStyle('A1:' . $pCoordinate)->getFont()->setSize(11)->setBold(true);
//表头填充样式
$objSheet->getStyle('A1:' . $pCoordinate)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
}
/**
* 设置数据
* @param PHPExcel_Worksheet $objSheet 工作表句柄
* @param array $fieldData 表头字段及格式
* @param array $dataInfo 数据信息
*/
protected function setData(PHPExcel_Worksheet $objSheet, array $fieldData, array $dataInfo): void
{
if (empty($dataInfo)) {
return;
}
foreach ($dataInfo as $key => $value) {
$key += 2;
foreach ($fieldData as $fieldKey => $fieldDatum) {
if (!empty($fieldDatum['dateType'])) {
$objSheet->setCellValueExplicit($fieldDatum['pColumn'] . $key, $value[$fieldKey], $fieldDatum['dateType']);
continue;
}
$objSheet->setCellValue($fieldDatum['pColumn'] . $key, $value[$fieldKey]);
}
}
}
/**
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
*/
protected function outPut()
{
$fileName = $this->title . date('YmdHis ') . '.xlsx';
$objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, 'Excel2007');
ob_start();
$objWriter->save('php://output');
//设置header头
$res = response()->getSwooleResponse();
$res->header('Content-Type', 'application/vnd.ms-excel');
$res->header('Content-Disposition', 'attachment;filename=' . $fileName);
$res->header('Cache-Control', 'max-age=0');
$res->write(ob_get_clean());
}
/**
* 保存文件
* @return string
*/
protected function saveFile(): string
{
if (!file_exists($this->filePath)) {
mkdir($this->filePath, 0777, true);
}
$path = $this->filePath . $this->title . '.xlsx';
try {
$objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, 'Excel2007');
$objWriter->save($path);
return $path;
} catch (Exception $e) {
Log::error(date("Y-m-d H:i:s") . '--' . $path . '--文件保存失败', [$e->getMessage(), $e->getFile(), $e->getLine()]);
return false;
}
}
/**
* 数字生成字母,1=A开始
* @param int $num
* @return string
*/
public function numToExcelLetter(int $num): string
{
$result = '';
while ($num > 0) {
$mod = $num % 26;
$num = (int)($num / 26);
if ($mod == 0) {
$num--;
$temp = 'Z' . $result;
} else {
$temp = chr(64 + $mod) . $result;
}
$result = $temp;
}
return $result;
}
}