11 |
use CGI::Carp qw(fatalsToBrowser); |
use CGI::Carp qw(fatalsToBrowser); |
12 |
use CGI qw(path_translated); |
use CGI qw(path_translated); |
13 |
use Encode qw/decode/; |
use Encode qw/decode/; |
14 |
|
use Data::Dump qw/dump/; |
15 |
|
|
16 |
# Connect to DB |
# Connect to DB |
17 |
my $connect = "DBI:Pg:dbname=new"; |
my $connect = "DBI:Pg:dbname=new"; |
18 |
my $user = "web"; |
my $user = "web"; |
19 |
my $passwd = ""; |
my $passwd = ""; |
20 |
|
|
21 |
|
my $db_encoding = 'iso-8859-2'; |
22 |
|
my $xls_date_format = 'dd.mm.yyyy'; |
23 |
|
|
24 |
my $debug = 1; |
my $debug = 1; |
25 |
|
|
26 |
my $sql_dir = path_translated || '.'; |
my $sql_dir = path_translated || '.'; |
40 |
$workbook = Spreadsheet::WriteExcel->new("sql_result.xls"); |
$workbook = Spreadsheet::WriteExcel->new("sql_result.xls"); |
41 |
} |
} |
42 |
|
|
43 |
|
my $date_format = $workbook->add_format(num_format => $xls_date_format); |
44 |
|
|
45 |
my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr; |
my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr; |
46 |
|
|
47 |
sub _c { |
sub _c { |
48 |
return decode('iso-8859-2', shift); |
return decode( $db_encoding, shift ); |
49 |
} |
} |
50 |
|
|
51 |
foreach my $sql_file (@sql_files) { |
foreach my $sql_file (@sql_files) { |
98 |
} |
} |
99 |
$row++; |
$row++; |
100 |
|
|
101 |
|
my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }; |
102 |
|
|
103 |
while (my @row = $sth->fetchrow_array() ) { |
while (my @row = $sth->fetchrow_array() ) { |
104 |
for(my $col=0; $col<=$#row; $col++) { |
for(my $col=0; $col<=$#row; $col++) { |
105 |
$worksheet->write($row, $col, _c( $row[$col] ) ); |
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 ); |
109 |
|
} else { |
110 |
|
$worksheet->write($row, $col, _c( $data ) ); |
111 |
|
} |
112 |
} |
} |
113 |
$row++; |
$row++; |
114 |
} |
} |