CODEWITHSUNDEEP
perlexcelspreadsheet
Nikhil Jain
Nikhil Jain

Reputation: 8352

How can I add hyperlinks in a array_ref in Spreadsheet::WriteExcel?

I want to write an array reference into Excel like 

@eec =  ( 
                ['maggie', 'milly', 'molly', 'may'  ], 
                [13,       14,      15,      16     ], 
                ['shell',  'star',  'crab',  'stone'] 
         ); 

$worksheet->write_col('A1', \@eec);

But my problem is that I want to embed hyper-links also.

I know how to add hyperlink to a particular lable like, 

$worksheet->write('A4',  'http://www.perl.com/', 'Perl', $format);

but what should I do to show hyperlink let say on label 'may'?

for more detail see Example of how to use the Spreadsheet::WriteExcel module to write 1D and 2D arrays of data.

Upvotes: 0

Views: 1614

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

The write() method in Spreadsheet::WriteExcel is a syntactic wrapper for a number of other methods.

In general if it doesn't do what you want there are two remedies: use the wrapped method that you require explicitly or modify the behaviour of the write() method to suit your needs.

If the first case you just need to unroll the implicit loop that write() gives you for nested data. Something like this:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $workbook  = Spreadsheet::WriteExcel->new( 'example.xls' );
my $worksheet = $workbook->add_worksheet();


my @eec = (
    [ 'maggie', 'milly', 'molly', 'may'   ],
    [ 13,       14,      15,      16      ],
    [ 'shell',  'star',  'crab',  'stone' ]
);


my $row = 0;

for my $aref ( @eec ) {

    my $col = 0;

    for my $token ( @$aref ) {

        if ( $token eq 'may' ) {
            $worksheet->write_url( $row, $col, 'http://foo.com', $token );
        }
        else {
            $worksheet->write( $row, $col, $token );
        }

        $col++;
    }

    $row++;
}

__END__

In the second case you can modify write() by using the add_write_handler() method. Have a look at the documentation and examples. This is in the slightly advanced category so if you are new to Spreadsheet::WriteExcel just use the simpler method above.

Upvotes: 1

w.k
w.k

Reputation: 8386

AFIU in Spreadsheet::WriteExcel has no methods for formatting URLs for later writing, so simplest way is add URLs without labels inside arrays as that:

@eec =   (
                ['maggie', 'milly', 'molly', 'http://www.perl.com/'  ],
                [13,       14,      15,      16     ],
                ['shell',  'star',  'crab',  'stone'],
            );

Or write over specific cell with $worksheet->write_url method.

Upvotes: 0

Related Questions