viewvc.cgi/SQL2XLS/sql2xls.cgi

#!/usr/bin/perl
use warnings;
use strict;

=head1 NAME

sql2xls.pl - convert sql queries on file system to Excel file

=head1 USAGE

Each file in current directory which ends in C<< *.sql >> will
be converted to Excel sheet. If you want to have specific order, you can
prefix filenames with numbers which will be striped when creating sheet
names.

Comments in sql files (lines beginning with --) will be placed
in first line in bold.

To specify database on which SQL query is executed
C<< \c database >> syntax is supported.

You can also run script from command line, and it will produce
C<< sql_reports.xls >> file.

=head1 AUTHOR

Dobrica Pavlinusic, dpavlin@rot13.org

=cut

use Spreadsheet::WriteExcel;
use DBI;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(path_translated);
use Encode qw/decode/;
use Data::Dump qw/dump/;

# edit following to set defaults
my $dsn      = 'DBI:Pg:dbname=';
my $database = 'template1';
my $user     = 'dpavlin';
my $passwd   = '';
my $path     = 'sql_reports.xls';

my $db_encoding     = 'iso-8859-2';
my $xls_date_format = 'dd.mm.yyyy';

my $debug = 1;

my $sql_dir = path_translated || '.';
$sql_dir =~ s,/[^/]+$,,;

opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
closedir DIR;

my $workbook;
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("-");
} else {
        # Create a new Excel workbook
        $workbook = Spreadsheet::WriteExcel->new( $path );
        warn "Creating XLS file $path\n";
}

my $date_format = $workbook->add_format(num_format => $xls_date_format);

my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;

sub _c {
        return decode( $db_encoding, shift );
}

foreach my $sql_file (@sql_files) {

        my $sheet_name = $sql_file;
        $sheet_name =~ s/\d+_//;
        $sheet_name =~ s/_/ /g;
        $sheet_name =~ s/\.sql//;

        # Add a worksheet
        my $worksheet = $workbook->addworksheet($sheet_name);

        print STDERR "working on $sql_file...\n" if ($debug);

        open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
        my $comment;
        my $sql = "";
        while(<SQL>) {
                chomp;
                if (/^\\c\s+(\S+)/) {
                        warn "## connect to $1\n" if $debug;
                        $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
                } elsif (/^--(.+)/) {
                        $comment.=$1;
                } else {
                        $sql.= ' ' . $_;
                }
        }
        close(SQL);

        $sql =~ s/\s\s+/ /gs;

        print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);

        my $row = 0;

        if ($comment) {

                #  Add and define a format
                my $fmt_comment = $workbook->addformat();    # Add a format
                $fmt_comment->set_bold();

                $worksheet->write($row, 0, _c($comment), $fmt_comment);
                $row+=2;
        }

        my $sth = $dbh->prepare($sql);
        $sth->execute();

        my $fmt_header = $workbook->addformat();    # Add a format
        $fmt_header->set_italic();

        for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
                $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
        }
        $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++) {
                        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++;
        }

}

$dbh->disconnect;

1;

__END__

