How to export XLS database tools from MySQL
Editor to share with you how to export XLS database tools from MySQL. I hope you will get something after reading this article. Let's discuss it together.
This script is a tool for generating excel xls files using Perl. Depending on some modules, you can use it under linux to generate xls files. It is used on the command line + parameters. It's very convenient.
#! / usr/bin/perl
# = =
# mysql to excel
# lastmodify at 2005-1-5
# copyright by hoowa
# = =
Use strict; # strict syntax checking
Use DBI; # engine
Use Unicode::Map; # Unicode engine
# use Spreadsheet::WriteExcel; # Excel report engine
Use Spreadsheet::WriteExcel::Big; # large File Excel report engine
My $hostname=192.168.1.133
My $username=user
My $password=pass
My $dbname=db
My $trans_compress=1; # any non-one number turns off database-to-program transfer compression
$| = 1
My @ cols= (An A, B, B, C, C, D, E, E, F, G, G.
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)
# parsing the content
If ($# ARGV! = 1) {
Print qq~syntax: my2excel.pl "[where expression]"
~
Exit
}
$ARGV [1] = ~ s / "/ / g
Warn qq~
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 allowances! (max num. >". ($# cols+1) ")
"
Exit
}
Warn "write to: $ARGV [0]
"
# generate GB2312 coding system
My $map = Unicode::Map- > new ("gb2312")
# generate report
My $report = Spreadsheet::WriteExcel::Big- > new ("$ARGV [0]") | | die "cannot generate report file: $!"
# create a worksheet for a report
My $sheet = $report- > add_worksheet (data_report)
# create format
My $title_style = $report- > add_format (); $title_style- > set_size (11); $title_style- > set_bold (); $title_style- > set_align (center)
# initialize data pointer
My $sheet_col = 0
# create a table
For (my $iS0tramplicative isetometric 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); # freeze rows
While (my @ row = $sth- > fetchrow_array) {
$sheet_col++
For (my $iwritebot Unicode ($sheet_col,$i,$map- > to_unicode ($row [$I]))
}
}
Warn "all please pay attention!
"
# end
END {
$report- > close () if ($report)
$dbh- > disconnect ()
}
After reading this article, I believe you have a certain understanding of "how to export XLS database tools from MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!
