如何循环特定的列并检索特定的行- 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.

期望-使用@list数组中的变量列表并通过Column6进行循环，如果匹配的检索特定列，Column2,Column3,Column5并打印出来。

Thanks for teaching!

谢谢教授!

1 个解决方案

#1

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

查看电子表格:ParseExcel。它们的概要包含以下代码示例，应该可以帮助您入门。:)

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.

要将单元格与值进行比较，可以使用Perl的regex匹配。

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

Cheers, MrMcKizzle

欢呼,MrMcKizzle

#1