Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Database -> Excel

Reply
Thread Tools

Database -> Excel

 
 
Robert
Guest
Posts: n/a
 
      04-01-2005
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
 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      04-01-2005
Robert <(E-Mail Removed)> wrote in news:sigzero-06C786.22411331032005
@news.isp.giganews.com:

> I see a whole passle of Excel modules on CPAN.


What exactly is a "passle"?

> 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?


If all you need is to save some tables in a format that can be viewed
using Excel, generating CSV files should fit the bill. See Text::CSV_XS.

If you want to generate native Excel binary files,

if you are on Windows, you can use the facilities provided by
Win32::OLE.

I have no experience with it, but you can also use

http://search.cpan.org/~jmcnamara/Sp...iteExcel-2.12/

Sinan

--
A. Sinan Unur <(E-Mail Removed)>
(reverse each component and remove .invalid for email address)

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/cl...uidelines.html
 
Reply With Quote
 
 
 
 
John W. Krahn
Guest
Posts: n/a
 
      04-01-2005
A. Sinan Unur wrote:
> Robert <(E-Mail Removed)> wrote in news:sigzero-06C786.22411331032005
> @news.isp.giganews.com:
>
>>I see a whole passle of Excel modules on CPAN.

>
> What exactly is a "passle"?


You'll have to watch an old John Wayne movie to find out.


John
--
use Perl;
program
fulfillment
 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      04-01-2005
"John W. Krahn" <(E-Mail Removed)> wrote in news:qw53e.142720
$gJ3.140027@clgrps13:

> A. Sinan Unur wrote:
>> Robert <(E-Mail Removed)> wrote in news:sigzero-06C786.22411331032005
>> @news.isp.giganews.com:
>>
>>>I see a whole passle of Excel modules on CPAN.

>>
>> What exactly is a "passle"?

>
> You'll have to watch an old John Wayne movie to find out.


I have seen quite a few John Wayne movies but they were all dubbed. Too
bad.

I see the OP has provided the meaning. I am duly enlightened.

Sinan

--
A. Sinan Unur <(E-Mail Removed)>
(reverse each component and remove .invalid for email address)

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/cl...uidelines.html
 
Reply With Quote
 
Kiloran
Guest
Posts: n/a
 
      04-01-2005

"Robert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


I'm using Spreadsheet-WriteExcel with Activestate Perl 5.6.1 on Windows XP
and I'm quite happy with it. It can format cells, column widths, freeze
panes and a bunch of other stuff. It has a limitation of around 7MB per
file, but that is overcome with Spreadsheet-WriteExcel-Big.
I suspect it's quicker to create files this way than using OLE

--Alan


 
Reply With Quote
 
Bob Walton
Guest
Posts: n/a
 
      04-02-2005
Robert wrote:

> 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.
--
Bob Walton
Email: http://bwalton.com/cgi-bin/emailbob.pl
 
Reply With Quote
 
Mike
Guest
Posts: n/a
 
      04-02-2005
Robert <(E-Mail Removed)> wrote in news:sigzero-06C786.22411331032005
@news.isp.giganews.com:

> 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


I think I'm doing this very same thing. I'm using Spreadsheet-WriteExcel,
DBI, and DBD-Oracle. I'm creating the spreadsheet on HP-UX and sending
it out via e-mail to the interested parties.

Cheers,
Mike
 
Reply With Quote
 
Tad McClellan
Guest
Posts: n/a
 
      04-03-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) <(E-Mail Removed)> wrote:

> That is really close to what I am doing...



What is really close to what you am doing?

Please quote some context when composing a followup, like
everyone else does. Thank you.


--
Tad McClellan SGML consulting
(E-Mail Removed) Perl programming
Fort Worth, Texas
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
GridView to Excel then Excel to database table. mohaaron@gmail.com ASP .Net 0 11-06-2007 06:40 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM



Advertisments