in reply to Read excel column and compare with array

Hello snehit.ar,

Why don't you use the method rows from the same module Spreadsheet::Read, which actually does exactly what you want. See example bellow based on the data that you provide us.

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Spreadsheet::Read;

my $book = ReadData("test.xlsx");
my $sheet = $book->[1]; # first data sheet
my @rows = Spreadsheet::Read::rows ($book->[1]);
print Dumper \@rows;

__END__

$ perl test.pl
$VAR1 = [
          [
            'AA1',
            'BB1'
          ],
          [
            undef,
            undef
          ],
          [
            111,
            101
          ],
          [
            222,
            202
          ],
          [
            333,
            303
          ],
          [
            444,
            404
          ],
          [
            555,
            505
          ],
          [
            666,
            606
          ],
          [
            777,
            707
          ],
          [
            888,
            808
          ],
          [
            999,
            909
          ]
        ];

[download]

Update: Or if you do not want to retrieve and iterate over the rows to get the values that you want. You can get them directly like the example bellow:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Spreadsheet::Read;

my $book = ReadData("test.xlsx");
my $sheet = $book->[1]; # first data sheet
my @column = $sheet->{cell}[2]; # 2nd column, unformatted
print Dumper \@column;

__END__

$ perl test.pl
$VAR1 = [
          [
            undef,
            'BB1',
            undef,
            101,
            202,
            303,
            404,
            505,
            606,
            707,
            808,
            909
          ]
        ];

[download]

Update2: Alternative way of printing the Array of Arrays (@column).

use feature 'say';
foreach my $row (@column) {
    for (@$row) { say $_ if (defined) }
}

[download]

Hope this helps, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re^2: Read excel column and compare with array
by snehit.ar (Beadle) on Jul 17, 2017 at 09:17 UTC
    hello BR, Thanks for the replay. But it gives all excel rows,column. I want to display only column(BB1) and store in array . 
    $VAR1 = [
          [
            101
          ],
          [
            202
          ],
          [ 
            303
          ],
          [
            404
          ],
          [
            505
          ],
          [
         .....
        ];

    [download]
[reply]
[d/l]

      That's pretty trivial with map:

      #!/usr/bin/env perl 
use strict;
use warnings;
use Data::Dumper;

my @rows = (
          [
            'AA1',
            'BB1'
          ],
          [
            undef,
            undef
          ],
          [
            111,
            101
          ],
          [
            222,
            202
          ],
          [
            333,
            303
          ],
          [
            444,
            404
          ],
          [
            555,
            505
          ],
          [
            666,
            606
          ],
          [
            777,
            707
          ],
          [
            888,
            808
          ],
          [
            999,
            909
          ]
);

my @bb1 = map { $_->[1] } @rows;
print Dumper \@bb1;

      [download]
[reply]
[d/l]
        Thanks for help..
[reply]

      Hello again snehit.ar,

      Well you can iterate over the array of arrays and simply choose to keep or print the second value of the array (I am not going to create the foreach loop for you). A minimum amount of effort is required, read more here perldsc/ARRAYS OF ARRAYS. Regarding the column straight data read my update.

      Minor note, my username on the forum is thanos1983, BR stands for (Best Regards) :)

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]
        Hi thanos1983, I am sorry for your name. I have tired this ... 
        #!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Spreadsheet::Read;
use Spreadsheet::XLSX;
use Text::Iconv;
my $converter = Text::Iconv -> new ("utf-8", "windows-1251");
my $excel = Spreadsheet::XLSX -> new ('C:/SLB/Dashboard/DCSS_Applicati
+ons.xlsx', $converter);
my @DCSS_ss; 
foreach my $sheet (@{$excel -> {Worksheet}}) {

    printf("Sheet: %s\n", $sheet->{Name});

    $sheet -> {MaxRow} ||= $sheet -> {MinRow};

    foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {

    
    $sheet -> {MaxCol} ||= $sheet -> {MinCol};
    my $cell = $sheet -> {Cells} [$row] [2];
        s/^\s+|\s+$//g for $cell->{Val};
        
        if ($cell) {
         push @DCSS_ss, {
            SSRID =>$cell -> {Val}
            };
        }
    }
}

print Dumper \@DCSS_ss;

        [download]
        TY
[reply]
[d/l]
Re^2: Read excel column and compare with array
by snehit.ar (Beadle) on Aug 08, 2017 at 08:43 UTC
    Hi, I need to format my output of XL column : But not getting as expected ... 

    AR-AB-1111
AR-AB-23213
AR-AB-232
AR-AB-234
AR-AB-1
AR-AB-4
AR-AB-32
AR-AB-21
AR-AB-32

    [download]

    #!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use XML::XPath;
use Spreadsheet::Read;

my $book = ReadData("C:/SLB/Dashboard/DCSS_Applications.xlsx");
my $sheet = $book->[1]; # first data sheet
my @column = ($sheet->{cell}[3]); # 2nd column, unformatted
#@column =~ s/EAR-AA-//;
 
use feature 'say';
foreach my $row (@column) {
    for (@$row) {
        my $rowdata;
        #say $_ if (defined);
        $rowdata = @$row;
        say    $rowdata =~ s/AR-AB-//;
    }
}

    [download]

    Expected output :

    1111
23213
232
234
1
4
32
21
32

    [download]

[reply]
[d/l]
[select]

      Can you explain in English what this part of your code does?

      $rowdata =~ s/EAR-AA-//;

      [download]

      Your input data as shown will not match the replacement.

[reply]
[d/l]
        Sorry by mistake,I edited it .
[reply]

In Section Seekers of Perl Wisdom