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


Hi Monks

I have to add links to worksheets within a workbook

The first sheet of the Workbook has links to the remaining sheets of the Workbook

For example,the Workbook has sheets by name Sales,Maintainence,Expenses,Profits,Region.

I want to create links to these sheets in the first sheet

I am using the write_url method to do this

The following is the code i have written for it....
my $workbook = Spreadsheet::WriteExcel -> new ( "url.xls" );
my $row = 1,$col =1;
my @links = ( "Sales","Maintainence","Sales","Profits","Region" );

my $worksheet1 = $workbook->add_worksheet ( "Summary" );

my $worksheet2 = $workbook->add_worksheet ( "Sales" );
my $worksheet3 = $workbook->add_worksheet ( "Maintainence" );
my $worksheet4 = $workbook->add_worksheet ( "Expenses" );
my $worksheet5 = $workbook->add_worksheet ( "Profits" );
my $worksheet6 = $workbook->add_worksheet ( "Region" );

foreach my $link ( @links ) { 

my $url = "q{internal:'$link'!A1}";

$worksheet1->write_url ( $row,$col, $url,$label );
$row++;
$col++;
}

[download]

How can this be done?

Thanks in advance!!!

Replies are listed 'Best First'.
Re: Internal links in Excel
by Animator (Hermit) on Oct 01, 2008 at 14:43 UTC

    Change: 

    foreach my $link ( @links ) { 
  my $url = "q{internal:'$link'!A1}";

  $worksheet1->write_url ( $row,$col, $url,$label );
  $row++;
  $col++;
}

    [download]

    Into: 

    foreach my $link ( @links ) { 
  my $url = "internal:'$link'!A1";

  $worksheet1->write_url ( $row,$col, $url,$label );
  $row++;
  $col++;
}

    [download]

    I'm guessing your confusing comes from looking at the examples in Spreadsheet::WriteExcel and not knowing q() and qq().

    The examples from Spreadsheet::WriteExcel: 

        $worksheet->write_url('A6',  'internal:Sheet2!A1'                 
+  );
    $worksheet->write_url('A7',  'internal:Sheet2!A1',   $format      
+  );
    $worksheet->write_url('A8',  'internal:Sheet2!A1:B2'              
+  );
    $worksheet->write_url('A9',  q{internal:'Sales Data'!A1}          
+  );

    [download]

    I'm guessing the $worksheet->write_url('A9',  q{internal:'Sales Data'!A1}); confused you but it means exactly the same as : $worksheet->write_url('A9',  'internal:\'Sales Data\'!A1');

    In essence: q(foo) is the same 'foo' which is the same as q{foo} (that is: single quotes but you get to choose the delimiter).
    qq(foo) is the same 'foo' which is the same as qq{foo} (that is: double quotes but you get to choose the delimiter).

    I tested this by creating an xls file via Spreadsheet::WriteExcel and opening it in OpenOffice. Don't have access to Microsoft Excel at the moment of this writing.

[reply]
[d/l]
[select]

      Thanks Animator!!!

      Your solution works!!
[reply]
        'BATSOPLINK'!A1 this is the output I get when I try to print it via my $url = "internal:'$val$i'!A1"; Want to get rid of "!A1" and just want to print the value which refers to the cell
[reply]
Re: Internal links in Excel
by tmaly (Monk) on Oct 01, 2008 at 14:34 UTC
[reply]
Re: Internal links in Excel
by Anonymous Monk on Oct 01, 2008 at 12:08 UTC
[reply]

Back to Seekers of Perl Wisdom