/[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 1 by dpavlin, Mon Mar 24 14:05:54 2008 UTC revision 7 by dpavlin, Mon Nov 3 18:50:02 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 AUTHOR
26    
27    Dobrica Pavlinusic, dpavlin@rot13.org
28    
29    =cut
30    
31  use Spreadsheet::WriteExcel;  use Spreadsheet::WriteExcel;
32  use DBI;  use DBI;
33  use CGI::Carp qw(fatalsToBrowser);  use CGI::Carp qw(fatalsToBrowser);
34  use CGI qw(path_translated);  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;  my $debug = 1;
49    
# Line 21  my @sql_files = sort grep { /\.sql$/i && Line 55  my @sql_files = sort grep { /\.sql$/i &&
55  closedir DIR;  closedir DIR;
56    
57  my $workbook;  my $workbook;
58  if ($0 =~ m/\.cgi$/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
59          # use as cgi script          # use as cgi script
60          print "Content-type: application/vnd.ms-excel\n\n";          print "Content-type: application/vnd.ms-excel\n\n";
61          $workbook = Spreadsheet::WriteExcel->new("-");          $workbook = Spreadsheet::WriteExcel->new("-");
62  } else {  } else {
63          # Create a new Excel workbook          # Create a new Excel workbook
64          $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");          $workbook = Spreadsheet::WriteExcel->new( $path );
65            warn "Creating XLS file $path\n";
66  }  }
67    
68  # Connect to DB  my $date_format = $workbook->add_format(num_format => $xls_date_format);
 my $connect = "DBI:Pg:dbname=new";  
 my $user = "web";  
 my $passwd = "";  
69    
70  my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;  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) {  foreach my $sql_file (@sql_files) {
77    
# Line 47  foreach my $sql_file (@sql_files) { Line 83  foreach my $sql_file (@sql_files) {
83          # Add a worksheet          # Add a worksheet
84          my $worksheet = $workbook->addworksheet($sheet_name);          my $worksheet = $workbook->addworksheet($sheet_name);
85    
86          print STDERR "working on $sql_file...\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
87    
88          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
89          my $comment;          my $comment = '';
90          my $sql = "";          my $sql = "";
91          while(<SQL>) {          while(<SQL>) {
92                  chomp;                  chomp;
93                  if (/^--(.+)/) {                  if (/^\\c\s+(\S+)/) {
94                            $dbh->disconnect if $dbh;
95                            print STDERR "## connect to $1\n" if $debug;
96                            $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
97                    } elsif (/^--(.+)/) {
98                          $comment.=$1;                          $comment.=$1;
99                  } else {                  } else {
100                          $sql.=$_;                          $sql.= ' ' . $_;
101                  }                  }
102          }          }
103          close(SQL);          close(SQL);
104    
105            $sql =~ s/\s\s+/ /gs;
106    
107          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);          print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
108    
109          my $row = 0;          my $row = 0;
# Line 72  foreach my $sql_file (@sql_files) { Line 114  foreach my $sql_file (@sql_files) {
114                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
115                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
116    
117                  $worksheet->write($row, 0, $comment, $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
118                  $row+=2;                  $row+=2;
119          }          }
120    
121          my $sth = $dbh->prepare($sql) || die $dbh->errstr();          my $sth = $dbh->prepare($sql);
122          $sth->execute() || die $sth->errstr();          $sth->execute();
123    
124          my $fmt_header = $workbook->addformat();    # Add a format          my $fmt_header = $workbook->addformat();    # Add a format
125          $fmt_header->set_italic();          $fmt_header->set_italic();
# Line 87  foreach my $sql_file (@sql_files) { Line 129  foreach my $sql_file (@sql_files) {
129          }          }
130          $row++;          $row++;
131    
132            my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
133    
134          while (my @row = $sth->fetchrow_array() ) {          while (my @row = $sth->fetchrow_array() ) {
135                  for(my $col=0; $col<=$#row; $col++) {                  for(my $col=0; $col<=$#row; $col++) {
136                          $worksheet->write($row, $col, $row[$col] );                          my $data = $row[$col];
137                            if ( $types[$col] =~ m/^date/i ) {
138                                    $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
139                                    $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
140                                    warn "## $data\n";
141                                    $worksheet->write_date_time( $row, $col, $data, $date_format );
142                            } else {
143                                    $worksheet->write($row, $col, _c( $data ) );
144                            }
145                  }                  }
146                  $row++;                  $row++;
147          }          }
# Line 102  $dbh->disconnect; Line 154  $dbh->disconnect;
154    
155  __END__  __END__
156    
 =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.7
  ViewVC Help
Powered by ViewVC 1.1.26