Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

How can I obtain the names of all worksheets in an Excel workbook?

I do know how to obtain the number of worksheets, but not their names. 
my $num_sheets= $new_book->Worksheets->Count;
print "\nNumber of excel worksheets $num_sheets\n";

[download]
Your help is highly appreciated.....Thanks.

Replies are listed 'Best First'.
Re: List of worksheets in an Excel workbook
by Limbic~Region (Chancellor) on Jun 07, 2005 at 23:21 UTC
    Anonymous Monk,
    It really helps to specify what module you are using. I will assume Spreadsheet::ParseExcel. There are examples in TFM that you should be able to modify without much trouble: 
    for my $wrksheet ( @{$new_book->{Worksheet}} ) {
    print $wrksheet->{Name}, "\n";
}

    [download]

    Cheers - L~R

[reply]
[d/l]
Re: List of worksheets in an Excel workbook
by bart (Canon) on Jun 08, 2005 at 00:01 UTC
    It appears to me that you're using Win32::OLE. Well, you can get at the sheet itself by specifying an index (apparently: 1-based). One of the properties of a worksheet is, of course, the name. 
    my $Book = $Excel->Workbooks->Open( $filename );      # open the file
my $sheetcount = $Book->Worksheets->Count;
foreach my $i (1 .. $sheetcount) {
    printf "Sheet #%d: %s\n", $i, $Book->Worksheets($i)->Name;
}

    [download]
[reply]
[d/l]
Re: List of worksheets in an Excel workbook
by bmann (Priest) on Jun 08, 2005 at 00:46 UTC
    If you are using Win32::OLE, you can use its "in" function to enumerate any OLE collection:

    #!perl
use warnings;
use strict;
use Win32::OLE qw(in); # import "in"

my $excel = Win32::OLE->new('Excel.Application', 'Quit');  
$excel->{Visible} = 1;
# q:\s\test.xls is a new worksheet with three sheets
my $xls = $excel->Workbooks->Open('q:\s\test.xls'); 

foreach my $sheet ( in $xls->Worksheets ) {
    print $sheet->Name, $/;
}
__END__
Output:
Sheet1
Sheet2
Sheet3

    [download]

    This also works with cells in a range, open workbooks, etc.

[reply]
[d/l]
      This is what I tried 
      use strict;
use warnings;
use Win32;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn =2;           

my $excel = Win32::OLE->new('Excel.Application', 'Quit');  
$excel->{Visible} = 1;

my $xls = $excel->Workbooks->Open('c:/work/report1.xls'); 

foreach my $sheet ( in $xls->Worksheets ) 
{
    my $current_sheet = $sheet->Name, $/;
    print "Processing => $current_sheet\n"; 
    
}

      [download]
      the correct result were returned with the following warning 
      
Useless use of a variable in void context at C:\Perl\tool6.pl line 16.
Processing => Midrange Servers
Processing => Add-Del April 2005
Processing => Add-Del FEB 2005
Processing => Add-Del JAN 2005
Processing => Add-Del DEC 2004
Processing => Add-Del NOV 2004
Processing => Add-Del OCT 2004
Processing => Add-Del Sep 2004

      [download]
      Nor sure what the warning is for! and what does /$ do? And if there is a way to obtain the names of the sheets directly rather than using a temp variable?

      Thanks
[reply]
[d/l]
[select]
        look in perldoc perlvar for $/ -- it's the input record separator. So print $sheet->Name, $/; is just a fancier, more generic way to do print $sheet->Name, "\n";

        what's wrong with using a temp variable? You can always turn the loop into a map and get an array: 
        # Loop method from above:
foreach my $sheet ( in $xls->Worksheets ) {
    print $sheet->Name, $/;
}

# one line to get array of names:
my @names = map { $_->Name } in $xls->Worksheets;

        [download]

        As for the warning, it's because you have my $current_sheet = $sheet->Name, $/; but i think it was intended as: my $current_sheet = $sheet->Name . $/;
[reply]
[d/l]
[select]
        davidrw answered what $/ is, the input record separator. I just used it to print the newline. I most likely wouldn't want the newline if I were assigning the sheet name to a variable.

        Since $/ defaults to "\n", I use it in one-liners so I don't have to think about shell quoting issues (I use both Win32's cmd and bash regularly), and it looks like it's crept into my test scripts too. I would not use it as a newline in production code.

        About the warning, my $current_sheet = $sheet->Name, $/ is parsed as 

        ((my $current_sheet = $sheet->Name), $/);

        [download]
        The comma operator in scalar context evaluates the left side, throws it away and returns the right side, a bare "$/". Look in perldiag for "Useless use of %s in void context".
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom