/[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 14 - (hide annotations)
Mon Nov 3 20:42:55 2008 UTC (15 years, 4 months ago) by dpavlin
Original Path: sql2xls.cgi
File size: 5330 byte(s)
added configuration example and improved pod a bit
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 13 my $config_path = "$sql_dir/config.pl";
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 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 3 return decode( $db_encoding, shift );
120 dpavlin 2 }
121    
122 dpavlin 1 foreach my $sql_file (@sql_files) {
123    
124     my $sheet_name = $sql_file;
125     $sheet_name =~ s/\d+_//;
126     $sheet_name =~ s/_/ /g;
127     $sheet_name =~ s/\.sql//;
128    
129     # Add a worksheet
130 dpavlin 11 warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
131     my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
132 dpavlin 1
133 dpavlin 6 print STDERR "working on $sql_file\n" if ($debug);
134 dpavlin 1
135 dpavlin 13 open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
136 dpavlin 7 my $comment = '';
137 dpavlin 1 my $sql = "";
138     while(<SQL>) {
139     chomp;
140 dpavlin 4 if (/^\\c\s+(\S+)/) {
141 dpavlin 6 $dbh->disconnect if $dbh;
142     print STDERR "## connect to $1\n" if $debug;
143 dpavlin 5 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
144 dpavlin 4 } elsif (/^--(.+)/) {
145 dpavlin 1 $comment.=$1;
146     } else {
147 dpavlin 4 $sql.= ' ' . $_;
148 dpavlin 1 }
149     }
150     close(SQL);
151    
152 dpavlin 4 $sql =~ s/\s\s+/ /gs;
153    
154 dpavlin 1 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
155    
156     my $row = 0;
157    
158     if ($comment) {
159    
160     # Add and define a format
161     my $fmt_comment = $workbook->addformat(); # Add a format
162     $fmt_comment->set_bold();
163    
164 dpavlin 8 $comment =~ s/^\s+//;
165     $comment =~ s/\s+$//;
166    
167 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
168 dpavlin 1 $row+=2;
169     }
170    
171 dpavlin 5 my $sth = $dbh->prepare($sql);
172     $sth->execute();
173 dpavlin 1
174     my $fmt_header = $workbook->addformat(); # Add a format
175     $fmt_header->set_italic();
176    
177     for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
178     $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
179     }
180     $row++;
181    
182 dpavlin 6 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
183 dpavlin 3
184 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
185     for(my $col=0; $col<=$#row; $col++) {
186 dpavlin 3 my $data = $row[$col];
187     if ( $types[$col] =~ m/^date/i ) {
188     $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
189 dpavlin 4 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
190     warn "## $data\n";
191 dpavlin 3 $worksheet->write_date_time( $row, $col, $data, $date_format );
192     } else {
193     $worksheet->write($row, $col, _c( $data ) );
194     }
195 dpavlin 1 }
196     $row++;
197     }
198    
199     }
200    
201     $dbh->disconnect;
202    
203     1;
204    
205     __END__
206    

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26