Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

reading excel files

by glwtta (Hermit)
on Jan 16, 2003 at 23:51 UTC ( [id://227564]=perlquestion: print w/replies, xml ) Need Help??

glwtta has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

Basically I need to read excel files from perl. A quick term on CPAN turned up Spreadsheet::ParseExcel, I haven't had time to play with it that much, but was wondering what others' experience has been - does it work? does it work well? And more importantly, are there other alternative I haven't found?

I know it's kind of a vague question, but any and all thoughts are appreciated.

Replies are listed 'Best First'.
Re: reading excel files
by jmcnamara (Monsignor) on Jan 17, 2003 at 00:02 UTC
Re: reading excel files
by krujos (Curate) on Jan 17, 2003 at 00:13 UTC

    You may also want to checkout:
    ParseExcel::Simple, which makes... well the name says it all. Spreadsheet::ParseExcel::SaveParser will let you read and write the files if you need to do that. The documentation for all of the above is pretty good. There are also lots of nodes here that will help you out.

    Depending on your situation you may also find DBD::Excel useful. It contains DBI drivers for excel.

    Good Luck
    Josh
Re: reading excel files
by pg (Canon) on Jan 17, 2003 at 04:26 UTC
    It really depends on what you want to do with the spreadsheet.

    If you are interested in using it as a "database", then Win32::ODBC would be one choice. Two advantages are:
    1. This package comes with activestate Perl. No download, no installation.
    2. The code you written does not care the underlying database, it can be sql server, access, text file, fox pro, dbase, andthing that supports ODBC.
    One simple example, before you try it, first create an ODBC object myExcel, and in the underlying excel spreadsheet, enter some data:
    use Win32::ODBC; use Data::Dumper; $Data = new Win32::ODBC("myExcel") || die "failed\n"; $Data->sql('SELECT * FROM [Sheet1$]'); while ($Data->FetchRow()) { my %hash = $Data->DataHash(); print Dumper(\%hash); }
    If you are more interested in OLE, then you probably want to take a look at Win32::OLE.
Re: reading excel files
by Willard B. Trophy (Hermit) on Jan 17, 2003 at 14:45 UTC

    It's good, but with certain caveats.

    A former employer is making heavy use of it to do signage for a very large, very old Canadian department store. Seems they used to get product tickets sent as XLS, and some poor designer had to print it out, and copy the details back into QuarkXPress by hand. As these tickets were delivered in batches of hundreds at a time, this was slow, and mind-numbing.

    With Spreadsheet::ParseExcel, they are now able to write out XPress Tags data of the fields they want, and are able to turn around a job in a couple of hours.

    If you are using a Windows box, the previous suggestion to use one of the builtin routines makes sense. On any other platform, though, it's Spreadsheet::ParseExcel is useful.

    Not all XLS files are created equal, though. Ones that have been through OpenOffice don't seem to contain any data when parsed by Spreadsheet::ParseExcel, so be careful, test, and test your tests!

    --
    $,="\n";foreach(split('',"\3\3\3c>\0>c\177cc\0~c~``\0cc\177cc")) {$a++;$_=unpack('B8',$_);tr,01,\40#,;$b[$a%6].=$_};print@b,"\n"

Re: reading excel files
by CountZero (Bishop) on Jan 17, 2003 at 07:06 UTC

    If everything else fails, you can still export your spreadsheet to a tab-separated value text-file and start from there (of course only if your just interested in the data because all formatting and other goodies will be lost)

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: reading excel files
by Anonymous Monk on Jan 17, 2003 at 02:53 UTC
    A while back I hacked a CSV interface into this utility: http://chicago.sourceforge.net/xlhtml/ It did a very nice job of reading Excel documents.
Re: reading excel files
by glwtta (Hermit) on Jan 17, 2003 at 16:25 UTC
    Thanks for the great help everyone,

    All I need this for is to read data out, saving the user having to save-as tab delimited first (hey, the less they need to do, the better things seems to run ;) ), looks like this will do the trick just fine.

    As an aside, my last experience with reading excel was with java, where I had to use some library that actually launched excel to read the file - it worked horribly and crashed all the time; took me several weeks to realize that what I was told were "excel" file were just tab delimited text files with .xls - excel just opened them and converted on the fly. Well, live and learn :)

Re: reading excel files
by KiwiMan (Initiate) on Jan 18, 2003 at 18:36 UTC
    glwtta Looked at the module and decided that WIN32:OLE was the way forward. Within ActivateState there is documentation for OLE. Regards Mark

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://227564]
Approved by krujos
Front-paged by ibanix
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2024-04-19 15:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found