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

Contents of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 10 - (show annotations)
Mon Nov 3 19:19:39 2008 UTC (15 years, 4 months ago) by dpavlin
Original Path: sql2xls.cgi
File size: 3913 byte(s)
read config.pl file if it exists

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

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26