viewvc.cgi/SQL2XLS/sql2xlsx.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
our $dsn      = 'DBI:Pg:dbname=';
our $database = 'template1';
our $user     = 'dpavlin';
our $passwd   = '';
our $path     = 'sql_reports.xls';

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

our $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 $config_path = "$sql_dir/config.pl";
warn "# using $config_path\n";
require $config_path if -e $config_path;

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+)/) {
                        $dbh->disconnect if $dbh;
                        print STDERR "## 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();

                $comment =~ s/^\s+//;
                $comment =~ s/\s+$//;

                $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 { $dbh->type_info($_) ? $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	dpavlin	10	our $dsn = 'DBI:Pg:dbname=';
40			our $database = 'template1';
41			our $user = 'dpavlin';
42			our $passwd = '';
43			our $path = 'sql_reports.xls';
44	dpavlin	2
45	dpavlin	10	our $db_encoding = 'iso-8859-2';
46			our $xls_date_format = 'dd.mm.yyyy';
47	dpavlin	3
48	dpavlin	10	our $debug = 1;
49	dpavlin	1
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	dpavlin	10	my $config_path = "$sql_dir/config.pl";
58			warn "# using $config_path\n";
59			require $config_path if -e $config_path;
60
61	dpavlin	1	my $workbook;
62	dpavlin	5	if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
63	dpavlin	1	# use as cgi script
64			print "Content-type: application/vnd.ms-excel\n\n";
65			$workbook = Spreadsheet::WriteExcel->new("-");
66			} else {
67			# Create a new Excel workbook
68	dpavlin	5	$workbook = Spreadsheet::WriteExcel->new( $path );
69			warn "Creating XLS file $path\n";
70	dpavlin	1	}
71
72	dpavlin	3	my $date_format = $workbook->add_format(num_format => $xls_date_format);
73
74	dpavlin	5	my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) \|\| die $DBI::errstr;
75	dpavlin	1
76	dpavlin	2	sub _c {
77	dpavlin	3	return decode( $db_encoding, shift );
78	dpavlin	2	}
79
80	dpavlin	1	foreach my $sql_file (@sql_files) {
81
82			my $sheet_name = $sql_file;
83			$sheet_name =~ s/\d+_//;
84			$sheet_name =~ s/_/ /g;
85			$sheet_name =~ s/\.sql//;
86
87			# Add a worksheet
88			my $worksheet = $workbook->addworksheet($sheet_name);
89
90	dpavlin	6	print STDERR "working on $sql_file\n" if ($debug);
91	dpavlin	1
92			open(SQL,$sql_file) \|\| die "can't open sql file '$sql_file': $!";
93	dpavlin	7	my $comment = '';
94	dpavlin	1	my $sql = "";
95			while(<SQL>) {
96			chomp;
97	dpavlin	4	if (/^\\c\s+(\S+)/) {
98	dpavlin	6	$dbh->disconnect if $dbh;
99			print STDERR "## connect to $1\n" if $debug;
100	dpavlin	5	$dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) \|\| die $DBI::errstr;
101	dpavlin	4	} elsif (/^--(.+)/) {
102	dpavlin	1	$comment.=$1;
103			} else {
104	dpavlin	4	$sql.= ' ' . $_;
105	dpavlin	1	}
106			}
107			close(SQL);
108
109	dpavlin	4	$sql =~ s/\s\s+/ /gs;
110
111	dpavlin	1	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
112
113			my $row = 0;
114
115			if ($comment) {
116
117			# Add and define a format
118			my $fmt_comment = $workbook->addformat(); # Add a format
119			$fmt_comment->set_bold();
120
121	dpavlin	8	$comment =~ s/^\s+//;
122			$comment =~ s/\s+$//;
123
124	dpavlin	2	$worksheet->write($row, 0, _c($comment), $fmt_comment);
125	dpavlin	1	$row+=2;
126			}
127
128	dpavlin	5	my $sth = $dbh->prepare($sql);
129			$sth->execute();
130	dpavlin	1
131			my $fmt_header = $workbook->addformat(); # Add a format
132			$fmt_header->set_italic();
133
134			for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
135			$worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
136			}
137			$row++;
138
139	dpavlin	6	my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
140	dpavlin	3
141	dpavlin	1	while (my @row = $sth->fetchrow_array() ) {
142			for(my $col=0; $col<=$#row; $col++) {
143	dpavlin	3	my $data = $row[$col];
144			if ( $types[$col] =~ m/^date/i ) {
145			$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
146	dpavlin	4	$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
147			warn "## $data\n";
148	dpavlin	3	$worksheet->write_date_time( $row, $col, $data, $date_format );
149			} else {
150			$worksheet->write($row, $col, _c( $data ) );
151			}
152	dpavlin	1	}
153			$row++;
154			}
155
156			}
157
158			$dbh->disconnect;
159
160			1;
161
162			__END__
163