Newbie
Newbie

Reputation: 35

How to loop through specific column and retrieve the specific row - Excel

I learned how to retrieve specific values by pointing at specific columns. But now I want to learn how to loop(loop through an array) through the specific column, if matched a specific variable(from array), it will retrieve specific columns and print it out.

Excel

Column1 Column2 Column3 Column4 Column5            Column6
1        2700K    80      NA    L130-2780003000W21 T3C27821L-01BA
2        3000K    80      NA    L130-3080003000W21 T3C30821L-01BA
3        3500K    80      NA    L130-3580003000W21 T3C35821L-01BA
4        4000K    80      NA    L130-4080003000W21 T3C40821L-01BA
5        5000K    80      NA    L130-5080003000W21 T3C50821L-01BA
6        5700K    80      NA    L130-5780003000W21 T3C57821L-01BA
7        6500K    80      NA    L130-6580003000W21 T3C65821L-01BA
8        2200K    80      NA    L130-2280002011001 T3422811L-01BA

Current Code

use strict;
use warnings;
use Spreadsheet::ParseXLSX;
my $parser   = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse('C:\testing\parts.xlsx');

 if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
# Array
my @list = ();

@list = ("T3C27821L-01BA","T3C35821L-01BA","T3C50821L-01BA","T3C65821L-01BA");

my $worksheet = ($workbook->worksheet('Sheet 1'));
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 ) {
        # Here will loop through the Column6 and retrieve
        # specific columns if matched(array matched Column6).
        foreach my $parts (@list) {
            # If matched, retrieve Column 2, Column 3, Column 5.
            # Here will print out the variable (from array) and
            # the columns that retrieved.
            print "Parts - $parts, $Column2, $Column3, Column5";
        }
    }
}

Expected - Use the list of variables in @list array and loop through Column6, if matched retrieve specific columns, Column2,Column3,Column5 and print it out.

Thanks for teaching!

Upvotes: 1

Views: 2064

Answers (1)

MrMcKizzle
MrMcKizzle

Reputation: 150

Take a look at Spreadsheet::ParseExcel. Their Synopsis contains the following code example that should get you started. :)

use strict;
use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');

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;

            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(),       "\n";
            print "Unformatted = ", $cell->unformatted(), "\n";
            print "\n";
        }
    }
}

To compare a cell to a value you would could use Perl's regex matching.

if($cell->value() =~ /2700K/) {
    print "Cell found!";
}

Cheers, MrMcKizzle

Upvotes: 1

Related Questions