Database -> Excel

Discussion in 'Perl Misc' started by Robert, Apr 1, 2005.

  1. Robert

    Robert Guest

    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
     
    Robert, Apr 1, 2005
    #1
    1. Advertising

  2. Robert <> 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/Spreadsheet-WriteExcel-2.12/

    Sinan

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

    comp.lang.perl.misc guidelines on the WWW:
    http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
     
    A. Sinan Unur, Apr 1, 2005
    #2
    1. Advertising

  3. A. Sinan Unur wrote:
    > Robert <> 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
     
    John W. Krahn, Apr 1, 2005
    #3
  4. "John W. Krahn" <> wrote in news:qw53e.142720
    $gJ3.140027@clgrps13:

    > A. Sinan Unur wrote:
    >> Robert <> 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 <>
    (reverse each component and remove .invalid for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://mail.augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html
     
    A. Sinan Unur, Apr 1, 2005
    #4
  5. Robert

    Kiloran Guest

    "Robert" <> wrote in message
    news:...
    > 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
     
    Kiloran, Apr 1, 2005
    #5
  6. Robert

    Bob Walton Guest

    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::Dumper;
    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
     
    Bob Walton, Apr 2, 2005
    #6
  7. Robert

    Mike Guest

    Robert <> 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
     
    Mike, Apr 2, 2005
    #7
  8. <> 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
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, Apr 3, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SmltIEhlYXZleQ==?=

    Excel _WorkBook vs. Excel.Workbook

    =?Utf-8?B?SmltIEhlYXZleQ==?=, Sep 29, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,706
    Steve C. Orr [MVP, MCSD]
    Sep 29, 2004
  2. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,434
    Carl Prothman [MVP]
    Jan 12, 2005
  3. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,165
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,608
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Replies:
    0
    Views:
    445
Loading...

Share This Page