PhpSpreadsheet导出PostgreSQL数据文件无法在Excel中打开求助

我使用PhpSpreadsheet库从PostgreSQL数据库导出数据表，文件能正常下载，在LibreOffice Calc中可正常打开，但无法在Excel中打开。相关代码、数据库配置如下：

导出代码 #

<?php
session_start();

if (!isset($_SESSION["users"])) {
    header("Location: login.php");
    exit;
}
?>

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$host = "";
$port = "";
$dbname = "";
$user = "";
$password = "";

$query = "SELECT * FROM tabela_selecionados";

$conn = pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");
$result = pg_query($conn, $query);

if (!$result) {
    die("Erro ao consultar os produtos selecionados: " . pg_last_error());
}

if (pg_num_rows($result) > 0) {
    echo "<h1 class='p-3 mb-2 bg-primary text-white'>Produtos Selecionados</h1>";
    echo "<div class='table-responsive'>";
    echo "<table class='table table-striped table-bordered table-condensed table-hover'>";
    echo "<tr>";
    echo "<th>CODIGO</th>";
    echo "<th>FOB ATUAL</th>";
    echo "<th>CODIGO CHINA</th>";
    echo "<th>NOME</th>";
    echo "<th>DATA FOB</th>";
    echo "<th>ANO</th>";
    echo "<th>OBSERVACAO</th>";
    echo "</tr>";

    while ($row = pg_fetch_assoc($result)) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["cod_bbr"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["fob_atual"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["cod_a"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["nome"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["data_fob"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["ano"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["obs"]) . "</td>";
        echo "</tr>";
    }

    echo "</table>";
    echo "</div>";

    pg_close($conn);

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

    $rowIndex = 1;

    $sheet->setCellValue('A'.$rowIndex, 'CODIGO');
    $sheet->setCellValue('B'.$rowIndex, 'FOB ATUAL');
    $sheet->setCellValue('C'.$rowIndex, 'CODIGO CHINA');
    $sheet->setCellValue('D'.$rowIndex, 'NOME');
    $sheet->setCellValue('E'.$rowIndex, 'DATA FOB');
    $sheet->setCellValue('F'.$rowIndex, 'ANO');
    $sheet->setCellValue('G'.$rowIndex, 'OBSERVACAO');

    while ($row = pg_fetch_assoc($result)) {
        $rowIndex++;
        $sheet->setCellValue('A'.$rowIndex, $row["cod_bbr"]);
        $sheet->setCellValueExplicit('B'.$rowIndex, $row["fob_atual"]);
        $sheet->setCellValue('C'.$rowIndex, $row["cod_a"]);
        $sheet->setCellValue('D'.$rowIndex, $row["nome"]);
        $sheet->setCellValue('E'.$rowIndex, $row["data_fob"]);
        $sheet->setCellValue('F'.$rowIndex, $row["ano"]);
        $sheet->setCellValue('G'.$rowIndex, $row["obs"]);
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;');
    header('Content-Disposition: attachment;filename="produtos_selecionados.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');

} else {
    echo "<h1>Nenhum produto selecionado.</h1>";
}
?>

数据库字段配置 #

• cod_a: character varying(120)
• nome: character varying(600)
• cod_bbr: character varying(30)
• fob_atual: numeric(10,2)
• data_fob: character varying(30)
• ano: character varying(8)
• obs: character varying(600)
  数据库编码为UTF-8

修复方案 #

1. 解决查询结果指针耗尽问题
   已经用pg_fetch_assoc遍历过一次$result输出HTML表格，指针已移到结果末尾，第二次遍历填充Excel时没有数据，导致文件结构不完整。可以提前把数据存入数组复用：

// 替换原有的HTML遍历逻辑
$data = [];
echo "<table...>"; // 保留表格标签
while ($row = pg_fetch_assoc($result)) {
    $data[] = $row;
    // 输出表格行
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row["cod_bbr"]) . "</td>";
    // ...其他列输出
    echo "</tr>";
}
echo "</table>";

// 填充Excel时遍历$data数组
foreach ($data as $row) {
    $rowIndex++;
    $sheet->setCellValue('A'.$rowIndex, $row["cod_bbr"]);
    // ...其他单元格设置
}

2. 移除Excel输出前的HTML内容
   Excel文件是二进制格式，任何提前输出的HTML字符（比如<h1>、表格标签）都会污染文件，导致Excel无法识别。建议将导出功能拆分到单独的PHP文件，比如export.php，通过按钮跳转触发下载，避免同时输出HTML和Excel内容。

3. 优化数据格式（可选）

• 对于data_fob字段，如果是日期字符串，转换为Excel支持的日期格式：

use PhpOffice\PhpSpreadsheet\Shared\Date;

// 假设data_fob格式为'Y-m-d'
$dateObj = DateTime::createFromFormat('Y-m-d', $row["data_fob"]);
if ($dateObj) {
    $sheet->setCellValue('E'.$rowIndex, Date::dateTimeToExcel($dateObj));
    // 设置单元格格式为日期
    $sheet->getStyle('E'.$rowIndex)->getNumberFormat()->setFormatCode(Date::FORMAT_DATE_YYYYMMDD2);
}

内容的提问来源于stack exchange，提问作者luis alves