Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

read-excel.pl: a code sample for Spreadsheet::ParseExcel

by da (Friar)
on May 31, 2002 at 15:27 UTC ( #170743=snippet: print w/ replies, xml ) Need Help??

Description: This is a filter for reading .xls files without opening Excel. It is also a short demo of the basic features of Spreadsheet::ParseExcel.

Usage:

./read-excel.pl filename.xls

Features

For each tab (worksheet) in a file (workbook), spit out columns separated by spaces, and rows separated by c/r.
#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;

use strict;

my $filename = shift || "test.xls";

my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);

my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    print "Worksheet $sheet: $sheetName\n";
    next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{Max
+Col})));
    foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
        foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
            next unless (defined $eSheet->{Cells}[$row][$column]);
            print $eSheet->{Cells}[$row][$column]->Value . " ";
        }
        print "\n";
    }
}
Comment on read-excel.pl: a code sample for Spreadsheet::ParseExcel
Download Code
Replies are listed 'Best First'.
Re: read-excel.pl: a code sample for Spreadsheet::ParseExcel
by graff (Chancellor) on Jun 01, 2002 at 04:16 UTC
    I tried installing Spreadsheet::ParseExcel from CPAN on my linux box and it did not install. The place where it failed made me think I should try installing the OLE module from CPAN first, and that failed as well.

    I haven't checked the usenet news groups about this yet, and there was nothing in the README file about it, but is it just stupid to attempt this? Is Spreadsheet::ParseExcel inherently non-portable?

      Yes- it requires OLE::Storage_Lite, as it says near the top of the README:
      REQUIREMENT This module requires these modules: OLE::Storage_Lite Jcode.pm (if you are using FmtJapan, or FmtJapan2) Unicode::Map (if you are using FmtJapan2 or FmtUnicode)
      I installed both of these using the CPAN module, which is quite useful if you haven't seen it. The CPAN process automates installation nicely; it asks if you want to fulfill unmet dependencies and install OLE::Storage_Lite. I said yes, it installed OK, and I re-ran 'install Spreadsheet::ParseExcel' with no problems.

      There could be further dependencies you'd need to install for OLE::Storage_Lite to work; but it's much easier with the CPAN module.

      ___ -DA $_='daniel@coder.com 519-575-3733 /Prescient Code Solutions/ coder.c +om ';s/-/ /g;s/([.@])/ $1/g;@y=(42*1476312054+7*3,14120504e4,-42*330261-3 +3, 42*5436+3,42*2886+10,42*434987+5);s/(.)/ord(uc($1))/ge;for(@x=split/32 +/; @y; map{print chr} split /(..)/, shift(@x) + shift(@y)) {perlmonk.da.r +u}
        Thank you. Yes, I always use CPAN. (Pretty soon I'll never be able to do it any other way!)

        Turns out that OLE::Storage_Lite also required IO::Scalar, which I didn't have yet, but once that was in, everything went smoothly. Reading excel spreadsheets from my linux partition will save me a ton of grief!!

Re: read-excel.pl: a code sample for Spreadsheet::ParseExcel
by Anonymous Monk on Sep 11, 2009 at 12:53 UTC
    OMG thank you to whoever wrote this PM it just saved me COUNTLESS hours working on separate excel files and sheet within them.
Re: read-excel.pl: a code sample for Spreadsheet::ParseExcel
by Anonymous Monk on Nov 07, 2002 at 17:44 UTC
    I am trying to read an Excel spreadsheet and then input the data into an ora db, all on solaris. When using the Spreadsheet::ParseExcel to get the data from the spreadsheet, I am having trouble extracting the data from cells that are formatted as 'Date'. I have done the conversion to text on win32 using variants, but those packages are only for win32. Does anyone know what methods in Spreadsheet::ParseExcel I need to use to extract as text a value from a cell that is set to be a 'Date' in the excel spreadsheet?
      As far as I know, you just use Value. What exactly does it output for date cells? When I just ran the above sample code on a linux box, I got a formatted date output where there is a date in the original data (an Excel 2000-formatted spreadsheet).

      ___ -DA $_='daniel@coder.com 519-575-3733 /Prescient Code Solutions/ coder.c +om ';s/-/ /g;s/([.@])/ $1/g;@y=(42*1476312054+7*3,14120504e4,-42*330261-3 +3, 42*5436+3,42*2886+10,42*434987+5);s/(.)/ord(uc($1))/ge;for(@x=split/32 +/; @y; map{print chr} split /(..)/, shift(@x) + shift(@y)) {perlmonk.da.r +u}

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://170743]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (11)
As of 2015-08-27 21:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The oldest computer book still on my shelves (or on my digital media) is ...













    Results (334 votes), past polls