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

I was doing this project and I tried to use the Spreadsheet::ParseExcel::Simple module. Anyways, I have a question regarding how it works. Basically, is there any way to control where the parser fetches the data to? For example, I have a spreadsheet that has 4 sheets. I want to get data from the 2nd sheet alone. Is there any way to specify it so that it'd ONLY get data from that one sheet alone? I also want to have some control as to methods to extract bits of data from certain elements inside the array(s) as well.

Also wanted to ask about methods of going through the array and getting specific data from the elements inside it.

I tried to rewrite it like this:

#!/usr/bin/perl

use Spreadsheet::ParseExcel::Simple;

my $xls = Spreadsheet::ParseExcel::Simple->read('spreadsheet.xls');
foreach my $sheet ($xls->sheets) {
    while ($sheet->has_data) {  
        my @data = $sheet->next_row;
        print @data[5];
    }
}

[download]

But it only gets the 5th element (of all the rows), of all the sheets. Is there a way to get specific columns, specific rows, and specific sheets?

The module can be found at: Spreadsheet::ParseExcel::Simple

I know this is alot to ask, but I would really appreciate the assistance. Thank you.

Edit: <code> tags, CPAN link and some minor formatting, larsen

Title edit by tye

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel::Simple
by jmcnamara (Monsignor) on Aug 21, 2003 at 22:45 UTC

    The Spreadsheet::ParseExcel::Simple sheets() method returns a list of worksheet objects. Therefore, to access the second worksheet you can do something like the following: 
        #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel::Simple;


    my $workbook  = Spreadsheet::ParseExcel::Simple->read('example.xls
+');

    my $worksheet = ($workbook->sheets())[1];

    while ($worksheet->has_data()) {  
        my @data = $worksheet->next_row();
        print "@data\n";
    }

    [download]
    To get to a specific row you will have to repeatedly call next_row(). You cannot explicitly reference a column using Spreadsheet::ParseExcel::Simple so you will have to programmitically build up a column array using the data returned from the rows.

    --
    John.

[reply]
[d/l]
      Hello, I am complete newbie to Perl, but... what about looking here http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.59/lib/Spreadsheet/ParseExcel/Workbook.pm It is about Spreasheet::ParseExcel, still look worksheet() method.
[reply]
Re: Spreadsheet::ParseExcel::Simple
by CombatSquirrel (Hermit) on Aug 21, 2003 at 22:26 UTC
    From the docs: 
      @sheets = $xls->sheets;

    [download]
    Each spreadsheet can contain one or more worksheets. This fetches them all back. You can then iterate over them, or jump straight to the one you wish to play with.
    So, to get, for example, the 3rd worksheet you can do something like $thirdsheet = ($xls->sheets)[3];
    In your example, you are getting the 6th column, which consists of the 6th element in each single row.
    As far as I see it, the only way to get a specific row, is in an empty for-loop: 
    for (1..5) { # skip first five rows
   $sheet->has_data or die "Can't get 5th row: Too few rows\n";
   $sheet->next_row;
}
my @data = $sheet->next_row;

    [download]
    Hope this helps (and works - this computer doesn't have Excel installed, so it's untested).
    Addendum: You might be interested in the Spreadsheet::ParseExcel module, which has a broader variety of commands. Specifically, you can get the contents of single cells from a worksheet.
[reply]
[d/l]
[select]
      I tried lookin through the Spreadsheet::ParseExcel module too...but, I couldn't really understand how to get the program to scroll through columns and rows. Like, I'm considering using 2 for loops. The first go through the rows, while the second one goes down the column inside the row. Anyways, the problem is that the ParseExcel module doesn't really offer a good explanation of how I'd go about doing this.
[reply]
        If you should afterall be interested in Spreadsheet::ParseExcel, then maybe the following code from the docs (after naming the variables with slightly better readable names) is going to help you 
        use strict;
use Spreadsheet::ParseExcel;
my $BookObject = Spreadsheet::ParseExcel::Workbook->Parse('Excel/Test9
+7.xls');
foreach my $WorksheetObject (@{$BookObject->{Worksheet}}) {
   print "--------- SHEET:", $WorksheetObject->{Name}, "\n";
   for(my $RowIndex = $WorksheetObject->{MinRow}; # starting at minimu
+m row value
       defined $WorksheetObject->{MaxRow} &&
         $RowIndex <= $WorksheetObject->{MaxRow}; # ending at maximum 
+row value
       $RowIndex++) {
      for (my $ColumnIndex = $WorksheetObject->{MinCol};
           defined $WorksheetObject->{MaxCol} &&
             $ColumnIndex <= $WorksheetObject->{MaxCol};
           $ColumnIndex++) {
          $CellObject = $WorksheetObject->{Cells}[$RowIndex][$ColumnIn
+dex]; # get cell
          print "( $RowIndex , $ColumnIndex ) =>",
                $CellObject->Value, "\n"    # print cell contents ...
             if ($CellObject);              # ... if cell exists
      }
   }
}

        [download]
        Overview over the Spreadsheet::ParseExcell class:
        class Spreadsheet::ParseExcel
            | via ->Parse
           class Spreadsheet::ParseExcel::Workbook
               | via ->{Worksheet}
              class Spreadsheet::ParseExcel::Worksheet
                   | via ->{Cells}[$Row][$Column]
                 class Spreadsheet::ParseExcel::Cell
         And as well, but not so important:
        class Spreadsheet::ParseExcel::Format
        class Spreadsheet::ParseExcel::Font
        class Spreadsheet::ParseExcel::Fmt
         Hope this helped.
        CombatSquirrel.
[reply]
[d/l]

Back to Seekers of Perl Wisdom