您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

使用 Perl 腳本將 MySQL 資料匯出為 Excel (XLS) 格式完整指南

編輯：MySQL 資料庫相關知識

前言

在資料庫管理與報表製作的實務工作中，將 MySQL 資料匯出為 Excel 檔案是常見需求。本文介紹一個基於 Perl 的命令列工具，能夠高效地將 MySQL 查詢結果直接生成 Excel 97-2003 格式 (.xls) 的檔案，支援大量資料處理與中文編碼轉換，適合在 Linux 環境下自動化部署。

工具特點與依賴模組

此腳本採用 Perl 語言編寫，核心依賴以下 CPAN 模組：

DBI：資料庫介面層，負責連接 MySQL
DBD::mysql：MySQL 專用驅動程式
Unicode::Map：處理字元編碼轉換（預設轉為 GB2312）
Spreadsheet::WriteExcel::Big：支援超過 65,535 列的大型 Excel 檔案生成

使用方式簡單：perl my2Excel.pl <輸出檔名> ""，即可從指定資料表抓取符合條件的資料並寫入 .xls 檔案。

核心程式碼解析

1. 資料庫連接設定

my $hostname = '192.168.1.133';
my $username = 'user';
my $passWord = 'pass';
my $dbname   = 'db';
my $trans_compress = 1;  # 啟用壓縮傳輸

實際部署時請修改上述連線參數，建議透過設定檔或環境變數管理敏感資訊。

2. 參數解析與 SQL 執行

die "syntax: my2Excel.pl  \"[where expression]\""
  unless @ARGV == 2;

my $sql = $ARGV[1];
$sql =~ s/\"//g;  # 移除雙引號

my $dbh = DBI->connect(
  "DBI:mysql:mysql_compression=$trans_compress;database=$dbname;host=$hostname",
  $username, $passWord
) or die $DBI::errstr;

my $sth = $dbh->prepare($sql) or die $dbh->errstr;
my $rows = $sth->execute() or die $sth->errstr;
warn "rows: $rows found.\n";

腳本接受兩個參數：輸出檔名與 WHERE 條件子句（不含 WHERE 關鍵字）。執行查詢後取得欄位名稱陣列 @{$sth->{NAME}}。

3. Excel 欄位對應與寬度設定

my @cols = ('A:A','B:B','C:C', ..., 'ZZ:Z');  # 支援至 ZZ 欄

for my $i (0 .. $#cols_name) {
  $sheet->set_column($cols[$i], length($cols_name[$i]) + 4);
  $sheet->write_unicode($sheet_col, $i, $map->to_unicode($cols_name[$i]), $title_style);
}
$sheet->freeze_panes(1, 0);  # 凍結標題列

預先定義 Excel 欄位字母對應表，自動依欄位名稱長度調整寬度，並以粗體置中格式寫入標題列。

4. 資料寫入與編碼轉換

my $map = Unicode::Map->new("gb2312");
my $report = Spreadsheet::WriteExcel::Big->new($ARGV[0]) or die "無法建立報表：$!";

while (my @row = $sth->fetchrow_array) {
  $sheet_col++;
  for my $i (0 .. $#cols_name) {
    next if $row[$i] eq '';
    $sheet->write_unicode($sheet_col, $i, $map->to_unicode($row[$i]));
  }
}
warn "all done!!!\n";

使用 Unicode::Map 將資料從資料庫編碼（通常為 UTF-8）轉為 GB2312 再寫入 Excel，確保中文顯示正常。Spreadsheet::WriteExcel::Big 解決了舊版模組無法處理 65,535 列以上資料的限制。

5. 資源釋放

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

END 區塊確保程式結束時正確關閉檔案與資料庫連線，避免資源洩漏。

使用範例

# 匯出 users 表中 status=1 的所有資料
perl my2Excel.pl active_users.xls "SELECT * FROM users WHERE status = 1"

# 匯出訂單表近 30 天資料
perl my2Excel.pl recent_orders.xls "SELECT * FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)"

注意事項與擴充建議

編碼處理：若資料庫使用 UTF-8，且 Excel 需支援繁體中文，可將 gb2312 改為 cp950 或 big5
大量資料效能：對於百萬列以上資料，建議分批次匯出或改用 CSV 格式再轉檔
安全性：避免直接在命令列傳遞密碼，建議使用 --defaults-file 或環境變數
現代替代方案：新專案可考慮使用 Python pandas + openpyxl 或 xlsxwriter，支援 .xlsx 格式且維護性更佳