7 sql2xls.pl - convert sql queries on file system to Excel file
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
16 Comments in sql files (lines beginning with C<-->) will be placed
17 in first line in bold.
19 To specify database on which SQL query is executed
20 C<\c database> syntax is supported.
22 You can also run script from command line, and it will produce
23 C<sql_reports.xls> file.
25 If run within directory, it will use files in it to produce file.
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.
33 Only required file is this script C<< sql2xls.cgi >>
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
38 ScriptAlias /xls-reports /srv/SQL2XLS/sql2xls.cgi
40 in Apache's virtual host configuration to get nice URLs
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
46 $dsn = 'DBI:mysql:dbname=';
47 $database = 'database';
50 $path = 'sql_reports.xls';
52 $db_encoding = 'utf-8';
53 $xls_date_format = 'dd.mm.yyyy';
59 There is none. Use apache auth modules if you need it.
63 Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
67 use Spreadsheet::WriteExcel;
69 use CGI::Carp qw(fatalsToBrowser);
70 use Encode qw/decode/;
71 use Data::Dump qw/dump/;
73 # edit following to set defaults
74 our $dsn = 'DBI:Pg:dbname=';
75 our $database = 'template1';
76 our $user = 'dpavlin';
78 our $path = 'sql_reports.xls';
80 our $db_encoding = 'iso-8859-2';
81 our $xls_date_format = 'dd.mm.yyyy';
85 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
86 $sql_dir =~ s,/[^/]+$,,;
88 my $config_path = "$sql_dir/config.pl";
89 warn "# using $config_path\n";
90 require $config_path if -e $config_path;
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";
97 warn "# reading SQL queries from $sql_dir\n" if $debug;
99 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
100 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
104 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
106 print "Content-type: application/vnd.ms-excel\n\n";
107 $workbook = Spreadsheet::WriteExcel->new("-");
109 # Create a new Excel workbook
110 $workbook = Spreadsheet::WriteExcel->new( $path );
111 warn "Creating XLS file $path\n";
114 my $date_format = $workbook->add_format(num_format => $xls_date_format);
116 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
119 return decode( $db_encoding, shift );
122 foreach my $sql_file (@sql_files) {
124 my $sheet_name = $sql_file;
125 $sheet_name =~ s/\d+_//;
126 $sheet_name =~ s/_/ /g;
127 $sheet_name =~ s/\.sql//;
130 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) );
133 print STDERR "working on $sql_file\n" if ($debug);
135 open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
140 if (/^\\c\s+(\S+)/) {
141 $dbh->disconnect if $dbh;
142 print STDERR "## connect to $1\n" if $debug;
143 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
144 } elsif (/^--(.+)/) {
152 $sql =~ s/\s\s+/ /gs;
154 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
160 # Add and define a format
161 my $fmt_comment = $workbook->addformat(); # Add a format
162 $fmt_comment->set_bold();
164 $comment =~ s/^\s+//;
165 $comment =~ s/\s+$//;
167 $worksheet->write($row, 0, _c($comment), $fmt_comment);
171 my $sth = $dbh->prepare($sql);
174 my $fmt_header = $workbook->addformat(); # Add a format
175 $fmt_header->set_italic();
177 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
178 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
182 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
184 while (my @row = $sth->fetchrow_array() ) {
185 for(my $col=0; $col<=$#row; $col++) {
186 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 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
191 $worksheet->write_date_time( $row, $col, $data, $date_format );
193 $worksheet->write($row, $col, _c( $data ) );