Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
No such thing as a small change
 
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
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 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}
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.

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 romping around the Monastery: (6)
As of 2014-04-16 06:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (415 votes), past polls