/[SQL2XLS]/sql2xls.cgi
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1 by dpavlin, Mon Mar 24 14:05:54 2008 UTC revision 13 by dpavlin, Mon Nov 3 20:32:44 2008 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl -w  #!/usr/bin/perl -T
2  #  use warnings;
 # convert sql queries on file system to excel file (one query per  
 # sheet)  
 #  
 # Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10  
   
3  use strict;  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    If run within directory, it will use files in it to produce file.
26    
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.
30    
31    =head1 INSTALLATION
32    
33    Only required file is this script C<< sql2xls.cgi >>
34    
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
37    
38       ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
39    
40    in Apache's virtual host configuration to get nice URLs
41    
42    =head1 AUTHOR
43    
44    Dobrica Pavlinusic, dpavlin@rot13.org
45    
46    =cut
47    
48  use Spreadsheet::WriteExcel;  use Spreadsheet::WriteExcel;
49  use DBI;  use DBI;
50  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
51  use CGI qw(path_translated);  use Encode qw/decode/;
52    use Data::Dump qw/dump/;
53    
54  my $debug = 1;  # edit following to set defaults
55    our $dsn      = 'DBI:Pg:dbname=';
56    our $database = 'template1';
57    our $user     = 'dpavlin';
58    our $passwd   = '';
59    our $path     = 'sql_reports.xls';
60    
61  my $sql_dir = path_translated || '.';  our $db_encoding     = 'iso-8859-2';
62    our $xls_date_format = 'dd.mm.yyyy';
63    
64    our $debug = 1;
65    
66    my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
67  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
68    
69    my $config_path = "$sql_dir/config.pl";
70    warn "# using $config_path\n";
71    require $config_path if -e $config_path;
72    
73    my $reports_path = $ENV{PATH_INFO};
74    $reports_path =~ s/\.\.//g; # some protection against path exploits
75    $reports_path ||= shift @ARGV; # for CLI invocation
76    $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
77    
78    warn "# reading SQL queries from $sql_dir\n" if $debug;
79    
80  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
81  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
82  closedir DIR;  closedir DIR;
83    
84  my $workbook;  my $workbook;
85  if ($0 =~ m/\.cgi$/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
86          # use as cgi script          # use as cgi script
87          print "Content-type: application/vnd.ms-excel\n\n";          print "Content-type: application/vnd.ms-excel\n\n";
88          $workbook = Spreadsheet::WriteExcel->new("-");          $workbook = Spreadsheet::WriteExcel->new("-");
89  } else {  } else {
90          # Create a new Excel workbook          # Create a new Excel workbook
91          $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");          $workbook = Spreadsheet::WriteExcel->new( $path );
92            warn "Creating XLS file $path\n";
93  }  }
94    
95  # Connect to DB  my $date_format = $workbook->add_format(num_format => $xls_date_format);
96  my $connect = "DBI:Pg:dbname=new";  
97  my $user = "web";  my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 my $passwd = "";  
98    
99  my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;  sub _c {
100            return decode( $db_encoding, shift );
101    }
102    
103  foreach my $sql_file (@sql_files) {  foreach my $sql_file (@sql_files) {
104    
# Line 45  foreach my $sql_file (@sql_files) { Line 108  foreach my $sql_file (@sql_files) {
108          $sheet_name =~ s/\.sql//;          $sheet_name =~ s/\.sql//;
109    
110          # Add a worksheet          # Add a worksheet
111          my $worksheet = $workbook->addworksheet($sheet_name);          warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
112            my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
113    
114          print STDERR "working on $sql_file...\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
115    
116          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";          open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
117          my $comment;          my $comment = '';
118          my $sql = "";          my $sql = "";
119          while(<SQL>) {          while(<SQL>) {
120                  chomp;                  chomp;
121                  if (/^--(.+)/) {                  if (/^\\c\s+(\S+)/) {
122                            $dbh->disconnect if $dbh;
123                            print STDERR "## connect to $1\n" if $debug;
124                            $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
125                    } elsif (/^--(.+)/) {
126                          $comment.=$1;                          $comment.=$1;
127                  } else {                  } else {
128                          $sql.=$_;                          $sql.= ' ' . $_;
129                  }                  }
130          }          }
131          close(SQL);          close(SQL);
132    
133            $sql =~ s/\s\s+/ /gs;
134    
135          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
136    
137          my $row = 0;          my $row = 0;
# Line 72  foreach my $sql_file (@sql_files) { Line 142  foreach my $sql_file (@sql_files) {
142                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
143                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
144    
145                  $worksheet->write($row, 0, $comment, $fmt_comment);                  $comment =~ s/^\s+//;
146                    $comment =~ s/\s+$//;
147    
148                    $worksheet->write($row, 0, _c($comment), $fmt_comment);
149                  $row+=2;                  $row+=2;
150          }          }
151    
152          my $sth = $dbh->prepare($sql) || die $dbh->errstr();          my $sth = $dbh->prepare($sql);
153          $sth->execute() || die $sth->errstr();          $sth->execute();
154    
155          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->addformat();    # Add a format
156          $fmt_header->set_italic();          $fmt_header->set_italic();
# Line 87  foreach my $sql_file (@sql_files) { Line 160  foreach my $sql_file (@sql_files) {
160          }          }
161          $row++;          $row++;
162    
163            my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
164    
165          while (my @row = $sth->fetchrow_array() ) {          while (my @row = $sth->fetchrow_array() ) {
166                  for(my $col=0; $col<=$#row; $col++) {                  for(my $col=0; $col<=$#row; $col++) {
167                          $worksheet->write($row, $col, $row[$col] );                          my $data = $row[$col];
168                            if ( $types[$col] =~ m/^date/i ) {
169                                    $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
170                                    $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
171                                    warn "## $data\n";
172                                    $worksheet->write_date_time( $row, $col, $data, $date_format );
173                            } else {
174                                    $worksheet->write($row, $col, _c( $data ) );
175                            }
176                  }                  }
177                  $row++;                  $row++;
178          }          }
# Line 102  $dbh->disconnect; Line 185  $dbh->disconnect;
185    
186  __END__  __END__
187    
 =head1 NAME  
   
 sql2xls.pl - convert sql queries on file system to excel file  
   
 =head1 USAGE  
   
 Edit top of script and edit @sql_files array which describes  
 files which are going to be loaded and executed producing  
 Excel sheets (one sheet per file)  
   
 Comments in sql files (lines beginning with --) will be placed  
 in outout sheet on top in bold.  
   
 Sheet will have name same as sql file.  
   
 Run script and examine b<sql_result.xls> file.  
   
 =head1 AUTHOR  
   
 Dobrica Pavlinusic, dpavlin@rot13.org  
   
Legend:
Removed from v.1  
changed lines
  Added in v.13
  ViewVC Help
Powered by ViewVC 1.1.26