detect hyperlink in an excel file

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

I want to detect whether any hyperlink is associated with a cell value in an excel file.How can i accomplish this?

I used Spreadsheet::ParseExcel::Cell and its method $cell->type() but it returns only text for a hyperlink.Is there any other method to detect its style?

use Spreadsheet::XLSX;
use Spreadsheet::ParseExcel;

my $excel = Spreadsheet::XLSX -> new ('Sample.xlsx');

foreach my $sheet (@{$excel -> {Worksheet}}) 
{

    printf("Sheet: %s\n", $sheet->{Name});
    
    $sheet -> {MaxRow} ||= $sheet -> {MinRow};
    
     foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) 
    {
     
            $sheet -> {MaxCol} ||= $sheet -> {MinCol};
            
            foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}
+) 
            {
            
                    my $cell = $sheet -> {Cells} [$row] [$col];

                    if ($cell) 
                    {
                        printf("( %s , %s ) => %s\n", $row, $col, $cel
+l -> {Val});
                        my $type=$cell->type();
                        print $type."\n";
                    }

            }

    }

}
[download]

Comment on detect hyperlink in an excel file Download Code

Replies are listed 'Best First'.
Re: detect hyperlink in an excel file by roboticus (Chancellor) on Dec 24, 2010 at 14:42 UTC
mouleeshmichael: I don't know off the top of my head. But when I have this type of question about a module and I don't see the answer in the documentation, I frequently use Data::Dumper or equivalent to dump all the information about an object (a cell, in this case) to see if there are any hints. Sometimes the name of a key may lead you to a method that will do the trick for you. Be sure to compare the cell to one that's not a hyperlink to see what changes between the two. I hope this helps. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re: detect hyperlink in an excel file by Sinistral (Monsignor) on Dec 24, 2010 at 15:18 UTC
The property you want is Hyperlinks when using VBA, but I'm not sure what the Perl API equivalent might be. I found a VBA function that will return the link as well as a way to get a list of all hyperlinks. Perhaps these will be useful?	[reply]
Re: detect hyperlink in an excel file by davies (Monsignor) on Dec 24, 2010 at 15:51 UTC
First, I don't know what a hyperlink's "style" is, but that may be down to my ignorance on all matters HTML. Second, your code is using .xlsx, which implies to me Excel 2007 or later. I haven't used these enough to know the technicalities, and I don't have a copy myself. I can't find a direct way to do this in 2002/2003 VBA, as the `Sheet.Hyperlinks(index)` object seems extremely buggy. For me, it always returns the last one in the sheet, regardless of the index it is given. But it is possible to do something for that one hyperlink that may be good enough for what you want. AFAICT, `.Address` returns the formula and `.Range` returns the evaluated text. This won't deal with situations where the author has written a hyperlink something like `="http://"&"www.google.com"`, but that may be rare enough for you not to care, or it may be possible to sort out such anomalies manually. Assuming away all the problems above, my pseudocode would be: `use Win32::OLE; Create new instance of Excel Open target file read-only for (1..$wb->Sheets->count){ for ($sht->Hyperlinks->Count; 1; -1) { if ($hl->Range ne $hl->Address) { Do something interesting } $hl->Delete $wb->SaveAs(TempFile) $wb->Close; Open Tempfile } }` [download] Note that this is very pseudo, and will need you to do a lot of fleshing out. But it may work. Regards, John Davies Update: corrected bug in pseudocode	[reply] [d/l] [select]