/[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 11 - (show annotations)
Mon Nov 3 19:30:12 2008 UTC (15 years, 4 months ago) by dpavlin
Original Path: sql2xls.cgi
File size: 4019 byte(s)
implement 31 char limit for sheet name with 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 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 warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
89 my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
90
91 print STDERR "working on $sql_file\n" if ($debug);
92
93 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
94 my $comment = '';
95 my $sql = "";
96 while(<SQL>) {
97 chomp;
98 if (/^\\c\s+(\S+)/) {
99 $dbh->disconnect if $dbh;
100 print STDERR "## connect to $1\n" if $debug;
101 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
102 } elsif (/^--(.+)/) {
103 $comment.=$1;
104 } else {
105 $sql.= ' ' . $_;
106 }
107 }
108 close(SQL);
109
110 $sql =~ s/\s\s+/ /gs;
111
112 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
113
114 my $row = 0;
115
116 if ($comment) {
117
118 # Add and define a format
119 my $fmt_comment = $workbook->addformat(); # Add a format
120 $fmt_comment->set_bold();
121
122 $comment =~ s/^\s+//;
123 $comment =~ s/\s+$//;
124
125 $worksheet->write($row, 0, _c($comment), $fmt_comment);
126 $row+=2;
127 }
128
129 my $sth = $dbh->prepare($sql);
130 $sth->execute();
131
132 my $fmt_header = $workbook->addformat(); # Add a format
133 $fmt_header->set_italic();
134
135 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
136 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
137 }
138 $row++;
139
140 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
141
142 while (my @row = $sth->fetchrow_array() ) {
143 for(my $col=0; $col<=$#row; $col++) {
144 my $data = $row[$col];
145 if ( $types[$col] =~ m/^date/i ) {
146 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
147 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
148 warn "## $data\n";
149 $worksheet->write_date_time( $row, $col, $data, $date_format );
150 } else {
151 $worksheet->write($row, $col, _c( $data ) );
152 }
153 }
154 $row++;
155 }
156
157 }
158
159 $dbh->disconnect;
160
161 1;
162
163 __END__
164

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26