从MySQL导出XLS数据库工具

从MySQL导出XLS数据库工具2008-05-12这个脚本是使用Perl生成excelxls文件的工具。依赖一些模块，你可以在linux下使用，产生xls文件。使用方式是命令行+参数。非常方便。
#！/usr/bin/perl
#===============================
# mysql to excel
# lastmodify at 2005-1-5
# copyright by hoowa
#=============================
use strict; #严格语法检测
use DBI; #数据库引擎
use Unicode::Map; #Unicode引擎
#use Spreadsheet::WriteExcel; #Excel报表引擎
use Spreadsheet::WriteExcel::Big; #大文件Excel报表引擎
my $hostname="192.168.1.133";
my $username="user";
my $password="pass";
my $dbname="db";
my $trans_compress=1; #任何非一的数关闭数据库到程序间传输压缩
$|=1;
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","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","PP:P","QQ:Q","RR:R","SS:S","TT:T","UU:U","VV:V","WW:W",
"XX:X","YY:Y","ZZ:Z"）;
#解析来内容
if （$#ARGV ！= "1"） {
print qq~syntax: my2excel.pl <writefilename> "[where expression]"
~;
exit;
}
$ARGV[1]=~ s/"//g;
warn qq~
mysql to excel
by hoowa.sun
=====================
sql: $ARGV[1]
~;
my $dbh = 
DBI->connect（"DBI:mysql:mysql_compression=$trans_compress;
database=$dbname;host=$hostname",$username,$password）;
my $sth = $dbh->prepare（"$ARGV[1]"） || die $dbh->errstr;
my $rows = $sth->execute（） or die $sth->errstr;
warn "rows: $rows found.
";
my @cols_name = @{$sth->{"NAME"}};
if （$#cols_name > $#cols） {
print "table $ARGV[1] fields out of allow！！（max num. > ".（$#cols+1）."）
";
exit;
}
warn "write t $ARGV[0]
";
#生成GB2312编码系统
my $map = Unicode::Map->new（"gb2312"）;
#产生报表
my $report = Spreadsheet::WriteExcel::Big->new（"$ARGV[0]"） || die "不能生成报表文件：$！";
#创建报表的工作表
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"）;
#初始化数据指针
my $sheet_col = 0;
#创建表格
for （my $i=0;$i<=$#cols_name ;$i++） {
$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）;#冻结行
while （my @row = $sth->fetchrow_array） {
$sheet_col++;
for （my $i=0;$i<=$#cols_name ;$i++） {
next if （$row[$i] eq ""）;
$sheet->write_unicode（$sheet_col,$i,$map->to_unicode（$row[$i]））;
}
}
warn "all done！！！
";
#结束
END {
$report->close（） if （$report）;
$dbh->disconnect（）;
}