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': $!";
69 warn "## connect to $1\n" if $debug;
70 $dbh = DBI->connect('DBI:Pg:dbname=' . $1,$user,$passwd) || die $DBI::errstr;
81 print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
87 # Add and define a format
88 my $fmt_comment = $workbook->addformat(); # Add a format
89 $fmt_comment->set_bold();
91 $worksheet->write($row, 0, _c($comment), $fmt_comment);
95 my $sth = $dbh->prepare($sql) || die $dbh->errstr();
96 $sth->execute() || die $sth->errstr();
98 my $fmt_header = $workbook->addformat(); # Add a format
99 $fmt_header->set_italic();
101 for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
102 $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
106 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
108 while (my @row = $sth->fetchrow_array() ) {
109 for(my $col=0; $col<=$#row; $col++) {
110 my $data = $row[$col];
111 if ( $types[$col] =~ m/^date/i ) {
112 $data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
113 $data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
115 $worksheet->write_date_time( $row, $col, $data, $date_format );
117 $worksheet->write($row, $col, _c( $data ) );
133 sql2xls.pl - convert sql queries on file system to excel file
137 Edit top of script and edit @sql_files array which describes
138 files which are going to be loaded and executed producing
139 Excel sheets (one sheet per file)
141 Comments in sql files (lines beginning with --) will be placed
142 in outout sheet on top in bold.
144 Sheet will have name same as sql file.
146 Run script and examine b<sql_result.xls> file.
150 Dobrica Pavlinusic, dpavlin@rot13.org