--- sql2xls.cgi	2008/03/24 15:57:14	2
+++ sql2xls.cgi	2008/03/24 23:02:15	4
@@ -11,12 +11,16 @@
 use CGI::Carp qw(fatalsToBrowser);
 use CGI qw(path_translated);
 use Encode qw/decode/;
+use Data::Dump qw/dump/;
 
 # Connect to DB
 my $connect = "DBI:Pg:dbname=new";
 my $user = "web";
 my $passwd = "";
 
+my $db_encoding = 'iso-8859-2';
+my $xls_date_format = 'dd.mm.yyyy';
+
 my $debug = 1;
 
 my $sql_dir = path_translated || '.';
@@ -36,10 +40,12 @@
 	$workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
 }
 
+my $date_format = $workbook->add_format(num_format => $xls_date_format);
+
 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
 
 sub _c {
-	return decode('iso-8859-2', shift);
+	return decode( $db_encoding, shift );
 }
 
 foreach my $sql_file (@sql_files) {
@@ -59,14 +65,19 @@
 	my $sql = "";
 	while(<SQL>) {
 		chomp;
-		if (/^--(.+)/) {
+		if (/^\\c\s+(\S+)/) {
+			warn "## connect to $1\n" if $debug;
+			$dbh = DBI->connect('DBI:Pg:dbname=' . $1,$user,$passwd) || die $DBI::errstr;
+		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
-			$sql.=$_;
+			$sql.= ' ' . $_;
 		}
 	}
 	close(SQL);
 
+	$sql =~ s/\s\s+/ /gs;
+
 	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
 
 	my $row = 0;
@@ -92,9 +103,19 @@
 	}
 	$row++;
 
+	my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
+
 	while (my @row = $sth->fetchrow_array() ) {
 		for(my $col=0; $col<=$#row; $col++) {
-			$worksheet->write($row, $col, _c( $row[$col] ) );
+			my $data = $row[$col];
+			if ( $types[$col] =~ m/^date/i ) {
+				$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
+				$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
+				warn "## $data\n";
+				$worksheet->write_date_time( $row, $col, $data, $date_format );
+			} else {
+				$worksheet->write($row, $col, _c( $data ) );
+			}
 		}
 		$row++;
 	}