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

Hi, I'm writing a general utility (distributed as a PAR app) that converts an Excel spreadsheet to an HTML table without the gastly formatting that Excel's export feature uses. I'm using Win32::OLE.

I'm missing one piece of the puzzle. As I parse the spreadsheet, how do I test for the presence in a hyperlink in a given cell so I can grab the url via the hyperlink object when I've found one? I'd like to avoid the user having to specify in advance which columns have hyperlinks, especially since they may not be so simply organized.

I've reviewed everything I could find in the archives. Of special interest to others might be Using Win32::OLE and Excel - Tips and Tricks and an excellent summary of nodes at Index of Spreadsheet FAQs. All the examples I've come across refer to parsing links when you know you have one.

Is there a cleaner solution than trying to access a hyperlink object in each cell and skipping those that throw an exception on the attempt?

Thanks, Sergej
  • Comment on Testing for Excel hyperlinks with Win32::OLE

Replies are listed 'Best First'.
Re: Testing for Excel hyperlinks with Win32::OLE
by davidrw (Prior) on Sep 27, 2005 at 15:42 UTC
    For worst-case, your solution doesn't seem too bad .. Might be able to find a better way by poking around with recording a macro and seeing what code comes out ... For example, I just made this (actions i took are in comments): 
    Sub Macro1()
' Right-click a cell
    Range("C10").Select
' From the context menu, click "Hyperlink...", and in there fill in th
+e dialog
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://blah.example.com", TextToDisplay:="foo"
    Range("C10").Select
' Right-click cell again, do "Edit Hyperlink..." and s/foo/bar/
    Selection.Hyperlinks(1).TextToDisplay = "bar"
End Sub

    [download]
    But what's of interest here is it seems that Excel keeps track of all the hyperlinks in the ActiveSheet.Hyperlinks collection, so you could hash that up on it's Anchor property so you know what cells all of the hyperlinks are in ...
    Update: I was able to access that collection, but i can't seem to get the Anchor property from it.. 
    use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 3; # Die on Errors.
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open('C:\Documents and Settings\David\hy
+perlinks.xls'); 
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate();    
foreach ( 1.. $Sheet->Hyperlinks->Count() ){
  print "\n";
  warn $Sheet->Hyperlinks($_)->TextToDisplay;
  warn $Sheet->Hyperlinks($_)->Address;
#  warn $Sheet->Hyperlinks($_)->Anchor;  # This errors with "Member no
+t found"
}
$Book = $Excel->Workbooks->Close();

    [download]
[reply]
[d/l]
[select]
      It doesn't seem like quite the right approach, but I was able to locate the cell containing the hyperlink using $Sheet->Hyperlinks($_)->Range->Column and $Sheet->Hyperlinks($_)->Range->Row.

      --
      I'd like to be able to assign to an luser

[reply]
[d/l]
[select]
        Ah .. So you could do this at the beginning: 
        my $cellsWithHyperlinks = {};
$cellsWithHyperlinks->{ $_->Column }->{ $_->Row }++
  for map { $Sheet->Hyperlinks($_)->Range } 1 .. $Sheet->Hyperlinks->C
+ount();

        [download]
        And then while you're looping over the cells to write out the HTML: 
        foreach my $col ( 1 .. 10 ){
  warn "Col # $col has hyperlinks somewhere" if exists $cellsWithHyper
+links->{$col};
  foreach my $row ( 1 .. 10 ){
    warn "(Col,Row)=($col,$row) has a hyperlink" if exists $cellsWithH
+yperlinks->{$col}->{$row};
  }
}

        [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom