git.rot13.org Git - SQL2XLS/blob - sql2xls.cgi

   1 #!/usr/bin/perl
   2 use warnings;
   3 use strict;
   4
   5 =head1 NAME
   6
   7 sql2xls.pl - convert sql queries on file system to Excel file
   8
   9 =head1 USAGE
  10
  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
  14 names.
  15
  16 Comments in sql files (lines beginning with --) will be placed
  17 in first line in bold.
  18
  19 To specify database on which SQL query is executed
  20 C<< \c database >> syntax is supported.
  21
  22 You can also run script from command line, and it will produce
  23 C<< sql_reports.xls >> file.
  24
  25 =head1 INSTALLATION
  26
  27 Only required file is this script C<< sql2xls.cgi >>
  28
  29 If your server is configured to execute C<.cgi> files, you can
  30 drop this script anywhere, but you can also add something like
  31
  32    ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
  33
  34 in Apache's virtual host configuration to get nice URLs
  35
  36 =head1 AUTHOR
  37
  38 Dobrica Pavlinusic, dpavlin@rot13.org
  39
  40 =cut
  41
  42 use Spreadsheet::WriteExcel;
  43 use DBI;
  44 use CGI::Carp qw(fatalsToBrowser);
  45 use CGI qw(path_translated);
  46 use Encode qw/decode/;
  47 use Data::Dump qw/dump/;
  48
  49 # edit following to set defaults
  50 our $dsn      = 'DBI:Pg:dbname=';
  51 our $database = 'template1';
  52 our $user     = 'dpavlin';
  53 our $passwd   = '';
  54 our $path     = 'sql_reports.xls';
  55
  56 our $db_encoding     = 'iso-8859-2';
  57 our $xls_date_format = 'dd.mm.yyyy';
  58
  59 our $debug = 1;
  60
  61 my $sql_dir = path_translated || '.';
  62 $sql_dir =~ s,/[^/]+$,,;
  63
  64 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
  65 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
  66 closedir DIR;
  67
  68 my $config_path = "$sql_dir/config.pl";
  69 warn "# using $config_path\n";
  70 require $config_path if -e $config_path;
  71
  72 my $workbook;
  73 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
  74         # use as cgi script
  75         print "Content-type: application/vnd.ms-excel\n\n";
  76         $workbook = Spreadsheet::WriteExcel->new("-");
  77 } else {
  78         # Create a new Excel workbook
  79         $workbook = Spreadsheet::WriteExcel->new( $path );
  80         warn "Creating XLS file $path\n";
  81 }
  82
  83 my $date_format = $workbook->add_format(num_format => $xls_date_format);
  84
  85 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
  86
  87 sub _c {
  88         return decode( $db_encoding, shift );
  89 }
  90
  91 foreach my $sql_file (@sql_files) {
  92
  93         my $sheet_name = $sql_file;
  94         $sheet_name =~ s/\d+_//;
  95         $sheet_name =~ s/_/ /g;
  96         $sheet_name =~ s/\.sql//;
  97
  98         # Add a worksheet
  99         warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
 100         my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
 101
 102         print STDERR "working on $sql_file\n" if ($debug);
 103
 104         open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
 105         my $comment = '';
 106         my $sql = "";
 107         while(<SQL>) {
 108                 chomp;
 109                 if (/^\\c\s+(\S+)/) {
 110                         $dbh->disconnect if $dbh;
 111                         print STDERR "## connect to $1\n" if $debug;
 112                         $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 113                 } elsif (/^--(.+)/) {
 114                         $comment.=$1;
 115                 } else {
 116                         $sql.= ' ' . $_;
 117                 }
 118         }
 119         close(SQL);
 120
 121         $sql =~ s/\s\s+/ /gs;
 122
 123         print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
 124
 125         my $row = 0;
 126
 127         if ($comment) {
 128
 129                 #  Add and define a format
 130                 my $fmt_comment = $workbook->addformat();    # Add a format
 131                 $fmt_comment->set_bold();
 132
 133                 $comment =~ s/^\s+//;
 134                 $comment =~ s/\s+$//;
 135
 136                 $worksheet->write($row, 0, _c($comment), $fmt_comment);
 137                 $row+=2;
 138         }
 139
 140         my $sth = $dbh->prepare($sql);
 141         $sth->execute();
 142
 143         my $fmt_header = $workbook->addformat();    # Add a format
 144         $fmt_header->set_italic();
 145
 146         for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
 147                 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
 148         }
 149         $row++;
 150
 151         my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
 152
 153         while (my @row = $sth->fetchrow_array() ) {
 154                 for(my $col=0; $col<=$#row; $col++) {
 155                         my $data = $row[$col];
 156                         if ( $types[$col] =~ m/^date/i ) {
 157                                 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
 158                                 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
 159                                 warn "## $data\n";
 160                                 $worksheet->write_date_time( $row, $col, $data, $date_format );
 161                         } else {
 162                                 $worksheet->write($row, $col, _c( $data ) );
 163                         }
 164                 }
 165                 $row++;
 166         }
 167
 168 }
 169
 170 $dbh->disconnect;
 171
 172 1;
 173
 174 __END__
 175