CODEWITHSUNDEEP
excelperl
V. Tej
V. Tej

Reputation: 81

perl script to read an xlsx file(which has many sheets) using the sheet name

I am trying to write a perl script which reads an excel file(which has many sheets in it) using the sheet name.

I know how to access a particular sheet of the excel file using the sheet number, but not sure how to read it using sheet name. Any help provided is highly appreciated.

Below is the code I wrote to access the sheet using sheet number:

my $Sheet_Number = 26;
my $workbook = ReadData("<path to the excel file>");

for (my $i =2; $i<$limit; $i++){
    my $cell = "A" . $i;
    my $key_1 = $workbook->[$Sheet_Number]{$cell};
}

Thanks

----Edit----

I want to open the particular sheet within the Excel file by using the sheet name. And then read the data from that particular sheet. The name of the sheet will be entered by the user while running the script from the command line arguments.

Below is the code that I am using after getting suggested answers for my earlier question: 

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse("$path");
my $worksheet;
if ($worksheet->get_name() eq "$Sheet_Name"){
for (my $i =2; $i<$limit; $i++){
    my $cell = $worksheet->get_cell($i,"A");
    my $value = $cell->value();
    push @array_keys, $value;
    }
}

I want to read the values of Column A of the particular sheet and push it into an array.

$Sheet_Name : It is the name of the sheet which is entered by the user as cmd line arg.
$path : It is the complete path to the Excel file

Error Message: Can't call method "get_name" on an undefined value at perl_script.pl (The error points to the line where the if-condition is used.)

Thanks for the help.

-----EDIT----

Anyone, with any leads on this post, please post your answer or suggestions. Appreciate any responses.

Thanks

Upvotes: 0

Views: 3890

Answers (1)

Hambone
Hambone

Reputation: 16377

The get_name() method of the worksheet object, in conjunction with Perl's grep command should get you this:

my ($worksheet) = grep { $_->get_name() eq 'Sheet2' } $workbook->worksheets();

This would be an un-golfed version of the same:

my $worksheet;
foreach $worksheet ($workbook->worksheets()) {
  last if $worksheet->get_name() eq 'Sheet2';
}

Assuming there is a match... if not, I guess my un-golfed version would give you the last worksheet if there was no match.

-- Edit --

I made assumptions and -- you certainly do need to first call the method to load the workbook:

use strict;
use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('/var/tmp/foo.xls');

Then the code above should work.

Upvotes: 1

Related Questions