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

Annotation of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 16 - (hide annotations)
Mon Nov 3 21:53:30 2008 UTC (15 years, 4 months ago) by dpavlin
Original Path: sql2xls.cgi
File size: 5394 byte(s)
undef $db_encoding to disable decode of encoding

1 dpavlin 13 #!/usr/bin/perl -T
2 dpavlin 5 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 dpavlin 14 Each file in current directory which ends in C<*.sql> will
12 dpavlin 5 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 dpavlin 14 Comments in sql files (lines beginning with C<-->) will be placed
17 dpavlin 5 in first line in bold.
18    
19     To specify database on which SQL query is executed
20 dpavlin 14 C<\c database> syntax is supported.
21 dpavlin 5
22     You can also run script from command line, and it will produce
23 dpavlin 14 C<sql_reports.xls> file.
24 dpavlin 5
25 dpavlin 13 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 dpavlin 12 =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 dpavlin 14 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 dpavlin 5 =head1 AUTHOR
62    
63 dpavlin 14 Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
64 dpavlin 5
65     =cut
66    
67 dpavlin 1 use Spreadsheet::WriteExcel;
68     use DBI;
69     use CGI::Carp qw(fatalsToBrowser);
70 dpavlin 2 use Encode qw/decode/;
71 dpavlin 3 use Data::Dump qw/dump/;
72 dpavlin 1
73 dpavlin 5 # edit following to set defaults
74 dpavlin 10 our $dsn = 'DBI:Pg:dbname=';
75     our $database = 'template1';
76     our $user = 'dpavlin';
77     our $passwd = '';
78     our $path = 'sql_reports.xls';
79 dpavlin 2
80 dpavlin 10 our $db_encoding = 'iso-8859-2';
81     our $xls_date_format = 'dd.mm.yyyy';
82 dpavlin 3
83 dpavlin 10 our $debug = 1;
84 dpavlin 1
85 dpavlin 13 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
86 dpavlin 1 $sql_dir =~ s,/[^/]+$,,;
87    
88 dpavlin 15 my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
89 dpavlin 13 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 dpavlin 1 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
100     my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
101     closedir DIR;
102    
103     my $workbook;
104 dpavlin 5 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
105 dpavlin 1 # use as cgi script
106     print "Content-type: application/vnd.ms-excel\n\n";
107     $workbook = Spreadsheet::WriteExcel->new("-");
108     } else {
109     # Create a new Excel workbook
110 dpavlin 5 $workbook = Spreadsheet::WriteExcel->new( $path );
111     warn "Creating XLS file $path\n";
112 dpavlin 1 }
113    
114 dpavlin 3 my $date_format = $workbook->add_format(num_format => $xls_date_format);
115    
116 dpavlin 5 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
117 dpavlin 1
118 dpavlin 2 sub _c {
119 dpavlin 16 return shift unless $db_encoding;
120 dpavlin 3 return decode( $db_encoding, shift );
121 dpavlin 2 }
122    
123 dpavlin 1 foreach my $sql_file (@sql_files) {
124    
125     my $sheet_name = $sql_file;
126     $sheet_name =~ s/\d+_//;
127     $sheet_name =~ s/_/ /g;
128     $sheet_name =~ s/\.sql//;
129    
130     # Add a worksheet
131 dpavlin 11 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 dpavlin 1
134 dpavlin 6 print STDERR "working on $sql_file\n" if ($debug);
135 dpavlin 1
136 dpavlin 13 open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
137 dpavlin 7 my $comment = '';
138 dpavlin 1 my $sql = "";
139     while(<SQL>) {
140     chomp;
141 dpavlin 4 if (/^\\c\s+(\S+)/) {
142 dpavlin 6 $dbh->disconnect if $dbh;
143     print STDERR "## connect to $1\n" if $debug;
144 dpavlin 5 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
145 dpavlin 4 } elsif (/^--(.+)/) {
146 dpavlin 1 $comment.=$1;
147     } else {
148 dpavlin 4 $sql.= ' ' . $_;
149 dpavlin 1 }
150     }
151     close(SQL);
152    
153 dpavlin 4 $sql =~ s/\s\s+/ /gs;
154    
155 dpavlin 1 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
156    
157     my $row = 0;
158    
159     if ($comment) {
160    
161     # Add and define a format
162     my $fmt_comment = $workbook->addformat(); # Add a format
163     $fmt_comment->set_bold();
164    
165 dpavlin 8 $comment =~ s/^\s+//;
166     $comment =~ s/\s+$//;
167    
168 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
169 dpavlin 1 $row+=2;
170     }
171    
172 dpavlin 5 my $sth = $dbh->prepare($sql);
173     $sth->execute();
174 dpavlin 1
175     my $fmt_header = $workbook->addformat(); # Add a format
176     $fmt_header->set_italic();
177    
178     for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
179     $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
180     }
181     $row++;
182    
183 dpavlin 6 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
184 dpavlin 3
185 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
186     for(my $col=0; $col<=$#row; $col++) {
187 dpavlin 3 my $data = $row[$col];
188     if ( $types[$col] =~ m/^date/i ) {
189     $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
190 dpavlin 4 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
191     warn "## $data\n";
192 dpavlin 3 $worksheet->write_date_time( $row, $col, $data, $date_format );
193     } else {
194     $worksheet->write($row, $col, _c( $data ) );
195     }
196 dpavlin 1 }
197     $row++;
198     }
199    
200     }
201    
202     $dbh->disconnect;
203    
204     1;
205    
206     __END__
207    

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26