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

Annotation of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 6 - (hide annotations)
Mon Nov 3 18:44:42 2008 UTC (15 years, 4 months ago) by dpavlin
File size: 3738 byte(s)
disconnect from database before next connect, support unknown types
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     my $dsn = 'DBI:Pg:dbname=';
40     my $database = 'template1';
41     my $user = 'dpavlin';
42     my $passwd = '';
43     my $path = 'sql_reports.xls';
44 dpavlin 2
45 dpavlin 5 my $db_encoding = 'iso-8859-2';
46 dpavlin 3 my $xls_date_format = 'dd.mm.yyyy';
47    
48 dpavlin 1 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 dpavlin 5 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
59 dpavlin 1 # 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 dpavlin 5 $workbook = Spreadsheet::WriteExcel->new( $path );
65     warn "Creating XLS file $path\n";
66 dpavlin 1 }
67    
68 dpavlin 3 my $date_format = $workbook->add_format(num_format => $xls_date_format);
69    
70 dpavlin 5 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
71 dpavlin 1
72 dpavlin 2 sub _c {
73 dpavlin 3 return decode( $db_encoding, shift );
74 dpavlin 2 }
75    
76 dpavlin 1 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 dpavlin 6 print STDERR "working on $sql_file\n" if ($debug);
87 dpavlin 1
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 dpavlin 4 if (/^\\c\s+(\S+)/) {
94 dpavlin 6 $dbh->disconnect if $dbh;
95     print STDERR "## connect to $1\n" if $debug;
96 dpavlin 5 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
97 dpavlin 4 } elsif (/^--(.+)/) {
98 dpavlin 1 $comment.=$1;
99     } else {
100 dpavlin 4 $sql.= ' ' . $_;
101 dpavlin 1 }
102     }
103     close(SQL);
104    
105 dpavlin 4 $sql =~ s/\s\s+/ /gs;
106    
107 dpavlin 1 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 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
118 dpavlin 1 $row+=2;
119     }
120    
121 dpavlin 5 my $sth = $dbh->prepare($sql);
122     $sth->execute();
123 dpavlin 1
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 dpavlin 6 my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
133 dpavlin 3
134 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
135     for(my $col=0; $col<=$#row; $col++) {
136 dpavlin 3 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 dpavlin 4 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
140     warn "## $data\n";
141 dpavlin 3 $worksheet->write_date_time( $row, $col, $data, $date_format );
142     } else {
143     $worksheet->write($row, $col, _c( $data ) );
144     }
145 dpavlin 1 }
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