/[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 2 by dpavlin, Mon Mar 24 15:57:14 2008 UTC revision 12 by dpavlin, Mon Nov 3 20:15:09 2008 UTC
# Line 1  Line 1 
1  #!/usr/bin/perl -w  #!/usr/bin/perl
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    =head1 INSTALLATION
26    
27    Only required file is this script C<< sql2xls.cgi >>
28    
29    If your server is configured to execute C<.cgi> files, you can
30    drop this script anywhere, but you can also add something like
31    
32       ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
33    
34    in Apache's virtual host configuration to get nice URLs
35    
36    =head1 AUTHOR
37    
38    Dobrica Pavlinusic, dpavlin@rot13.org
39    
40    =cut
41    
42  use Spreadsheet::WriteExcel;  use Spreadsheet::WriteExcel;
43  use DBI;  use DBI;
44  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
45  use CGI qw(path_translated);  use CGI qw(path_translated);
46  use Encode qw/decode/;  use Encode qw/decode/;
47    use Data::Dump qw/dump/;
48    
49    # edit following to set defaults
50    our $dsn      = 'DBI:Pg:dbname=';
51    our $database = 'template1';
52    our $user     = 'dpavlin';
53    our $passwd   = '';
54    our $path     = 'sql_reports.xls';
55    
56  # Connect to DB  our $db_encoding     = 'iso-8859-2';
57  my $connect = "DBI:Pg:dbname=new";  our $xls_date_format = 'dd.mm.yyyy';
 my $user = "web";  
 my $passwd = "";  
58    
59  my $debug = 1;  our $debug = 1;
60    
61  my $sql_dir = path_translated || '.';  my $sql_dir = path_translated || '.';
62  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
# Line 26  opendir(DIR, $sql_dir) || die "can't ope Line 65  opendir(DIR, $sql_dir) || die "can't ope
65  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
66  closedir DIR;  closedir DIR;
67    
68    my $config_path = "$sql_dir/config.pl";
69    warn "# using $config_path\n";
70    require $config_path if -e $config_path;
71    
72  my $workbook;  my $workbook;
73  if ($0 =~ m/\.cgi$/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
74          # use as cgi script          # use as cgi script
75          print "Content-type: application/vnd.ms-excel\n\n";          print "Content-type: application/vnd.ms-excel\n\n";
76          $workbook = Spreadsheet::WriteExcel->new("-");          $workbook = Spreadsheet::WriteExcel->new("-");
77  } else {  } else {
78          # Create a new Excel workbook          # Create a new Excel workbook
79          $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");          $workbook = Spreadsheet::WriteExcel->new( $path );
80            warn "Creating XLS file $path\n";
81  }  }
82    
83  my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;  my $date_format = $workbook->add_format(num_format => $xls_date_format);
84    
85    my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
86    
87  sub _c {  sub _c {
88          return decode('iso-8859-2', shift);          return decode( $db_encoding, shift );
89  }  }
90    
91  foreach my $sql_file (@sql_files) {  foreach my $sql_file (@sql_files) {
# Line 50  foreach my $sql_file (@sql_files) { Line 96  foreach my $sql_file (@sql_files) {
96          $sheet_name =~ s/\.sql//;          $sheet_name =~ s/\.sql//;
97    
98          # Add a worksheet          # Add a worksheet
99          my $worksheet = $workbook->addworksheet($sheet_name);          warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
100            my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
101    
102          print STDERR "working on $sql_file...\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
103    
104          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
105          my $comment;          my $comment = '';
106          my $sql = "";          my $sql = "";
107          while(<SQL>) {          while(<SQL>) {
108                  chomp;                  chomp;
109                  if (/^--(.+)/) {                  if (/^\\c\s+(\S+)/) {
110                            $dbh->disconnect if $dbh;
111                            print STDERR "## connect to $1\n" if $debug;
112                            $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
113                    } elsif (/^--(.+)/) {
114                          $comment.=$1;                          $comment.=$1;
115                  } else {                  } else {
116                          $sql.=$_;                          $sql.= ' ' . $_;
117                  }                  }
118          }          }
119          close(SQL);          close(SQL);
120    
121            $sql =~ s/\s\s+/ /gs;
122    
123          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
124    
125          my $row = 0;          my $row = 0;
# Line 77  foreach my $sql_file (@sql_files) { Line 130  foreach my $sql_file (@sql_files) {
130                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
131                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
132    
133                    $comment =~ s/^\s+//;
134                    $comment =~ s/\s+$//;
135    
136                  $worksheet->write($row, 0, _c($comment), $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
137                  $row+=2;                  $row+=2;
138          }          }
139    
140          my $sth = $dbh->prepare($sql) || die $dbh->errstr();          my $sth = $dbh->prepare($sql);
141          $sth->execute() || die $sth->errstr();          $sth->execute();
142    
143          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->addformat();    # Add a format
144          $fmt_header->set_italic();          $fmt_header->set_italic();
# Line 92  foreach my $sql_file (@sql_files) { Line 148  foreach my $sql_file (@sql_files) {
148          }          }
149          $row++;          $row++;
150    
151            my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
152    
153          while (my @row = $sth->fetchrow_array() ) {          while (my @row = $sth->fetchrow_array() ) {
154                  for(my $col=0; $col<=$#row; $col++) {                  for(my $col=0; $col<=$#row; $col++) {
155                          $worksheet->write($row, $col, _c( $row[$col] ) );                          my $data = $row[$col];
156                            if ( $types[$col] =~ m/^date/i ) {
157                                    $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
158                                    $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
159                                    warn "## $data\n";
160                                    $worksheet->write_date_time( $row, $col, $data, $date_format );
161                            } else {
162                                    $worksheet->write($row, $col, _c( $data ) );
163                            }
164                  }                  }
165                  $row++;                  $row++;
166          }          }
# Line 107  $dbh->disconnect; Line 173  $dbh->disconnect;
173    
174  __END__  __END__
175    
 =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.2  
changed lines
  Added in v.12
  ViewVC Help
Powered by ViewVC 1.1.26