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

Win32 Perl and writing to Excel Workbooks

by talwyn (Monk)
on Dec 27, 2002 at 23:40 UTC ( [id://222646]=perlquestion: print w/replies, xml ) Need Help??

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

I'm using Win32::ODBC. Everything seems to work fairly well, However I was
wondering if anyone knew how to insert data in excel
that excel will recognize as a hyperlink.

Appreciate any help.

  • Comment on Win32 Perl and writing to Excel Workbooks

Replies are listed 'Best First'.
Re: Win32 Perl and writing to Excel Workbooks
by tachyon (Chancellor) on Dec 28, 2002 at 01:43 UTC

    You will need to RTFS of Spreadsheet::Writeexcel::Worksheet which shows you exactly what undocumented features you need to access :-) to make it work. Here is the relevant code: 

    ######################################################################
+#########
#
# write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)
#
# This is the more general form of write_url(). It allows a hyperlink 
+to be
# written to a range of cells. This function also decides the type of 
+hyperlink
# to be written. These are either, Web (http, ftp, mailto), Internal
# (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1').
#
# See also write_url() above for a general description and return valu
+es.
#
sub write_url_range {

    my $self = shift;

    # Check for a cell reference in A1 notation and substitute row and
+ column
    if ($_[0] =~ /^\D/) {
        @_ = $self->_substitute_cellref(@_);
    }

    # Check the number of args
    return -1 if @_ < 5;

    # Reverse the order of $string and $format if necessary.
    local @_ = @_; # Protect the callers args
    ($_[5], $_[6]) = ($_[6], $_[5]) if ref $_[5];

    my $url = $_[4];

    # Check for internal/external sheet links or default to web link
    return $self->_write_url_internal(@_) if $url =~ m[^internal:];
    return $self->_write_url_external(@_) if $url =~ m[^external:];
    return $self->_write_url_web(@_);
}



######################################################################
+#########
#
# _write_url_web($row1, $col1, $row2, $col2, $url, $string, $format)
#
# Used to write http, ftp and mailto hyperlinks.
# The link type ($options) is 0x03 is the same as absolute dir ref wit
+hout
# sheet. However it is differentiated by the $unknown2 data stream.
#
# See also write_url() above for a general description and return valu
+es.
#
sub _write_url_web {

    my $self    = shift;

    my $record      = 0x01B8;                       # Record identifie
+r
    my $length      = 0x00000;                      # Bytes to follow

    my $row1        = $_[0];                        # Start row
    my $col1        = $_[1];                        # Start column
    my $row2        = $_[2];                        # End row
    my $col2        = $_[3];                        # End column
    my $url         = $_[4];                        # URL string
    my $str         = $_[5];                        # Alternative labe
+l
    my $xf          = $_[6] || $self->{_url_format};# The cell format


    # Write the visible label using the write_string() method.
    $str            = $url unless defined $str;
    my $str_error   = $self->write_string($row1, $col1, $str, $xf);
    return $str_error if $str_error == -2;


    # Pack the undocumented parts of the hyperlink stream
    my $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B0200
+0000");
    my $unknown2    = pack("H*", "E0C9EA79F9BACE118C8200AA004BA90B");


    # Pack the option flags
    my $options     = pack("V", 0x03);

    # Convert URL to a null terminated wchar string
    $url            = join("\0", split('', $url));
    $url            = $url . "\0\0\0";


    # Pack the length of the URL
    my $url_len     = pack("V", length($url));


    # Calculate the data length
    $length         = 0x34 + length($url);


    # Pack the header data
    my $header      = pack("vv",   $record, $length);
    my $data        = pack("vvvv", $row1, $row2, $col1, $col2);


    # Write the packed data
    $self->_append( $header,
                    $data,
                    $unknown1,
                    $options,
                    $unknown2,
                    $url_len,
                    $url);

    return $str_error;
}


######################################################################
+#########
#
# _write_url_internal($row1, $col1, $row2, $col2, $url, $string, $form
+at)
#
# Used to write internal reference hyperlinks such as "Sheet1!A1".
#
# See also write_url() above for a general description and return valu
+es.
#
sub _write_url_internal {

    my $self    = shift;

    my $record      = 0x01B8;                       # Record identifie
+r
    my $length      = 0x00000;                      # Bytes to follow

    my $row1        = $_[0];                        # Start row
    my $col1        = $_[1];                        # Start column
    my $row2        = $_[2];                        # End row
    my $col2        = $_[3];                        # End column
    my $url         = $_[4];                        # URL string
    my $str         = $_[5];                        # Alternative labe
+l
    my $xf          = $_[6] || $self->{_url_format};# The cell format

    # Strip URL type
    $url            =~ s[^internal:][];


    # Write the visible label
    $str            = $url unless defined $str;
    my $str_error   = $self->write_string($row1, $col1, $str, $xf);
    return $str_error if $str_error == -2;


    # Pack the undocumented parts of the hyperlink stream
    my $unknown1    = pack("H*", "D0C9EA79F9BACE118C8200AA004BA90B0200
+0000");


    # Pack the option flags
    my $options     = pack("V", 0x08);


    # Convert the URL type and to a null terminated wchar string
    $url            = join("\0", split('', $url));
    $url            = $url . "\0\0\0";


    # Pack the length of the URL as chars (not wchars)
    my $url_len     = pack("V", int(length($url)/2));


    # Calculate the data length
    $length         = 0x24 + length($url);


    # Pack the header data
    my $header      = pack("vv",   $record, $length);
    my $data        = pack("vvvv", $row1, $row2, $col1, $col2);


    # Write the packed data
    $self->_append( $header,
                    $data,
                    $unknown1,
                    $options,
                    $url_len,
                    $url);

    return $str_error;
}


######################################################################
+#########
#
# _write_url_external($row1, $col1, $row2, $col2, $url, $string, $form
+at)
#
# Write links to external directory names such as 'c:\foo.xls',
# c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1
+'.
#
# Note: Excel writes some relative links with the $dir_long string. We
+ ignore
# these cases for the sake of simpler code.
#
# See also write_url() above for a general description and return valu
+es.
#
sub _write_url_external {

    my $self    = shift;

    # Network drives are different. We will handle them separately
    # MS/Novell network drives and shares start with \\
    return $self->_write_url_external_net(@_) if $_[4] =~ m[^external:
+\\\\];


    my $record      = 0x01B8;                       # Record identifie
+r
    my $length      = 0x00000;                      # Bytes to follow

    my $row1        = $_[0];                        # Start row
    my $col1        = $_[1];                        # Start column
    my $row2        = $_[2];                        # End row
    my $col2        = $_[3];                        # End column
    my $url         = $_[4];                        # URL string
    my $str         = $_[5];                        # Alternative labe
+l
    my $xf          = $_[6] || $self->{_url_format};# The cell format


    # Strip URL type and change Unix dir separator to Dos style (if ne
+eded)
    #
    $url            =~ s[^external:][];
    $url            =~ s[/][\\]g;


    # Write the visible label
    ($str = $url)   =~ s[\#][ - ] unless defined $str;
    my $str_error   = $self->write_string($row1, $col1, $str, $xf);
    return $str_error if $str_error == -2;


    # Determine if the link is relative or absolute:
    # Absolute if link starts with DOS drive specifier like C:
    # Otherwise default to 0x00 for relative link.
    #
    my $absolute    = 0x00;
       $absolute    = 0x02  if $url =~ m/^[A-Za-z]:/;


    # Determine if the link contains a sheet reference and change some
+ of the
    # parameters accordingly.
    # Split the dir name and sheet name (if it exists)
    #
    my ($dir_long , $sheet) = split /\#/, $url;
    my $link_type           = 0x01 | $absolute;
    my $sheet_len;

    if (defined $sheet) {
        $link_type |= 0x08;
        $sheet_len  = pack("V", length($sheet) + 0x01);
        $sheet      = join("\0", split('', $sheet));
        $sheet     .= "\0\0\0";
    }
    else {
        $sheet_len  = '';
        $sheet      = '';
    }


    # Pack the link type
    $link_type      = pack("V", $link_type);


    # Calculate the up-level dir count e.g. (..\..\..\ == 3)
    my $up_count    = 0;
    $up_count++       while $dir_long =~ s[^\.\.\\][];
    $up_count       = pack("v", $up_count);


    # Store the short dos dir name (null terminated)
    my $dir_short   = $dir_long . "\0";


    # Store the long dir name as a wchar string (non-null terminated)
    $dir_long       = join("\0", split('', $dir_long));
    $dir_long       = $dir_long . "\0";


    # Pack the lengths of the dir strings
    my $dir_short_len = pack("V", length $dir_short      );
    my $dir_long_len  = pack("V", length $dir_long       );
    my $stream_len    = pack("V", length($dir_long) + 0x06);


    # Pack the undocumented parts of the hyperlink stream
    my $unknown1 =pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000000'
+       );
    my $unknown2 =pack("H*",'0303000000000000C000000000000046'        
+       );
    my $unknown3 =pack("H*",'FFFFADDE000000000000000000000000000000000
+000000');
    my $unknown4 =pack("v",  0x03                                     
+       );


    # Pack the main data stream
    my $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                      $unknown1     .
                      $link_type    .
                      $unknown2     .
                      $up_count     .
                      $dir_short_len.
                      $dir_short    .
                      $unknown3     .
                      $stream_len   .
                      $dir_long_len .
                      $unknown4     .
                      $dir_long     .
                      $sheet_len    .
                      $sheet        ;


    # Pack the header data
    $length         = length $data;
    my $header      = pack("vv",   $record, $length);


    # Write the packed data
    $self->_append( $header, $data);

    return $str_error;
}




