/[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 16 by dpavlin, Mon Nov 3 21:53:30 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 8  sql2xls.pl - convert sql queries on file Line 8  sql2xls.pl - convert sql queries on file
8    
9  =head1 USAGE  =head1 USAGE
10    
11  Each file in current directory which ends in C<< *.sql >> will  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  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  prefix filenames with numbers which will be striped when creating sheet
14  names.  names.
15    
16  Comments in sql files (lines beginning with --) will be placed  Comments in sql files (lines beginning with C<-->) will be placed
17  in first line in bold.  in first line in bold.
18    
19  To specify database on which SQL query is executed  To specify database on which SQL query is executed
20  C<< \c database >> syntax is supported.  C<\c database> syntax is supported.
21    
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    To configure default database, user, password and other settings create
43    C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
44    like this:
45    
46      $dsn      = 'DBI:mysql:dbname=';
47      $database = 'database';
48      $user     = 'user';
49      $passwd   = 'password';
50      $path     = 'sql_reports.xls';
51    
52      $db_encoding     = 'utf-8';
53      $xls_date_format = 'dd.mm.yyyy';
54    
55      $debug = 1;
56    
57    =head1 SECURITY
58    
59    There is none. Use apache auth modules if you need it.
60    
61  =head1 AUTHOR  =head1 AUTHOR
62    
63  Dobrica Pavlinusic, dpavlin@rot13.org  Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
64    
65  =cut  =cut
66    
67  use Spreadsheet::WriteExcel;  use Spreadsheet::WriteExcel;
68  use DBI;  use DBI;
69  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
 use CGI qw(path_translated);  
70  use Encode qw/decode/;  use Encode qw/decode/;
71  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
72    
73  # edit following to set defaults  # edit following to set defaults
74  my $dsn      = 'DBI:Pg:dbname=';  our $dsn      = 'DBI:Pg:dbname=';
75  my $database = 'template1';  our $database = 'template1';
76  my $user     = 'dpavlin';  our $user     = 'dpavlin';
77  my $passwd   = '';  our $passwd   = '';
78  my $path     = 'sql_reports.xls';  our $path     = 'sql_reports.xls';
79    
80  my $db_encoding     = 'iso-8859-2';  our $db_encoding     = 'iso-8859-2';
81  my $xls_date_format = 'dd.mm.yyyy';  our $xls_date_format = 'dd.mm.yyyy';
82    
83  my $debug = 1;  our $debug = 1;
84    
85  my $sql_dir = path_translated || '.';  my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
86  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
87    
88    my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
89    warn "# using $config_path\n";
90    require $config_path if -e $config_path;
91    
92    my $reports_path = $ENV{PATH_INFO};
93    $reports_path =~ s/\.\.//g; # some protection against path exploits
94    $reports_path ||= shift @ARGV; # for CLI invocation
95    $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
96    
97    warn "# reading SQL queries from $sql_dir\n" if $debug;
98    
99  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";  opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
100  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
101  closedir DIR;  closedir DIR;
# Line 70  my $date_format = $workbook->add_format( Line 116  my $date_format = $workbook->add_format(
116  my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;  my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
117    
118  sub _c {  sub _c {
119            return shift unless $db_encoding;
120          return decode( $db_encoding, shift );          return decode( $db_encoding, shift );
121  }  }
122    
# Line 81  foreach my $sql_file (@sql_files) { Line 128  foreach my $sql_file (@sql_files) {
128          $sheet_name =~ s/\.sql//;          $sheet_name =~ s/\.sql//;
129    
130          # Add a worksheet          # Add a worksheet
131          my $worksheet = $workbook->addworksheet($sheet_name);          warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
132            my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
133    
134          print STDERR "working on $sql_file\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
135    
136          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': $!";
137          my $comment = '';          my $comment = '';
138          my $sql = "";          my $sql = "";
139          while(<SQL>) {          while(<SQL>) {
# Line 114  foreach my $sql_file (@sql_files) { Line 162  foreach my $sql_file (@sql_files) {
162                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
163                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
164    
165                    $comment =~ s/^\s+//;
166                    $comment =~ s/\s+$//;
167    
168                  $worksheet->write($row, 0, _c($comment), $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
169                  $row+=2;                  $row+=2;
170          }          }
Legend:
Removed from v.7  
changed lines
  Added in v.16
  ViewVC Help
Powered by ViewVC 1.1.26