Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Internal links in Excel

by anand_perl (Novice)
on Oct 01, 2008 at 12:00 UTC ( #714787=perlquestion: print w/replies, xml ) Need Help??
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++; }

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


    foreach my $link ( @links ) { my $url = "q{internal:'$link'!A1}"; $worksheet1->write_url ( $row,$col, $url,$label ); $row++; $col++; }


    foreach my $link ( @links ) { my $url = "internal:'$link'!A1"; $worksheet1->write_url ( $row,$col, $url,$label ); $row++; $col++; }

    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} + );

    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.

      Thanks Animator!!!

      Your solution works!!
        '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
Re: Internal links in Excel
by tmaly (Monk) on Oct 01, 2008 at 14:34 UTC
Re: Internal links in Excel
by Anonymous Monk on Oct 01, 2008 at 12:08 UTC

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://714787]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2018-02-22 09:53 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (289 votes). Check out past polls.