/[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 3 - (show annotations)
Mon Mar 24 16:35:29 2008 UTC (16 years ago) by dpavlin
File size: 3195 byte(s)
convert SQL date into excel dates (with format dd.mm.yyyy)

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 (/^--(.+)/) {
69 $comment.=$1;
70 } else {
71 $sql.=$_;
72 }
73 }
74 close(SQL);
75
76 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
77
78 my $row = 0;
79
80 if ($comment) {
81
82 # Add and define a format
83 my $fmt_comment = $workbook->addformat(); # Add a format
84 $fmt_comment->set_bold();
85
86 $worksheet->write($row, 0, _c($comment), $fmt_comment);
87 $row+=2;
88 }
89
90 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
91 $sth->execute() || die $sth->errstr();
92
93 my $fmt_header = $workbook->addformat(); # Add a format
94 $fmt_header->set_italic();
95
96 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
97 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
98 }
99 $row++;
100
101 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
102
103 while (my @row = $sth->fetchrow_array() ) {
104 for(my $col=0; $col<=$#row; $col++) {
105 my $data = $row[$col];
106 if ( $types[$col] =~ m/^date/i ) {
107 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
108 $worksheet->write_date_time( $row, $col, $data, $date_format );
109 } else {
110 $worksheet->write($row, $col, _c( $data ) );
111 }
112 }
113 $row++;
114 }
115
116 }
117
118 $dbh->disconnect;
119
120 1;
121
122 __END__
123
124 =head1 NAME
125
126 sql2xls.pl - convert sql queries on file system to excel file
127
128 =head1 USAGE
129
130 Edit top of script and edit @sql_files array which describes
131 files which are going to be loaded and executed producing
132 Excel sheets (one sheet per file)
133
134 Comments in sql files (lines beginning with --) will be placed
135 in outout sheet on top in bold.
136
137 Sheet will have name same as sql file.
138
139 Run script and examine b<sql_result.xls> file.
140
141 =head1 AUTHOR
142
143 Dobrica Pavlinusic, dpavlin@rot13.org
144

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26