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

First off, I'm new to Perl so the odds of me doing something stupid or missing something obvious just went up enormously. That being said, I'm trying to write a package to use in conjunction with Spreadsheet::WriteExcel. The idea is to have a bunch of canned format statements that I can call as needed without having to plug in a couple of pages of code every time I write a new script...and, of course, if I want to make a format change later on, I only have to modify one file instead of all of them. The package code reads as follows: 
#!/usr/bin/perl -w

package                     StdExcelFormats;

use strict;
use warnings;

BEGIN {
    use Exporter;
    our  (@ISA, @EXPORT, %EXPORT_TAGS, $VERSION);

    use                     Exporter;
    use Spreadsheet::WriteExcel;

    @ISA            = qw(Exporter);
    @EXPORT         = qw(
                            %format_h1
                            %format_h2
                            %format_colhead
                            %format_norml
                            %format_normr
                            %format_normc
                            %format_boldl
                            %format_boldr
                            %format_boldc
                            %format_url
                            @landscape_lgl
                            %landscape_ltr
                        );
    %EXPORT_TAGS    = ( );
    $VERSION        = 1.00;

}

#
# Define formats
#

# Large Heading
our %format_h1 = (
                        bold            => 1,
                        size            => 14,
                        color           => 'black',
                        align           => 'left',
                    );

# <---snip (more hashes - variations on a theme)-->

# Page Layout - Legal, Landscape
our @landscape_lgl = (
                        '$worksheet->set_paper(5)',
                        '$worksheet->set_landscape()',
                        '$worksheet->set_margins_LR(.50)',
                        '$worksheet->set_center()',
                        '$worksheet->set_hide_gridlines()',
                        '$worksheet->set_fit_to_pages()',
                    );
1;

[download]
Inside my script, it have the following: 
#!/usr/bin/perl -w -I/(path to package)
use diagnostics;
use DBI;
use Spreadsheet::WriteExcel;
use StdExcelFormats;

# Create a new Excel Workbook
my $workbook = Spreadsheet::WriteExcel->new($WorkbookName);

# Add a worksheet
my $worksheet = $workbook->addworksheet($rtr);

my $format_h1       = $workbook->add_format(%format_h1);
my $format_h2       = $workbook->add_format(%format_h2);
my $format_colhead  = $workbook->add_format(%format_colhead);
my $format_norml    = $workbook->add_format(%format_norml);
my $format_normr    = $workbook->add_format(%format_normr);
my $format_normc    = $workbook->add_format(%format_normc);
my $format_boldl    = $workbook->add_format(%format_boldl);
my $format_boldr    = $workbook->add_format(%format_boldr);
my $format_boldc    = $workbook->add_format(%format_boldc);
my $format_url      = $workbook->add_format(%format_url);

# <---snip--->

sub write_body_excel{
    print "sub write_body_excel\n" if $DEBUG;
    print "rid = ",$rid,"iid = ",$interface,"\n" if $DEBUG;
    &make_url;
    $worksheet->write_url($xlrow, 0, $url, $name, %format_url);
    $worksheet->write($xlrow, 1, $description, $format_norml);
    $worksheet->write($xlrow, 2, $bytesin, $format_normr);
    $worksheet->write($xlrow, 3, $bytesout, $format_normr);
    $worksheet->write($xlrow, 4, $ratein, $format_normr);
    $worksheet->write($xlrow, 5, $rateout, $format_normr);
    $worksheet->write($xlrow, 6, $utilin, $format_normr);
    $worksheet->write($xlrow, 7, $utilout, $format_normr);
    $worksheet->write($xlrow, 8, $maxratein, $format_normr);
    $worksheet->write($xlrow, 9, $maxrateout, $format_normr);
    $worksheet->write($xlrow, 10, $maxutilin, $format_normr);
    $worksheet->write($xlrow, 11, $maxutilout, $format_normr);
    $worksheet->write($xlrow, 12, $nfratein, $format_normr);
    $worksheet->write($xlrow, 13, $nfrateout, $format_normr);
    $worksheet->write($xlrow, 14, $nfutilin, $format_normr);
    $worksheet->write($xlrow, 15, $nfutilout, $format_normr);
    $xlrow += 1;
}

[download]
All this works fine. My problem is with these lines. Since I can't pass a hash for this, I'm hoping to step through an array of the format statements I want to use. 

foreach $statement (@landscape_lgl) {
    $worksheet->$statement;            # line 79 - see error below
    print "$worksheet->$statement","\n";
}

[download]
Which gives me... 

