使用PhpSpreadsheet库整理Xlsx文件的技术求助

以下是修正后的完整代码，已匹配预期排版效果：

<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 1. 设置标题行（A1:D1）
$sheet->mergeCells('A1:D1');
$sheet->setCellValue('A1', 'LISTAGEM DE PENDÊNCIAS');
// 标题样式：背景色、字体、对齐、边框
$titleStyle = [
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => '5481eb'],
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 18,
        'family' => 'Verdana',
    ],
    'alignment' => [
        'horizontal' => 'center',
        'vertical' => 'center',
    ],
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$sheet->getStyle('A1:D1')->applyFromArray($titleStyle);

// 2. 设置列宽和默认行高
$sheet->getDefaultRowDimension()->setRowHeight(25);
$sheet->getColumnDimension('A')->setWidth(17);
$sheet->getColumnDimension('B')->setWidth(17);
$sheet->getColumnDimension('C')->setWidth(28);
$sheet->getColumnDimension('D')->setWidth(28);

// 3. 设置第二行（PROTOCOLO行）
$sheet->mergeCells('B2:D2');
$sheet->setCellValue('A2', 'PROTOCOLO:');
$sheet->setCellValue('B2', '2/2021');
// A2样式：深灰背景、白色加粗字体、居中对齐、边框
$labelCellStyle = [
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => '707070'],
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 12,
    ],
    'alignment' => [
        'horizontal' => 'center',
        'vertical' => 'center',
    ],
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
// B2:D2样式：浅灰背景、白色加粗字体、左对齐、边框
$valueCellStyle = [
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'C4C0C0'],
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 12,
    ],
    'alignment' => [
        'horizontal' => 'left',
        'vertical' => 'center',
    ],
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$sheet->getStyle('A2')->applyFromArray($labelCellStyle);
$sheet->getStyle('B2:D2')->applyFromArray($valueCellStyle);

// 4. 设置第三行（CÓDIGO DO LOTE行）
$sheet->mergeCells('B3:D3');
$sheet->setCellValue('A3', 'CÓDIGO DO LOTE:');
$sheet->setCellValue('B3', '1234');
$sheet->getStyle('A3')->applyFromArray($labelCellStyle);
$sheet->getStyle('B3:D3')->applyFromArray($valueCellStyle);

// 5. 设置表头行（A4:D4）
$sheet->mergeCells('A4:B4');
$sheet->setCellValue('A4', 'VÍNCULO');
$sheet->setCellValue('C4', 'PENDÊNCIAS');
$sheet->setCellValue('D4', 'STATUS');
// 表头样式：深灰背景、白色加粗字体、居中对齐、边框
$headerStyle = [
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => '707070'],
    ],
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF'],
        'size' => 12,
    ],
    'alignment' => [
        'horizontal' => 'center',
        'vertical' => 'center',
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$sheet->getStyle('A4:D4')->applyFromArray($headerStyle);

// 6. 设置数据行（A5:D7）
$dataRows = [
    ['Leandro Benjamin Souza', 'CPF', 'Apto'],
    ['Leandro Benjamin Souza', 'CPF', 'Apto'],
    ['Leandro Benjamin Souza', 'CPF', 'Apto'],
];
$rowNum = 5;
foreach ($dataRows as $row) {
    $sheet->mergeCells("A{$rowNum}:B{$rowNum}");
    $sheet->setCellValue("A{$rowNum}", $row[0]);
    $sheet->setCellValue("C{$rowNum}", $row[1]);
    $sheet->setCellValue("D{$rowNum}", $row[2]);
    // 数据行样式：边框、垂直居中
    $dataStyle = [
        'alignment' => [
            'vertical' => 'center',
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => '000000'],
            ],
        ],
    ];
    $sheet->getStyle("A{$rowNum}:D{$rowNum}")->applyFromArray($dataStyle);
    // 合并单元格左对齐，C、D列居中
    $sheet->getStyle("A{$rowNum}")->getAlignment()->setHorizontal('left');
    $sheet->getStyle("C{$rowNum}:D{$rowNum}")->getAlignment()->setHorizontal('center');
    $rowNum++;
}

// 7. 保存文件
$writer = new Xlsx($spreadsheet);
$writer->save('Listagem de Protocolos com Pendencia.xlsx');

主要修改说明： #

1. 样式统一管理：将重复的样式（标签单元格、值单元格、表头、数据行）定义为独立数组，避免重复编码和样式覆盖。
2. 修正对齐范围：移除全局整列的对齐设置，改为针对特定行/单元格设置对齐，避免覆盖后续自定义对齐。
3. 补充边框样式：为所有单元格添加完整边框（表头用allBorders保证内部边框显示），解决原代码边框缺失问题。
4. 优化表头样式：给表头添加白色加粗字体和居中对齐，匹配预期效果。
5. 数据行规范：统一数据行的边框和对齐方式，合并单元格左对齐，C、D列居中。
6. 简化重复代码：用循环处理数据行，减少冗余代码。

内容的提问来源于stack exchange，提问作者Rodrigo Franco