/[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 12 - (show annotations)
Mon Nov 3 20:15:09 2008 UTC (15 years, 4 months ago) by dpavlin
File size: 4332 byte(s)
suggest ScriptAlias to get nice URLs

1 #!/usr/bin/perl
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 --) 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 INSTALLATION
26
27 Only required file is this script C<< sql2xls.cgi >>
28
29 If your server is configured to execute C<.cgi> files, you can
30 drop this script anywhere, but you can also add something like
31
32 ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
33
34 in Apache's virtual host configuration to get nice URLs
35
36 =head1 AUTHOR
37
38 Dobrica Pavlinusic, dpavlin@rot13.org
39
40 =cut
41
42 use Spreadsheet::WriteExcel;
43 use DBI;
44 use CGI::Carp qw(fatalsToBrowser);
45 use CGI qw(path_translated);
46 use Encode qw/decode/;
47 use Data::Dump qw/dump/;
48
49 # edit following to set defaults
50 our $dsn = 'DBI:Pg:dbname=';
51 our $database = 'template1';
52 our $user = 'dpavlin';
53 our $passwd = '';
54 our $path = 'sql_reports.xls';
55
56 our $db_encoding = 'iso-8859-2';
57 our $xls_date_format = 'dd.mm.yyyy';
58
59 our $debug = 1;
60
61 my $sql_dir = path_translated || '.';
62 $sql_dir =~ s,/[^/]+$,,;
63
64 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
65 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
66 closedir DIR;
67
68 my $config_path = "$sql_dir/config.pl";
69 warn "# using $config_path\n";
70 require $config_path if -e $config_path;
71
72 my $workbook;
73 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
74 # use as cgi script
75 print "Content-type: application/vnd.ms-excel\n\n";
76 $workbook = Spreadsheet::WriteExcel->new("-");
77 } else {
78 # Create a new Excel workbook
79 $workbook = Spreadsheet::WriteExcel->new( $path );
80 warn "Creating XLS file $path\n";
81 }
82
83 my $date_format = $workbook->add_format(num_format => $xls_date_format);
84
85 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
86
87 sub _c {
88 return decode( $db_encoding, shift );
89 }
90
91 foreach my $sql_file (@sql_files) {
92
93 my $sheet_name = $sql_file;
94 $sheet_name =~ s/\d+_//;
95 $sheet_name =~ s/_/ /g;
96 $sheet_name =~ s/\.sql//;
97
98 # Add a worksheet
99 warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
100 my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
101
102 print STDERR "working on $sql_file\n" if ($debug);
103
104 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
105 my $comment = '';
106 my $sql = "";
107 while(<SQL>) {
108 chomp;
109 if (/^\\c\s+(\S+)/) {
110 $dbh->disconnect if $dbh;
111 print STDERR "## connect to $1\n" if $debug;
112 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
113 } elsif (/^--(.+)/) {
114 $comment.=$1;
115 } else {
116 $sql.= ' ' . $_;
117 }
118 }
119 close(SQL);
120
121 $sql =~ s/\s\s+/ /gs;
122
123 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
124
125 my $row = 0;
126
127 if ($comment) {
128
129 # Add and define a format
130 my $fmt_comment = $workbook->addformat(); # Add a format
131 $fmt_comment->set_bold();
132
133 $comment =~ s/^\s+//;
134 $comment =~ s/\s+$//;
135
136 $worksheet->write($row, 0, _c($comment), $fmt_comment);
137 $row+=2;
138 }
139
140 my $sth = $dbh->prepare($sql);
141 $sth->execute();
142
143 my $fmt_header = $workbook->addformat(); # Add a format
144 $fmt_header->set_italic();
145
146 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
147 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
148 }
149 $row++;
150
151 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
152
153 while (my @row = $sth->fetchrow_array() ) {
154 for(my $col=0; $col<=$#row; $col++) {
155 my $data = $row[$col];
156 if ( $types[$col] =~ m/^date/i ) {
157 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
158 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
159 warn "## $data\n";
160 $worksheet->write_date_time( $row, $col, $data, $date_format );
161 } else {
162 $worksheet->write($row, $col, _c( $data ) );
163 }
164 }
165 $row++;
166 }
167
168 }
169
170 $dbh->disconnect;
171
172 1;
173
174 __END__
175

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26