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

Annotation of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 11 - (hide 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 dpavlin 5 #!/usr/bin/perl
2     use warnings;
3     use strict;
4 dpavlin 1
5 dpavlin 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 dpavlin 1 use Spreadsheet::WriteExcel;
32     use DBI;
33     use CGI::Carp qw(fatalsToBrowser);
34     use CGI qw(path_translated);
35 dpavlin 2 use Encode qw/decode/;
36 dpavlin 3 use Data::Dump qw/dump/;
37 dpavlin 1
38 dpavlin 5 # edit following to set defaults
39 dpavlin 10 our $dsn = 'DBI:Pg:dbname=';
40     our $database = 'template1';
41     our $user = 'dpavlin';
42     our $passwd = '';
43     our $path = 'sql_reports.xls';
44 dpavlin 2
45 dpavlin 10 our $db_encoding = 'iso-8859-2';
46     our $xls_date_format = 'dd.mm.yyyy';
47 dpavlin 3
48 dpavlin 10 our $debug = 1;
49 dpavlin 1
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 dpavlin 10 my $config_path = "$sql_dir/config.pl";
58     warn "# using $config_path\n";
59     require $config_path if -e $config_path;
60    
61 dpavlin 1 my $workbook;
62 dpavlin 5 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
63 dpavlin 1 # 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 dpavlin 5 $workbook = Spreadsheet::WriteExcel->new( $path );
69     warn "Creating XLS file $path\n";
70 dpavlin 1 }
71    
72 dpavlin 3 my $date_format = $workbook->add_format(num_format => $xls_date_format);
73    
74 dpavlin 5 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
75 dpavlin 1
76 dpavlin 2 sub _c {
77 dpavlin 3 return decode( $db_encoding, shift );
78 dpavlin 2 }
79    
80 dpavlin 1 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 dpavlin 11 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 dpavlin 1
91 dpavlin 6 print STDERR "working on $sql_file\n" if ($debug);
92 dpavlin 1
93     open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
94 dpavlin 7 my $comment = '';
95 dpavlin 1 my $sql = "";
96     while(<SQL>) {
97     chomp;
98 dpavlin 4 if (/^\\c\s+(\S+)/) {
99 dpavlin 6 $dbh->disconnect if $dbh;
100     print STDERR "## connect to $1\n" if $debug;
101 dpavlin 5 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
102 dpavlin 4 } elsif (/^--(.+)/) {
103 dpavlin 1 $comment.=$1;
104     } else {
105 dpavlin 4 $sql.= ' ' . $_;
106 dpavlin 1 }
107     }
108     close(SQL);
109    
110 dpavlin 4 $sql =~ s/\s\s+/ /gs;
111    
112 dpavlin 1 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 dpavlin 8 $comment =~ s/^\s+//;
123     $comment =~ s/\s+$//;
124    
125 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
126 dpavlin 1 $row+=2;
127     }
128    
129 dpavlin 5 my $sth = $dbh->prepare($sql);
130     $sth->execute();
131 dpavlin 1
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 dpavlin 6 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
141 dpavlin 3
142 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
143     for(my $col=0; $col<=$#row; $col++) {
144 dpavlin 3 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 dpavlin 4 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
148     warn "## $data\n";
149 dpavlin 3 $worksheet->write_date_time( $row, $col, $data, $date_format );
150     } else {
151     $worksheet->write($row, $col, _c( $data ) );
152     }
153 dpavlin 1 }
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