/[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

Annotation of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (hide annotations)
Mon Mar 24 14:05:54 2008 UTC (16 years ago) by dpavlin
Original Path: sql2xls.cgi
File size: 2663 byte(s)
begin work on web reports which return SQL queries
as XLS table (readable in Gnumeric also :-)

1 dpavlin 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