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);
16 my $sql_dir = path_translated || '.';
17 $sql_dir =~ s,/[^/]+$,,;
19 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
20 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
24 if ($0 =~ m/\.cgi$/i) {
26 print "Content-type: application/vnd.ms-excel\n\n";
27 $workbook = Spreadsheet::WriteExcel->new("-");
29 # Create a new Excel workbook
30 $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
34 my $connect = "DBI:Pg:dbname=new";
38 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
40 foreach my $sql_file (@sql_files) {
42 my $sheet_name = $sql_file;
43 $sheet_name =~ s/\d+_//;
44 $sheet_name =~ s/_/ /g;
45 $sheet_name =~ s/\.sql//;
48 my $worksheet = $workbook->addworksheet($sheet_name);
50 print STDERR "working on $sql_file...\n" if ($debug);
52 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
65 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
71 # Add and define a format
72 my $fmt_comment = $workbook->addformat(); # Add a format
73 $fmt_comment->set_bold();
75 $worksheet->write($row, 0, $comment, $fmt_comment);
79 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
80 $sth->execute() || die $sth->errstr();
82 my $fmt_header = $workbook->addformat(); # Add a format
83 $fmt_header->set_italic();
85 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
86 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
90 while (my @row = $sth->fetchrow_array() ) {
91 for(my $col=0; $col<=$#row; $col++) {
92 $worksheet->write($row, $col, $row[$col] );
107 sql2xls.pl - convert sql queries on file system to excel file
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)
115 Comments in sql files (lines beginning with --) will be placed
116 in outout sheet on top in bold.
118 Sheet will have name same as sql file.
120 Run script and examine b<sql_result.xls> file.
124 Dobrica Pavlinusic, dpavlin@rot13.org