in reply to Re^7: Reading Excel file in perl
in thread Reading Excel file in perl

I have the below code which reads the column value. But the prb in the code is it prints each column value 3times. Could someone please help in finding the bug. The excel looks like below: 
 
      perl flavors    modules     class     Candidate Name
       vanilla          excel     first       Nancy
       strawberry      dumper     second       jimmy
       lettuce           array     third       roma

[download]
The perl code is as below: 
 
#use strict;
use Spreadsheet::ParseExcel;

my $FileName = "/home/dujnne/praice/excel/test.xls";
my $excel = Spreadsheet::ParseExcel::Workbook->Parse($FileName) or die
+ "Unable to open $FileName\n";

#locate columns in the spreadsheet from which we want to extract data

my $found = 0;
my ($rowCnt, $colCnt) = (0,0);

foreach my $sheet (@{$excel->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});

    $sheet->{MaxRow} ||= $sheet->{MinRow};

    $rowCnt = $sheet->{MinRow};

    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        $sheet->{MaxCol} ||= $sheet->{MinCol};
        $colCnt = $sheet->{MinCol};

        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
                     my $cell = $sheet->{Cells}[$row][$colCnt];
                                  if ( lc $cell->{Val} eq "class" ) {
                $found = 1;
                print "OKAY: found the column as "class"\n";
                print "INFO: searching for all classes \n";
                $colCnt++;
            }
            if ( $found ) {
                print $sheet->{Cells}[$row][$colCnt]->{Val}, "\n";
                next;
            }
        }
    }
    $found = 0;


}

[download]

Replies are listed 'Best First'.
Re^9: Reading Excel file in perl
by Athanasius (Archbishop) on Nov 26, 2012 at 08:28 UTC

    Hello Pauler,

    I don’t really understand what this code is trying to do, so I’ll just offer some observations:

    1. The code as given does not compile. This line:

      print "OKAY: found the column as "class"\n";

      [download]

      needs to be:

      print "OKAY: found the column as \"class\"\n";

      [download]

      with the inner double-quote characters backslashed.

    2. You should always — yes, always! — begin each script with:

      use strict;
use warnings;

      [download]

      — and never comment them out again!

    3. I suspect the script’s main problem is with the next statement on the 6th-last line. This ends the current iteration of the innermost loop, but as it’s the last statement within that loop, it currently does nothing. Perhaps you meant last? Or next LABEL; where LABEL refers back to a previous (i.e., outer) loop? See next and last in Perl documentation.

    Hope that helps,

    Athanasius <°(((>< contra mundum

[reply]
[d/l]
[select]
      Hi Guys , Thanks for all the help. I have got the solution for reading only 1 column value via perl. 
      foreach my $sheet (@{$excel->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    $sheet->{MaxRow} ||= $sheet->{MinRow};
    $rowCnt = $sheet->{MinRow};

    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        $sheet->{MaxCol} ||= $sheet->{MinCol};
        $colCnt = $sheet->{MinCol};

       foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
            my $cell = $sheet->{Cells}[$row][$col]->{Val};

            if ( $cell eq "Column Heading" ) {
                $val_col=$col;
                print "OKAY: found column values as below\n";
                my $res = $sheet->{Cells}[$rowCnt][$col]->{Val}; #Read
+s each value for the column
                print $res, "\n";
                next;
           }
            if ($val_col eq $col ) {
                print $cell, "\n";
                push (@col_arr, $cell);
             }
          foreach $cell (@data){
                print "into new loop\n";
        }
        }
    }
}

      [download]
      Thanks
[reply]
[d/l]

In Section Seekers of Perl Wisdom