XLSX retriving if matches

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//
            
               
         }
        
        }
    }
}
[download]

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

Thank you

Comment on XLSX retriving if matches Download Code

Replies are listed 'Best First'.
Re: XLSX retriving if matches by runrig (Abbot) on Jun 16, 2017 at 17:02 UTC
Spreadsheet::ParseExcel::Stream will parse the spreadsheet into rows, so that it is easy to process a row at a time. It does, however, use the older Spreadsheet::XLSX as the underlying parser (must update it someday...).	[reply]
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.	[reply] [d/l]
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: Initialize an array at the start of each row; Initialize a processing flag to false at the start of each row; Push each cell into that array as it is processed during the inner loop; Set the processing flag to true when you have your `$pattern`match; 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. <Reveal this spoiler or all in this thread>	[reply] [d/l] [select]