400 lines
16 KiB
PHP
400 lines
16 KiB
PHP
<?php
|
||
namespace extend\excel;
|
||
use think\Exception;
|
||
use PhpOffice\PhpSpreadsheet\IOFactory;
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
use PhpOffice\PhpSpreadsheet\Style\Border;
|
||
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
||
use PhpOffice\PhpSpreadsheet\Style\Color;
|
||
/**
|
||
* 导出
|
||
* Class Export
|
||
* @package extend\excel
|
||
*/
|
||
class Export
|
||
{
|
||
/**
|
||
* 配置项
|
||
* @var array
|
||
*/
|
||
private $config = [
|
||
// 默认行高
|
||
'row_height' => 24,
|
||
// 默认列宽
|
||
'column_width' => 18,
|
||
// 字体
|
||
'font_family' => '微软雅黑',
|
||
// 字体大小
|
||
'font_size' => 11,
|
||
// 字体颜色
|
||
'color' => '000000',
|
||
// 垂直对齐方式
|
||
'visibility_align' => Alignment::VERTICAL_CENTER,
|
||
// 水平对齐方式
|
||
'text_align' => Alignment::HORIZONTAL_CENTER,
|
||
// 边框样式
|
||
'border_style' => Border::BORDER_THIN,
|
||
// 边框颜色
|
||
'border_color' => '000000',
|
||
// 分割线填充样式
|
||
'cut_fill_style' => Fill::FILL_SOLID,
|
||
// 分割线填充颜色
|
||
'cut_fill_color' => 'CCCCCC',
|
||
// 是否自动换行
|
||
'is_auto_wrap' => true,
|
||
// 行开始位置
|
||
'row_start_num' => 0,
|
||
// 表头是否冻结
|
||
'header_freeze' => false,
|
||
// 是否筛选
|
||
'auto_filter' => true
|
||
];
|
||
|
||
/**
|
||
* 单元格
|
||
* @var array
|
||
*/
|
||
private $cellKeys = [
|
||
'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'
|
||
];
|
||
|
||
/**
|
||
* Excel实例对象
|
||
* @var null
|
||
*/
|
||
private $excelObj = null;
|
||
|
||
/**
|
||
* 文件后缀
|
||
* @var string
|
||
*/
|
||
private $fileExt = '.xlsx';
|
||
|
||
/**
|
||
* Excel 写入类型
|
||
* @var string
|
||
*/
|
||
private $writerType = 'Xlsx';
|
||
|
||
/**
|
||
* 构造函数
|
||
* Export constructor.
|
||
* @param array $config
|
||
*/
|
||
public function __construct(array $config = [])
|
||
{
|
||
$this->excelObj = new Spreadsheet();
|
||
$this->init($config);
|
||
}
|
||
|
||
/**
|
||
* 初始化
|
||
* @param array $config
|
||
*/
|
||
private function init(array $config = [])
|
||
{
|
||
$this->config = array_merge($this->config, $config);
|
||
}
|
||
|
||
/**
|
||
* 整理数据
|
||
* @param array $data
|
||
* @throws \PHPExcel_Exception
|
||
*/
|
||
private function neatenData(array $data)
|
||
{
|
||
if (!empty($data)) {
|
||
|
||
// 行开始位置
|
||
$row_num = $this->config['row_start_num'];
|
||
|
||
$data = json_decode(json_encode($data), true);
|
||
|
||
if (isset($data[0])) {
|
||
|
||
if (isset($data[0][0])) {
|
||
// 多个单子是一家, 分别有多家合并到一个表格
|
||
$temp_data = $data;
|
||
} else {
|
||
// 多个单子合并到一个表格
|
||
$temp_data = array(
|
||
$data
|
||
);
|
||
}
|
||
} else {
|
||
// 只一个单子
|
||
$temp_data = array(
|
||
array(
|
||
$data
|
||
)
|
||
);
|
||
}
|
||
|
||
// 统一设置默认行高度
|
||
$this->excelObj->getActiveSheet()->getDefaultRowDimension()->setRowHeight($this->config['row_height']);
|
||
// 统一设置字体
|
||
$this->excelObj->getDefaultStyle()->getFont()->setName($this->config['font_family']);
|
||
// 统一设置字体大小
|
||
$this->excelObj->getDefaultStyle()->getFont()->setSize($this->config['font_size']);
|
||
// 统一设置字体颜色
|
||
$this->excelObj->getDefaultStyle()->getFont()->getColor()->setRGB($this->config['color']);
|
||
// 统一设置垂直居中
|
||
$this->excelObj->getDefaultStyle()->getAlignment()->setVertical($this->config['visibility_align']);
|
||
// 是否自动换行
|
||
$this->excelObj->getDefaultStyle()->getAlignment()->setWrapText($this->config['is_auto_wrap']);
|
||
|
||
foreach ($temp_data as $temp_k => $temp_val) {
|
||
// 最后一个下标
|
||
$last_key = -1;
|
||
foreach ($temp_val as $key => $val) {
|
||
$last_key = $key;
|
||
// 处理表头数据, 以表头规定规则
|
||
$cell_header_data = (isset($val['cell_header']) && is_array($val['cell_header'])) ? $val['cell_header'] : [];
|
||
// 处理横向合并后的表头数据
|
||
$cell_header = [];
|
||
foreach ($cell_header_data as $k => $v) {
|
||
$cell_header[] = $v;
|
||
// 处理合并, 填充
|
||
if (isset($v['colspan']) && $v['colspan'] > 1) {
|
||
for ($i = 1; $i < $v['colspan']; $i++) $cell_header[] = [];
|
||
}
|
||
}
|
||
// 处理表头标题
|
||
if (isset($val['head_title']) && is_array($val['head_title']) && !empty($val['head_title'])) {
|
||
$head_title = array_merge([
|
||
// 标题内容
|
||
'value' => '默认标题',
|
||
// 是否加粗
|
||
'is_bold' => true,
|
||
// 字体大小
|
||
'font_size' => 20,
|
||
// 字体颜色
|
||
'color' => $this->config['color'],
|
||
// 水平对齐方式
|
||
'text_align' => $this->config['text_align']
|
||
], $val['head_title']);
|
||
|
||
$head_title = array_merge($head_title, [
|
||
// 行高
|
||
'row_height' => $head_title['font_size'] + 20,
|
||
]);
|
||
|
||
$row_num++;
|
||
$cell = $this->cellKeys[0] . $row_num;
|
||
// 设置内容
|
||
$this->excelObj->getActiveSheet()->setCellValue($cell, $head_title['value']);
|
||
// 设置行高
|
||
$this->excelObj->getActiveSheet()->getRowDimension($row_num)->setRowHeight($head_title['row_height']);
|
||
// 合并单元格
|
||
$cell = $cell . ':' . $this->cellKeys[count($cell_header) - 1] . $row_num;
|
||
$this->excelObj->getActiveSheet()->mergeCells($cell);
|
||
// 设置边框
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->applyFromArray([
|
||
'borders' => array(
|
||
'outline' => array(
|
||
'style' => $this->config['border_style'],
|
||
'color' => array('argb' => $this->config['border_color'])
|
||
),
|
||
),
|
||
]);
|
||
// 是否加粗
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getFont()->setBold($head_title['is_bold']);
|
||
// 字体大小
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getFont()->setSize($head_title['font_size']);
|
||
// 字体颜色
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getFont()->getColor()->setRGB($head_title['color']);
|
||
// 设置文字位置
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getAlignment()->setHorizontal($head_title['text_align']);
|
||
}
|
||
|
||
// 处理表头
|
||
$row_num++;
|
||
foreach ($cell_header as $cell_k => $cell_v) {
|
||
if (empty($cell_v)) continue;
|
||
$cell_v = array_merge([
|
||
// 字段
|
||
'field' => '',
|
||
// 名称
|
||
'name' => '',
|
||
// 横向合并
|
||
'colspan' => 1,
|
||
// 列宽
|
||
'width' => $this->config['column_width'],
|
||
// 水平对齐方式
|
||
'text_align' => $this->config['text_align'],
|
||
], $cell_v);
|
||
$cell_header[$cell_k] = $cell_v;
|
||
|
||
$cell = $this->cellKeys[$cell_k] . $row_num;
|
||
// 设置内容
|
||
$this->excelObj->getActiveSheet()->setCellValue($cell, $this->specialCharsDeal($cell_v['name']));
|
||
// 设置行高
|
||
$this->excelObj->getActiveSheet()->getRowDimension($row_num)->setRowHeight($this->config['row_height']);
|
||
// 设置列宽
|
||
$this->excelObj->getActiveSheet()->getColumnDimension($this->cellKeys[$cell_k])->setWidth($cell_v['width']);
|
||
// 处理合并
|
||
if ($cell_v['colspan'] > 1) {
|
||
$cell = $cell . ':' . $this->cellKeys[$cell_k + $cell_v['colspan'] - 1] . $row_num;
|
||
$this->excelObj->getActiveSheet()->mergeCells($cell);
|
||
}
|
||
// 设置文字位置
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getAlignment()->setHorizontal($cell_v['text_align']);
|
||
// 边框
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->applyFromArray([
|
||
'borders' => array(
|
||
'outline' => array(
|
||
'style' => $this->config['border_style'],
|
||
'color' => array('argb' => $this->config['border_color'])
|
||
),
|
||
),
|
||
]);
|
||
}
|
||
// 表头每列筛选 TODO
|
||
if ($this->config['auto_filter']) {
|
||
//$this->excelObj->getActiveSheet()->setAutoFilter($this->cellKeys[0] . $row_num . ':' . $this->cellKeys[count($cell_header) - 1] . $row_num);
|
||
}
|
||
// 冻结表头 TODO
|
||
$temp_val[$key]['cell_header'] = $cell_header;
|
||
// 处理数据
|
||
$table_data = [];
|
||
$val['data'] = (isset($val['data']) && !empty($val['data'])) ? $val['data'] : [];
|
||
foreach ($val['data'] as $vv) $table_data[] = $vv;
|
||
foreach ($table_data as $k => $v) {
|
||
$row_num++;
|
||
foreach ($cell_header as $cell_k => $cell_v) {
|
||
if (empty($cell_v)) continue;
|
||
$cell = $this->cellKeys[$cell_k] . $row_num;
|
||
// 设置内容
|
||
$this->excelObj->getActiveSheet()->setCellValue($cell, $this->specialCharsDeal((isset($v[$cell_v['field']]) ? $v[$cell_v['field']] : '')));
|
||
// 设置行高
|
||
$this->excelObj->getActiveSheet()->getRowDimension($row_num)->setRowHeight($this->config['row_height']);
|
||
// 处理合并
|
||
if ($cell_v['colspan'] > 1) {
|
||
$cell = $cell . ':' . $this->cellKeys[$cell_k + $cell_v['colspan'] - 1] . $row_num;
|
||
$this->excelObj->getActiveSheet()->mergeCells($cell);
|
||
}
|
||
// 设置文字位置
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getAlignment()->setHorizontal($cell_v['text_align']);
|
||
// 设置边框
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->applyFromArray([
|
||
'borders' => array(
|
||
'outline' => array(
|
||
'style' => $this->config['border_style'],
|
||
'color' => array('argb' => $this->config['border_color'])
|
||
),
|
||
),
|
||
]);
|
||
}
|
||
}
|
||
}
|
||
$temp_data[$temp_k] = $temp_val;
|
||
// 分割线
|
||
if ($temp_k < (count($temp_data) - 1)) {
|
||
$row_num++;
|
||
// 设置行高
|
||
$this->excelObj->getActiveSheet()->getRowDimension($row_num)->setRowHeight($this->config['row_height']);
|
||
// 合并单元格
|
||
$cell = $this->cellKeys[0] . $row_num . ':' . $this->cellKeys[count($temp_data[$temp_k][$last_key]['cell_header']) - 1] . $row_num;
|
||
$this->excelObj->getActiveSheet()->mergeCells($cell);
|
||
// 填充背景色
|
||
$this->excelObj->getActiveSheet()->getStyle($cell)->getFill()->applyFromArray(array(
|
||
'type' => $this->config['cut_fill_style'],
|
||
'startcolor' => array(
|
||
'rgb' => $this->config['cut_fill_color'],
|
||
)
|
||
));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* string 特殊字符处理
|
||
* @param $value
|
||
* @return string
|
||
*/
|
||
private function specialCharsDeal($value)
|
||
{
|
||
if (is_string($value)) {
|
||
|
||
if (strpos($value, '=') !== false) $value = "'" . $value;
|
||
}
|
||
return $value;
|
||
}
|
||
|
||
/**
|
||
* 文件名
|
||
* @param string $file_name
|
||
* @return string
|
||
*/
|
||
private function fileName(string $file_name)
|
||
{
|
||
return $file_name . date('_YmdHis') . $this->fileExt;
|
||
}
|
||
|
||
/**
|
||
* 导出文件
|
||
* @param string $file_name
|
||
* @param array $data
|
||
* @throws \PHPExcel_Exception
|
||
* @throws \PHPExcel_Reader_Exception
|
||
* @throws \PHPExcel_Writer_Exception
|
||
*/
|
||
public function exportFile(string $file_name, array $data)
|
||
{
|
||
$file_name = $this->fileName($file_name);
|
||
$this->neatenData($data);
|
||
header('pragma:public');
|
||
// 将输出重定向到客户端的网络浏览器(Xlsx)
|
||
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
||
header('Content-Disposition: attachment;filename="' . $file_name . '"');//文件名
|
||
header('Cache-Control: max-age=0');
|
||
// 如果你服务于IE 9，那么以下可能是需要的
|
||
header('Cache-Control: max-age=1');
|
||
// 如果您通过SSL为工业工程服务，那么可能需要以下内容
|
||
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
|
||
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
|
||
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
|
||
header('Pragma: public'); // HTTP/1.0
|
||
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($this->excelObj, ucwords($this->writerType));
|
||
$writer->save('php://output');
|
||
}
|
||
|
||
/**
|
||
* 保存文件
|
||
* @param string $file_path
|
||
* @param array $data
|
||
* @return mixed|string
|
||
* @throws Exception
|
||
* @throws \PHPExcel_Exception
|
||
* @throws \PHPExcel_Reader_Exception
|
||
* @throws \PHPExcel_Writer_Exception
|
||
*/
|
||
public function saveFile(string $file_path, array $data,$fileExt='xlsx')
|
||
{
|
||
$file_path = $this->fileName($file_path);
|
||
$dir_path = pathinfo($file_path, PATHINFO_DIRNAME);
|
||
$this->neatenData($data);
|
||
if (dir_mkdir($dir_path)) {
|
||
// 执行保存
|
||
ob_end_clean();
|
||
$writer = IOFactory::createWriter($this->excelObj, ucwords($this->writerType));
|
||
$writer->save($file_path, true);
|
||
return $file_path;
|
||
}
|
||
throw new Exception('创建文件夹失败');
|
||
}
|
||
|
||
|
||
/**
|
||
* 析构函数
|
||
*/
|
||
public function __destruct()
|
||
{
|
||
$this->excelObj = null;
|
||
}
|
||
} |