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                         warn "## connect to $1\n" if $debug;
  95                         $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
  96                 } elsif (/^--(.+)/) {
  97                         $comment.=$1;
  98                 } else {
  99                         $sql.= ' ' . $_;
 100                 }
 101         }
 102         close(SQL);
 103
 104         $sql =~ s/\s\s+/ /gs;
 105
 106         print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
 107
 108         my $row = 0;
 109
 110         if ($comment) {
 111
 112                 #  Add and define a format
 113                 my $fmt_comment = $workbook->addformat();    # Add a format
 114                 $fmt_comment->set_bold();
 115
 116                 $worksheet->write($row, 0, _c($comment), $fmt_comment);
 117                 $row+=2;
 118         }
 119
 120         my $sth = $dbh->prepare($sql);
 121         $sth->execute();
 122
 123         my $fmt_header = $workbook->addformat();    # Add a format
 124         $fmt_header->set_italic();
 125
 126         for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
 127                 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
 128         }
 129         $row++;
 130
 131         my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
 132
 133         while (my @row = $sth->fetchrow_array() ) {
 134                 for(my $col=0; $col<=$#row; $col++) {
 135                         my $data = $row[$col];
 136                         if ( $types[$col] =~ m/^date/i ) {
 137                                 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
 138                                 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
 139                                 warn "## $data\n";
 140                                 $worksheet->write_date_time( $row, $col, $data, $date_format );
 141                         } else {
 142                                 $worksheet->write($row, $col, _c( $data ) );
 143                         }
 144                 }
 145                 $row++;
 146         }
 147
 148 }
 149
 150 $dbh->disconnect;
 151
 152 1;
 153
 154 __END__
 155