PHP 匯出 Excel 兩種實作方案

利用 HTTP Header 直接輸出 Excel 檔案

不需第三方函式庫，透過設定正確的 Content-Type 與 Content-Disposition 標頭，讓瀏覽器將 PHP 輸出的表格資料識別為 Excel 檔案下載。

方案一：直接輸出 Tab 分隔文字（最簡單）

<?php
// 設定下載標頭
header("Content-Type: application/vnd.ms-excel; charset=gbk");
header("Content-Disposition: attachment; filename=test_data.xls");

// 連接資料庫
$link = mysql_connect('localhost', 'root', 'password');
if ($link) {
    mysql_select_db('dataui', $link);
    mysql_query("SET NAMES 'gbk'");
} else {
    die("資料庫連接失敗！");
}

// 輸出標題列（Tab 分隔）
echo "項目名稱\t項目詳情\n";

$sql = "SELECT * FROM php168_item_content WHERE fid='11'";
$query = mysql_query($sql);
while ($rs = mysql_fetch_array($query)) {
    echo $rs['title'] . "\t";
    $sql2 = "SELECT * FROM php168_item_content_1 WHERE id='{$rs['id']}'";
    $query2 = mysql_query($sql2);
    while ($rs2 = mysql_fetch_array($query2)) {
        echo $rs2['content'] . "\t";
    }
    echo "\n";
}
?>

方案二：搭配 Smarty 模板輸出 Excel XML 格式

<?php
// 模擬資料
$emps[0] = ['id' => '00001', 'name' => 'abc', 'sexual' => '男', 'age' => 28];
$emps[1] = ['id' => '00002', 'name' => 'bbc', 'sexual' => '男', 'age' => 23];
$emps[2] = ['id' => '00003', 'name' => 'cba', 'sexual' => '女', 'age' => 20];

ini_set('include_path', '/data/website/htdocs/includes');
require_once('Smarty.class.php');
$smarty = new Smarty();
$smarty->assign('emps', $emps);

// 輸出標頭
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=test.xls");
$smarty->display('excel-xml.tpl');
?>

Smarty 模板範例 (excel-xml.tpl)

{* Excel 2003 XML 格式 *}
<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">編號</Data></Cell>
        <Cell><Data ss:Type="String">姓名</Data></Cell>
        <Cell><Data ss:Type="String">性別</Data></Cell>
        <Cell><Data ss:Type="Number">年齡</Data></Cell>
      </Row>
      {foreach from=$emps item=emp}
      <Row>
        <Cell><Data ss:Type="String">{$emp.id}</Data></Cell>
        <Cell><Data ss:Type="String">{$emp.name}</Data></Cell>
        <Cell><Data ss:Type="String">{$emp.sexual}</Data></Cell>
        <Cell><Data ss:Type="Number">{$emp.age}</Data></Cell>
      </Row>
      {/foreach}
    </Table>
  </Worksheet>
</Workbook>

身分證字號等長數字科學計數法問題解決

Excel 預設會將長數字（如身分證字號、手機號碼）以科學計數法顯示。解決方法：

• 在資料前加上單引號 '：echo "'" . $id_card . "\t";
• 或輸出 Excel XML 格式時指定 ss:Type="String"
• 使用 PhpSpreadsheet 等函式庫設定儲存格格式為「文字」

現代化推薦：PhpSpreadsheet

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

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '編號')->setCellValue('B1', '姓名')->setCellValue('C1', '身分證字號');

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

$data = [
    ['00001', '王小明', 'A123456789'],
    ['00002', '李小華', 'B234567890'],
];
$sheet->fromArray($data, null, 'A2');

$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="export.xlsx"');
$writer->save('php://output');