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

Contents of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log


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

1 #!/usr/bin/perl -T
2 use warnings;
3 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 C<-->) 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 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
62
63 Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
64
65 =cut
66
67 use Spreadsheet::WriteExcel;
68 use DBI;
69 use CGI::Carp qw(fatalsToBrowser);
70 use Encode qw/decode/;
71 use Data::Dump qw/dump/;
72
73 # edit following to set defaults
74 our $dsn = 'DBI:Pg:dbname=';
75 our $database = 'template1';
76 our $user = 'dpavlin';
77 our $passwd = '';
78 our $path = 'sql_reports.xls';
79
80 our $db_encoding = 'iso-8859-2';
81 our $xls_date_format = 'dd.mm.yyyy';
82
83 our $debug = 1;
84
85 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
86 $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: $!";
100 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
101 closedir DIR;
102
103 my $workbook;
104 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
105 # 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 $workbook = Spreadsheet::WriteExcel->new( $path );
111 warn "Creating XLS file $path\n";
112 }
113
114 my $date_format = $workbook->add_format(num_format => $xls_date_format);
115
116 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
117
118 sub _c {
119 return shift unless $db_encoding;
120 return decode( $db_encoding, shift );
121 }
122
123 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 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);
135
136 open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
137 my $comment = '';
138 my $sql = "";
139 while(<SQL>) {
140 chomp;
141 if (/^\\c\s+(\S+)/) {
142 $dbh->disconnect if $dbh;
143 print STDERR "## connect to $1\n" if $debug;
144 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
145 } elsif (/^--(.+)/) {
146 $comment.=$1;
147 } else {
148 $sql.= ' ' . $_;
149 }
150 }
151 close(SQL);
152
153 $sql =~ s/\s\s+/ /gs;
154
155 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 $comment =~ s/^\s+//;
166 $comment =~ s/\s+$//;
167
168 $worksheet->write($row, 0, _c($comment), $fmt_comment);
169 $row+=2;
170 }
171
172 my $sth = $dbh->prepare($sql);
173 $sth->execute();
174
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 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
184
185 while (my @row = $sth->fetchrow_array() ) {
186 for(my $col=0; $col<=$#row; $col++) {
187 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 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
191 warn "## $data\n";
192 $worksheet->write_date_time( $row, $col, $data, $date_format );
193 } else {
194 $worksheet->write($row, $col, _c( $data ) );
195 }
196 }
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