CODEWITHSUNDEEP
excelperl
Thor_that_new_in_programming
Thor_that_new_in_programming

Reputation: 193

How to select the specific row that matched a specific variable in excel?

I got a list of variables that stored in an array. I know how to select specific columns and rows but I only want to select specific row that matched variables in array (assume all the variables is unique, only will appear in excel once). Is that possible do it and how it can be done?

Excel:

Name    Code    Age
John    0123    18
ean     1234    19

My code for now:

use strict;
use warnings;
use Spreadsheet::ParseXLSX;

@name = ("john", "tommy", "ming", "ean");

##Loop through the array and search in excel##
foreach my $test (@name) {

my $parser   = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse('C:\namelist.xlsx');

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets('Name List') ) {

    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 ) {

           **Any Idea what should I do here to get expected result?**

            }
        }
    }
 }

Expected result: While looping through the array @name, it will search in the excel, if the variable like John is matched, it will retrieve all the data of that row and store it in variables. EG. $code = 01234; and $Age=18;

Any related post/info/ans to share as I can't found one. Thanks!

Upvotes: 0

Views: 351

Answers (1)

Praveen
Praveen

Reputation: 902

Try using the below code in perl :

use strict;
use warnings;
use Spreadsheet::ParseXLSX;

my $parser   = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse('C:\namelist.xlsx');

if ( !defined $workbook ) {
 die $parser->error(), ".\n";
     }

my @name = ("john", "tommy", "ming", "ean");   
for my $worksheet ( $workbook->worksheets('Name List') ) {

    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;

        foreach my $result (@name)
          {
            my $string = $cell->value();
             if($result =~ m/$string/i)
               {  
                    my $codeCell = $worksheet->get_cell($row,1);
                    my $ageCell  = $worksheet->get_cell($row,2);
                    print "Name : $string\t";
                   if(defined $codeCell and $codeCell->value() ne "")
                     {
                          my $code = $codeCell->value(); 
                        print "Code : $code\t"; 
                        }
                    if(defined $ageCell and $ageCell->value() ne "")
                     {
                          my $age = $ageCell->value(); 
                        print "Age : $age\n"; 
                        }                
                  }

              }
          }
       }
   }

Upvotes: 2

Related Questions