parsing excel sheet from a perl program

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

hi , i am trying to parse an excel sheet through a perl program ,the core goal is the replace the value in a text file from the values in the excel sheet based on a certain conditions but main probelm is that reading is not even happening from excel sheet itself ,i havent got much experience in accessnig excel sheets from perl ,i tried google search and cpan module pages but couldnt really get a proper ,i am posting my code below ,excel sheet is present there ,it seems i cant attach anything ,when i execute this program it runs successfully but doesnt give any output

use strict;
use Spreadsheet::ParseExcel;    
my $excel = Spreadsheet::ParseExcel::Workbook->Parse("C:\\au.xls");
my $excel_file_url;
 #start looping the Excel sheet
foreach my $sheet (@{$excel->{Worksheet}}) 
    
    {
    $sheet->{MaxRow} ||= $sheet->{MinRow};
   foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        #looping for each row
    if ($row > 1) {
        #get the file url path from excel sheet at  corresponding row,
+ col 0
        my $cell_value=$sheet->{Cells}[$row][0];
        $excel_file_url=$cell_value->{Val};
        print $excel_file_url;
    }

   }

    }
[download]

Comment on parsing excel sheet from a perl program Download Code

Replies are listed 'Best First'.
Re: parsing excel sheet from a perl program by jrsimmon (Hermit) on Mar 08, 2010 at 19:49 UTC
Two things: You need to test for the success of your parse call You should print the values around each of your conditionals/loops to see why you're not entering them use strict; use Spreadsheet::ParseExcel; my $excel = Spreadsheet::ParseExcel::Workbook->Parse("C:\\au.xls"); die "Parse failed" unless defined $excel; my $excel_file_url; #start looping the Excel sheet foreach my $sheet (@{$excel->{Worksheet}}) { $sheet->{MaxRow} \|\|= $sheet->{MinRow}; print "$sheet->{MaxRow}\t$sheet->{MinRow}\n"; foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { print "Row: $row\n"; #looping for each row if ($row > 1) { #get the file url path from excel sheet at corresponding +row,+ col 0 my $cell_value=$sheet->{Cells}[$row][0]; $excel_file_url=$cell_value->{Val}; print $excel_file_url; } } } [download]	[reply] [d/l]
Re: parsing excel sheet from a perl program by NetWallah (Canon) on Mar 08, 2010 at 20:41 UTC
Wong Method call (Worksheet). The "worksheet(<name or number>)" method returns a single worksheet. "Worksheet" is a class name, not a method name. Try: `for my $sheet ( $excel->worksheets() ) { ...` [download] This will step through worksheets. Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why it works. Programmers combine Theory and Practice: Nothing works and they don't know why. -Anonymous	[reply] [d/l]
Re^2: parsing excel sheet from a perl program by rahulgsp83 (Novice) on Mar 09, 2010 at 14:09 UTC
hi, thanks for all you replies ,the issue is solved now , regards, rahul	[reply]
Re^3: parsing excel sheet from a perl program by ww (Archbishop) on Mar 09, 2010 at 14:20 UTC
Duped, in essence, by Reaped: Re^3: parsing excel sheet from a perl program, which I created when server burped: I had clicked create on this, after reviewing; but got the Gates, not this page. This seems to have happened multiple times lately (20100309). That's great. Now, give back (or, more precisely) "give ahead" a little help... for the future reader with the same sort of problem: specify which of the above (if any) were useful in reaching your solution.	[reply]
Re: parsing excel sheet from a perl program by ww (Archbishop) on Mar 08, 2010 at 20:40 UTC
As above, check your (implicit open) in line 3. Nonetheless, your script (with its numerous problems intact) reads the spreadsheet au.xls (when that's in same dir with the script and presumably, when au.xls actually exists where you say it does. au.xls, in same dir with script: `captionColA captionColB captionColC http://abc.com 3 abc http://www.xyz.com 4 xyz foo.bar 5 fb` [download] and, changing your test in line 12 to `if ($row > 0) {` (still not the right way to do this, but workable), execution looks like this: `pl_test>perl 827414.pl http://abc.com http://www.xyz.com foo.bar Use of uninitialized value in foreach loop entry at 827414.pl line 12. Use of uninitialized value in foreach loop entry at 827414.pl line 12. Argument "" isn't numeric in numeric gt (>) at 827414.pl line 14. Use of uninitialized value in foreach loop entry at 827414.pl line 12. Use of uninitialized value in foreach loop entry at 827414.pl line 12. Argument "" isn't numeric in numeric gt (>) at 827414.pl line 14. pl_test>` [download] And the minimally-modified (ie, largely uncorrected) script: #!/usr/bin/perl use strict; use warnings; # id: 827414 use Spreadsheet::ParseExcel; my $excel = Spreadsheet::ParseExcel::Workbook->Parse("au.xls"); my $excel_file_url; #start looping the Excel sheet for my $sheet (@{$excel->{Worksheet}}) { $sheet->{MaxRow} \|\|= $sheet->{MinRow}; for my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { #looping for each row if ($row > 0) { #get the file url path from excel sheet at corresponding +row, col 0 my $cell_value=$sheet->{Cells}[$row][0]; $excel_file_url=$cell_value->{Val}; print $excel_file_url . " \n"; } } } [download] HTH.	[reply] [d/l] [select]