「不使用擴展導出 Excel」通常指**不安裝 php_excel/php_com_dotnet 等 C 擴展**,改用純 PHP 库或原生格式。本文對比四種方案:**CSV (最簡)**、HTML Table (兼容好)、Tab 分隔文本 (原生 Excel 打開)、PhpSpreadsheet (功能全、純 PHP、現代標準),並給出生產級代碼模板。
| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| CSV (fputcsv) | 零依賴、內存極低、流式寫入、萬能兼容 | 無樣式、無多工作表、無公式、編碼易亂碼 | 大數據導出、簡單列表、備份 |
| HTML Table | 支持樣式/合併儲存格/超鏈接、瀏覽器直接打開 | 非真二進制 .xls、大文件內存大、Excel 警告 | 報表需簡單樣式、小數據量 |
| Tab 文本 (.xls) | Excel 雙擊直開、無依賴、支持多工作表(技巧) | 本質是文本、無真正樣式、編碼處理麻煩 | 快速導出、不想裝庫 |
| PhpSpreadsheet | 功能完整(樣式/圖表/公式/篩選/多表)、純 PHP、活躍維護 | 大數據內存佔用高、學習曲線稍陡 | 複雜報表、模板導出、生產標準 |
<?php
function exportCsv(array $data, string $filename, array $headers = [], string $encoding = 'UTF-8'): void
{
// 1. 設置響應頭
header('Content-Type: text/csv; charset=' . $encoding);
header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '.csv"');
header('Pragma: no-cache');
header('Expires: 0');
// 2. 打開輸出流
$output = fopen('php://output', 'w');
// 3. 寫入 BOM (解決 Excel 打開 UTF-8 CSV 亂碼)
if ($encoding === 'UTF-8') {
fwrite($output, "\xEF\xBB\xBF");
}
// 4. 寫入表頭
if ($headers) {
fputcsv($output, $headers);
}
// 5. 流式寫入數據 (生成器/游標逐行,避免內存爆炸)
foreach ($data as $row) {
// 統一編碼
$row = array_map(fn($v) => mb_convert_encoding($v, $encoding, 'UTF-8'), $row);
fputcsv($output, $row);
}
fclose($output);
exit;
}
// 使用:配合生成器處理百萬行
function getDataGenerator(): Generator
{
$pdo = new PDO(...);
$stmt = $pdo->query('SELECT id, name, email FROM users');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
yield $row;
}
}
exportCsv(getDataGenerator(), 'users_export', ['ID', '姓名', '郵箱']);
<?php
function exportHtmlExcel(array $data, array $headers, string $filename): void
{
header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');
echo "\xEF\xBB\xBF"; // UTF-8 BOM
?>
<!DOCTYPE html>
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<meta charset="UTF-8">
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DisplayGridlines/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
<![endif]-->
<style>
table { border-collapse: collapse; }
th, td { border: 1px solid #ccc; padding: 4px 8px; }
th { background: #4472C4; color: white; font-weight: bold; }
.number { mso-number-format: "0"; text-align: right; }
.date { mso-number-format: "yyyy-mm-dd"; }
</style>
</head>
<body>
<table>
<thead><tr>
<?php foreach ($headers as $h): ?>
<th><?php echo htmlspecialchars($h); ?></th>
<?php endforeach; ?>
</tr></thead>
<tbody>
<?php foreach ($data as $row): ?>
<tr>
<?php foreach ($row as $i => $cell): ?>
<td class="<?php echo is_numeric($cell) ? 'number' : ''; ?>">
<?php echo htmlspecialchars($cell); ?>
</td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>
<?php exit; ?>
// composer require phpoffice/phpspreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
function exportWithPhpSpreadsheet(array $data, array $headers, string $filename): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('數據導出');
// 1. 寫入表頭
$sheet->fromArray($headers, null, 'A1');
$headerRange = 'A1:' . $sheet->getCellByColumnAndRow(count($headers), 1)->getCoordinate();
$sheet->getStyle($headerRange)->applyFromArray([
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
]);
// 2. 寫入數據 (批量,高效)
$sheet->fromArray($data, null, 'A2');
// 3. 自動列寬
foreach (range('A', $sheet->getHighestColumn()) as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// 4. 輸出
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
}
// 大數據優化:分塊寫入 + 內存優化
function exportLargeData(Generator $dataGen, array $headers, string $filename): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray($headers, null, 'A1');
$rowNum = 2;
$batchSize = 1000;
$batch = [];
foreach ($dataGen as $row) {
$batch[] = $row;
if (count($batch) >= $batchSize) {
$sheet->fromArray($batch, null, 'A' . $rowNum);
$rowNum += $batchSize;
$batch = [];
// 可選:釋放內存
$spreadsheet->garbageCollect();
}
}
if ($batch) {
$sheet->fromArray($batch, null, 'A' . $rowNum);
}
// ... 同樣輸出頭與保存
}
// 文件名編碼:RFC 5987
$filename = '用戶數據導出';
$encoded = rawurlencode($filename);
header("Content-Disposition: attachment; filename=\"{$encoded}\"; filename*=UTF-8''{$encoded}");
// CSV 編碼:Excel 只認 UTF-8 BOM 或 GBK
// 方案 A: UTF-8 + BOM (推薦現代 Excel)
fwrite($fp, "\xEF\xBB\xBF");
// 方案 B: 轉 GBK (舊版 Excel 兼容)
$row = array_map(fn($v) => iconv('UTF-8', 'GBK//IGNORE', $v), $row);
filename* 屬性支持中文。ob_clean(),防止框架/錯誤輸出污染文件流。set_time_limit(0) 和 ini_set('memory_limit', '512M') 防止超時/內存溢出。選型建議:**百萬行以上 → CSV 流式**;**需樣式/多表/公式 → PhpSpreadsheet**;**幾千行簡單表格、臨時需求 → HTML Table**;**極簡環境無 Composer → Tab 文本**。現代項目首選 phpoffice/phpspreadsheet,它是 PHPExcel 官方繼任者,純 PHP、PSR-4、PHP 7.1+、功能最全。