Link excel worksheets using perl

Question

I am trying to write a perl script which links a cell value in say 1st worksheet to a value in 2nd worksheet using perl. I tried using the following modules which were available in perl :-

1. Excel::Writer::XLSX

2. Spreadsheet::WriteExcel

Can anyone help me with a simple perl script that does this.

Example :-

Worksheet1

---

Worksheet2

So, In the above example the Rules Column Items R1, R2, R3 in Worksheet1 should be hyperlinks that takes us to Rules Column Items R1, R2, R3 respectively when clicked.

Can someone show me how it can be done using perl or atleast push me into the right direction on how to use the module Spreadsheet::WriteExcel or Excel::Writer::XLSX for this problem.

Please help me out..!!

Answer 1

Using Excel::Writer::XLSX you would use the write_url followed by write_formula method. Here is an example:

#!/usr/bin/perl
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'test.xlsx' );
my $worksheet1 = $workbook->add_worksheet('Worksheet1');
my $worksheet2 = $workbook->add_worksheet('Worksheet2');

$worksheet2->write_string(0,0, 'Rules'); 
$worksheet2->write_string(1,0, 'R1');
$worksheet2->write_string(2,0, 'R2');
$worksheet2->write_string(3,0, 'R3');

my $format = $workbook->add_format( color => 'blue', underline => 1 );
$worksheet1->write_url( 1,1,  'internal:Worksheet2!A2', $format);
$worksheet1->write_formula( 1, 1, '=Worksheet2!A2' );