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.

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]

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]

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!

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]

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.

Where does your code have a problem?

Are you sure that column 2 corresponds with column BB?

err is Use of uninitialized value in foreach loop entry. Yes column B is BB1.

This means that ->{maxrow} is undefined. Are you certain that you want to use the second spreadsheet? You assign my $sheet = $read->[1] but then never use that.