Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

XLSX retriving if matches

by Nansh (Acolyte)
on Jun 16, 2017 at 15:52 UTC ( [id://1192954]=perlquestion: print w/replies, xml ) Need Help??

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

Hi my code is like this

use strict; use Spreadsheet::ParseXLSX; use Spreadsheet::WriteExcel; my $pattern="Name"; my $parser = Spreadsheet::ParseXLSX->new(); my $workbook = $parser->parse('somthing.xlsx'); if ( !defined $workbook ) { die $parser->error(), ".\n"; } for my $worksheet ( $workbook->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; if($cell->value eq $pattern) { //Here How to get the complete row info if $info equal to $pat +tern// } } } }

I need to get whole row if Name is found in the XLSX file

Thank you

Replies are listed 'Best First'.
Re: XLSX retriving if matches
by runrig (Abbot) on Jun 16, 2017 at 17:02 UTC
Re: XLSX retriving if matches
by 1nickt (Canon) on Jun 16, 2017 at 16:27 UTC

    Hi, if you don't want to dump the file as text as you asked about using the tool Corion suggested earlier, I suggest getting the owner of the spreadsheet to export it as a CSV document and then use Text::CSV_XS to work with it.

    I see that you've posted the example code from the doc for Spreadsheet::ParseExcel, which is a good way to start experimenting. As you learned there is not a method provided to get the full row. I don't know enough about Excel to know the reason for this, but I assume there is a good one.

    But, since you have the row number in $row and also the range of columns in the row, you could loop through the cells in the row for the column range and build an array of values, then return that as the "row".

    Sure would be simpler to work with CSV, though.

    Hope this helps

    The way forward always starts with a minimal test.
Re: XLSX retriving if matches
by marinersk (Priest) on Jun 17, 2017 at 02:52 UTC

    An old-school way to do it is to:

    1. Initialize an array at the start of each row;
    2. Initialize a processing flag to false at the start of each row;
    3. Push each cell into that array as it is processed during the inner loop;
    4. Set the processing flag to true when you have your $patternmatch;
    5. At the bottom of the row loop, after processing all the cells in that row, if the flag is set, the array contains the complete row so use it accordingly.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1192954]
Approved by herveus
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (2)
As of 2024-05-26 18:04 GMT
Find Nodes?
    Voting Booth?

    No recent polls found