7 sql2xls.pl - convert sql queries on file system to Excel file
11 Each file in current directory which ends in C<*.sql> will
12 be converted to Excel sheet. If you want to have specific order, you can
13 prefix filenames with numbers which will be striped when creating sheet
16 Comments in sql files (lines beginning with C<-->) will be placed
17 in first line in bold.
19 To specify database on which SQL query is executed
20 C<\c database> syntax is supported.
22 You can also run script from command line, and it will produce
23 C<sql_reports.xls> file.
25 If run within directory, it will use files in it to produce file.
27 When called as CGI, directory name can be appended to name of script
28 to produce report for any sub-directory within directory where
29 C<sql2xls.cgi> is installed.
33 Only required file is this script C<< sql2xls.cgi >>
35 If your server is configured to execute C<.cgi> files, you can
36 drop this script anywhere, but you can also add something like
38 ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
40 in Apache's virtual host configuration to get nice URLs
42 To configure default database, user, password and other settings create
43 C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
46 $dsn = 'DBI:mysql:dbname=';
47 $database = 'database';
50 $path = 'sql_reports.xls';
52 $db_encoding = 'utf-8';
53 $xls_date_format = 'dd.mm.yyyy';
59 There is none. Use apache auth modules if you need it.
63 Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
67 use Excel::Writer::XLSX;
69 use CGI::Carp qw(fatalsToBrowser);
70 use Encode qw/decode/;
71 use Data::Dump qw/dump/;
73 our $dsn = 'DBI:Pg:dbname=';
74 our $database = 'template1';
75 our $user = 'dpavlin';
77 our $path = 'sql_reports.xlsx';
79 our $db_encoding = 'iso-8859-2';
80 our $xls_date_format = 'dd.mm.yyyy';
82 our $debug = $ENV{DEBUG} || 0;
84 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
85 $sql_dir =~ s,/[^/]+$,,;
88 my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
89 warn "# using $config_path\n";
90 require $config_path if -e $config_path;
95 my $reports_path = $ENV{PATH_INFO} || '';
96 $reports_path =~ s/\.\.//g; # some protection against path exploits
97 $reports_path ||= shift @ARGV; # for CLI invocation
98 $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
102 warn "SQL queries from $sql_dir\n";
104 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
105 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
109 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
111 print "Content-type: application/vnd.ms-excel\n\n";
112 $workbook = Excel::Writer::XLSX->new("-");
114 # Create a new Excel workbook
115 $path =~ s{\.xls$}{\.xlsx};
116 $workbook = Excel::Writer::XLSX->new( $path );
117 warn "Creating XLS file $path\n";
120 my $date_format = $workbook->add_format(num_format => $xls_date_format);
124 $dbh->disconnect if $dbh;
125 my $database = shift || return;
126 print STDERR "## connect to $database\n" if $debug;
127 $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
128 if ( $db_encoding ) {
129 if ( $dsn =~ m{Pg} ) {
130 $dbh->do( qq{ set client_encoding = '$db_encoding'; } );
131 } elsif ( $dsn =~ m{mysql} ) {
132 $dbh->do( qq{ set names '$db_encoding'; } );
134 warn "Don't know how to set encoding to $db_encoding for $dsn";
139 use_database( $database );
142 return shift unless $db_encoding;
143 return decode( $db_encoding, shift );
146 foreach my $sql_file (@sql_files) {
148 my $sheet_name = $sql_file;
149 $sheet_name =~ s/\d+[_-]//;
150 $sheet_name =~ s/_/ /g;
151 $sheet_name =~ s/\.sql//;
154 warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
155 my $worksheet = $workbook->add_worksheet( substr($sheet_name,0,31) );
157 print STDERR "working on $sql_file\n" if ($debug);
159 open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
164 if (/^\\c\s+(\S+)/) {
166 } elsif (/^--(.+)/) {
169 $full_sql.= ' ' . $_;
174 $full_sql =~ s/\s\s+/ /gs;
175 $full_sql .= ';' unless $full_sql =~ m/;\s*/s;
177 print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
183 # Add and define a format
184 my $fmt_comment = $workbook->add_format(); # Add a format
185 $fmt_comment->set_bold();
187 $comment =~ s/^\s+//;
188 $comment =~ s/\s+$//;
190 $worksheet->write($row, 0, _c($comment), $fmt_comment);
194 my $fmt_header = $workbook->add_format(); # Add a format
195 $fmt_header->set_italic();
197 foreach my $sql ( split(/;/, $full_sql ) ) {
201 my $sth = $dbh->prepare($sql);
204 next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL
207 map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
210 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
211 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
215 while (my @row = $sth->fetchrow_array() ) {
216 for(my $col=0; $col<=$#row; $col++) {
217 my $data = $row[$col];
218 next unless defined $data;
219 if ( $types[$col] && $types[$col] =~ m/^date/i ) {
220 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
221 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\d\d:\d\d)$/$1T$2/;
222 warn "## by type datetime $data\n" if $debug;
223 $worksheet->write_date_time( $row, $col, $data, $date_format );
224 } elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
225 warn "## heuristic date time: $1T$2\n" if $debug;
226 $worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
228 $worksheet->write($row, $col, _c( $data ) );
234 $row++; # separete queries by one row