Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re^7: Reading Excel file in perl

by marto (Bishop)
on Nov 14, 2012 at 10:13 UTC ( #1003779=note: print w/replies, xml ) Need Help??


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

You claim to know about Spreadsheet::ParseExcel, have you read the documentation? Lots of code as well as links to examples. Super Search for more threads on this subject.

Replies are listed 'Best First'.
Re^8: Reading Excel file in perl
by Pauler (Initiate) on Nov 26, 2012 at 07:07 UTC
    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
    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; }

      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";

        needs to be:

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

        with the inner double-quote characters backslashed.

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

        use strict; use warnings;

        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

        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"; } } } }
        Thanks

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1003779]
help
Chatterbox?
[stevieb]: Marshall: no. I commented on the thread yesterday. Visual Studio updates the tags ok, but corrupts the exe
[stevieb]: I advised the OP that a possible workaround would be to add a version function/flag that displays the required copyright/license info instead
[Marshall]: Darn! I'll look at the thread. Must not be updatinga byte count somewhere. The .exe format is a complex critter.
[stevieb]: that way, it's still "hard coded" into the exe at least, despite not being visible via Properties
[Marshall]: I also looked into PerlApp from Active State, but they don't sell their Dev Kit independent of a very expensive ($1,200) per year license anymore.
[Corion]: I would assume that the PE format hasn't changed that much since the days of yore, but it seems that I would be wrong in that assumption
[Marshall]: When I bought my copy 15+ years ago, it was just a couple hundred bucks.
[Marshall]: I looked at MS specs for PE format and I didn't see any changes in last decade, but evidenly that would be wrong.

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (11)
As of 2016-12-08 18:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:













    Results (144 votes). Check out past polls.