萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 匯出 Excel 解決科學記號與中文亂碼:MIME 類型、儲存格格式與編碼完整指南

PHP 以 HTML 表格輸出模擬 Excel 匯出時,常遇兩大問題:長數字 (身分證、卡號) 自動變科學記號、中文亂碼。本文系統解決:設定正確 MIME、儲存格格式強制文本、編碼轉換、檔名編碼,並提供可直接使用的完整範本。

核心解決方案

  1. MIME 類型:application/vnd.ms-excel 觸發 Excel 開啟
  2. 儲存格格式:style="vnd.ms-excel.numberformat:@" 強制文本格式 (防科學記號)
  3. 編碼轉換:UTF-8 → GB2312 (Excel 相容性最佳)
  4. 檔名編碼:iconv('UTF-8', 'GB2312//IGNORE', $filename)

完整匯出範本

<?php
// 1. 設定標頭
$filename = iconv("UTF-8", "GB2312//IGNORE", "會員名單_" . date('Ymd') . ".xls");
header("Content-Type: application/vnd.ms-excel");
header("Accept-Ranges: bytes");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");

// 2. 輸出 HTML 表格 (Excel 可識別)
echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" 
      xmlns:x="urn:schemas-microsoft-com:office:excel" 
      xmlns="https://www.w3.org/TR/REC-html40">';
echo '<head>';
echo '<meta http-equiv="Content-Type" content="text/html; charset=gb2312">';
echo '<!--[if gte mso 9]><xml>';
echo '<x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
echo '<x:Name>Sheet1</x:Name>';
echo '<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>';
echo '</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook>';
echo '</xml><![endif]-->';
echo '</head>';

// 3. 表頭 (轉碼)
echo "<table><tr>";
echo "<th>" . iconv("UTF-8", "GB2312//IGNORE", "會員名") . "</th>";
echo "<th>" . iconv("UTF-8", "GB2312//IGNORE", "帳號") . "</th>";
echo "<th>" . iconv("UTF-8", "GB2312//IGNORE", "聯絡人") . "</th>";
echo "</tr>";

// 4. 資料列
foreach ($list as $row) {
    echo "<tr>";
    // 一般欄位:轉碼輸出
    echo "<td>" . iconv("UTF-8", "GB2312//IGNORE", $row['user_name']) . "</td>";
    // 長數字欄位:強制文本格式 + 轉碼
    echo "<td style='vnd.ms-excel.numberformat:@'>" . $row['account_id'] . "</td>";
    echo "<td>" . iconv("UTF-8", "GB2312//IGNORE", $row['contact_name']) . "</td>";
    echo "</tr>";
}
echo "</table></html>";
?>

常用儲存格格式代碼

格式CSS 樣式值適用欄位
文本 (防科學記號)vnd.ms-excel.numberformat:@身分證、信用卡、手機號、編號
日期vnd.ms-excel.numberformat:yyyy/mm/dd生日、建立日期
數字 (千分位、2 位小數)vnd.ms-excel.numberformat:#,##0.00金額、價格
貨幣vnd.ms-excel.numberformat:¥#,##0.00價格顯示
百分比vnd.ms-excel.numberformat:#0.00%比率、折扣
自訂 (年月)vnd.ms-excel.numberformat:yy-mm月份統計

樣式優先級規則

Excel 套用樣式優先序:單元格 (td) > 列 (tr) > 表格 (table)。最貼近資料的樣式生效。

進階:使用 PhpSpreadsheet (現代標準)

HTML 模擬法有局限 (無法公式、合併儲存格、圖表、.xlsx)。推薦使用 PhpSpreadsheet:

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

// 設定儲存格格式為文本
$sheet->getStyle('B')->getNumberFormat()->setFormatCode(
    \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT
);

$sheet->setCellValue('A1', '會員名');
$sheet->setCellValue('B1', '身分證字號');
$sheet->setCellValue('C1', '聯絡人');

$row = 2;
foreach ($list as $data) {
    $sheet->setCellValue('A' . $row, $data['user_name']);
    $sheet->setCellValue('B' . $row, $data['account_id']); // 自動套用文本格式
    $sheet->setCellValue('C' . $row, $data['contact_name']);
    $row++;
}

$writer = new Xlsx($spreadsheet);
$filename = "會員名單_" . date('Ymd') . ".xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");
header('Cache-Control: max-age=0');
$writer->save('php://output');
?>

兩種方案選擇指南

場景推薦方案理由
簡單報表、相容舊 IE、無複雜格式HTML 表格法零依賴、速度快、記憶體少
需 .xlsx、公式、樣式、合併儲存格、大數據PhpSpreadsheet標準支援、功能完整、記憶體較大
超大數據 (10萬行+)CSV / 分批輸出 / 佇列異步避免記憶體耗盡

避坑清單

  1. ✅ 長數字欄位 必須 加 style='vnd.ms-excel.numberformat:@'
  2. ✅ 中文內容 必須 iconv('UTF-8', 'GB2312//IGNORE', ...)
  3. ✅ 檔名含中文 必須 iconv 轉碼
  4. ✅ 輸出前 無任何額外輸出 (空白、BOM、錯誤訊息)
  5. ✅ 測試用 Excel 2016+、WPS、LibreOffice 多環境驗證

掌握 Excel 匯出核心技巧,能快速滿足業務報表需求,避免「匯出亂碼、數字變科學記號」的經典坑。

copyright © 萬盛學電腦網 all rights reserved