Spreadsheet:: ParseExcel in PERL query

Question

I am writing a PERL code to pick values from specific rows of a particular excel sheet. I am using the Spreadsheet::ParseExcel module for this purpose. I have written this code as of now

use Spreadsheet::ParseExcel::FmtDefault;
use Spreadsheet::ParseExcel;

      my $parser   = Spreadsheet::ParseExcel->new();

        #my $name = <STDIN>;
  die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;
      my $workbook = $parser->parse($ARGV[0]);
  my @values;
      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;
                $cell->value();
          my $cell_type = $cell->{Type};
                if ($cell_type =~/Numeric/)
              {
                push @values, $cell->unformatted();
      }
            }
        }
    }

I am able to pick all the numeric values in a particular excel sheet with this particular code, but I would like to tweak the code so that it could pick up numeric values in specific columns as per the users needs (eg: all the numeric values in row B or row C). How do I go about tweaking my code to make that possible or are there simpler modules available in which the range (eg B2 - B22) can be specified. Any help is appreciated.

So you need B column values from 2nd row to 22nd row or in generic to get values froma particular column ? — Praveen, Nov 05 '14 at 06:34
@Praveen Generic code to get values from a particular column that the user needs — The Last Word, Nov 05 '14 at 06:39

Praveen · Accepted Answer · 2014-11-05T09:48:03.973

Check with the below perl code. Excel sheet row and column values start with (0,0). So accordingly enter your column and row numbers repectively to get your desired output.

Code:

use strict;
use warnings;
use Spreadsheet::ParseXLSX;

my $parser   = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse('C:\Users\Perl\test1.xls');

if ( !defined $workbook ) {
 die $parser->error(), ".\n";
     }
my $worksheet = $workbook->worksheet(0);
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

COLUMNS: print "Select the column number required from Excel\n";
my $column = <STDIN>;
chomp($column);
unless ($column =~/[0-9]+/){
print "Bummer!! Please Enter a number\n";
goto COLUMNS;
}

if($column gt $col_max){
     print "No such columns defined in the Excel";
     goto END;
      }

ROWS:print "Select number of rows required from column $column:\n";
my $rows = <STDIN>;
chomp($rows);
my $count = $rows+1;
unless ($rows =~/[0-9]+/){
print "Bummer!! Please Enter a number\n";
goto ROWS;
}

  ROW_LABEL: if($rows le $row_max)
    {  
    print "\nThe $count row values from column $column are:\n"; 
    for my $row ( $row_min .. $rows ) {

        my $cell = $worksheet->get_cell( $row, $column );
        next unless $cell;
        my $result = $cell->value();        
        print $result . "\n";
          }
       }
    else
     {   
      $rows = $row_max;
      goto ROW_LABEL ;
         }       
 END:

Spreadsheet:: ParseExcel in PERL query

1 Answers1