Export XLS database tools from MySQL

Author: hoowa

This script uses Perl to generate excel xls files. Depending on some modules, you can use it in linux to generate xls files. The command line + parameter is used. Very convenient.
#! /Usr/bin/perl

#====================================
# Mysql to excel
# Lastmodify at 2005-1-5
# Copyright by hoowa
#==================================
Use strict; # strict syntax check
Use DBI; # Database 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.htm;
My $ username = user;
My $ password = pass;
My $ dbname = db;
My $ trans_compress = 1; # transfer and compression between the database and the program from a non-zero number

$ | = 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 );

# Parsed content
If ($ # ARGV! = 1 ){
Print qq ~ Syntax: my2excel. pl "[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 $ something = $ dbh-> prepare ("$ ARGV [1]") | die $ dbh-> errstr;
My $ rows = $ something-> execute () or die $ something-> errstr;

Warn "rows: $ rows found .";

My @ cols_name =@{$ something-> {NAME }};
If ($ # cols_name >$ # cols ){
Print "table $ ARGV [1] fields out of allow !! (Max num.> ". ($ # cols + 1 ).")";
Exit;
}

Warn "write to: $ ARGV [0]";

# Generate a GB2312 Encoding System
My $ map = Unicode: Map-> new ("gb2312 ");
# Generate Reports
My $ report = Spreadsheet: WriteExcel: Big-> new ("$ ARGV [0]") | die "cannot generate report files: $! ";
# Create a report Worksheet
My $ sheet = $ report-> add_worksheet (data_report );
# Create a 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 $ 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); # freeze rows

While (my @ row = $ th-> 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
END {
$ Report-> close () if ($ report );
$ Dbh-> disconnect ();
}