2022年从MySQL导出XLS数据库工具(跨平台)Mysql教程.docx
2022年从MySQL导出XLS数据库工具(跨平台)Mysql教程 这个脚本是运用Perl生成excel xls文件的工具。依靠一些模块，你可以在linux下运用，产生xls文件。运用方式是吩咐行+参数。特别便利这个脚本是运用Perl生成excel xls文件的工具。依靠一些模块，你可以在linux下运用，产生xls文件。运用方式是吩咐行+参数。特别便利。 #!/usr/bin/perl#=# mysql to excel# lastmodify at 2022-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 qqsyntax: my2excel.pl <writefilename> "where expression" ; exit; }$ARGV1= s/"/g;warn qqmysql to excelby hoowa.sun=sql: $ARGV1;my $dbh =DBI->connect("DBI:mysql:mysql_compression=$trans_compress; database=$dbname;host=$hostname",$username,$password); my $sth = $dbh->prepare("$ARGV1") | 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 $ARGV1 fields out of allow!(max num. > ".($#cols+1).")n" exit; }warn "write to: $ARGV0n"#生成GB2312编码系统my $map = Unicode:Map->new("gb2312");#产生报表my $report = Spreadsheet:WriteExcel:Big->new("$ARGV0") | 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!n"#结束END { $report->close() if ($report); $dbh->disconnect(); }
