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 AUTHOR
  26
  27 Dobrica Pavlinusic, dpavlin@rot13.org
  28
  29 =cut
  30
  31 use Spreadsheet::WriteExcel;
  32 use DBI;
  33 use CGI::Carp qw(fatalsToBrowser);
  34 use CGI qw(path_translated);
  35 use Encode qw/decode/;
  36 use Data::Dump qw/dump/;
  37
  38 # edit following to set defaults
  39 my $dsn      = 'DBI:Pg:dbname=';
  40 my $database = 'template1';
  41 my $user     = 'dpavlin';
  42 my $passwd   = '';
  43 my $path     = 'sql_reports.xls';
  44
  45 my $db_encoding     = 'iso-8859-2';
  46 my $xls_date_format = 'dd.mm.yyyy';
  47
  48 my $debug = 1;
  49
  50 my $sql_dir = path_translated || '.';
  51 $sql_dir =~ s,/[^/]+$,,;
  52
  53 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
  54 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
  55 closedir DIR;
  56
  57 my $workbook;
  58 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
  59         # use as cgi script
  60         print "Content-type: application/vnd.ms-excel\n\n";
  61         $workbook = Spreadsheet::WriteExcel->new("-");
  62 } else {
  63         # Create a new Excel workbook
  64         $workbook = Spreadsheet::WriteExcel->new( $path );
  65         warn "Creating XLS file $path\n";
  66 }
  67
  68 my $date_format = $workbook->add_format(num_format => $xls_date_format);
  69
  70 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
  71
  72 sub _c {
  73         return decode( $db_encoding, shift );
  74 }
  75
  76 foreach my $sql_file (@sql_files) {
  77
  78         my $sheet_name = $sql_file;
  79         $sheet_name =~ s/\d+_//;
  80         $sheet_name =~ s/_/ /g;
  81         $sheet_name =~ s/\.sql//;
  82
  83         # Add a worksheet
  84         my $worksheet = $workbook->addworksheet($sheet_name);
  85
  86         print STDERR "working on $sql_file\n" if ($debug);
  87
  88         open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
  89         my $comment = '';
  90         my $sql = "";
  91         while(<SQL>) {
  92                 chomp;
  93                 if (/^\\c\s+(\S+)/) {
  94                         $dbh->disconnect if $dbh;
  95                         print STDERR "## connect to $1\n" if $debug;
  96                         $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
  97                 } elsif (/^--(.+)/) {
  98                         $comment.=$1;
  99                 } else {
 100                         $sql.= ' ' . $_;
 101                 }
 102         }
 103         close(SQL);
 104
 105         $sql =~ s/\s\s+/ /gs;
 106
 107         print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
 108
 109         my $row = 0;
 110
 111         if ($comment) {
 112
 113                 #  Add and define a format
 114                 my $fmt_comment = $workbook->addformat();    # Add a format
 115                 $fmt_comment->set_bold();
 116
 117                 $worksheet->write($row, 0, _c($comment), $fmt_comment);
 118                 $row+=2;
 119         }
 120
 121         my $sth = $dbh->prepare($sql);
 122         $sth->execute();
 123
 124         my $fmt_header = $workbook->addformat();    # Add a format
 125         $fmt_header->set_italic();
 126
 127         for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
 128                 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
 129         }
 130         $row++;
 131
 132         my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
 133
 134         while (my @row = $sth->fetchrow_array() ) {
 135                 for(my $col=0; $col<=$#row; $col++) {
 136                         my $data = $row[$col];
 137                         if ( $types[$col] =~ m/^date/i ) {
 138                                 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
 139                                 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
 140                                 warn "## $data\n";
 141                                 $worksheet->write_date_time( $row, $col, $data, $date_format );
 142                         } else {
 143                                 $worksheet->write($row, $col, _c( $data ) );
 144                         }
 145                 }
 146                 $row++;
 147         }
 148
 149 }
 150
 151 $dbh->disconnect;
 152
 153 1;
 154
 155 __END__
 156