/[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 5 - (show annotations)
Mon Nov 3 18:31:58 2008 UTC (15 years, 4 months ago) by dpavlin
File size: 3683 byte(s)
cleanup mode in preparation for first release

- move pod documentation to top
- check GATEWAY_INTERFACE to detect cgi, so it works as
  simple command-line xls dumper again
- disabled AutoCommit so we can't corrupt data by invoking reports
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 warn "## connect to $1\n" if $debug;
95 $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
96 } elsif (/^--(.+)/) {
97 $comment.=$1;
98 } else {
99 $sql.= ' ' . $_;
100 }
101 }
102 close(SQL);
103
104 $sql =~ s/\s\s+/ /gs;
105
106 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
107
108 my $row = 0;
109
110 if ($comment) {
111
112 # Add and define a format
113 my $fmt_comment = $workbook->addformat(); # Add a format
114 $fmt_comment->set_bold();
115
116 $worksheet->write($row, 0, _c($comment), $fmt_comment);
117 $row+=2;
118 }
119
120 my $sth = $dbh->prepare($sql);
121 $sth->execute();
122
123 my $fmt_header = $workbook->addformat(); # Add a format
124 $fmt_header->set_italic();
125
126 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
127 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
128 }
129 $row++;
130
131 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
132
133 while (my @row = $sth->fetchrow_array() ) {
134 for(my $col=0; $col<=$#row; $col++) {
135 my $data = $row[$col];
136 if ( $types[$col] =~ m/^date/i ) {
137 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
138 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
139 warn "## $data\n";
140 $worksheet->write_date_time( $row, $col, $data, $date_format );
141 } else {
142 $worksheet->write($row, $col, _c( $data ) );
143 }
144 }
145 $row++;
146 }
147
148 }
149
150 $dbh->disconnect;
151
152 1;
153
154 __END__
155

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26