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/;
14 use Data::Dump qw/dump/;
17 my $connect = "DBI:Pg:dbname=new";
21 my $db_encoding = 'iso-8859-2';
22 my $xls_date_format = 'dd.mm.yyyy';
26 my $sql_dir = path_translated || '.';
27 $sql_dir =~ s,/[^/]+$,,;
29 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
30 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
34 if ($0 =~ m/\.cgi$/i) {
36 print "Content-type: application/vnd.ms-excel\n\n";
37 $workbook = Spreadsheet::WriteExcel->new("-");
39 # Create a new Excel workbook
40 $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
43 my $date_format = $workbook->add_format(num_format => $xls_date_format);
45 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
48 return decode( $db_encoding, shift );
51 foreach my $sql_file (@sql_files) {
53 my $sheet_name = $sql_file;
54 $sheet_name =~ s/\d+_//;
55 $sheet_name =~ s/_/ /g;
56 $sheet_name =~ s/\.sql//;
59 my $worksheet = $workbook->addworksheet($sheet_name);
61 print STDERR "working on $sql_file...\n" if ($debug);
63 open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
76 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
82 # Add and define a format
83 my $fmt_comment = $workbook->addformat(); # Add a format
84 $fmt_comment->set_bold();
86 $worksheet->write($row, 0, _c($comment), $fmt_comment);
90 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
91 $sth->execute() || die $sth->errstr();
93 my $fmt_header = $workbook->addformat(); # Add a format
94 $fmt_header->set_italic();
96 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
97 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
101 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
103 while (my @row = $sth->fetchrow_array() ) {
104 for(my $col=0; $col<=$#row; $col++) {
105 my $data = $row[$col];
106 if ( $types[$col] =~ m/^date/i ) {
107 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
108 $worksheet->write_date_time( $row, $col, $data, $date_format );
110 $worksheet->write($row, $col, _c( $data ) );
126 sql2xls.pl - convert sql queries on file system to excel file
130 Edit top of script and edit @sql_files array which describes
131 files which are going to be loaded and executed producing
132 Excel sheets (one sheet per file)
134 Comments in sql files (lines beginning with --) will be placed
135 in outout sheet on top in bold.
137 Sheet will have name same as sql file.
139 Run script and examine b<sql_result.xls> file.
143 Dobrica Pavlinusic, dpavlin@rot13.org