/[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 8 - (show annotations)
Mon Nov 3 18:51:49 2008 UTC (15 years, 4 months ago) by dpavlin
File size: 3792 byte(s)
strip spaces around comments
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 my $dsn = 'DBI:Pg:dbname=';
40 my $database = 'template1';
41 my $user = 'dpavlin';
42 my $passwd = '';
43 my $path = 'sql_reports.xls';
44
45 my $db_encoding = 'iso-8859-2';
46 my $xls_date_format = 'dd.mm.yyyy';
47
48 my $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 $workbook;
58 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
59 # use as cgi script
60 print "Content-type: application/vnd.ms-excel\n\n";
61 $workbook = Spreadsheet::WriteExcel->new("-");
62 } else {
63 # Create a new Excel workbook
64 $workbook = Spreadsheet::WriteExcel->new( $path );
65 warn "Creating XLS file $path\n";
66 }
67
68 my $date_format = $workbook->add_format(num_format => $xls_date_format);
69
70 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
71
72 sub _c {
73 return decode( $db_encoding, shift );
74 }
75
76 foreach my $sql_file (@sql_files) {
77
78 my $sheet_name = $sql_file;
79 $sheet_name =~ s/\d+_//;
80 $sheet_name =~ s/_/ /g;
81 $sheet_name =~ s/\.sql//;
82
83 # Add a worksheet
84 my $worksheet = $workbook->addworksheet($sheet_name);
85
86 print STDERR "working on $sql_file\n" if ($debug);
87
88 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
89 my $comment = '';
90 my $sql = "";
91 while(<SQL>) {
92 chomp;
93 if (/^\\c\s+(\S+)/) {
94 $dbh->disconnect if $dbh;
95 print STDERR "## connect to $1\n" if $debug;
96 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
97 } elsif (/^--(.+)/) {
98 $comment.=$1;
99 } else {
100 $sql.= ' ' . $_;
101 }
102 }
103 close(SQL);
104
105 $sql =~ s/\s\s+/ /gs;
106
107 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
108
109 my $row = 0;
110
111 if ($comment) {
112
113 # Add and define a format
114 my $fmt_comment = $workbook->addformat(); # Add a format
115 $fmt_comment->set_bold();
116
117 $comment =~ s/^\s+//;
118 $comment =~ s/\s+$//;
119
120 $worksheet->write($row, 0, _c($comment), $fmt_comment);
121 $row+=2;
122 }
123
124 my $sth = $dbh->prepare($sql);
125 $sth->execute();
126
127 my $fmt_header = $workbook->addformat(); # Add a format
128 $fmt_header->set_italic();
129
130 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
131 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
132 }
133 $row++;
134
135 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
136
137 while (my @row = $sth->fetchrow_array() ) {
138 for(my $col=0; $col<=$#row; $col++) {
139 my $data = $row[$col];
140 if ( $types[$col] =~ m/^date/i ) {
141 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
142 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
143 warn "## $data\n";
144 $worksheet->write_date_time( $row, $col, $data, $date_format );
145 } else {
146 $worksheet->write($row, $col, _c( $data ) );
147 }
148 }
149 $row++;
150 }
151
152 }
153
154 $dbh->disconnect;
155
156 1;
157
158 __END__
159

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26