1	dpavlin	5	#!/usr/bin/perl
2			use warnings;
3			use strict;
4	dpavlin	1
5	dpavlin	5	=head1 NAME
6
7			sql2xls.pl - convert sql queries on file system to Excel file
8
9			=head1 USAGE
10
11			Each file in current directory which ends in C<< *.sql >> will
12			be converted to Excel sheet. If you want to have specific order, you can
13			prefix filenames with numbers which will be striped when creating sheet
14			names.
15
16			Comments in sql files (lines beginning with --) will be placed
17			in first line in bold.
18
19			To specify database on which SQL query is executed
20			C<< \c database >> syntax is supported.
21
22			You can also run script from command line, and it will produce
23			C<< sql_reports.xls >> file.
24
25			=head1 AUTHOR
26
27			Dobrica Pavlinusic, dpavlin@rot13.org
28
29			=cut
30
31	dpavlin	1	use Spreadsheet::WriteExcel;
32			use DBI;
33			use CGI::Carp qw(fatalsToBrowser);
34			use CGI qw(path_translated);
35	dpavlin	2	use Encode qw/decode/;
36	dpavlin	3	use Data::Dump qw/dump/;
37	dpavlin	1
38	dpavlin	5	# edit following to set defaults
39			my $dsn = 'DBI:Pg:dbname=';
40			my $database = 'template1';
41			my $user = 'dpavlin';
42			my $passwd = '';
43			my $path = 'sql_reports.xls';
44	dpavlin	2
45	dpavlin	5	my $db_encoding = 'iso-8859-2';
46	dpavlin	3	my $xls_date_format = 'dd.mm.yyyy';
47
48	dpavlin	1	my $debug = 1;
49
50			my $sql_dir = path_translated \|\| '.';
51			$sql_dir =~ s,/[^/]+$,,;
52
53			opendir(DIR, $sql_dir) \|\| die "can't opendir $sql_dir: $!";
54			my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
55			closedir DIR;
56
57			my $workbook;
58	dpavlin	5	if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
59	dpavlin	1	# use as cgi script
60			print "Content-type: application/vnd.ms-excel\n\n";
61			$workbook = Spreadsheet::WriteExcel->new("-");
62			} else {
63			# Create a new Excel workbook
64	dpavlin	5	$workbook = Spreadsheet::WriteExcel->new( $path );
65			warn "Creating XLS file $path\n";
66	dpavlin	1	}
67
68	dpavlin	3	my $date_format = $workbook->add_format(num_format => $xls_date_format);
69
70	dpavlin	5	my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) \|\| die $DBI::errstr;
71	dpavlin	1
72	dpavlin	2	sub _c {
73	dpavlin	3	return decode( $db_encoding, shift );
74	dpavlin	2	}
75
76	dpavlin	1	foreach my $sql_file (@sql_files) {
77
78			my $sheet_name = $sql_file;
79			$sheet_name =~ s/\d+_//;
80			$sheet_name =~ s/_/ /g;
81			$sheet_name =~ s/\.sql//;
82
83			# Add a worksheet
84			my $worksheet = $workbook->addworksheet($sheet_name);
85
86			print STDERR "working on $sql_file...\n" if ($debug);
87
88			open(SQL,$sql_file) \|\| die "can't open sql file '$sql_file': $!";
89			my $comment;
90			my $sql = "";
91			while(<SQL>) {
92			chomp;
93	dpavlin	4	if (/^\\c\s+(\S+)/) {
94			warn "## connect to $1\n" if $debug;
95	dpavlin	5	$dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) \|\| die $DBI::errstr;
96	dpavlin	4	} elsif (/^--(.+)/) {
97	dpavlin	1	$comment.=$1;
98			} else {
99	dpavlin	4	$sql.= ' ' . $_;
100	dpavlin	1	}
101			}
102			close(SQL);
103
104	dpavlin	4	$sql =~ s/\s\s+/ /gs;
105
106	dpavlin	1	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
107
108			my $row = 0;
109
110			if ($comment) {
111
112			# Add and define a format
113			my $fmt_comment = $workbook->addformat(); # Add a format
114			$fmt_comment->set_bold();
115
116	dpavlin	2	$worksheet->write($row, 0, _c($comment), $fmt_comment);
117	dpavlin	1	$row+=2;
118			}
119
120	dpavlin	5	my $sth = $dbh->prepare($sql);
121			$sth->execute();
122	dpavlin	1
123			my $fmt_header = $workbook->addformat(); # Add a format
124			$fmt_header->set_italic();
125
126			for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
127			$worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
128			}
129			$row++;
130
131	dpavlin	3	my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };
132
133	dpavlin	1	while (my @row = $sth->fetchrow_array() ) {
134			for(my $col=0; $col<=$#row; $col++) {
135	dpavlin	3	my $data = $row[$col];
136			if ( $types[$col] =~ m/^date/i ) {
137			$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
138	dpavlin	4	$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
139			warn "## $data\n";
140	dpavlin	3	$worksheet->write_date_time( $row, $col, $data, $date_format );
141			} else {
142			$worksheet->write($row, $col, _c( $data ) );
143			}
144	dpavlin	1	}
145			$row++;
146			}
147
148			}
149
150			$dbh->disconnect;
151
152			1;
153
154			__END__
155