Robert said:
I see a whole passle of Excel modules on CPAN. I am querying an Oracle
database and I want to save that query into an Excel file. What module
would you suggest that I use?
Robert
Well, Excel can be accessed as a database. Since you're already
doing database stuff (with DBI, I hope), you could access a named
region in Excel as a table. Steps:
1. make an Excel spreadsheet with a named region containing your
data, starting with a row of column headings.
2. set up a system or user DSN (start..settings..control
panel..administrative tools..data sources (odbc)
Run code something like:
use warnings;
use strict;
use DBI;
my $db=DBI->connect('dbi:ODBC:junk');
die "couldn't open" unless $db;
my $sth=$db->prepare('select * from junk');
die "couldn't prepare" unless $sth;
my $rv=$sth->execute;
die "couldn't execute" unless $rv;
my $arr_ref=$sth->fetchall_arrayref;
use Data:
umper;
print Dumper($arr_ref);
$db->disconnect;
Here's the Excel region named 'junk' (highlight, then
insert..name..define, enter 'junk':
blah foo bar baz barf gorp
1 3 5 7 9 11
2 5 8 11 14 17
3 4 5 6 7 8
4 7 10 13 16 19
5 6 7 8 9 10
6 9 12 15 18 21
7 8 9 10 11 12
8 3 -2 -7 -12 -17
9 6 3 0 -3 -6
10 8 6 4 2 0
and here's the output:
D:\junk>perl junk525.pl
$VAR1 = [
[
'1.0',
'3.0',
'5.0',
'7.0',
'9.0',
'11.0'
],
[
'2.0',
'5.0',
'8.0',
'11.0',
'14.0',
'17.0'
],
[
'3.0',
'4.0',
'5.0',
'6.0',
'7.0',
'8.0'
],
[
'4.0',
'7.0',
'10.0',
'13.0',
'16.0',
'19.0'
],
[
'5.0',
'6.0',
'7.0',
'8.0',
'9.0',
'10.0'
],
[
'6.0',
'9.0',
'12.0',
'15.0',
'18.0',
'21.0'
],
[
'7.0',
'8.0',
'9.0',
'10.0',
'11.0',
'12.0'
],
[
'8.0',
'3.0',
'-2.0',
'-7.0',
'-12.0',
'-17.0'
],
[
'9.0',
'6.0',
'3.0',
'0.0',
'-3.0',
'-6.0'
],
[
'10.0',
'8.0',
'6.0',
'4.0',
'2.0',
'0.0'
]
];
D:\junk>
You can, of course, use the SQL 'insert into' etc.
