Spreadsheet::ParseExcel - How to get certain Cells

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

how may I get certain cells (rows and columns) from an Excel file using
the module Spreadsheet::ParseExcel???

Let's asume an Excel-sheet contains the following values:

      [    A     ][    B     ][   C     ]
[1]   Cell_A1  Cell_B1  Cell_C1
[2]   Cell_A2  Cell_B2  Cell_C2
[3]   Cell_A3  Cell_B3  Cell_C3
[4]   Cell_A4  Cell_B4  Cell_C4
[5]   Cell_A5  Cell_B5  Cell_C5
[6]   Cell_A6  Cell_B6  Cell_C6

Let's asume further B2 - B5 are the cells I would like to parse. I
tried it out with the following code, but there are few mistakes.

use strict;
use Spreadsheet::ParseExcel;
my $Excel_file = new Spreadsheet::ParseExcel;

my $oBook = $Excel_file->Parse('example.xls');
my $iRow=1;
my $iColumn=0;
my ($oWkS, $oWkC);

#for(my $iSheet=0; $iSheet < $oBook-> ; $iSheet++) {
for(my $iSheet = $oBook->[0]) {
        $oWkS = $oBook->[$iSheet];
        print "--------- SHEET: ", $oWkS->, "\n";
        for($iRow ;
                defined $oWkS-> && $iRow <= $oWkS-> ;
$iRow++) {
                            defined $oWkS-> && $iColumn <=
$oWkS-> ; $iColumn++) {
                $oWkC = $oWkS->[$iRow][$iColumn];
                # print "( $iRow , $iColumn ) =>", $oWkC->Value, "\n"
                print $oWkC->Value, "\n" if($oWkC);

My mistakes I know about already:

1. I do not know how to specify the range to be parsed.
2. The value I am getting from the script above is the whole row.
     But I am looking for the columns B2 - B5 which should give me
     the following values:


As I am in general quite new to Perl and to the module in particular,
I got stuck at this point.

Therefore, I appreciate any help.

Thank you in advance.


Re: Spreadsheet::ParseExcel - How to get certain Cells

Tom Brown wrote:
Quoted text here. Click to load it

Don't need a for() there because you're only going through one
Worksheet, the first one, [0];

my $iSheet = $oBook->[0];

Quoted text here. Click to load it

Since you know what row.. and column.. it might be easier to understand as:

Quoted text here. Click to load it

    for my $iRow ( 2 .. 5 ) {

Quoted text here. Click to load it

Since you want a specific column, you don't need another 'for' loop.
You could set this outside of your first loop.

        my $iColumn = 2;

Quoted text here. Click to load it

Because of the third 'for' loop.  Had you uncommented the print above
you should have seen the values for iRow and iColumn and seen
that it was going through every cell in every row and column.

Quoted text here. Click to load it

Most (All?) languages have 'for' loops, so this shouldn't be a new topic.

Maybe reviewing:
    perldoc perlinto
    perldoc perlsyn
might help you get reaquainted. :-)

I also must say you did a good job stating your issue, showing
code, and sample data.  I wish more people would do the same.

Re: Spreadsheet::ParseExcel - How to get certain Cells

Dear J.,

many many thanks for your assistance. With your explanation it was
quite easy to get the values I was looking for.

Unfortunatelly, when I invoked the script on the live sheet (what the
script is intended for), I am facing a new challenge. Before I start to
describe it, please have a look on the data source/structure:

       [   A   ] [    B     ][          C          ]
[13]     1      Cell_B1
[23]     2      Cell_B2
[37]     3      Cell_B3
[41]     4      Cell_B4
[64]     5      Cell_B5
[71]     6      Cell_B6
[80]     7      Cell_B7

As can be seen, the index is not consecutively numbered (due to some
makros and VBS inside the Excel-sheet). Therefore, column A is the
point of reference where I have to define the range to get the values
from column C.

I've tried it already by myself, but I do not get the correct values.
Here is the failing candidate:

[------------------------start of code--------------------------]
my $oBook = $Excel_file->Parse('devices.xls');
my ($oWkS, $oWkC);

my $iSheet = $oBook->[4];
        $oWkS = $oBook->[$iSheet];
        print "--------- SHEET: ", $oWkS->, "\n"x2;

for my $indexRow ( 3 .. 6 ) {
    my $indexColumn = 1;
    $oWkC = $oWkS->[$indexRow][3];
    #print "( $iRow , $iColumn ) =>", $oWkC->Value, "\n" if($oWkC);
    print $oWkC->Value, "\n" if($oWkC);
[------------------------End of code--------------------------]

In plaintext: I would like to get the values in column C for the range
A3 .. A6. So, as result the script should give the following IP
addresses back:

So, where is my fault?

Once again, many thanks for any assistance.


Re: Spreadsheet::ParseExcel - How to get certain Cells

Tom Brown wrote:
Quoted text here. Click to load it

Ahhh... what?  Where does column A come in to all of this?
The module processes rows and columns. If the worksheet
looks like that, and you want column 3, rows 3-6, then your code
looks OK.  If you want something else, then I don't understand.

Quoted text here. Click to load it
      ^^^^^^^ that's not used.

Quoted text here. Click to load it

What values are you seeing?

Quoted text here. Click to load it

I don't see anything wrong, so I'm guessing its maybe
that the macro's aren't supported, so the worksheet isn't
what you expect, or it's the wrong worksheet.

Uncomment the print, so you can tell what row and column is being
processed, compare it to your worksheet, and modify as needed. Maybe
print the upper left corner of values ( rows 0-6 and columns 0-3 )
to see how the values compare to the worksheet you're expecting.

for my $row ( 0 .. 6 ) {
   for my $col ( 0 .. 3 ) {
      my $oWkC = $oWkS->[$row][$col];
      print "[$row,$col]=", $oWkC->Value, " ";
   print "\n"

Re: Spreadsheet::ParseExcel - How to get certain Cells

On 2008-02-25 18:15:42 +0100, "J. Gleixner"

Quoted text here. Click to load it

After sleeping over it, I conclude Perl, respectively the module
Spreadsheet::ParseExcel, can't provide what I was looking for. For a
simple reason: The initial point where I started was just a partial
view (let's say excerpt) on a sheet, i. e., the actual sheet has in
fact a quite different data structure as shown in the filtered excel
view. Therefore, the output from my script/Spreadsheet::ParseExcel is
based on the real content of the sheet and not on my partial view.

However, your comments, J., nudged me into the proper direction and I
got now what I was initally looking for.

Many thanks,


Re: Spreadsheet::ParseExcel - How to get certain Cells

Quoted text here. Click to load it

I have to play with Perl and Excel from time to time, but my problem
goes the other way -- writing to an Excel file rather than reading
one. The crude way is writing the data to a csv file that Excel will
open natively. This works perfectly for the data but it doesn't
configure the workbook. The other way is to generate an xml file that
Excel will open. This also works perfectly, it's more work for me but
the file will also open in a browser which you can connect with a CSS
file and pretty it up for users that only want to look at the data,
not manipulate it. And in fact, you can do both for your two kinds of
users, those that want to slice and dice the data and those that only
want to look at it.

I'd be very interested in your solution. I'd also be interested to
know why you couldn't open your data file as an xml file and read the
data from that.


Re: Spreadsheet::ParseExcel - How to get certain Cells

In article

Quoted text here. Click to load it

Have you considered using the module Spreadsheet::WriteExcel to write
an Excel file?

Jim Gibson

 Posted Via Usenet.com Premium Usenet Newsgroup Services

Re: Spreadsheet::ParseExcel - How to get certain Cells

Quoted text here. Click to load it

Hello CC,

I am wondering about your interest for my solution as you have to write
data. As already mentioned by Jim Gibson, Spreadsheet::WriteExcel
should be the apropriate module for you.

However, here is my solution to parse excel files:

----------------------------cut it here-----------------------------------

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
use Getopt::Long;
use Pod::Usage;
use IO::File;

my %OPT = (
   "excel-file" => undef,
   "startrow" => "1",
   "column" => "9",
   "worksheet" => "0",

GetOptions (\%OPT, qw(
)) or pod2usage(2);

if ($OPT) {
   pod2usage(-verbose => 2);

if (!defined @ARGV) {

my $Excel_file = new Spreadsheet::ParseExcel;

my $iSheet = $oBook->[$OPT];
        my $ws = $oBook->[$iSheet];

for my $indexRow ( $OPT .. $OPT ) {
    my $hostname = $ws->[$indexRow][$OPT];
    printf "%s.domain.net\n", $hostname->Value;


=head1 NAME

B<parse_excel> - Parse Excel spreadsheets for particular ranges


B<parse_excel> [options] [file ...]


=over 8

=item B<-ws | -worksheet>

=item B<-c  | -column>

=item B<-s  | -startrow>

=item B<-e  | -endrow>

=item B<-man>


The primarily intention of B<parse_excel> is to extract host-lists from
Excel files. Examples are shown below. Take into consideration, that rows,
columns and worksheets start at zero. So, if you have to get cell A1 from
the first worksheet you have to address worksheet 0, column 0 and row 0.

=head1 OPTIONS

=over 2

=item B<-worksheet> I<integer>

Default is 0 for the first worksheet

=item B<-column> I<integer>

Column to be collected. Default is 9 for hostnames (old)

=item B<-startrow> I<integer>

Starting row to parse from. Default = 1

=item B<-endrow> I<integer>

Last row to parse data from

=item B<-man>

Show complete usage information



A basic test just to get the value from worksheet 1, cell A1 (which
means the value from column 0, row 0 from worksheet 0)

    parse_excel -ws 0 -c 0 -s 0 -e 0 spreadsheet.xls

Get from the first worksheet, column 0 the rows 0, 1 and 2 (bear in
mind the defaults mentioned above):

    parse_excel -e 3 spreadsheet.xls

Get rows 2 till 242 from worksheet 0, column 0 and redirect it into a file:

    parse_excel -s 1 -e 241 spreadsheet.xls > hosts.lst

Extract some cells using long options:

    parse_excel -column 11 -startrow 3 -endrow 9 data/spreadsheet.xls

Get from the second worksheet and column 10 the rows 4, 5, 6, 7 and 8
(bear in mind that counters start at 0!!!)

    parse_excel -ws 1 -c 9 -s 3 -e 7 data/spreadsheet.xls

=head1 FILES

=over 3

=item I<spreadsheet.xls>

Take any Excel95, Excel97 or Excel2000 file as input file.


=head1 SEE ALSO

=over 2

=item I<auto_change_config>

Examine I<auto_change_config> to perform configuration-rollouts by
telnet or ssh for large-scaled amount of devices.


=head1 ERRORS

=over 2

=item <Can't call method "Value" on an undefined value at ./parse_excel
line 51.>

The specified range covers fields (i. e. cells) without any value.
Specify ranges
for cells which contain data to get rid of this message or just ignore it.


=head1 AUTHOR

Site Timeline