V. Tej V. Tej - 3 months ago 22
Perl Question

perl script to read all the elements in particular column of an excel file

I wrote a perl script to read a particular cell value of a column in an excel file.

use strict;
use warnings;
use feature 'say';
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;

my $workbook = ReadData ("C::/Users/Tej/Work.xlsx");
print $workbook->[6]{D4} . "\n";


All good till here. I want to write the logic to read all cell values under column D until there are values in that column. Can anyone help me with it.

Thanks

Answer

Suppose my data is:

enter image description here

One way is to do what you want (since 'D' corresponds to 4th column):

$ cat a.pl
use strict;
use warnings;
use feature 'say';
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;
use Data::Dumper;

my $workbook = ReadData ("/tmp/file.xls", parser => "xls");
print Dumper($workbook->[1]{cell}[4]);
foreach my $cell (@{$workbook->[1]{cell}[4]}) {
   if ($cell) {
       print $cell . "\n";
   }
}


$ perl a.pl
$VAR1 = [
          undef,
          'grid',
          1115,
          1512,
          212
        ];
grid
1115
1512
212

Another way is to use Spreadsheet::BasicReadNamedCol:

$ cat a.pl
use strict;
use warnings;
use feature 'say';
use Spreadsheet::BasicReadNamedCol;
use Data::Dumper;
my @columnHeadings = (
    'grid',
);
my $workbook = new Spreadsheet::BasicReadNamedCol("/tmp/file.xls");
$workbook->setColumns(@columnHeadings);
while (my $data = $workbook->getNextRow()) {
    print "@{$data}[0]\n";
}
$ perl a.pl
grid
1115
1512
212
Comments