將 PHP 陣列匯出為 CSV 是資料匯出、報表產生的核心功能。本文涵蓋基礎 fputcsv() 用法、編碼處理、大量資料優化、HTTP 下載回應與進階格式控制。
<?php
$sales = [
['east', '2005-01-01', '2005-02-01', 12.54],
['west', '2005-01-01', '2005-02-01', 546.33],
['south', '2005-01-01', '2005-02-01', 93.26],
['north', '2005-01-01', '2005-02-01', 945.21],
['All Regions', '--', '--', 1597.34],
];
$fh = fopen('file.csv', 'w');
if (!$fh) {
die("無法開啟檔案寫入");
}
foreach ($sales as $row) {
if (fputcsv($fh, $row) === false) {
die("CSV 寫入失敗");
}
}
fclose($fh);
echo "匯出完成:file.csv";
?>
<?php
function exportCsvDownload(array $data, string $filename = 'export.csv', array $options = []): void {
$defaults = [
'delimiter' => ',',
'enclosure' => '"',
'bom' => true, // 加入 UTF-8 BOM 讓 Excel 正確識別中文
'headers' => [], // 可選:標題列
'encoding' => 'UTF-8', // 來源資料編碼
];
$opts = array_merge($defaults, $options);
// 設定下載標頭
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');
$output = fopen('php://output', 'w');
// 寫入 BOM
if ($opts['bom']) {
fwrite($output, "\xEF\xBB\xBF");
}
// 寫入標題列
if (!empty($opts['headers'])) {
$headerRow = $opts['headers'];
if ($opts['encoding'] !== 'UTF-8') {
$headerRow = array_map(fn($v) => mb_convert_encoding($v, 'UTF-8', $opts['encoding']), $headerRow);
}
fputcsv($output, $headerRow, $opts['delimiter'], $opts['enclosure']);
}
// 寫入資料
foreach ($data as $row) {
if ($opts['encoding'] !== 'UTF-8') {
$row = array_map(fn($v) => mb_convert_encoding($v, 'UTF-8', $opts['encoding']), $row);
}
fputcsv($output, $row, $opts['delimiter'], $opts['enclosure']);
}
fclose($output);
exit;
}
// 使用範例
$data = [
['張三', '25', '台北市', '工程師'],
['李四', '30', '新北市', '設計師'],
];
exportCsvDownload($data, 'members.csv', [
'headers' => ['姓名', '年齡', '城市', '職業'],
'delimiter' => ',',
]);
?>
<?php
function exportLargeCsv(callable $dataGenerator, string $filename, array $options = []): void {
// 設定標頭(同上)
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
$output = fopen('php://output', 'w');
fwrite($output, "\xEF\xBB\xBF"); // BOM
$count = 0;
$first = true;
foreach ($dataGenerator() as $row) {
if ($first && !empty($options['headers'])) {
fputcsv($output, $options['headers']);
$first = false;
}
// 編碼轉換
if (($options['encoding'] ?? 'UTF-8') !== 'UTF-8') {
$row = array_map(fn($v) => mb_convert_encoding($v, 'UTF-8', $options['encoding']), $row);
}
fputcsv($output, $row);
$count++;
// 定期 flush 避免記憶體累積
if ($count % 1000 === 0) {
ob_flush();
flush();
}
}
fclose($output);
}
// 使用:資料庫游標逐行產生
exportLargeCsv(function() {
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$stmt = $pdo->query('SELECT * FROM large_table');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
yield $row; // Generator 逐行產生
}
}, 'large_export.csv');
?>
<?php
function exportWithMapping(array $data, array $columnMap, string $filename): void {
// $columnMap = ['csv_header' => 'db_field'|callable]
$headers = array_keys($columnMap);
$output = fopen('php://output', 'w');
fwrite($output, "\xEF\xBB\xBF");
fputcsv($output, $headers);
foreach ($data as $row) {
$csvRow = [];
foreach ($columnMap as $header => $source) {
if (is_callable($source)) {
$csvRow[] = $source($row);
} elseif (is_string($source)) {
$csvRow[] = $row[$source] ?? '';
} else {
$csvRow[] = '';
}
}
fputcsv($output, $csvRow);
}
fclose($output);
}
// 使用
exportWithMapping($users, [
'會員編號' => 'id',
'姓名' => fn($r) => $r['last_name'] . $r['first_name'],
'年齡' => fn($r) => date_diff(date_create($r['birthdate']), new DateTime())->y,
'註冊時間' => 'created_at',
], 'users.csv');
fputcsv() 會自動處理:
"")<?php
$testRow = [
'normal',
'contains, comma',
'contains "quote"',
"line1\nline2",
'ends with comma,',
];
fputcsv($fh, $testRow);
// 輸出:normal,"contains, comma","contains ""quote""","line1
// line2","ends with comma,"
?>
| 問題 | 解決方案 |
|---|---|
| Excel 開啟中文亂碼 | 加入 UTF-8 BOM:fwrite($fp, "\xEF\xBB\xBF") |
| 長數字變科學記號(如身分證、信用卡號) | 前置單引號:"'\t" . $id 或設定欄位格式為文字 |
| 前置零消失(如 00123 → 123) | 同前置單引號法,或匯出為 xlsx 格式 |
| 記憶體耗盡 | 使用 Generator 分批、避免一次載入全部陣列 |
| 超時 | ini_set('max_execution_time', 0)、Nginx/Apache proxy timeout 同步調整 |
<?php
// 使用 league/csv (composer require league/csv)
use League\Csv\Writer;
$writer = Writer::createFromFileObject(new SplTempFileObject());
$writer->insertOne(['姓名', '年齡', '城市']);
$writer->insertAll($data);
// 直接輸出下載
$writer->output('members.csv');
// 或取得字串
$csv = $writer->toString();
?>
fputcsv() 處理格式化,fopen('php://output') 直接輸出\xEF\xBB\xBF)league/csv 或 PhpSpreadsheet (xlsx 支援)