/[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

Annotation of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 8 - (hide annotations)
Mon Nov 3 18:51:49 2008 UTC (15 years, 4 months ago) by dpavlin
File size: 3792 byte(s)
strip spaces around comments
1 dpavlin 5 #!/usr/bin/perl
2     use warnings;
3     use strict;
4 dpavlin 1
5 dpavlin 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 dpavlin 1 use Spreadsheet::WriteExcel;
32     use DBI;
33     use CGI::Carp qw(fatalsToBrowser);
34     use CGI qw(path_translated);
35 dpavlin 2 use Encode qw/decode/;
36 dpavlin 3 use Data::Dump qw/dump/;
37 dpavlin 1
38 dpavlin 5 # 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 dpavlin 2
45 dpavlin 5 my $db_encoding = 'iso-8859-2';
46 dpavlin 3 my $xls_date_format = 'dd.mm.yyyy';
47    
48 dpavlin 1 my $debug = 1;
49    
50     my $sql_dir = path_translated || '.';
51     $sql_dir =~ s,/[^/]+$,,;
52    
53     opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
54     my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
55     closedir DIR;
56    
57     my $workbook;
58 dpavlin 5 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
59 dpavlin 1 # use as cgi script
60     print "Content-type: application/vnd.ms-excel\n\n";
61     $workbook = Spreadsheet::WriteExcel->new("-");
62     } else {
63     # Create a new Excel workbook
64 dpavlin 5 $workbook = Spreadsheet::WriteExcel->new( $path );
65     warn "Creating XLS file $path\n";
66 dpavlin 1 }
67    
68 dpavlin 3 my $date_format = $workbook->add_format(num_format => $xls_date_format);
69    
70 dpavlin 5 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
71 dpavlin 1
72 dpavlin 2 sub _c {
73 dpavlin 3 return decode( $db_encoding, shift );
74 dpavlin 2 }
75    
76 dpavlin 1 foreach my $sql_file (@sql_files) {
77    
78     my $sheet_name = $sql_file;
79     $sheet_name =~ s/\d+_//;
80     $sheet_name =~ s/_/ /g;
81     $sheet_name =~ s/\.sql//;
82    
83     # Add a worksheet
84     my $worksheet = $workbook->addworksheet($sheet_name);
85    
86 dpavlin 6 print STDERR "working on $sql_file\n" if ($debug);
87 dpavlin 1
88     open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
89 dpavlin 7 my $comment = '';
90 dpavlin 1 my $sql = "";
91     while(<SQL>) {
92     chomp;
93 dpavlin 4 if (/^\\c\s+(\S+)/) {
94 dpavlin 6 $dbh->disconnect if $dbh;
95     print STDERR "## connect to $1\n" if $debug;
96 dpavlin 5 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
97 dpavlin 4 } elsif (/^--(.+)/) {
98 dpavlin 1 $comment.=$1;
99     } else {
100 dpavlin 4 $sql.= ' ' . $_;
101 dpavlin 1 }
102     }
103     close(SQL);
104    
105 dpavlin 4 $sql =~ s/\s\s+/ /gs;
106    
107 dpavlin 1 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
108    
109     my $row = 0;
110    
111     if ($comment) {
112    
113     # Add and define a format
114     my $fmt_comment = $workbook->addformat(); # Add a format
115     $fmt_comment->set_bold();
116    
117 dpavlin 8 $comment =~ s/^\s+//;
118     $comment =~ s/\s+$//;
119    
120 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
121 dpavlin 1 $row+=2;
122     }
123    
124 dpavlin 5 my $sth = $dbh->prepare($sql);
125     $sth->execute();
126 dpavlin 1
127     my $fmt_header = $workbook->addformat(); # Add a format
128     $fmt_header->set_italic();
129    
130     for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
131     $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
132     }
133     $row++;
134    
135 dpavlin 6 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
136 dpavlin 3
137 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
138     for(my $col=0; $col<=$#row; $col++) {
139 dpavlin 3 my $data = $row[$col];
140     if ( $types[$col] =~ m/^date/i ) {
141     $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
142 dpavlin 4 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
143     warn "## $data\n";
144 dpavlin 3 $worksheet->write_date_time( $row, $col, $data, $date_format );
145     } else {
146     $worksheet->write($row, $col, _c( $data ) );
147     }
148 dpavlin 1 }
149     $row++;
150     }
151    
152     }
153    
154     $dbh->disconnect;
155    
156     1;
157    
158     __END__
159    

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26