基于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);