/[SQL2XLS]/sql2xlsx.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 /sql2xlsx.cgi

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

revision 7 by dpavlin, Mon Nov 3 18:50:02 2008 UTC revision 13 by dpavlin, Mon Nov 3 20:32:44 2008 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl  #!/usr/bin/perl -T
2  use warnings;  use warnings;
3  use strict;  use strict;
4    
# Line 22  C<< \c database >> syntax is supported. Line 22  C<< \c database >> syntax is supported.
22  You can also run script from command line, and it will produce  You can also run script from command line, and it will produce
23  C<< sql_reports.xls >> file.  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  =head1 AUTHOR
43    
44  Dobrica Pavlinusic, dpavlin@rot13.org  Dobrica Pavlinusic, dpavlin@rot13.org
# Line 31  Dobrica Pavlinusic, dpavlin@rot13.org Line 48  Dobrica Pavlinusic, dpavlin@rot13.org
48  use Spreadsheet::WriteExcel;  use Spreadsheet::WriteExcel;
49  use DBI;  use DBI;
50  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
 use CGI qw(path_translated);  
51  use Encode qw/decode/;  use Encode qw/decode/;
52  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
53    
54  # edit following to set defaults  # edit following to set defaults
55  my $dsn      = 'DBI:Pg:dbname=';  our $dsn      = 'DBI:Pg:dbname=';
56  my $database = 'template1';  our $database = 'template1';
57  my $user     = 'dpavlin';  our $user     = 'dpavlin';
58  my $passwd   = '';  our $passwd   = '';
59  my $path     = 'sql_reports.xls';  our $path     = 'sql_reports.xls';
60    
61  my $db_encoding     = 'iso-8859-2';  our $db_encoding     = 'iso-8859-2';
62  my $xls_date_format = 'dd.mm.yyyy';  our $xls_date_format = 'dd.mm.yyyy';
63    
64  my $debug = 1;  our $debug = 1;
65    
66  my $sql_dir = path_translated || '.';  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;
# Line 81  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>) {
# Line 114  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                    $comment =~ s/^\s+//;
146                    $comment =~ s/\s+$//;
147    
148                  $worksheet->write($row, 0, _c($comment), $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
149                  $row+=2;                  $row+=2;
150          }          }
Legend:
Removed from v.7  
changed lines
  Added in v.13
  ViewVC Help
Powered by ViewVC 1.1.26