/[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 2 - (show annotations)
Mon Mar 24 15:57:14 2008 UTC (16 years ago) by dpavlin
File size: 2745 byte(s)
fix encoding of characters in generated sheet

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
15 # Connect to DB
16 my $connect = "DBI:Pg:dbname=new";
17 my $user = "web";
18 my $passwd = "";
19
20 my $debug = 1;
21
22 my $sql_dir = path_translated || '.';
23 $sql_dir =~ s,/[^/]+$,,;
24
25 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
26 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
27 closedir DIR;
28
29 my $workbook;
30 if ($0 =~ m/\.cgi$/i) {
31 # use as cgi script
32 print "Content-type: application/vnd.ms-excel\n\n";
33 $workbook = Spreadsheet::WriteExcel->new("-");
34 } else {
35 # Create a new Excel workbook
36 $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
37 }
38
39 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
40
41 sub _c {
42 return decode('iso-8859-2', shift);
43 }
44
45 foreach my $sql_file (@sql_files) {
46
47 my $sheet_name = $sql_file;
48 $sheet_name =~ s/\d+_//;
49 $sheet_name =~ s/_/ /g;
50 $sheet_name =~ s/\.sql//;
51
52 # Add a worksheet
53 my $worksheet = $workbook->addworksheet($sheet_name);
54
55 print STDERR "working on $sql_file...\n" if ($debug);
56
57 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
58 my $comment;
59 my $sql = "";
60 while(<SQL>) {
61 chomp;
62 if (/^--(.+)/) {
63 $comment.=$1;
64 } else {
65 $sql.=$_;
66 }
67 }
68 close(SQL);
69
70 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
71
72 my $row = 0;
73
74 if ($comment) {
75
76 # Add and define a format
77 my $fmt_comment = $workbook->addformat(); # Add a format
78 $fmt_comment->set_bold();
79
80 $worksheet->write($row, 0, _c($comment), $fmt_comment);
81 $row+=2;
82 }
83
84 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
85 $sth->execute() || die $sth->errstr();
86
87 my $fmt_header = $workbook->addformat(); # Add a format
88 $fmt_header->set_italic();
89
90 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
91 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
92 }
93 $row++;
94
95 while (my @row = $sth->fetchrow_array() ) {
96 for(my $col=0; $col<=$#row; $col++) {
97 $worksheet->write($row, $col, _c( $row[$col] ) );
98 }
99 $row++;
100 }
101
102 }
103
104 $dbh->disconnect;
105
106 1;
107
108 __END__
109
110 =head1 NAME
111
112 sql2xls.pl - convert sql queries on file system to excel file
113
114 =head1 USAGE
115
116 Edit top of script and edit @sql_files array which describes
117 files which are going to be loaded and executed producing
118 Excel sheets (one sheet per file)
119
120 Comments in sql files (lines beginning with --) will be placed
121 in outout sheet on top in bold.
122
123 Sheet will have name same as sql file.
124
125 Run script and examine b<sql_result.xls> file.
126
127 =head1 AUTHOR
128
129 Dobrica Pavlinusic, dpavlin@rot13.org
130

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26