/[SQL2XLS]/sql2xls.cgi
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /sql2xls.cgi

Parent Directory Parent Directory | Revision Log Revision Log


Revision 3 - (hide annotations)
Mon Mar 24 16:35:29 2008 UTC (16 years ago) by dpavlin
File size: 3195 byte(s)
convert SQL date into excel dates (with format dd.mm.yyyy)

1 dpavlin 1 #!/usr/bin/perl -w
2     #
3     # convert sql queries on file system to excel file (one query per
4     # sheet)
5     #
6     # Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10
7    
8     use strict;
9     use Spreadsheet::WriteExcel;
10     use DBI;
11     use CGI::Carp qw(fatalsToBrowser);
12     use CGI qw(path_translated);
13 dpavlin 2 use Encode qw/decode/;
14 dpavlin 3 use Data::Dump qw/dump/;
15 dpavlin 1
16 dpavlin 2 # Connect to DB
17     my $connect = "DBI:Pg:dbname=new";
18     my $user = "web";
19     my $passwd = "";
20    
21 dpavlin 3 my $db_encoding = 'iso-8859-2';
22     my $xls_date_format = 'dd.mm.yyyy';
23    
24 dpavlin 1 my $debug = 1;
25    
26     my $sql_dir = path_translated || '.';
27     $sql_dir =~ s,/[^/]+$,,;
28    
29     opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
30     my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
31     closedir DIR;
32    
33     my $workbook;
34     if ($0 =~ m/\.cgi$/i) {
35     # use as cgi script
36     print "Content-type: application/vnd.ms-excel\n\n";
37     $workbook = Spreadsheet::WriteExcel->new("-");
38     } else {
39     # Create a new Excel workbook
40     $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
41     }
42    
43 dpavlin 3 my $date_format = $workbook->add_format(num_format => $xls_date_format);
44    
45 dpavlin 1 my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
46    
47 dpavlin 2 sub _c {
48 dpavlin 3 return decode( $db_encoding, shift );
49 dpavlin 2 }
50    
51 dpavlin 1 foreach my $sql_file (@sql_files) {
52    
53     my $sheet_name = $sql_file;
54     $sheet_name =~ s/\d+_//;
55     $sheet_name =~ s/_/ /g;
56     $sheet_name =~ s/\.sql//;
57    
58     # Add a worksheet
59     my $worksheet = $workbook->addworksheet($sheet_name);
60    
61     print STDERR "working on $sql_file...\n" if ($debug);
62    
63     open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
64     my $comment;
65     my $sql = "";
66     while(<SQL>) {
67     chomp;
68     if (/^--(.+)/) {
69     $comment.=$1;
70     } else {
71     $sql.=$_;
72     }
73     }
74     close(SQL);
75    
76     print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
77    
78     my $row = 0;
79    
80     if ($comment) {
81    
82     # Add and define a format
83     my $fmt_comment = $workbook->addformat(); # Add a format
84     $fmt_comment->set_bold();
85    
86 dpavlin 2 $worksheet->write($row, 0, _c($comment), $fmt_comment);
87 dpavlin 1 $row+=2;
88     }
89    
90     my $sth = $dbh->prepare($sql) || die $dbh->errstr();
91     $sth->execute() || die $sth->errstr();
92    
93     my $fmt_header = $workbook->addformat(); # Add a format
94     $fmt_header->set_italic();
95    
96     for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
97     $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
98     }
99     $row++;
100    
101 dpavlin 3 my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
102    
103 dpavlin 1 while (my @row = $sth->fetchrow_array() ) {
104     for(my $col=0; $col<=$#row; $col++) {
105 dpavlin 3 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 dpavlin 1 }
113     $row++;
114     }
115    
116     }
117    
118     $dbh->disconnect;
119    
120     1;
121    
122     __END__
123    
124     =head1 NAME
125    
126     sql2xls.pl - convert sql queries on file system to excel file
127    
128     =head1 USAGE
129    
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)
133    
134     Comments in sql files (lines beginning with --) will be placed
135     in outout sheet on top in bold.
136    
137     Sheet will have name same as sql file.
138    
139     Run script and examine b<sql_result.xls> file.
140    
141     =head1 AUTHOR
142    
143     Dobrica Pavlinusic, dpavlin@rot13.org
144    

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26