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

Perl 腳本：MySQL 查詢結果直接匯出 Excel (XLS) 格式工具

編輯：MySQL 資料庫相關知識

工具特性

命令列介面：perl my2excel.pl output.xls "SELECT * FROM table"
支援大量資料：使用 Spreadsheet::WriteExcel::Big
自動欄寬調整、標題列凍結、粗體標題
Big5/GB2312 編碼轉換（透過 Unicode::Map）
壓縮傳輸選項

完整腳本 `my2excel.pl`


#!/usr/bin/perl
# MySQL to Excel Export Tool
# 需模組: DBI, DBD::mysql, Unicode::Map, Spreadsheet::WriteExcel::Big

use strict;
use DBI;
use Unicode::Map;
use Spreadsheet::WriteExcel::Big;

# ========== 資料庫連線設定 ==========
my $hostname = '192.168.1.133';
my $username = 'user';
my $password = 'pass';
my $dbname   = 'db';
my $trans_compress = 1;  # 1=啟用壓縮
# ====================================

$| = 1;  # 自動 flush

# Excel 欄位對應 (支援至 ZZ 欄)
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:A','BB:B','CC:C','DD:D',
            'EE:E','FF:F','GG:G','HH:H','II:I','JJ:J','KK:K','LL:L','MM:M',
            'NN:N','OO:O','PP:P','QQ:Q','RR:R','SS:S','TT:T','UU:U','VV:V',
            'WW:W','XX:X','YY:Y','ZZ:Z');

# 參數檢查
die "Usage: $0  \"SQL query\"\n" unless @ARGV == 2;
my ($outfile, $sql) = @ARGV;
$sql =~ s/\"//g;

warn "MySQL to Excel\nby hoowa.sun\n=====================\nsql: $sql\n";

# 連線
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";

my @cols_name = @{$sth->{NAME}};
die "欄位數超過支援上限 (".(@cols+1).")\n" if @cols_name > @cols;

# 編碼對應 (GB2312 -> Unicode)
my $map = Unicode::Map->new("gb2312") or die "無法建立 GB2312 對應表\n";

# 建立 Excel
my $report = Spreadsheet::WriteExcel::Big->new($outfile) or die "無法建立 $outfile: $!\n";
my $sheet = $report->add_worksheet('data_report');

# 標題樣式
my $title_style = $report->add_format();
$title_style->set_size(11);
$title_style->set_bold();
$title_style->set_align('center');

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

# 寫入資料
my $row_idx = 0;
while (my @row = $sth->fetchrow_array) {
    $row_idx++;
    for my $i (0 .. $#cols_name) {
        next unless defined $row[$i] && $row[$i] ne '';
        $sheet->write_unicode($row_idx, $i, $map->to_unicode($row[$i]));
    }
}

warn "all done!!!\n";

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

使用範例


# 匯出完整資料表
perl my2excel.pl users.xls "SELECT id, name, email, created_at FROM users WHERE status=1"

# 匯出聯結查詢
perl my2excel.pl orders.xls "SELECT o.id, u.name, o.total, o.created_at FROM orders o JOIN users u ON o.uid=u.id WHERE o.date>='2024-01-01'"

# 指定壓縮傳輸 (預設啟用)
# 修改腳本 $trans_compress = 0 可關閉

依賴模組安裝 (CPAN)


cpan DBI DBD::mysql Unicode::Map Spreadsheet::WriteExcel::Big
# 或系統套件管理器
# Ubuntu/Debian: apt-get install libdbi-perl libdbd-mysql-perl libunicode-map-perl libspreadsheet-writeexcel-perl
# CentOS: yum install perl-DBI perl-DBD-MySQL perl-Unicode-Map perl-Spreadsheet-WriteExcel-Big