Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.