http://qs321.pair.com?node_id=419326

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

I am using Spreadsheet::WriteExcel. I need to add many worksheets to a workbook. I need either:
  1. Some way to reorder the worksheets alphabetically by name.
  2. A method of telling the add_worksheet method where to insert the new worksheet (it defaults to after the last sheet)
I tried selecting an existing worksheet before using add_worksheet, thinking this would insert the new worksheet after the selected one, but this did not work: 
use strict;
use Spreadsheet::WriteExcel;
my $workbook  = Spreadsheet::WriteExcel->new("foo.xls");
my $worksheet1 = $workbook->add_worksheet("alfa");
my $worksheet2 = $workbook->add_worksheet("charlie");
# Select "alfa" thinking "bravo" would insert after
# it... it does not work.  Final order remains
# "alfa, charlie, bravo", not "alfa, bravo, charlie"
# as desired.
$worksheet1->select();
my $worksheet3 = $workbook->add_worksheet("bravo");
$workbook->close();

[download]

Any insights, perhaps a different module? Thanks.
-- Zeno - Barcelona Perl Mongers http://barcelona.pm.org http://www.javajunkies.org

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel: How to change order of worksheets?
by dragonchild (Archbishop) on Jan 04, 2005 at 18:09 UTC
    Upon sourcediving Spreadsheet::WriteExcel::Workbook, add_worksheet() hardcodes the index to the size of the {_worksheets} arrayref. So, #2 won't work.

    However, #1 may be able to work, if you're willing to break encapsulation. But, I'll let jmcnamara show you the code. The names and indices are used all over the place. It's not enough to reorder the {_worksheets} arrayref.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]

      Thanks for the insight. I figured that the reason the original module would not allow specifying where to insert the new worksheet was because it would be a very complex modification.

      Great sig, by the way.

      -- Zeno - Barcelona Perl Mongers http://barcelona.pm.org http://www.javajunkies.org
[reply]
Re: Spreadsheet::WriteExcel: How to change order of worksheets?
by friedo (Prior) on Jan 04, 2005 at 18:13 UTC
    Perhaps it would be easier to sort your data alphabetically before creating the worksheets. Is that an option?
[reply]
[reply]

        Perhaps it would be easier to sort your data alphabetically before creating the worksheets.

        This is the method that I would suggest as well.

        Although it would be possible to do a behind the scenes reordering of the worksheets it wouldn't be clean and it would invalidate any formulas that referred to other worksheets.

        --
        John.

[reply]
Re: Spreadsheet::WriteExcel: How to change order of worksheets?
by clscott (Friar) on Jan 04, 2005 at 18:18 UTC
    Do you know the names of the workbooks in advance so you can sort on that? You can store your worksheets in a hash so you can reference them properly later to addataand so on. 
    my %worksheets;
foreach my $w (sort @ws_names_from somewhere){
   $worksheets{$w} = $workbook->add_worksheet( $w );
}

    [download]
    --
    Clayton
[reply]
[d/l]

      I think this may be the simplest approach. Actually, I need to parse through some of the names to avoid creating inappropriate sheets (the data gets dirty and I end up with strange sheet names like "null") sometimes. Using your hash would allow this better. Thanks.

      -- Zeno - Barcelona Perl Mongers http://barcelona.pm.org http://www.javajunkies.org
[reply]
        Hmmm... Was anyone else wondering what this spreadsheet was about as they read "avoid creating inappropriate sheets (the data gets dirty"?

        I started thinking he was cataloguing "P&L of movies by classification" and had to suppress X-rated titles.

        Too bad the rest of the sentence took all the fun out of it... :)


        Mike
[reply]

Back to Seekers of Perl Wisdom