/[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 1 - (show annotations)
Mon Mar 24 14:05:54 2008 UTC (16 years ago) by dpavlin
File size: 2663 byte(s)
begin work on web reports which return SQL queries
as XLS table (readable in Gnumeric also :-)

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

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26