PhpSpreadsheet 导出excel通用代码[php8.1]

PhpSpreadsheet 导出excel通用代码,要求php8.1以上：

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\Hyperlink;


class Addonsexcel {

    //获取列名['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'];
    public static function getExcelColumnName($columnIndex) {
        $columnName = '';
        while ($columnIndex >= 0) {
            $remainder = $columnIndex % 26;
            $columnName = chr(65 + $remainder) . $columnName;
            $columnIndex = intval($columnIndex / 26) - 1;
        }
        return $columnName;
    }
    
    //处理表格数据
    public static function getdata(Spreadsheet $spreadsheet,object $sheet,array $fieldConfig,array $data ) {
        // 预处理字段配置
        $filteredConfig = array_filter($fieldConfig, fn($f) => $f[2]);
        $fieldMap = array_column($filteredConfig, null, 0);

        // 收集所有嵌套父级字段,父级字段不可隐藏
        $parentFields = [];
        foreach ($fieldConfig as [$field, $title]) {
            if (str_contains($field, '.')) {
                [$parent, $child] = explode('.', $field, 2);
                $parentFields[$parent] = true;
            }
        }
        foreach ($fieldConfig as [$field, $title]) {
            if(isset($parentFields[$field])){
                $parentFields[$field]=$title;
            }
        }
        // 分离简单字段和嵌套字段
        $simpleHeaders = [];
        $nestedGroups = [];
    
        foreach ($filteredConfig as [$field, $title]) {
            if (str_contains($field, '.')) {
                [$parent, $child] = explode('.', $field, 2);
                // 获取父级中文名（优先使用配置中的显示名称）
                $parentTitle = $fieldMap[$parent][1] ?? $parentFields[$parent] ?? $parent;
                $nestedGroups[$parent] ??= ['title' => $parentTitle, 'children' => []];
                $nestedGroups[$parent]['children'][$child] = $title;
            } 
        }
 
        // 过滤简单字段（排除父级）
        foreach ($filteredConfig as [$field, $title]) {
            if (!str_contains($field, '.') && !isset($parentFields[$field])) {
                $simpleHeaders[$field] = $title;
            }
        }

        // 构建表头结构
        $currentCol = 'A';
        $headerRow = 1;
        $subHeaderRow = 2;

        // 处理简单字段（合并单元格）
        foreach ($simpleHeaders as $title) {
            $sheet->mergeCells("{$currentCol}{$headerRow}:{$currentCol}{$subHeaderRow}")->setCellValue($currentCol.$headerRow, $title);
            $currentCol++;
        }

        // 处理嵌套字段
        if(!empty($nestedGroups)){
            foreach ($nestedGroups as $group) {
                $startCol = $currentCol;
                $childrenCount = count($group['children']);

                // 合并父级标题
                $endCol = chr(ord($startCol) + $childrenCount - 1);
                $sheet->mergeCells("{$startCol}{$headerRow}:{$endCol}{$headerRow}") ->setCellValue($startCol.$headerRow, $group['title']);

                // 添加子字段
                foreach ($group['children'] as $childTitle) {
                    $sheet->setCellValue($currentCol.$subHeaderRow, $childTitle);
                    $currentCol++;
                }
            }
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => 'center', 'vertical' => 'center'],
            'borders' => ['allBorders' => ['borderStyle' => 'thin']],
            'fill' => ['fillType' => 'solid', 'color' => ['rgb' => 'F2F2F2']]
        ];

        //$lastCol = chr(ord('A') + count($simpleHeaders) + array_sum(array_map(fn($g) => count($g['children']), $nestedGroups)) - 1);
        $columnIndex = count($simpleHeaders) + array_sum(array_map(fn($g) => count($g['children']), $nestedGroups)) - 1;
        $lastCol = self::getExcelColumnName($columnIndex);
        $sheet->getStyle("A{$headerRow}:{$lastCol}{$subHeaderRow}")->applyFromArray($headerStyle);
        // 填充数据
        $dataRow = $subHeaderRow + 1;
        foreach ($data as $item) {
            $currentCol = 'A';

            // 简单字段
            foreach (array_keys($simpleHeaders) as $field) {
                if(in_array($field,['url')  && $item[$field] != ''){
                    $sheet->setCellValue($currentCol.$dataRow, '电子存档（点击获取）');
                    $sheet->getCell($currentCol.$dataRow)->setHyperlink(new Hyperlink($item[$field]));
                    $sheet->getCell($currentCol.$dataRow)->getHyperlink()->setTooltip('跳转外部安全网页，请放心使用');
                }else{
                    $sheet->setCellValue($currentCol.$dataRow, $item[$field] ?? '');
                }
                $currentCol++;
            }

            // 嵌套字段
            foreach ($nestedGroups as $parent => $group) {
                $nestedData = $item[$parent] ?? [];
                foreach (array_keys($group['children']) as $child) {
                    $sheet->setCellValue($currentCol.$dataRow, $nestedData[$child] ?? '');
                    $currentCol++;
                }
            }

            $dataRow++;
        }

        // 设置数据样式
        $sheet->getStyle("A{$subHeaderRow}:{$lastCol}".($dataRow-1))->getBorders()->applyFromArray(['allBorders' => ['borderStyle' => 'thin']]);

        // 自动列宽
        foreach (range('A', $lastCol) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }
        
        return $spreadsheet;
    }
    /**
    * 通用Excel导出函数
    * @param array $data 数据源
    * @param array $fieldConfig 字段配置 [[字段名, 中文名, 是否显示], ...]
    * @param string $filename 输出文件名
    */
    public static function exportExcel(string $filename ,array $field,array $data ): void {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle($filename);
        $spreadsheet = self::getdata($spreadsheet,$sheet,$field, $data);
        // 输出文件
        $writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        $writer->save('php://output');
    }

调用示例

$data = [
    [
        'project_name' => '项目A',
        'cost_info' => [
            'material_cost' => 5000,
            'labor_cost' => 3000
        ],
        'cost_info1' => [
            'material_cost' => 5000,
            'labor_cost' => 3000
        ]
    ]
];

$fieldConfig = [
    ['project_name', '项目名称', true],
    ['cost_info', '成本信息', true],
    ['cost_info.material_cost', '材料成本', true],
    ['cost_info.labor_cost', '人工成本', true]
];

\Addonsexcel::exportExcel('项目分析报告.xlsx',$fieldConfig,$data);