######################################################################
+#########
#
# _write_url_external_net($row1, $col1, $row2, $col2, $url, $string, $
+format)
#
# Write links to external MS/Novell network drives and shares such as
# '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'.
#
# See also write_url() above for a general description and return valu
+es.
#
sub _write_url_external_net {

    my $self    = shift;

    my $record      = 0x01B8;                       # Record identifie
+r
    my $length      = 0x00000;                      # Bytes to follow

    my $row1        = $_[0];                        # Start row
    my $col1        = $_[1];                        # Start column
    my $row2        = $_[2];                        # End row
    my $col2        = $_[3];                        # End column
    my $url         = $_[4];                        # URL string
    my $str         = $_[5];                        # Alternative labe
+l
    my $xf          = $_[6] || $self->{_url_format};# The cell format


    # Strip URL type and change Unix dir separator to Dos style (if ne
+eded)
    #
    $url            =~ s[^external:][];
    $url            =~ s[/][\\]g;


    # Write the visible label
    ($str = $url)   =~ s[\#][ - ] unless defined $str;
    my $str_error   = $self->write_string($row1, $col1, $str, $xf);
    return $str_error if $str_error == -2;


    # Determine if the link contains a sheet reference and change some
+ of the
    # parameters accordingly.
    # Split the dir name and sheet name (if it exists)
    #
    my ($dir_long , $sheet) = split /\#/, $url;
    my $link_type           = 0x0103; # Always absolute
    my $sheet_len;

    if (defined $sheet) {
        $link_type |= 0x08;
        $sheet_len  = pack("V", length($sheet) + 0x01);
        $sheet      = join("\0", split('', $sheet));
        $sheet     .= "\0\0\0";
    }
    else {
        $sheet_len   = '';
        $sheet       = '';
    }

    # Pack the link type
    $link_type      = pack("V", $link_type);


    # Make the string null terminated
    $dir_long       = $dir_long . "\0";


    # Pack the lengths of the dir string
    my $dir_long_len  = pack("V", length $dir_long);


    # Store the long dir name as a wchar string (non-null terminated)
    $dir_long       = join("\0", split('', $dir_long));
    $dir_long       = $dir_long . "\0";


    # Pack the undocumented part of the hyperlink stream
    my $unknown1    = pack("H*",'D0C9EA79F9BACE118C8200AA004BA90B02000
+000');


    # Pack the main data stream
    my $data        = pack("vvvv", $row1, $row2, $col1, $col2) .
                      $unknown1     .
                      $link_type    .
                      $dir_long_len .
                      $dir_long     .
                      $sheet_len    .
                      $sheet        ;


    # Pack the header data
    $length         = length $data;
    my $header      = pack("vv",   $record, $length);


    # Write the packed data
    $self->_append( $header, $data);

    return $str_error;
}

    [download]

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[reply]
[d/l]
Re: Win32 Perl and writing to Excel Workbooks
by vek (Prior) on Dec 28, 2002 at 00:11 UTC
    If switching to Spreadsheet::WriteExcel is an option then you should be able to use the write_url method to insert a hyperlink. From the doc: 
    $worksheet->write_url('A3', 'http://www.perl.com/', $format);

    [download]
    -- vek --
[reply]
[d/l]
[select]
      I looked at that... And I don't think it will suit my application as I am writing the results of an SQL query
      from a database and it was easier to write the results out with SQL as well rather than calcing the cells.

      I was wondering if there was a hidden character used to indicate a link and if so what it was. Or else how the link is formatted

      -talwyn

[reply]
Re: Win32 Perl and writing to Excel Workbooks
by Solo (Deacon) on Dec 28, 2002 at 13:33 UTC
    Hyperlinks can be expressed as formulas in Excel, so if you can enter a formula into a cell, this should work:

    =HYPERLINK("http://www.perlmonks.org/","Perl Monks")

    [download]
    --
    The ability to destroy planets is insignificant next to the power of the Deacon Effect.
[reply]
[d/l]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://222646]
Approved by tachyon
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others wandering the Monastery: (7)
As of 2024-04-23 09:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found