跨平台 MySQL 導出 Excel 工具：Perl + Spreadsheet::WriteExcel::Big 實現大文件生成

需在 Linux 服務器無圖形界面環境下將 MySQL 查詢結果導出為 Excel (.xls) 文件供業務下載分析。本工具基於 Perl DBI + Spreadsheet::WriteExcel::Big 實現，支持命令行參數化、大文件流式寫入、Unicode 編碼處理，無需 Windows/Office 環境。

依賴模塊安裝

# CPAN 安裝
cpan DBI DBD::mysql Unicode::Map Spreadsheet::WriteExcel::Big

# 或系統包管理器
apt-get install libdbi-perl libdbd-mysql-perl libunicode-map-perl libspreadsheet-writeexcel-perl
# 注意：WriteExcel::Big 可能需從 CPAN 獲取最新版

核心腳本：mysql2excel.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Unicode::Map;
use Spreadsheet::WriteExcel::Big;  # 支持 > 65536 行、大文件

# ===== 配置區 =====
my $hostname = '192.168.1.133';
my $username = 'user';
my $password = 'pass';
my $dbname   = 'db';
my $trans_compress = 1;  # 1=啟用壓縮協議

# Excel 列字母映射（支持超出 Z 列）
my @cols = ('A:A','B:B','C:C','D:D','E:E','F:F','G:G','H:H','I:I','J:J',
            'K:K','L:L','M:M','N:N','O:O','P:P','Q:Q','R:R','S:S','T:T',
            'U:U','V:V','W:W','X:X','Y:Y','Z:Z',
            'AA:AA','BB:BB','CC:CC','DD:DD','EE:EE','FF:FF','GG:GG','HH:HH',
            'II:II','JJ:JJ','KK:KK','LL:LL','MM:MM','NN:NN','OO:OO','PP:PP',
            'QQ:QQ','RR:RR','SS:SS','TT:TT','UU:UU','VV:VV','WW:WW','XX:XX',
            'YY:YY','ZZ:ZZ');
# ====================

$| = 1;  # 刷新輸出緩衝

# 參數解析：mysql2excel.pl "output.xls" "SELECT * FROM table WHERE ..."
die "Usage: $0  \"SELECT ...\"\n" unless @ARGV == 2;
my ($outFile, $sql) = @ARGV;
$sql =~ s/^\"|\"$//g;  # 去除包裹雙引號

warn "MySQL to Excel\nby Perl Script\n=====================\nSQL: $sql\n";

# 數據庫連接
my $dsn = "DBI:mysql:database=$dbname;host=$hostname;mysql_compression=$trans_compress";
my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1, PrintError => 0, AutoCommit => 1 })
    or die "Connect failed: $DBI::errstr";

my $sth = $dbh->prepare($sql) or die "Prepare failed: $dbh->errstr";
my $rows = $sth->execute() or die "Execute failed: $sth->errstr";
warn "Rows fetched: $rows\n";

# 列名獲取
my $colNames = $sth->{NAME};
my $colCount = @$colNames;
die "Columns ($colCount) exceed max supported (" . scalar(@cols) . ")\n" if $colCount > @cols;

# 創建 Excel 工作簿
my $workbook = Spreadsheet::WriteExcel::Big->new($outFile)
    or die "Create workbook failed: $!";
my $worksheet = $workbook->add_worksheet('Data');

# 寫入表頭
my $headerFormat = $workbook->add_format(bold => 1, bgcolor => '#4472C4', color => 'white', border => 1);
for my $i (0 .. $colCount-1) {
    $worksheet->write(0, $i, $colNames->[$i], $headerFormat);
}

# 流式寫入數據行
my $rowNum = 1;
my $dataFormat = $workbook->add_format(border => 1);
while (my $rowRef = $sth->fetchrow_arrayref) {
    for my $i (0 .. $colCount-1) {
        my $val = $rowRef->[$i];
        # Unicode 處理：DBI 返回 UTF-8，WriteExcel 內部處理
        $worksheet->write($rowNum, $i, $val // '', $dataFormat);
    }
    $rowNum++;
    # 進度提示
    warn "Written $rowNum rows...\n" if $rowNum % 10000 == 0;
}

$workbook->close();
warn "Export completed: $rowNum rows written to $outFile\n";

$dbh->disconnect();

使用示例

# 導出全表
perl mysql2excel.pl "orders_2024.xls" "SELECT * FROM orders WHERE YEAR(created_at)=2024"

# 導出多表 JOIN 結果
perl mysql2excel.pl "customer_summary.xls" "
    SELECT c.id, c.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    HAVING total_amount > 10000
"

關鍵技術點解析

現代替代方案對比

總結

Perl + Spreadsheet::WriteExcel::Big 為「Linux 無界面導出大 Excel」提供了經典可靠方案。隨著 Python 生態成熟，pandas.ExcelWriter(engine='openpyxl', mode='w') 配合 chunksize 分塊寫入已成主流選擇。選型原則：存量 Perl 系統續用、新建項目首選 Python、Web 服務化用 Node.js、純數據遷移用 mysqldump。

1. 上一篇文章： SQL Server 異構數據整合：鏈接服務器聯邦查詢 Access 與 SQL Server 聯合分析
2. 下一篇文章： Access 遷移 SQL Server 完整避坑指南：字段類型、函數、語法、行為差異全景對比

MySQL 資料庫相關知識

MySQL 5.7 分區表全解析：類型、限制、管理與最佳實踐

什麼是分區 將一張大表按規則水平拆分為多個物理分區，邏輯上仍

MSSQL 2000 本地企業管理器遠程註冊與連線伺服器

環境 Windows XP，SQL Server 2000

MySQL 通配符陷阱：下劃線 `_` 也是單字符通配符

現象重現 表 action_conf 含資料： exp_si

MySQL 查詢優化實戰：OR、IN 與 UNION ALL 效率對比測試分析

核心結論先行 不要迷信「UNION ALL 永遠優於 OR/