/[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 4 - (show annotations)
Mon Mar 24 23:02:15 2008 UTC (16 years ago) by dpavlin
Original Path: sql2xls.cgi
File size: 3457 byte(s)
- support for \c database just like psql does
  (you can pipe sql into psql and it will work!)
- better support for multi-line SQL queries

1 #!/usr/bin/perl -w
2 #
3 # convert sql queries on file system to excel file (one query per
4 # sheet)
5 #
6 # Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10
7
8 use strict;
9 use Spreadsheet::WriteExcel;
10 use DBI;
11 use CGI::Carp qw(fatalsToBrowser);
12 use CGI qw(path_translated);
13 use Encode qw/decode/;
14 use Data::Dump qw/dump/;
15
16 # Connect to DB
17 my $connect = "DBI:Pg:dbname=new";
18 my $user = "web";
19 my $passwd = "";
20
21 my $db_encoding = 'iso-8859-2';
22 my $xls_date_format = 'dd.mm.yyyy';
23
24 my $debug = 1;
25
26 my $sql_dir = path_translated || '.';
27 $sql_dir =~ s,/[^/]+$,,;
28
29 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
30 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
31 closedir DIR;
32
33 my $workbook;
34 if ($0 =~ m/\.cgi$/i) {
35 # use as cgi script
36 print "Content-type: application/vnd.ms-excel\n\n";
37 $workbook = Spreadsheet::WriteExcel->new("-");
38 } else {
39 # Create a new Excel workbook
40 $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
41 }
42
43 my $date_format = $workbook->add_format(num_format => $xls_date_format);
44
45 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
46
47 sub _c {
48 return decode( $db_encoding, shift );
49 }
50
51 foreach my $sql_file (@sql_files) {
52
53 my $sheet_name = $sql_file;
54 $sheet_name =~ s/\d+_//;
55 $sheet_name =~ s/_/ /g;
56 $sheet_name =~ s/\.sql//;
57
58 # Add a worksheet
59 my $worksheet = $workbook->addworksheet($sheet_name);
60
61 print STDERR "working on $sql_file...\n" if ($debug);
62
63 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
64 my $comment;
65 my $sql = "";
66 while(<SQL>) {
67 chomp;
68 if (/^\\c\s+(\S+)/) {
69 warn "## connect to $1\n" if $debug;
70 $dbh = DBI->connect('DBI:Pg:dbname=' . $1,$user,$passwd) || die $DBI::errstr;
71 } elsif (/^--(.+)/) {
72 $comment.=$1;
73 } else {
74 $sql.= ' ' . $_;
75 }
76 }
77 close(SQL);
78
79 $sql =~ s/\s\s+/ /gs;
80
81 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
82
83 my $row = 0;
84
85 if ($comment) {
86
87 # Add and define a format
88 my $fmt_comment = $workbook->addformat(); # Add a format
89 $fmt_comment->set_bold();
90
91 $worksheet->write($row, 0, _c($comment), $fmt_comment);
92 $row+=2;
93 }
94
95 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
96 $sth->execute() || die $sth->errstr();
97
98 my $fmt_header = $workbook->addformat(); # Add a format
99 $fmt_header->set_italic();
100
101 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
102 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
103 }
104 $row++;
105
106 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
107
108 while (my @row = $sth->fetchrow_array() ) {
109 for(my $col=0; $col<=$#row; $col++) {
110 my $data = $row[$col];
111 if ( $types[$col] =~ m/^date/i ) {
112 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
113 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
114 warn "## $data\n";
115 $worksheet->write_date_time( $row, $col, $data, $date_format );
116 } else {
117 $worksheet->write($row, $col, _c( $data ) );
118 }
119 }
120 $row++;
121 }
122
123 }
124
125 $dbh->disconnect;
126
127 1;
128
129 __END__
130
131 =head1 NAME
132
133 sql2xls.pl - convert sql queries on file system to excel file
134
135 =head1 USAGE
136
137 Edit top of script and edit @sql_files array which describes
138 files which are going to be loaded and executed producing
139 Excel sheets (one sheet per file)
140
141 Comments in sql files (lines beginning with --) will be placed
142 in outout sheet on top in bold.
143
144 Sheet will have name same as sql file.
145
146 Run script and examine b<sql_result.xls> file.
147
148 =head1 AUTHOR
149
150 Dobrica Pavlinusic, dpavlin@rot13.org
151

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26