基于PhpSpreadsheet库的封装类

基于PhpSpreadsheet库的封装类

工作中，时常会使用到将数据导出execl文件的需求，基于PhpSpreadsheet库封装好class类。

使用composer工具安装PhpSpreadsheet

使用composer命令安装，笔者用的是1.8版本

composer require phpoffice/phpspreadsheet 1.8

封装好的class

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class ExportHelper {
	//表头样式
	private static $styleArray = [
		'font' => [
			'bold' => true,
			'color' => [
				'argb' => 'FFFFFFFF',
			],
			'size' => 14,
		],
		'alignment' => [
			'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
		],
		'fill' => [
			'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
			'rotation' => 90,
			'startColor' => [
				'argb' => '4F81BD',
			],
			'endColor' => [
				'argb' => '4F81BD',
			],
		],
	];
	/**
	 * 导出数据
	 * @param   $filename   文件名
	 * @param   $title      表头
	 * @param   $data       数据
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
	 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
	 */
	public static function downloadExcel($filename, $title, $data) {
		$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
			'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
		$spreadSheet = new Spreadsheet();
		$workSheet = $spreadSheet->getActiveSheet(); //获取活动的表单
		//表头长度
		$title_count = count($title);
		for ($i = 0; $i < $title_count; $i++) {
			$workSheet->setCellValue($cellName[$i] . '1', $title[$i]);
			// $workSheet->getColumnDimension($cellName[$i])->setAutoSize(true);
			$workSheet->getColumnDimension($cellName[$i])->setWidth(22);
		}
		//n列数据
		$column = $i;
		//行下标
		$index = 0;
		foreach ($data as $row_index => $row_value) {
			//写入excel第n行
			$index = $row_index + 2;
			//列下标
			$cell_index = 0;
			foreach ($row_value as $k => $v) {
				//设置第n行数据的A,B,C...列
				if (!empty($v) || $v == '0') {
					$workSheet->setCellValueExplicit($cellName[$cell_index] . $index, $v, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
				}
				$cell_index++;
			}
			$workSheet->getRowDimension($index)->setRowHeight(20); //设置行高
		}
		//设置表头样式
		$workSheet->getStyle('A1:' . $cellName[$title_count - 1] . '1')->applyFromArray(self::$styleArray);
		$workSheet->getRowDimension('1')->setRowHeight(25);
		//n行数据加1行表头
		$row = $index;
		// 设置垂直居中
		$spreadSheet->getActiveSheet()->getStyle("A1:" . ($cellName[$column - 1]) . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
		// 设置水平居中
		$spreadSheet->getActiveSheet()->getStyle("A1:" . ($cellName[$column - 1]) . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
		header("Content-Disposition: attachment;filename=$filename.xlsx");
		header('Content-Type: applicsation/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		$writer = IOFactory::createWriter($spreadSheet, 'Xlsx');
		ob_end_clean();
		$writer->save('php://output');
	}
}

使用

<?php
$title = ["日期","新增用户","活跃用户"];
$data = [
	["2020-07-24",'22','122'],
	["2020-07-23",'22','555'],
	["2020-07-22",'22','444'],
];
ExportHelper::downloadExcel('test',$title,$data);