如何使用League\Csv库为CSV文件表头添加样式？

问题背景 #

我用PHP的League\Csv库生成CSV文件，希望给表头加上加粗、文字颜色这类样式，让它在Microsoft Excel里能突出显示。但目前生成的表头是纯文本，试了HTML标签完全没用，查League\Csv的文档也没找到相关方法。当前的CSV生成代码如下：

public function export_data_as_csv() {
    $projectFeature     = new ProjectSingle();
    $role_label         = $projectFeature->role_label;
    
    $estimations = $projectFeature->getEstimations($_POST['project_id']);
    
    // Instantiate a CSV object
    $csv = Writer::createFromString('');
    
    // Header CSV
    $header = [
        'Name', 
        'Est. Hours', 
        'Estimator', 
        'Assigned', 
        'Note', 
        'Problem'
    ];
    $csv->insertOne($header);

    $total_estimation = 0;
    foreach ($estimations as $division => $estimation) {
        if (!empty($estimation)) {
            // Grup parent
            $parent_data = [
                $role_label[$division],
                $estimation['post_estimation'],
                $estimation['estimator'],
                '', // Assigned
                '', // Note
                '', // Problem
            ];
            $csv->insertOne($parent_data);

            foreach ($estimation['timeline'] as $task_name => $timeline) {
                // Grup konten
                $content_data = [
                    $task_name,
                    $timeline['est_hours'],
                    '', // Estimator
                    $timeline['est_assigned'],
                    $timeline['est_notes'],
                    $timeline['problems_url'],
                ];
                $csv->insertOne($content_data);
            }

            $total_estimation += $estimation['post_estimation'];
        }
    }
    
    $total = ['TOTAL', $total_estimation, '', '', '', ''];
    $csv->insertOne($total);
    
    $file_name = $_POST['project_name'] . '-estimation.csv';
    // $csv->output($file_name);

    $csvData = $csv->toString(); // Get CSV data as a string
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $file_name . '"');
    echo $csvData;
    $_POST = [];
    exit();
}

解决方案 #

CSV是纯文本格式，本身不支持任何样式标记，所以直接用CSV实现表头样式是不可能的。要让Excel识别样式，最稳妥的方式是改用XLSX格式（Excel原生格式），用PhpSpreadsheet库来生成文件并设置样式。

步骤1：安装PhpSpreadsheet #

执行composer命令安装依赖：

composer require phpoffice/phpspreadsheet

步骤2：替换原有代码为XLSX生成逻辑 #

修改后的代码如下，保留原有业务逻辑，同时给表头添加加粗、文字颜色样式：

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

public function export_data_as_xlsx() {
    $projectFeature     = new ProjectSingle();
    $role_label         = $projectFeature->role_label;
    
    $estimations = $projectFeature->getEstimations($_POST['project_id']);
    
    // 创建Spreadsheet实例
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // 设置表头内容
    $header = [
        'Name', 
        'Est. Hours', 
        'Estimator', 
        'Assigned', 
        'Note', 
        'Problem'
    ];
    // 写入表头到第一行
    $sheet->fromArray($header, null, 'A1');
    
    // 设置表头样式：加粗、文字为深蓝色
    $headerStyle = [
        'font' => [
            'bold' => true,
            'color' => ['rgb' => '003366'],
        ]
    ];
    $sheet->getStyle('A1:F1')->applyFromArray($headerStyle);

    $total_estimation = 0;
    $currentRow = 2; // 从第二行开始写入数据
    
    foreach ($estimations as $division => $estimation) {
        if (!empty($estimation)) {
            // 写入分组父数据
            $parent_data = [
                $role_label[$division],
                $estimation['post_estimation'],
                $estimation['estimator'],
                '', // Assigned
                '', // Note
                '', // Problem
            ];
            $sheet->fromArray($parent_data, null, 'A' . $currentRow);
            $currentRow++;

            foreach ($estimation['timeline'] as $task_name => $timeline) {
                // 写入分组内容数据
                $content_data = [
                    $task_name,
                    $timeline['est_hours'],
                    '', // Estimator
                    $timeline['est_assigned'],
                    $timeline['est_notes'],
                    $timeline['problems_url'],
                ];
                $sheet->fromArray($content_data, null, 'A' . $currentRow);
                $currentRow++;
            }

            $total_estimation += $estimation['post_estimation'];
        }
    }
    
    // 写入总计行
    $total = ['TOTAL', $total_estimation, '', '', '', ''];
    $sheet->fromArray($total, null, 'A' . $currentRow);
    // 给总计行也设置加粗样式
    $sheet->getStyle('A' . $currentRow . ':B' . $currentRow)->getFont()->setBold(true);
    
    // 设置文件名并输出
    $file_name = $_POST['project_name'] . '-estimation.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $file_name . '"');
    
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    
    $_POST = [];
    exit();
}

关键说明 #

1. 用PhpSpreadsheet\Spreadsheet创建Excel文档实例，通过fromArray批量写入数据
2. 用getStyle选中表头单元格范围，通过applyFromArray设置字体加粗、颜色样式
3. 输出时设置正确的Content-Type，确保浏览器识别为XLSX文件
4. 总计行也可以按需添加样式，增强可读性

内容的提问来源于stack exchange，提问作者muhammad zulfan