3 # convert sql queries on file system to excel file (one query per
6 # Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10
9 use Spreadsheet::WriteExcel;
11 use CGI::Carp qw(fatalsToBrowser);
12 use CGI qw(path_translated);
13 use Encode qw/decode/;
16 my $connect = "DBI:Pg:dbname=new";
22 my $sql_dir = path_translated || '.';
23 $sql_dir =~ s,/[^/]+$,,;
25 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
26 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
30 if ($0 =~ m/\.cgi$/i) {
32 print "Content-type: application/vnd.ms-excel\n\n";
33 $workbook = Spreadsheet::WriteExcel->new("-");
35 # Create a new Excel workbook
36 $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
39 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
42 return decode('iso-8859-2', shift);
45 foreach my $sql_file (@sql_files) {
47 my $sheet_name = $sql_file;
48 $sheet_name =~ s/\d+_//;
49 $sheet_name =~ s/_/ /g;
50 $sheet_name =~ s/\.sql//;
53 my $worksheet = $workbook->addworksheet($sheet_name);
55 print STDERR "working on $sql_file...\n" if ($debug);
57 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
70 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
76 # Add and define a format
77 my $fmt_comment = $workbook->addformat(); # Add a format
78 $fmt_comment->set_bold();
80 $worksheet->write($row, 0, _c($comment), $fmt_comment);
84 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
85 $sth->execute() || die $sth->errstr();
87 my $fmt_header = $workbook->addformat(); # Add a format
88 $fmt_header->set_italic();
90 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
91 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
95 while (my @row = $sth->fetchrow_array() ) {
96 for(my $col=0; $col<=$#row; $col++) {
97 $worksheet->write($row, $col, _c( $row[$col] ) );
112 sql2xls.pl - convert sql queries on file system to excel file
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)
120 Comments in sql files (lines beginning with --) will be placed
121 in outout sheet on top in bold.
123 Sheet will have name same as sql file.
125 Run script and examine b<sql_result.xls> file.
129 Dobrica Pavlinusic, dpavlin@rot13.org