跨平台 MySQL 導出 Excel 工具：Perl + Spreadsheet::WriteExcel 實戰

工具定位

純 Perl 實現，無需 Windows/COM 組件，可在 Linux/Unix 直接生成原生 .xls (BIFF 格式)，支援大數據量導出，適合定時任務、報表自動化、異構系統數據交換。

依賴模組

• DBI + DBD::mysql：資料庫連接
• Unicode::Map：字符集轉換 (GB2312 → Unicode)
• Spreadsheet::WriteExcel::Big：支援 65,536 行以上大文件

安裝：cpanm DBI DBD::mysql Unicode::Map Spreadsheet::WriteExcel::Big

核心代碼結構解析

1. 連接與查詢

my $dbh = DBI->connect(
    "DBI:mysql:mysql_compression=1;database=$dbname;host=$hostname",
    $username, $password
);
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute() or die $sth->errstr;

2. Excel 工作簿初始化

my $report = Spreadsheet::WriteExcel::Big->new($outfile);
my $sheet  = $report->add_worksheet('data_report');
my $title_fmt = $report->add_format();
$title_fmt->set_size(11)->set_bold()->set_align('center');
$sheet->freeze_panes(1, 0);  # 凍結標題行

3. 字符集處理與寫入

my $map = Unicode::Map->new('gb2312');  # 來源編碼
# 寫入標題
$sheet->set_column($cols[$i], length($col_name)+4);
$sheet->write_unicode(0, $i, $map->to_unicode($col_name), $title_fmt);
# 寫入數據
while (my @row = $sth->fetchrow_array) {
    $sheet_col++;
    for my $i (0..$#col_name) {
        next if $row[$i] eq '';
        $sheet->write_unicode($sheet_col, $i, $map->to_unicode($row[$i]));
    }
}

4. 清理收尾

END {
    $report->close() if $report;
    $dbh->disconnect();
}

使用方式

perl mysql2excel.pl output.xls "SELECT * FROM orders WHERE created_at > '2024-01-01'"

• 參數 1：輸出文件路徑
• 參數 2：完整 SQL 查詢 (含 WHERE/JOIN 等)

遷移到現代 .xlsx 的建議

Spreadsheet::WriteExcel 僅支援舊版 .xls (BIFF8)，行數限 65,536，無 .xlsx 支援。新專案建議：

• Perl：Excel::Writer::XLSX (API 相容、支援 100 萬行、.xlsx 格式)
• Python：openpyxl / pandas.ExcelWriter (生態更豐富)
• Node.js：exceljs (流式寫入、低內存)

生產環境優化清單

1. 流式查詢：$sth = $dbh->prepare($sql, { mysql_use_result => 1 }) 減少內存
2. 分批提交：大表分批次寫入，避免單次事務過大
3. 編碼自動檢測：使用 Encode::Detect 替代硬編碼 GB2312
4. 錯誤重試：網絡抖動時自動重連續傳
5. 日誌審計：記錄導出時間、行數、文件大小、操作人

結語

這個 Perl 腳本展示了「無需圖形界面、跨平台、可自動化」的數據導出工程化思維。雖然 .xls 格式逐漸退出歷史舞台，但其模組化設計、字符集處理、大文件流式寫入等核心模式，在現代 ETL/報表管線中依然具有參考價值。