Can't locate object method "set_paper(5)" via package
        "Spreadsheet::WriteExcel::Worksheet" at ./test.pl line 79 (#1)
+<readmore>
    (F) You said to do (or require, or use) a file that couldn't be
    found. Perl looks for the file in all the locations mentioned in @
+INC,
    unless the file name included the full path to the file.  Perhaps 
+you
    need to set the PERL5LIB or PERL5OPT environment variable to say w
+here
    the extra library is, or maybe the script needs to add the library
+ name
    to @INC.  Or maybe you just misspelled the name of the file.  See
    perlfunc/require and lib.

Uncaught exception from user code:
        Can't locate object method "set_paper(5)" via package "Spreads
+heet::WriteExcel::Worksheet" at ./test.pl line 79.

[download]

perl -V gives

Summary of my perl5 (revision 5.0 version 8 subversion 0) configuratio
+n:
  Platform:
    osname=solaris, osvers=2.8, archname=sun4-solaris
    uname='sunos capacity1.telcove.com 5.8 generic_108528-19 sun4u spa
+rc sunw,ultra-250 '
    config_args='-de'
    hint=recommended, useposix=true, d_sigaction=define
    usethreads=undef use5005threads=undef useithreads=undef usemultipl
+icity=undef
    useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=undef uselongdouble=undef
    usemymalloc=n, bincompat5005=undef
  Compiler:
    cc='gcc', ccflags ='-fno-strict-aliasing -I/usr/local/include -D_L
+ARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64',
    optimize='-O',
    cppflags='-fno-strict-aliasing -I/usr/local/include'
    ccversion='', gccversion='2.95.2 19991024 (release)', gccosandvers
+='solaris2.8'
    intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321
    d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=1
+6
    ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
+ lseeksize=8
    alignbytes=8, prototype=define
  Linker and Libraries:
    ld='gcc', ldflags =' -L/usr/local/lib '
    libpth=/usr/local/lib /usr/lib /usr/ccs/lib
    libs=-lsocket -lnsl -ldl -lm -lc
    perllibs=-lsocket -lnsl -ldl -lm -lc
    libc=/lib/libc.so, so=so, useshrplib=false, libperl=libperl.a
    gnulibc_version=''
  Dynamic Linking:
    dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' '
    cccdlflags='-fPIC', lddlflags='-G -L/usr/local/lib'


Characteristics of this binary (from libperl):
  Compile-time options: USE_LARGE_FILES
  Built under solaris
  Compiled at Apr 25 2003 11:13:22
  @INC:
    /usr/local/lib/perl5/5.8.0/sun4-solaris
    /usr/local/lib/perl5/5.8.0
    /usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris
    /usr/local/lib/perl5/site_perl/5.8.0
    /usr/local/lib/perl5/site_perl/5.6.0
    /usr/local/lib/perl5/site_perl
    .

[download]

So...what stupidly obvious thing am I missing here? Can I even do this? And if so, how?

Jack

Edit by theorbtwo: Corrected/added <readmore> tags.

Replies are listed 'Best First'.
Re: Problem with an array in a package
by ysth (Canon) on Jan 14, 2004 at 16:37 UTC
    Try something like: 
    # Page Layout - Legal, Landscape
our %landscape_lgl = (
                        set_paper => [5],
                        set_landscape => [],
                        set_margins_LR => [.50],
                        set_center => [],
                        set_hide_gridlines => [],
                        set_fit_to_pages => []
                    );

    [download]
    and 
    foreach my $statement (keys(%landscape_lgl)) {
    $worksheet->$statement(@{$landscape_lgl{$statement}}),
    print "$worksheet->$statement","\n";
}

    [download]
    This will not preserve the order of your statements in the %landscape_lgl hash. If you need to do that, have a separate array: 
    @landscape_lgl = qw/set_paper set_landscape.../

    [download]
    And use that in place of the keys() part.

    Alternatively, keep your original loop, but instead of the $worksheet->$statement call in the foreach body, use 

    eval $statement;
warn "something went wrong in $statement: $@" if $@;

    [download]
    All code untested.
[reply]
[d/l]
[select]
      Many thanks!!! eval $statement; did the trick.
[reply]
[d/l]
Re: Problem with an array in a package
by Abigail-II (Bishop) on Jan 14, 2004 at 16:27 UTC
    It looks like @landscape_lgl contains a value set_paper(5), and it's unlikely you have made a method with that name in the package. So Perl will complain. The "explaination" given by diagnostics is confusing - ignore it.

    Abigail

[reply]
      Sorry, my bad. I previewed around 6 times and STILL managed to plug in the wrong code. @landscape_lgl actually reads... 
      our @landscape_lgl = (
                        'set_paper(5)',
                        'set_landscape()',
                        'set_margins_LR(.50)',
                        'set_center()',
                        'set_hide_gridlines()',
                        'set_fit_to_pages()',
                    );

      [download]
      Line 79 doesn't just produce the error. It plain doesn't work.
[reply]
[d/l]
        Indeed. And that's the problem. You are trying to call a method named set_paper(5), and that method doesn't exist. It's not going to call set_paper with argument 5.

        Abigail

[reply]

Back to Seekers of Perl Wisdom