Export XLS Database Tools from MySQL (cross-platform)

mysql| Data | Database This script is a tool for generating Excel xls files using Perl. Depending on some module, you can use it under Linux to produce XLS files. Use the command line + parameter. It's very handy. This script is a tool for generating Excel xls files using Perl. Depending on some module, you can use it under Linux to produce XLS files. Use the command line + parameter. Very convenient.
#!/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: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 ');

#解析来内容
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.\n";

My @cols_name = @{$sth->{' name '}};
if ($ #cols_name > $ #cols) {
print "Table $ARGV [1] fields out of allow!! (Max Num. >.) ($ #cols + 1). ") \ n ";
Exit
}

Warn "Write to: $ARGV [0]\n";

#生成GB2312编码系统
My $map = unicode::map->new ("gb2312");
#产生报表
My $report = spreadsheet::writeexcel::big->new ("$ARGV [0]") | | Die "Cannot generate report file: $!";
#创建报表的工作表
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!!! \ n ";

#结束
End {
$report->close () if ($report);
$DBH->disconnect ();
}