/[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 7 - (show annotations)
Mon Nov 3 18:50:02 2008 UTC (15 years, 4 months ago) by dpavlin
Original Path: sql2xls.cgi
File size: 3743 byte(s)
fix warning
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 $worksheet->write($row, 0, _c($comment), $fmt_comment);
118 $row+=2;
119 }
120
121 my $sth = $dbh->prepare($sql);
122 $sth->execute();
123
124 my $fmt_header = $workbook->addformat(); # Add a format
125 $fmt_header->set_italic();
126
127 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
128 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
129 }
130 $row++;
131
132 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
133
134 while (my @row = $sth->fetchrow_array() ) {
135 for(my $col=0; $col<=$#row; $col++) {
136 my $data = $row[$col];
137 if ( $types[$col] =~ m/^date/i ) {
138 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
139 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
140 warn "## $data\n";
141 $worksheet->write_date_time( $row, $col, $data, $date_format );
142 } else {
143 $worksheet->write($row, $col, _c( $data ) );
144 }
145 }
146 $row++;
147 }
148
149 }
150
151 $dbh->disconnect;
152
153 1;
154
155 __END__
156

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26