--- sql2xls.cgi	2008/11/06 10:49:02	23
+++ sql2xlsx.cgi	2010/12/28 13:56:16	27
@@ -64,7 +64,7 @@
 
 =cut
 
-use Spreadsheet::WriteExcel;
+use Excel::Writer::XLSX;
 use DBI;
 use CGI::Carp qw(fatalsToBrowser);
 use Encode qw/decode/;
@@ -74,12 +74,12 @@
 our $database = 'template1';
 our $user     = 'dpavlin';
 our $passwd   = '';
-our $path     = 'sql_reports.xls';
+our $path     = 'sql_reports.xlsx';
 
 our $db_encoding     = 'iso-8859-2';
 our $xls_date_format = 'dd.mm.yyyy';
 
-our $debug = 1;
+our $debug = $ENV{DEBUG} || 0;
 
 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
 $sql_dir =~ s,/[^/]+$,,;
@@ -99,7 +99,7 @@
 
 require_config;
 
-warn "# reading SQL queries from $sql_dir\n" if $debug;
+warn "SQL queries from $sql_dir\n";
 
 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
 my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
@@ -109,10 +109,11 @@
 if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
 	# use as cgi script
 	print "Content-type: application/vnd.ms-excel\n\n";
-	$workbook = Spreadsheet::WriteExcel->new("-");
+	$workbook = Excel::Writer::XLSX->new("-");
 } else {
 	# Create a new Excel workbook
-	$workbook = Spreadsheet::WriteExcel->new( $path );
+	$path =~ s{\.xls$}{\.xlsx};
+	$workbook = Excel::Writer::XLSX->new( $path );
 	warn "Creating XLS file $path\n";
 }
 
@@ -151,7 +152,7 @@
 
 	# Add a worksheet
 	warn "# clipping sheet name '$sheet_name' to 31 char limit\n" if length $sheet_name > 31;
-	my $worksheet = $workbook->addworksheet( substr($sheet_name,0,31) );
+	my $worksheet = $workbook->add_worksheet( substr($sheet_name,0,31) );
 
 	print STDERR "working on $sql_file\n" if ($debug);
 
@@ -180,7 +181,7 @@
 	if ($comment) {
 
 		#  Add and define a format
-		my $fmt_comment = $workbook->addformat();    # Add a format
+		my $fmt_comment = $workbook->add_format();    # Add a format
 		$fmt_comment->set_bold();
 
 		$comment =~ s/^\s+//;
@@ -190,17 +191,17 @@
 		$row+=2;
 	}
 
-	my $fmt_header = $workbook->addformat();    # Add a format
+	my $fmt_header = $workbook->add_format();    # Add a format
 	$fmt_header->set_italic();
 
 	foreach my $sql ( split(/;/, $full_sql ) ) {
 
-		warn "SQL: $sql\n" if $debug;
+		warn "SQL: $sql\n";
 
 		my $sth = $dbh->prepare($sql);
 		$sth->execute();
 
-		next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
+		next unless $sth->{NAME} && $sth->rows > 0; # $sth->rows alone doesn't work for insert into with MySQL
 
 		my @types = eval {
 			map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
@@ -231,6 +232,7 @@
 		}
 
 		$row++; # separete queries by one row
+		warn "# row $row\n";
 	}
 }